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.
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?
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";
}
target_lang
and source_lang
are values3
and values2
, respectively. If you want to change this, please modify above script.