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
}

Strange... I have the same code and it works correctly with the two proposed functions

=deepl(A1; "en"; "es")
=SI(A1 = "";"";deepl(A1;"es";"en"))

You saved me, I was left with the following formula:
=SI(C2 = "";"";deepl(ENCODEURL(H:H);"de";"fr"))
Thanks a lot man! :)

Thanks @mrtngrsbch it works for me now (after I refreshed my authentication code). I do still have one problem which is that my file has cells that contain multiple sentences. Unfortunately those cells give an error. Does anyone know how to fix that? (@pandoor?)

@rubenvanderzaag
Honestly I have only tried it with short words and my strategy is to translate them once and then move the strings to text cells, so I don't have to do the query all the time.
I also have a 'Comparative between DeepL & Google'
see it in: https://docs.google.com/spreadsheets/d/1SdkSzervJ0iLaO9DDrdH_16fJ4dMcD_-8KGImXuS1QQ/edit?usp=sharing

Sometimes DeepL gives a bad result...sometimes Google Translate

I work with free text strings, thesaurus and controlled vocabularies, but I ONLY use a machine translation for free texts, as machine translations of short words (without context) is almost impossible.

@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`;
  ...
}