GASでスプレッドシートCRUDを安全に実装する — ヘッダー定義 + 行番号管理

スプレッドシートCRUDの問題

スプレッドシートを操作するGASスクリプトは、列の番号が固定的に書かれているせいで、行の挿入・削除時に参照位置がズレて同期バグが発生する。また、大量行追記時はAPIレート制限に引っかかり、部分的な成功で終わる。

ヘッダー配列による列管理

列位置を定数で管理する代わりに、ヘッダー行から列名を読み取り、毎回の操作時に正しい列番号を取得する。

const HEADERS = ['id', 'name', 'email', 'status', 'createdAt'];

function getColumnIndex(sheetName, columnName) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  return headerRow.indexOf(columnName) + 1;
}

function appendObjectRow(sheetName, obj) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const row = HEADERS.map(header => obj[header] || '');
  sheet.appendRow(row);
}

function updateObjectRow(sheetName, rowNum, obj) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  HEADERS.forEach((header, idx) => {
    if (obj[header] !== undefined) {
      sheet.getRange(rowNum, idx + 1).setValue(obj[header]);
    }
  });
}

この方法でHEADERS配列の定義を変更すれば、スクリプト側の列参照は自動的に追従する。行番号（rowNum）は常に絶対位置なため、行挿入・削除後も参照ズレが起きない。

getDataRange vs getRange

スプレッドシートの全データを読み込む場合、getDataRange() はヘッダーから最後の行・列までを一度に取得する。部分更新では getRange() で座標を指定する。

function readAllData(sheetName) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  return data.slice(1).map((row, idx) => {
    const obj = {};
    HEADERS.forEach((header, col) => {
      obj[header] = row[col];
    });
    return { rowNum: idx + 2, ...obj };
  });
}

getDataRange() は内部で getLastRow() と getLastColumn() を実行するため、参照範囲が流動的なシートに向く。一方、固定範囲の更新は getRange(row, col, numRows, numCols) で指定したセルだけを更新する方が効率的。

flush()と更新のタイミング

GASはスプレッドシートへの書き込みをバッファリングし、flush() を呼ぶか処理終了時に一括反映する。複数の更新を行う場合、各操作後ではなく最後に1回だけ呼ぶ。

function batchUpdate(sheetName, updates) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  updates.forEach(({ rowNum, obj }) => {
    HEADERS.forEach((header, idx) => {
      if (obj[header] !== undefined) {
        sheet.getRange(rowNum, idx + 1).setValue(obj[header]);
      }
    });
  });
  SpreadsheetApp.flush();
}

flush() なしでも処理が終わると自動反映されるが、スクリプト内で直後に新しい読み取りが必要な場合は明示的に呼ぶ。

大量データのバッチ処理

1000行以上の一括追記はAPI呼び出し数が増えてタイムアウトリスク が高まる。複数回に分割し、各バッチ後に短い待機を入れる。

function batchAppendRows(sheetName, objects, batchSize = 500) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  for (let i = 0; i < objects.length; i += batchSize) {
    const batch = objects.slice(i, i + batchSize);
    const rows = batch.map(obj => HEADERS.map(header => obj[header] || ''));
    rows.forEach(row => sheet.appendRow(row));
    SpreadsheetApp.flush();
    Utilities.sleep(1000);
  }
}

バッチサイズは環境・スプレッドシートサイズで調整。500行単位が目安。

参照

• https://note.com/large_yarrow1156/n/nbbeaa3e6e1c8