スプレッドシートを簡易DBにするGASパターン集

結論から先に: 複数のシートをDB代わりに使うなら、実装をAIに任せる前に「IDは必ずA列」「関数名を統一する」という共通インターフェースを自分で決めておくべきです。先に型を固定してから実装させると、シートが増えても同じ関数セットを使い回せます。

課題

個人開発の規模だと、わざわざデータベースを立てるほどでもないが、単純な配列やJSONファイルだけで管理するのも心もとない、というデータ量のタスクがよくある。スプレッドシートは手軽だが、CRUD処理を毎回その場で書いていると、書き方が微妙に違うシートがどんどん増えていった。

最初のうちは「今回だけの一時的な処理だから」と、その場しのぎでシートを触るコードを書いていた。ところがシートの数が増えるにつれ、似たようなコードを毎回微妙に違う形で書き直す羽目になり、どのシートにどの書き方をしたかを覚えていられなくなった。

完成形

今は、シートをテーブルに見立てたCRUD関数の型を1つ決めて、新しいシートを作るたびにその型をコピーして使っている。列の1行目をヘッダーとして扱い、IDで行を検索・更新・削除できる最小限の関数セットだ。

AIとの作り方

最初にAIへ「スプレッドシートをDB的に使う関数を書いて」と頼んだところ、確かに動くコードは出てきたが、シートごとに微妙に実装が違うものが量産された。あるシートはID列が1列目、別のシートは2列目、関数名もバラバラ、という具合だ。

そこで、先に「共通インターフェース」を自分で決めてからAIに実装させる順番に変えた。具体的には「IDは必ずA列」「関数名はfindById/insertRow/updateById/deleteByIdに統一する」というルールを先に書き、それに沿って実装だけをAIに任せた。ルールを先に固定したことで、複数のシートに同じ関数セットをそのまま使い回せるようになった。

この「先にルールを決める、実装は後」という順番は、1週目で決めた役割分担の型そのものだった。改めて振り返ると、GASのコード設計にもこの型がそのまま応用できることに、作りながら気づいた。

サンプルコード

function findById(sheetName, id) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const rowIndex = data.findIndex(row => row[0] === id);
  return rowIndex === -1 ? null : data[rowIndex];
}

function insertRow(sheetName, rowValues) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  sheet.appendRow(rowValues);
}

function updateById(sheetName, id, newValues) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const rowIndex = data.findIndex(row => row[0] === id);
  if (rowIndex === -1) return false;
  sheet.getRange(rowIndex + 1, 1, 1, newValues.length).setValues([newValues]);
  return true;
}

function deleteById(sheetName, id) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const rowIndex = data.findIndex(row => row[0] === id);
  if (rowIndex === -1) return false;
  sheet.deleteRow(rowIndex + 1);
  return true;
}

複数シートで使い回すことを見越して、シート名を引数で渡す設計にしている。呼び出し側は次のように、対象シート名を指定するだけでよい。

const user = findById('users', 'u001');
insertRow('logs', ['l001', new Date(), 'ログイン']);

シート名と行構造さえ決めれば、この4関数をそのままコピーして使える。サンプルでは全行を毎回読み込んでいるため、行数が数千を超える規模になったらBigQuery移行(後日扱う予定)を検討した方がよい。

ハマりどころ

getDataRange().getValues()を関数ごとに毎回呼んでいたため、1回の処理で何度もシート全体を読み込んでしまい、実行が遅くなったことがあった。複数の関数をまとめて呼ぶ場合は、データを1回だけ読み込んで使い回すように直す必要がある。

特に、1つの処理の中でfindByIdを複数回呼ぶようなケースで顕著だった。呼ぶたびにシート全体を読み直していたので、行数が増えるにつれて体感できるほど遅くなった。データを1回読み込んでから、その場で必要な行を探す形に直したところ、体感速度がはっきり改善した。

もう1つ、IDの型が「数値」と「文字列」で混在してしまい、===比較が一致しないバグに何度か遭遇した。スプレッドシート側で数値として入力された値と、GAS側で文字列として生成したIDが一致しないのが原因だった。IDは必ず文字列に統一する、というルールを追加してから解消した。

function findById(sheetName, id) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const targetId = String(id); // 比較前に必ず文字列化する
  const rowIndex = data.findIndex(row => String(row[0]) === targetId);
  return rowIndex === -1 ? null : data[rowIndex];
}

比較の直前で両辺をString()で揃えるだけの単純な修正だが、この1行を入れ忘れたシートだけ検索がヒットしない、という不具合に何度か悩まされた。共通関数として1箇所にまとめておく価値を、身をもって実感した部分だ。

まとめ

シートをDB代わりに使うこと自体はよくある手法だが、「型を先に決めてからAIに実装させる」という順番を守らないと、シートの数だけ微妙に違う実装が増えていく。地味だが、複数シートを扱う段階になって初めて効いてくる工程だった。

前回は「GAS×Gemini APIで毎朝ニュース要約メールを届ける」について、次回は「GASトリガー設計と指数バックオフ再試行」について書く予定です。

note(作った経緯・所感はこちら): https://note.com/kar8

参考・関連リンク

• SpreadsheetApp 公式リファレンス: https://developers.google.com/apps-script/reference/spreadsheet