Apps Script公式リファレンス: Apps Script Reference |障害・課題追跡: IssueTracker |Google Workspace: Status Dashboard - Summary

2019年12月27日金曜日

シートのA列の英語をB列に日本語翻訳したい(トリガーでやってみる)


今回やりたいこと

スプレッドシートのA列に入力されている英語を日本語に翻訳してB列に入れたい

シートのA列の英語をB列に日本語翻訳したい(一気にやる)
シートのA列の英語をB列に日本語翻訳したい(B列最終行の次から)
では手動で実行するコードを書きました


件数が多い場合は実行時間制限(6分)にぶつかるので
今回はトリガーを使って試しました
  • 1分ごとのトリガーにmyFunction()を設定すると
  • 1分ごとに実行されて、最終行まで実行したらトリガーが自動で削除される



一日に翻訳できる量には制限があります
その上限を越えるとその日はそれ以上実行できなくなります



実行前


実行後



サンプルデータ
englishjapanese
candy
chocolate
gum
caramel
cake
cream puff
chips
pancake
biscuit
pudding
cookie




コード3.gs
var sourceLang = '';// 翻訳元の言語を空にしておくと自動判定してくれる
var targetLang = 'ja';// この言語に翻訳する 言語の種類は→https://cloud.google.com/translate/docs/languages
var sourceCol = 1;// 値を取得する列
var targetCol = 2;// 書き込む列
var numRow = 5;// 一度に取得する行数
var iterate = 2;// 繰り返す回数

function myFunction() {
  var ssUrl = 'https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0';
  var sheet = getTargetSheet(ssUrl);
  var lastRow = sheet.getLastRow();

  for(var i = 0; i < iterate; i++) {
    var resultArray = [];
    var lastRowB = getLastRowAt(sheet, targetCol);
    var startRow = lastRowB + 1;
    var numRows = lastRow - lastRowB;
    var range = sheet.getRange(startRow, sourceCol, numRow, 1);
    var values = range.getDisplayValues();
    values = arrayPushApply(values);
    values = values.filter(function(value) { return value !== ''; });
    if(values.length > 0) {
      resultArray.push(values);
      resultArray = arrayPushApply(resultArray);
      setTranslated(sheet, startRow, resultArray);
    } else {
      deleteTrigger('myFunction');
    }
  }
}

function setTranslated(sheet, startRow, resultArray) {
  var translateds = [];
  for(var i = 0; i < resultArray.length; i++) {
    var value = resultArray[i];
    var translated = LanguageApp.translate(value, sourceLang, targetLang);
    translateds.push([translated])
  }
  var range = sheet.getRange(startRow, targetCol, translateds.length, 1);
  range.setValues(translateds);
}

//二次元配列を一次元配列にして返す
function arrayPushApply(array){
  for(var i = 1; i < array.length; i++){
    Array.prototype.push.apply(array[0], array[i]);
  }
  return array[0];
}

function getTargetSheet(ssUrl) {
  var ss = SpreadsheetApp.openByUrl(ssUrl);
  var sheetId = ssUrl.split("gid=")[1];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() == sheetId) {
      break;
    }
  }
  var sheet = ss.getSheets()[i];
  return sheet;
}

function getLastRowAt(sheet, col) {
  var startRow = 1;
  var numCols = 1;
  var lastRow = sheet.getLastRow();
  var values = sheet.getRange(startRow, col, lastRow, numCols).getValues();
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i] != "") {
      break;
    }
  }
  var lastRowAt = i + 1;
  return lastRowAt;
}

function deleteTrigger(functionName) {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(allTriggers[i]);
      break;
    }
  }
}



関連記事

シートのA列の英語をB列に日本語翻訳したい(一気にやる)
シートのA列の英語をB列に日本語翻訳したい(B列最終行の次から)

Latest post

Google Apps Scriptの障害時はIssueTrackerを見てみる - Incidents for Apps Script are reported on Issue Tracker

IssueTracker > Apps Script issues https://issuetracker.google.com/savedsearches/566234 Google Apps Scriptの障害時は IssueTracker に課題が上がっていることが...