A column of product names or survey answers needs a Spanish copy next to it. translateCells(range, targetLang, opts) reads the range once, translates each non-blank text cell with LanguageApp, memoizes repeats, and writes the result to the columns on the right (or to opts.destination).
When to use
- Translating a block of labels or answers into another column
- Keeping the original text and the translation side by side
- Columns where the same phrase repeats often
How to use this snippet
/**
* Translate text cells in a range with LanguageApp; one read, one write.
* Blanks, numbers, and dates are passed through unchanged.
*
* @param {GoogleAppsScript.Spreadsheet.Range} range
* @param {string} targetLang e.g. 'es', 'fr', 'ja'
* @param {{sourceLang?: string, destination?: GoogleAppsScript.Spreadsheet.Range}} [opts]
* @return {Array<Array<*>>}
*/
function translateCells(range, targetLang, opts) {
opts = opts || {};
var src = opts.sourceLang || '';
var values = range.getValues();
var memo = {};
var out = values.map(function (row) {
return row.map(function (v) {
if (typeof v !== 'string' || !v.trim()) return v;
if (!(v in memo)) memo[v] = LanguageApp.translate(v, src, targetLang);
return memo[v];
});
});
var dest = opts.destination || range.offset(0, range.getNumColumns());
dest.offset(0, 0, out.length, out[0].length).setValues(out);
return out;
}
Example
function translateAnswersToSpanish() {
var sh = SpreadsheetApp.getActive().getSheetByName('Survey');
var range = sh.getRange('B2:B' + sh.getLastRow());
translateCells(range, 'es'); // writes into C2:C
}
Tips:
- Leave
sourceLangempty to auto-detect. - Repeated strings are translated once per run.
- The write overwrites the destination cells. Point it at an empty column.
LanguageApphas daily quotas. For thousands of rows, run in batches and write as you go.
Happy Coding!
