Google Sheets custom function to translate with DeepL API not working as expected

Question

I found @soMario correction of @Folleloide custom function to translate cell values in Google Sheets using DeepL API:

function deeplapi() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var text = sheet.getRange(3,2).getValue(); // define text before response
  var response = UrlFetchApp.fetch("https://api.deepl.com/v2/translate?auth_key=xxxx-xxxx-xxxx-xxxx-xxxx&text="+ text +"&target_lang=en&source_lang=es");
  var json = response.getContentText(); 
  var data = JSON.parse(json); 
  Logger.log(data);
}

This looks promising, and indeed the function works in Google Apps Script page: Custom function is working in Apps Script page

However, and this feels like a stupid question to ask really, but I must be missing something, because I can’t seem to call the function in an actual sheet—as for example the GoogleTranslate function works. As you can see here: Custom function named DEEPLAPI not working as expected

So for all of us programming dummies, if a generous soul could enlighten me, that would be great.

Answer 1

Modification points:

I thought that when you are actually using deeplapi() in your question, that function doesn't return the value. Because the last line of the function is Logger.log(data);. And also, in your following situation (This is from your question),

You are using the function deeplapi() as deeplapi(value1, value2, value3). But, your function deeplapi() doesn't use the arguments. I thought that these might be the reason of your issue.

If you want to use your function as deeplapi(value1, value2, value3), how about the following modification?

Modified script:

function deeplapi(value1, value2, value3) {
  var url = `https://api.deepl.com/v2/translate?auth_key=xxxx-xxxx-xxxx-xxxx-xxxx&text=${value1}&target_lang=${value3}&source_lang=${value2}`;
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var data = JSON.parse(json);
  return data.translations && data.translations.length > 0 ? data.translations[0].text : "No value";
}

• In this case, target_lang and source_lang are values3 and values2, respectively. If you want to change this, please modify above script.

Note:

• In this modified script, it supposes that you have already been able to use the API using your key and your current script. Please be careful this.

Reference:

• Custom Functions in Google Sheets