Apps Script から Gemini でスプレッドシート数千行を処理する — 6分の実行上限を越える分割と冪等性の設計

const MODEL = 'gemini-2.5-flash'; // 高速モデルを指定。新しい Flash 系に差し替え可能
const ENDPOINT = 'https://generativelanguage.googleapis.com/v1beta/models/'
  + MODEL + ':generateContent';
 
function classifyOne_(text) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const payload = {
    contents: [{ parts: [{ text:
      'あなたはアプリレビューの分類器です。次のレビューを分類してください。\n' + text }] }],
    generationConfig: {
      responseMimeType: 'application/json',
      responseSchema: {
        type: 'OBJECT',
        properties: {
          sentiment: { type: 'STRING', enum: ['positive', 'neutral', 'negative'] },
          topic: { type: 'STRING' },
          needs_reply: { type: 'BOOLEAN' },
        },
        required: ['sentiment', 'topic', 'needs_reply'],
      },
    },
  };
  const res = UrlFetchApp.fetch(ENDPOINT + '?key=' + apiKey, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true, // 429 等を例外にせず自分で判定する
  });
  const code = res.getResponseCode();
  if (code === 429 || code >= 500) {
    throw { retryable: true, code: code }; // 後述のバックオフで拾う
  }
  if (code !== 200) {
    throw { retryable: false, code: code, body: res.getContentText().slice(0, 200) };
  }
  const json = JSON.parse(res.getContentText());
  const out = json.candidates[0].content.parts[0].text;
  return JSON.parse(out); // responseSchema があるので JSON.parse が通る
}

function runBatch() {
  // 同じ関数の多重起動を防ぐ
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) return;
 
  const start = Date.now();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CFG.SHEET);
 
  try {
    while (Date.now() - start < CFG.TIME_BUDGET_MS) {
      const batch = nextPendingRows_(sheet, 20); // 20行ずつ取得
      if (batch.length === 0) {
        cleanupTriggers_();   // 未処理ゼロなら継続トリガーを掃除して終了
        Logger.log('全行の処理が完了しました');
        return;
      }
      for (const item of batch) {
        if (Date.now() - start >= CFG.TIME_BUDGET_MS) break; // 予算超過なら中断
        processRow_(sheet, item);
      }
    }
    // 予算を使い切った: 続きを次の実行へ引き継ぐ
    scheduleContinuation_();
  } finally {
    lock.releaseLock();
  }
}
 
// 30秒後に runBatch を一度だけ呼ぶトリガーを仕掛ける
function scheduleContinuation_() {
  cleanupTriggers_(); // 古い継続トリガーを必ず消してから新規作成（増殖防止）
  ScriptApp.newTrigger('runBatch').timeBased()
    .after(CFG.TRIGGER_DELAY_MS).create();
}
 
function cleanupTriggers_() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'runBatch') ScriptApp.deleteTrigger(t);
  });
}

function processRow_(sheet, item) {
  try {
    const result = withBackoff_(function () { return classifyOne_(item.text); });
    sheet.getRange(item.row, CFG.COL_OUTPUT).setValue(JSON.stringify(result));
    sheet.getRange(item.row, CFG.COL_STATUS).setValue('done');
  } catch (e) {
    // リトライ不能、またはリトライ上限超過: error を立てて隔離し、次へ進む
    sheet.getRange(item.row, CFG.COL_STATUS).setValue('error');
    sheet.getRange(item.row, CFG.COL_OUTPUT)
      .setValue('ERR ' + (e.code || '') + ' ' + (e.body || e.message || ''));
  }
}
 
// 指数バックオフ + ジッター。retryable な失敗だけ粘る
function withBackoff_(fn) {
  const MAX = 5;
  for (let attempt = 0; attempt < MAX; attempt++) {
    try {
      return fn();
    } catch (e) {
      if (!e.retryable || attempt === MAX - 1) throw e;
      const wait = Math.pow(2, attempt) * 1000 + Math.floor(Math.random() * 500);
      Utilities.sleep(wait); // 1s, 2s, 4s, 8s ... に揺らぎを足す
    }
  }
}

function resetErrors() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CFG.SHEET);
  const n = sheet.getLastRow() - CFG.HEADER_ROWS;
  if (n <= 0) return;
  const range = sheet.getRange(CFG.HEADER_ROWS + 1, CFG.COL_STATUS, n, 1);
  const vals = range.getValues();
  for (let i = 0; i < n; i++) {
    if (String(vals[i][0]).trim() === 'error') vals[i][0] = '';
  }
  range.setValues(vals);
}