1

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.

Archilecteur
  • 41
  • 6

1 Answers1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you @Tanaike for the answer! Using your modified script, now I get the following error message both in Apps Script and Google Sheets (I’ve replaced the API key obviously): `Exception: Invalid argument: https://api-free.deepl.com/v2/translate?auth_key=xxxxxx-xxxxx-xxxxx-xxxxxxxx&text=${value1}&target_lang=${value3}&source_lang=${value2}` – Archilecteur May 11 '21 at 12:03
  • @Archilecteur Thank you for replying. I apologize for the inconvenience. Unfortunately, from your replying, I cannot understand about the values of `value1`, `value2` and `value3`. I apologize for this. Can you provide the values you used? By the way, you have already confirmed that your script shown in your question worked. Is my understanding correct? – Tanaike May 11 '21 at 12:31
  • @Archilecteur By the way, in your question, I understood that you want to use the function of `deeplapi` as the custom function. In this case, when you directly run the function `deeplapi` with the script editor, an error occurs. Please be careful this. I'm worry that you might have done this. I think that if you have already confirmed that your script shown in your question worked, when you put a formula of `=deeplapi("sample text", "es", "en")` to a cell and your key can be used, the script works and the result value is returned. How about this? – Tanaike May 11 '21 at 12:41
  • The original script provided by Folleloide and soMario worked in the Apps Script panel, but not in Google Sheets. This time, I get the same message of error in both. – Archilecteur May 11 '21 at 14:04
  • My bad! Having specified the language values in the Apps Script panel, then it works fine in Google Sheets. Thank you! Merci! – Archilecteur May 11 '21 at 14:47