Use Deepl API and google sheets

user_15254227 0
Viewed 1430

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

google-apps-script google-sheets
2 Answers

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

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

It adds a DEEPL formula to Google Sheets like:

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