google-apps-script - How to print the result of a script on the current sheet

Question

I am working with a script that translates a text in a cell of a sheet through the Deepl API.

Thanks to a colleague help it worked correctly (Use Deepl API and google sheets).

In this case, I need to know how I can make the result of the translation to be included in a cell of the same sheet.

My data object is the following. The first script picks up the text from column 2, row 3. It would be perfect if the result is printed in column 2, row 4, but I don't know exactly how to do it.

I include an image of the corresponding data

I am using this script to translate the text of a cell in my sheet:

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

This way I get the result in the records section, but with this format: Información {translations=[{text=Hi, I'm Carlos, detected_source_language=ES}]}

First, I have tried to extract only the translation in this section and then add it in the same sheet. With this I get the exact translation, but I don't know how to include it in a specific range of my sheet.

Logger.log(data.translations[0].text);

In addition to this, I think that to get this result and paste it in the sheet I would have to use:

sheet.getRange () .setValues (sortedOutput);

Anyway, I'm not sure

标签： google-apps-scriptgoogle-sheets

Answer 1

Try something like that at the bottom of your script to paste the translation text into cell B4:

sheet.getRange('B4').setValue(data.translations[0].text)

Minimal reproducible example:

const data = {translations:[{text:"Hi, I'm Carlos", detected_source_language:"ES"}]};
console.log(data.translations[0].text)