Translate an entire Google Sheet using Google Translate

Question

I have a Google Sheet written in the Japanese Language. I need to convert it into English.

I know we can use googletranslate(<sheet!:cell>, <input_language>, <output_language>) to translate.

But without a script, this forces me to translate each and every row. I need a script that will convert my whole Google Sheet into English.

I prefer if it's converted into a separate sheet. Please let me know of the possible solutions.

TIA

Answer 1

• You want to translate Japanese to English for all cells of all sheets in a Spreadsheet.
• You want to copy the sheet with the translated values to the same Spreadsheet.
• You want to achieve this using Google Apps Script.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In this answer, Class LanguageApp is used.

Pattern 1:

In this pattern, each cell is translated with LanguageApp.translate().

Sample script:

Please copy and paste the following script to the container-bound script of the Spreadsheet. And run myFunction at the script editor. By this, all cells of all sheets in the Spreadsheet are translated from Japanese to English. And the translated values are put to the inserted sheet in the same Spreadsheet.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  sheets.forEach(sheet => {
    const s = sheet.copyTo(ss).setName(`translated_${sheet.getSheetName()}`);
    const range = s.getDataRange();
    const translatedValues = range.getDisplayValues().map(r => r.map(c => {
      Utilities.sleep(1000);
      return LanguageApp.translate(c, "ja", "en");
    }));
    range.setValues(translatedValues);
  });
}

• In this case, each cell is translated. So from the specification, Utilities.sleep(1000) (waiting 1 second) is required to be used. By this, when there are a lot of cells, the process time might be long.

Pattern 2:

In this pattern, all cells from a sheet are retrieved and converted to a string value, then the string value is translated. And the translated values are put to the sheet.

Sample script:

Please copy and paste the following script to the container-bound script of the Spreadsheet. And run myFunction at the script editor. By this, all cells of all sheets in the Spreadsheet are translated from Japanese to English. And the translated values are put to the inserted sheet in the same Spreadsheet.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  sheets.forEach(sheet => {
    const s = sheet.copyTo(ss).setName(`translated_${sheet.getSheetName()}`);
    const range = s.getDataRange();
    const delimiter = "#";
    const sourceValues = range.getDisplayValues().map(r => r.join(delimiter)).join("\n");
    const translatedValues = Utilities.parseCsv(LanguageApp.translate(sourceValues, "ja", "en"), delimiter.charCodeAt(0));
    range.setValues(translatedValues);
    Utilities.sleep(1000);
  });
}

• In this case, the values of all cells are translated by one request of LanguageApp.translate(). By this, the process time will be shorter than the pattern 1. But if the values are large, an error might occur. About this, I'm not sure. I apologize for this.
• In above script, # is used as the delimiter for converting to the string value. If # is used in the cells, please change the character.

Note:

• This modified script is run with enabling V8.

Reference:

• Class LanguageApp

Answer 2

I ran into the same problem, but I needed to have multiple language support and I didn't want any significant delay, nor did I want to worry whether the source cells were already in my desired destination language, so I came up with this solution.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Translations')
    .addItem('Japanese > English', 'jpToEn')
    .addToUi();
}

function jpToEn() {
  translate('jp','en')
}

function translate(inputLang, outputLang) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dest = sheet.copyTo(ss);
  dest.setName(`${inputLang}_${outputLang}_${sheet.getName()}`)
  var range = dest.getDataRange();
  var formula = "=iferror(googletranslate('" + sheet.getName() + "'!" + "R[0]C[0],\"" + inputLang +"\",\"" + outputLang + "\"),\"\")";
  range.setFormulaR1C1(formula);
}

As you can see, this adds a menu to to your Google Sheets document. You can make yours translate however you desire, as many different languages as you need. Just extend the menu or replace the items already there.

Answer 3

What I did is quite simple without using any scripts or add-ons.

I created a new sheet an on A1 cell I put a formula:

=GOOGLETRANSLATE(Sheet1!A1, "ru", "en")

This gave me a translation of my A1 cell from Sheet1.

Next I just copied this formula to all other cells in my new Sheet2. Voila, you have the translation.

However, Google Translate is quite bad with translating Russian into English in Google Sheets, so I had to change it by cutting all cells in Sheet2 and pasting "Only Values" back again. This gives you translated cells that can be modified.