How to Preserve Sheet Formats and Translate? ( Google Apps Script, google sheet )-CodePudding

How can i Preserve Sheet Formats and Translate? I don't want to use the translation latency(1000) or translate cell by cell. it was worked well but I have a large amount of text I want to translate.

I used the following code:

var range1 = sheet1.getRange("A:A");
text1 = range1.getValues();
translatedtext = LanguageApp.translate(text1, 'ko', 'en');

This is the starting language. from log. after getValues from sheet. If I 'setValue' this into the sheet as it is, it will be written in each cell as it is.

[안녕하세요], [사과], [], [바나나]

This is how it is translated. Line breaks are gone and replaced with commas, when pasting them into a sheet, it puts all the text in one cell.

hello, apple,, banana

CodePudding user response：

I believe your goal is as follows.

You want to translate the cell values of the sheet by reducing the process cost.

In this case, how about the following modification?

Modified script 1:

In this pattern, the cell values are translated after the retrieved values were converted to the string. By this flow, LanguageApp.translate is used by one call.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1"); // Please set your sheet name.
var range = sheet.getRange("A1:A"   sheet.getLastRow())
var values = JSON.parse(LanguageApp.translate(JSON.stringify(range.getValues()), 'ko', 'en'));
range.offset(0, 1).setValues(values);

When this script is run, the values are retrieved from column "A", and the values are translated, and then, the translated values are put into column "B".

Modified script 2:

In this pattern, the cell values are retrieved as the CSV data, and translated it. And then, the translated values are parsed and put into the cells. By this flow, LanguageApp.translate is used by one call.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1"); // Please set your sheet name.
var url = `https://docs.google.com/spreadsheets/export?exportFormat=csv&id=${ss.getId()}&gid=${sheet.getSheetId()}`;
var res = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } });
var csv = res.getContentText();
var translated = LanguageApp.translate(csv, 'ko', 'en');
var ar = Utilities.parseCsv(translated).map(([a]) => [a]);
sheet.getRange(1, 2, ar.length).setValues(ar);
// DriveApp.getFiles(); // This comment line is used for automatically detecting the scope of Drive API.

When this script is run, the values are retrieved from column "A", and the values are translated, and then, the translated values are put into column "B".
If an error related to the scopes occurs, please include the scope of Drive API.

Reference:

map()