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

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()