How to easily translate between Japanese and English in Google Sheets.

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

function jpToEn() {
  translate('ja', 'en');
}

function EnToJp() {
  translate('en', 'ja');
}

function batchTranslate(textArray, inputLang, outputLang) {
  var concatenatedText = textArray.join('|');
  var translatedText = LanguageApp.translate(concatenatedText, inputLang, outputLang);
  return translatedText.split('|');
}

function translate(inputLang, outputLang) {
  var errorCells = [];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var translatedSheetName = LanguageApp.translate(sheet.getName(), inputLang, outputLang);
  var dest = sheet.copyTo(ss);
  dest.setName(translatedSheetName);
  
  var originalRange = sheet.getDataRange();
  var originalValues = originalRange.getValues();
  var originalFormulas = originalRange.getFormulas();
  var dataValidations = originalRange.getDataValidations();

  var translatedValues = JSON.parse(JSON.stringify(originalValues));

  for (var i = 0; i < originalValues.length; i++) {
    for (var j = 0; j < originalValues[i].length; j++) {
      if (dataValidations && dataValidations[i][j] !== null) {
        continue;  // Skip cells with data validation
      }
      if (originalFormulas[i][j] === "" && isNaN(originalValues[i][j]) && typeof originalValues[i][j] === "string") {
        try {
          var translatedText = LanguageApp.translate(originalValues[i][j], inputLang, outputLang);
          dest.getRange(i + 1, j + 1).setValue(translatedText);
        } catch (e) {
          errorCells.push(`${String.fromCharCode(65 + j)}${i + 1}`);
        }
      }
    }
  }

  if (errorCells.length > 0) {
    SpreadsheetApp.getUi().alert(`Errors occurred in the following cells: ${errorCells.join(", ")}. Please check these cells manually.`);
  } else {
    SpreadsheetApp.getUi().alert('Translation completed. Please check the new sheet.');
  }
}

// MIT License
// Copyright (c) 2023 [ANJI]
//
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all
// copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
// SOFTWARE.