Deepl script for google sheets

deepl_for_google_sheets.txt

function deepl(value1, value2, value3) {
  var url = `https://api-free.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";
  // replace auth_key with a "Deepl for developer" API key
  // source : this script is a modified version of 
  // http://5.9.10.113/67485395/google-sheets-custom-function-to-translate-with-deepl-api-not-working-as-expecte
}

@rubenvanderzaag When you have "ERROR" it may be because you used more than 15,000 queries (I think that's the limit or it's around it). I translate online stores and I exceed it several times for one file. When it's like that I open a new Google account and take the file back.

Here's my working version that integrates some of the changes from everyone.

I also named variables and added comments to make the whole thing a bit more beginner friendly.

function deepl(text, sourceLang, targetLang) {

  const url = `https://api-free.deepl.com/v2/translate`;

  // Your API key goes here (find it at https://www.deepl.com/account/summary)
  const key = `0633f433-39b9-a390-90c9-353e782f9e9a:fx`; 

  // Encode the text as a URI component to deal with special characters 
  var value1 = encodeURIComponent(text);

  var value2 = sourceLang;
  var value3 = targetLang;

  // Build the query 
  const formData = `auth_key=${key}&text=${value1}&target_lang=${value3}&detected_source_language=${value2}`;
  
  const options = {
    'method' : 'post',
    'contentType': 'application/x-www-form-urlencoded',
    'payload': formData
  };
  
  var response = UrlFetchApp.fetch(url, options);
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  return data.translations && data.translations.length > 0 ? data.translations[0].text : "No value";

}

Hi folks,

I just received the newsletter from DeepL, which includes this other 'official' example.
https://github.com/DeepLcom/google-sheets-example

Hello, how can I escape some words which I do not wish DeepL to consider for translation? For example: I buy football shoes ... In this sentence, I do not wish to translate the word "football"... I want all languages contain the word "football" as it is (and for example, do not translate to swedish translation which is fotboll)... How can I do this in the google sheets api call formula?

@SableRaf
Hi, thanks for the working code.
How about adding those codes for protecting a quota of DeepL API?

function deepl(text, sourceLang, targetLang) {
+  if (!text) return "No value";
+  if (!sourceLang) return "No source lang";
+  if (!targetLang) return "No target lang";

  const url = `https://api-free.deepl.com/v2/translate`;
  ...
}