GASでスプレッドシートの文言を翻訳するときに translateのレート制限のエラーが出た場合は遅延を入れて対応する

背景

Next.jsの個人プロジェクトを多言語対応することにしました
多言語対応にはreact-intlを選択することにし、言語ごとに以下のようなjsonファイルを作る必要がありました

{
  "title": "タイトルです"
}

すべての文言を翻訳するのは難易度が高かったので、そこまで重要でない文言は自動翻訳で対応することにしました
同時に文言の管理も行いたかったので、スプレッドシートとGASを使うことにしました

スプレッドシートのフォーマット

フォーマットの構成は以下のようになっています

• A列に文言のID
• B列に日本語
• C列に英語
• D列に中国語(簡体字)
• E列に中国語(繁体字)
• F列に韓国語
• 1行目に項目名
• 2行から翻訳したい文言
  

作成した日本語を各言語に翻訳するGASのコード

// 翻訳先の言語コードのリスト
const langList = ["en", "zh-CN", "zh-TW", "ko"]

// 自動翻訳を実行する関数
function doTranslate() {
  // アクティブなシートを取得
  const sheet = SpreadsheetApp.getActiveSheet();

  // データが入っている範囲を特定
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const targetRange = `R2C2:R${lastRow}C${lastColumn}`;

  // シートのデータを二次元配列で取得する
  const sheetData = sheet.getRange(targetRange).getValues();

  for (let i = 0; i < sheetData.length; i++){
    // 日本語セルが空だったら翻訳処理をせず次の行に進む
    const jaText = sheetData[i][0];
    if(jaText == ""){
      continue;
    }

    // 日本語を各言語に翻訳する
    for (let j = 0; j < langList.length; j++){
      const targetColumn = j + 1;
      // 翻訳先のセルがすでに記入されていたら次のセルへ進む
      const target = sheetData[i][targetColumn];
      if(target !== ""){
        continue;
      }

      // 翻訳して配列へ格納
      const translatedText = LanguageApp.translate(jaText, 'ja', langList[j]);
      sheetData[i][targetColumn] = translatedText;
    }
  }

  // 配列をシートへ書き込む
  sheet.getRange(targetRange).setValues(sheetData)
}

コードの解説

• langListにはシートの項目順に言語コードを設定します
• データが入っている範囲は、翻訳したい文言が2列目の2行目から入っているのでR2C2:R${lastRow}C${lastColumn}になります
• 文言ごとに各言語に翻訳し、翻訳した文言は2次元配列に格納していきます
• 最後に、翻訳した文言の2次元配列をシートに反映させます

作成したコードの問題点

translateのレート制限を超えてしまい、途中終了してしまいました

遅延による対策

具体的な制限回数はわかりませんでしたが、文言ごとに1000msごとのディレイを設けて回避することにしました

function doTranslate() {
  // アクティブなシートを取得
  const sheet = SpreadsheetApp.getActiveSheet();

  // データが入っている範囲を特定
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  const targetRange = `R2C2:R${lastRow}C${lastColumn}`;

  // シートのデータを二次元配列で取得する
  const sheetData = sheet.getRange(targetRange).getValues();

  for (let i = 0; i < sheetData.length; i++){
    // 日本語セルが空だったら翻訳処理をせず次の行に進む
    const jaText = sheetData[i][0];
    if(jaText == ""){
      continue;
    }

    // 日本語を各言語に翻訳する
    for (let j = 0; j < langList.length; j++){
      const targetColumn = j + 1;
      // 翻訳先のセルがすでに記入されていたら次のセルへ進む
      const target = sheetData[i][targetColumn];
      if(target !== ""){
        continue;
      }

      // 翻訳して配列へ反映
      const translatedText = LanguageApp.translate(jaText, 'ja', langList[j]);
      sheetData[i][targetColumn] = translatedText;
    }

    // translateのレート制限に引っかからないよう間を置く
    Utilities.sleep(1000) // <- この行を追加
  }

  // 配列をシートへ書き込む
  sheet.getRange(targetRange).setValues(sheetData)
}