Use Deepl API and google sheets

Question

I'm working on a script to translate a text included in a google sheets cell with the deepl api. My code works and generates a result, but it does not recognise the text included in the cell.

This is the code I am using:

function deeplapi() {
  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 ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var text = sheet.getRange(3,2).getValue();

var json = response.getContentText(); 

var data = JSON.parse(json); 
Logger.log(data);
}

Although it generates a response, the text entered appears as "undefined".I don't know why this is happening.

Here is the image of my sheet and the result of the script.

The result is this. As you can see, the text appears as undefined:

Información {translations=[{text=undefined, detected_source_language=ES}]}

sheet

score 1 · Accepted Answer · answered Feb 21 '21 at 17:00

1

You need to define text before response since text is used in the argument inside the fetch function:

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);
}

answered Feb 21 '21 at 17:00

Marios

26,333
8
32
52

Sorry to ask you again! Do you know how I can print the translation result on a sheet? – Folleloide Feb 21 '21 at 17:58
What is your data object? Could you please ask a new question regarding that so you can provide clear view of the the `data` input? @Folleloide – Marios Feb 21 '21 at 18:14
I have added a question with the above. Thanks for your help! https://stackoverflow.com/questions/66305817/how-to-print-the-result-of-a-script-on-the-current-sheet – Folleloide Feb 21 '21 at 18:45

score 1 · Answer 2 · answered May 17 '22 at 21:41

1

I've found Translate with DeepL on the addon store.

It adds a DEEPL formula to Google Sheets like:

= DEEPL("Hey there", "es", "en")

answered May 17 '22 at 21:41

Mark Condo

11
1

score 0 · Answer 3 · answered Jun 26 '23 at 12:27

Other answers did not work for me.

function DEEPL(text, sourceLang, targetLang) {
    if (text == "") {
      return "";
    }
  
    var requestOptions = { 
      "method" : "post",
      "headers" : {
         "Authorization" : "DeepL-Auth-Key AUTH_KEY_HERE",
       },
       "payload": {
          "text": text,
          "source_lang": sourceLang,
          "target_lang": targetLang
       }
    }
  
    var response = UrlFetchApp.fetch("https://api-free.deepl.com/v2/translate", requestOptions);
    
    var json = response.getContentText();
    var data = JSON.parse(json);
    return data["translations"][0]["text"];
  }

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 02 '23 at 17:38

Use Deepl API and google sheets

3 Answers3

Linked