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
}

https://api-free.deepl.com is no longer works, need to be changed to just https://api.deepl.com

I encounter this error, do you have any idea why?

Exception: Request failed for https://api.deepl.com returned code 400. Truncated server response: {"message":"Value for 'source_lang' not supported."} (use muteHttpExceptions option to examine full response)
deepl @ Code.gs:3

Here is the code that I use (xxx = my key)

function deepl(value1, value2, value3) {
  var url = `https://api.deepl.com/v2/translate?auth_key=xxxxxxxxxxxxxxxxxxxxxxxx&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
}

Probably source lang is not supported by deepl. What lang did you use?

Hi @jcmauss2 i still received the same error
Exception: Request failed for https://api-free.deepl.com returned code 400. Truncated server response: {"message":"Value for 'source_lang' not supported."} (use muteHttpExceptions option to examine full response)
How did you solve that?

Did you check what is sent as source_lang and do deepl actually supports that?

Hi @r2d2ooooo and @thekip I managed to make it work using the code below. You receive the error while clicking "RUN" in preview, but it works in the spreadsheet with the formula =deepl(C3,"EN","ES") for example

XXX = API key

`function deepl(value1, value2, value3) {
  var url = `https://api.deepl.com/v2/translate?auth_key=XXXXXXXXXXXXd&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
}`

Hey, it doesn't work for me. I get this error message:
"Exception: Request failed for https://api.deepl.com returned code 403. Truncated server response: {"message":"Wrong endpoint. Use https://api.deepl.com"} (use muteHttpExceptions option to examine full response) (line 3).
"

Hi,
I have read it 10 times but I get an error in spreadsheets ;-(
Sorry but... could you give me a hint on how to track this error ?

et voici my solution:
=IF(G5 = "";"";deepl(G5;"es";"en"))

et voici my solution: =IF(G5 = "";"";deepl(G5;"es";"en"))

Thank you for this!
I add mine, because I had problems with special characters like "|":

=IF(C2 = "";"";deepl(ENCODEURL(C2);"de";"fr"))

... And my POST version of the deepl App Scripts function (the GET one is causing errors if your text is too long):

function deepl(value1, value2, value3) {
  var url = `https://api-free.deepl.com/v2/translate`;
  const formData = `auth_key=XXXXXXXXX&text=${value1}&target_lang=${value3}&source_lang=${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";
  // replace auth_key with a "Deepl for developer" API key
}

@pandoor A big thank you! I was finally able to translate my cells.
However, I still have one last problem. I need to copy all the content I just translated and paste it on another column. Once I paste, I don't have the translated content but the formula, I can't keep the translated content.
Do you have a solution? :)

I would like to point out that I am a beginner on google sheet. lol

@ALEX7835 You just have to paste without formatting: CTRL + SHIFT + V, instead of the classical CTRL + V. This way you don't copy the formulas, only the result of them.

@pandoor It works! Thanks a lot :)

@pandoor thank you for the script!

Most likely the api changed a bit, I finally here this code works:

function deepl(value1, value2, value3) {
  var url = `https://api-free.deepl.com/v2/translate`;
  const formData = `auth_key=XXXXXXXXXXXXXXXXXXXXXXXX&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";
  // replace auth_key with a "Deepl for developer" API key
}

@enogylop @pandoor For some reason I can't seem to get this to work. First of all, I'm not a developer so I'm quite new to this. I've copied and pasted the original script in Apps script and replaced the xxx with my own authentication key. When I click "run" in Apps script, I get the following error in the log: We're sorry, there was an unexpected error while creating the Cloud Platform project. Error code RESOURCE_EXHAUSTED.

When I try the formula in google sheets the error says: Internal error executing the custom function.

I've also tried all other scripts mentioned in this thread, but none of them seems to work for me. Does anyone have an idea what (I) might be (doing) wrong?

The code doesn't work anymore 😭 Could you please help us to find the right code? @pandoor

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