GAS自動化

GASスプレッドシート在庫管理サンプル｜現場で動いたコード公開

GASでスプレッドシートの在庫管理を作りたい非エンジニア向けに、現場で実際に動いた在庫履歴転記のサンプルコードを公開します。3,700種・1日150件の現場で、夕方の集計残業2〜2.5時間が消え、現場作業中に30秒/件で処理が回るようになった実話と、つまずいたタイムアウト問題の直し方も。

#GAS#スプレッドシート#在庫管理#サンプルコード#業務改善#非エンジニア
GASスプレッドシート在庫管理サンプル｜現場で動いたコード公開

こんなことで困っていませんか

  • スプレッドシートで在庫管理をしているが、毎日の入力と集計で残業が止まらない
  • GASという言葉は聞いたことがあるけれど、何から書けば動くのかわからない
  • ネットで見つけたサンプルコードを貼ってもエラーが出て、結局Excel運用に戻ってしまった
  • 「在庫数が変わったら履歴に残す」みたいな当たり前の処理を、自分で組めるようになりたい

過去の自分も、まったく同じところで止まっていました。

資材3,700種類、1拠点、1日の入出庫が約150件。現場が終わって事務所に戻ると、夕方からスプレッドシートに数字を転記する作業が始まります。日々の入力で1〜1.5時間、在庫確認で30〜40分、合わせて毎日2〜2.5時間の残業。家に帰る頃には子どもが寝ているという日が普通でした。

この記事では、その状態から抜け出すために自分が書いた GASスプレッドシート在庫管理のサンプルコード を、現場で実際に動いている本体から要点だけ抜き出して公開します。難しい設計書ではなく「現場で実際に動いて、夕方の残業をほぼ消した運用」そのものの抜粋です。

関連：QRコードと組み合わせた運用は QRコード×GASで現場の入力作業を自動化した話 に書きました。あわせて読むと、なぜ履歴転記が要になるのかが見えてきます。

こうやって解決しました（結論）

やったのは、ものすごくシンプルな1点だけです。

「在庫の数字そのものは書き換えず、ログシートに1行追記するだけにする」

これだけで、夕方の集計作業が消えます。なぜなら、現場で入出庫した瞬間に「いつ・どの資材が・いくつ・どこで動いたか」が履歴に残り、在庫数はスプレッドシート関数（SUMIFS）が自動で再計算してくれるからです。事務所に戻ってから転記し直す必要がなくなります。

導入前と導入後で、こう変わりました。

項目GAS導入前GAS導入後
日々の入力作業事務所で毎日1〜1.5時間（夕方残業）現場メニューから入力するだけ（事務所作業ゼロ化）
在庫確認事務所で30〜40分在庫集計シートを見るだけ
1件あたりの処理事務所での手作業で十数秒×150件分約30秒/件で現場作業中に分散処理（事務所工数ゼロ）
1日の集計残業2〜2.5時間（事務所での残業）ほぼゼロ

「在庫値を直接いじる」のをやめて、「動いた事実をログに残すだけ」に設計を切り替えただけです。

実コードから抜粋：3つのブロックで成り立つ最小構成

ここから本題です。現場で動いている本体（10ファイル・約4,000行）から、他の現場でも転用できる設計の型だけを3ブロック抜き出しました。コピペで完動するサンプルではなく、設計思想を真似てもらうための抜粋です。

ポイントは3つだけです。

  1. 構造を宣言してから書く（CONFIGオブジェクトに集約）
  2. 集計はGASではなく数式に任せる（SUMIFSで自動再計算）
  3. 在庫操作はログ追記1本に集約（在庫値そのものは書き換えない）

順番に見ていきます。

ブロック1：シート設計を最初に宣言する（CONFIG）

なぜこの設計か

GASでスプレッドシート操作を書くとき、いきなり getRange(2, 5) とマジックナンバーを散らすと、後で列を1本足しただけで全コードが壊れます。シート名・列番号・操作の種類を最初に1ファイルに集約しておくと、列が増えてもこのファイル1箇所の修正で済みます。

// 00_Config.gs ─ プロジェクト全体の構造宣言
const CONFIG = {
  SS_ID: 'YOUR_SS_ID_HERE',

  // シート名（日本語シート名でも、必ず定数経由で参照する）
  SHEET: {
    MASTER:    'M_Master',     // 資材マスタ
    LOG:       'T_Log',        // 入出庫ログ（追記専用）
    INVENTORY: 'Inventory',    // 在庫集計（数式で自動計算）
    AUDIT:     'T_Audit',      // 変更履歴
  },

  // T_Log の列番号（1始まり）
  LOG_COL: {
    TIMESTAMP: 1, QR_ID: 2, NAME: 3, TYPE: 4,
    LOCATION: 5, QTY: 6, OPERATOR: 7, NOTE: 8,
    CANCELLED: 9,   // 取消フラグ（TRUE なら集計から除外）
  },

  // 在庫操作の種類（プルダウンとSUMIFS条件で使用）
  IO_TYPES: [
    'SHELF_IN', 'SHELF_OUT', 'WH_IN', 'WH_OUT',
    'SHELF_TO_WH', 'WH_TO_SHELF',
    'ADJUST_PLUS', 'ADJUST_MINUS',
  ],
};

// シート取得のラッパー（毎回 getSheetByName を書かない）
function getSheet(key) {
  const name = CONFIG.SHEET[key];
  if (!name) throw new Error('Unknown sheet key: ' + key);
  const ss = SpreadsheetApp.openById(CONFIG.SS_ID);
  const sh = ss.getSheetByName(name);
  if (!sh) throw new Error('Sheet not found: ' + name);
  return sh;
}

読者が真似するときの注意点

スプレッドシートIDをコードに直書きすると、リポジトリ公開時に事故ります。本番では PropertiesService.getScriptProperties().getProperty('SS_ID') で外部化してください。実コードでは資材マスタの列が17本あるので、CONFIGがそのまま設計書代わりになります。

ブロック2：在庫集計はGASではなくSUMIFS数式に任せる

なぜこの設計か

在庫集計をGASのループで書くと、3,700種類×8操作タイプ＝3万回の計算で、GASの実行時間制限（6分）にすぐ引っかかります。集計はスプレッドシート関数（SUMIFS）に任せ、GASは数式を一度だけ生成する。この設計にすると、ログを1行追記するだけで在庫が即時再計算されます。

// 01_Setup.gs ─ 在庫集計シートを「数式入りで」再構築
function rebuildInventorySheet_() {
  const masterSh = getSheet('MASTER');
  const invSh    = getSheet('INVENTORY');
  const logName  = CONFIG.SHEET.LOG;

  // マスタから有効な資材だけ抽出
  const master = masterSh.getDataRange().getValues();
  const rows = master.slice(1).filter(r => r[16] === true);  // ACTIVE列

  rows.forEach((m, i) => {
    const r = i + 2;
    const initSh = m[7] || 0;  // 初期棚在庫

    // 棚在庫 = 初期 + 入庫系 - 出庫系。取消フラグ TRUE は除外
    const shelfFormula =
      `=${initSh}` +
      `+SUMIFS('${logName}'!F:F,'${logName}'!B:B,A${r},'${logName}'!D:D,"SHELF_IN",'${logName}'!I:I,"<>TRUE")` +
      `-SUMIFS('${logName}'!F:F,'${logName}'!B:B,A${r},'${logName}'!D:D,"SHELF_OUT",'${logName}'!I:I,"<>TRUE")`;
      // ※ 実コードでは ADJUST_PLUS / ADJUST_MINUS / 移動系も同様に加減算

    invSh.getRange(r, 5).setFormula(shelfFormula);
  });
}

読者が真似するときの注意点

SUMIFSの範囲指定で F:F（全列）を使うのはあえてです。行数が増えても式の修正が要りません。ただしログシートが10万行を超える規模になったら、F2:F100000 と上限を切るか、日付でパーティションを分けてください（体感速度が2〜3倍変わります）。実コードでは棚在庫・倉庫在庫・合計・過不足・要発注ステータスの5本の数式を1行で組み立てています。

ブロック3：履歴ログを1行追加するだけのcore処理

なぜこの設計か

入庫・出庫・移動・棚卸の全操作を T_Log への追記1本に集約します。在庫集計はブロック2のSUMIFSが勝手にやってくれるので、操作のコード本体は驚くほど短くなります。在庫値を直接書き換えないので、操作ミスがあっても取消フラグ1本で巻き戻せるのが最大の利点です。

// 02_Sidebar.gs ─ サイドバーから呼ばれる在庫操作の本体
function submitEntry(params) {
  const { qrId, ioType, qty, operator, locationId, hinmei, note } = params;

  // バリデーション（必須項目・数量・操作種別）
  if (!qrId)   throw new Error('QR_ID が指定されていません');
  if (!ioType) throw new Error('操作種別が未選択です');
  const q = Number(qty);
  if (!Number.isFinite(q) || q < 1) throw new Error('数量は1以上で');
  if (CONFIG.IO_TYPES.indexOf(ioType) === -1) throw new Error('不正な操作種別');

  // 出庫系の在庫不足チェック（現在庫を SUMIFS の結果から取得）
  const outTypes = ['SHELF_OUT', 'WH_OUT', 'SHELF_TO_WH', 'WH_TO_SHELF'];
  if (outTypes.indexOf(ioType) !== -1) {
    const stockMap = getStockMap();
    const cur = (stockMap[qrId] || { shelf: 0, wh: 0 });
    const currentStock = ioType.startsWith('SHELF') ? cur.shelf : cur.wh;
    if (q > currentStock) throw new Error(`在庫不足（現在庫:${currentStock}）`);
  }

  // T_Log に1行追記。これだけで Inventory シートの SUMIFS が再計算される
  const logSh = getSheet('LOG');
  logSh.appendRow([
    new Date(), qrId, hinmei || '', ioType,
    locationId || '', q,
    `${operator.code || '00000'} ${operator.name || ''}`.trim(),
    note || '', '',  // I列：CANCELLED は空 = 有効
  ]);

  return { success: true, qrId, hinmei, ioType, qty: q };
}

読者が真似するときの注意点

appendRow は同時実行に弱いので、複数人で使うなら LockService.getScriptLock().waitLock(10000) で排他制御を入れてください。1人運用なら不要、3人以上で同時に叩くなら必須です。実コードではこの後 logToAudit_() で変更履歴シートにも1行残しており、誰がいつ何をしたかを追跡できるようにしています。

全コードはこの記事には載せきれません。WMS全体は10ファイル・約4,000行で運用しています。ここで抜粋した3ブロックは、その中でも「他の現場でも転用できる設計の型」だけを抜き出したものです。

補足：現場全体の運用設計（資材コードの振り方・QRラベルの貼り方・入力画面のメニュー化）は 3,700種の資材を1人で在庫管理した話 にまとめてあります。コードよりも先に、運用の全体像を知りたい方はそちらから読むのが近道です。

[おおばこ追記欄1/3] このコードを現場でそのまま使うとき、追加でやっている運用ルールがあれば書いてください 記入例:

  • ログシートは月初に別ファイルへアーカイブし、本シートは1万行以下に保つ
  • 操作種別の選択ミス防止のため、サイドバーUIのプルダウンには日本語ラベルも併記
  • 在庫不足エラーが出たら、担当者ごとにSlackへ通知を飛ばしている

つまずいた一番のポイント：「全データから読む」と毎回タイムアウトする

ここからは失敗談です。最初に書いたコードは、ほぼ毎回タイムアウトで止まりました。

理由は単純で、編集されるたびに在庫シート全体（3,700行）を getValues() で読み込んで、ループで該当資材を探す書き方をしていたからです。1件編集するだけで毎回3,700行スキャンするので、入出庫が連続する時間帯はGASの実行時間制限（6分）にどんどん近づいていきました。

直したのが、まさに上の「ブロック2」の発想でした。

  • 編集された行と列だけを getRange(row, col) で読む：3,700行スキャンを「1セル取得」に変える
  • 集計はSUMIFS数式に任せる：GASは数式を一度生成するだけで、再計算は表計算エンジン側に肩代わりしてもらう

当初は「在庫が一定数を下回ったら毎日メールで通知」という定期トリガーも入れていました。ただ、運用していくうちに 通知より先に現場で気づく ことがほとんどで、今は止めています。最初から完璧に作ろうとせず、いらなくなったら外す、で十分でした。

つまずきの教訓を一行でまとめると、こうなります。

「全データから読む」をやめて「編集された場所だけを読む／集計は数式に任せる」に変えるだけで、たいてい速くなります。

[おおばこ追記欄2/3] このタイムアウト問題、導入当時の自分が一番ハマったポイントを補足してください 記入例:

  • 最初は「forループで全行回す」しか書き方を知らず、Excel感覚で組んでしまった
  • 速度改善のとき、Stack OverflowよりもAIに「このコードを最速化して」と頼むほうが早かった
  • 実行時間ログをスプレッドシートに書き出すようにしたら、どこが遅いか一発で分かった

過去の自分に伝えたい3つのこと

ここは、過去の自分（GASを知る前のおおばこ）に向けて書きます。同じところで止まっている人の参考になればうれしいです。

1. すぐ動く仕組みを作って、動かしてから試す

完成度を上げてから動かそうとすると、永遠に動きません。今回のコードも、最初はログシートにただ「日時とQR_IDと数量」の3列だけ書く20行程度のスクリプトから始めました。動いてから、操作種別・担当者・取消フラグを足していった順番です。

2. 少ない在庫数から始める

3,700種類を最初から相手にしないことです。自分の場合、最初は社内の備品（10種類くらい）で試しました。動くと分かってから本番の資材シートに移したので、不安なく広げられました。

3. 工夫と繰り返しが大切

GASは1回書いて終わりではなく、現場で使われながら少しずつ削っていく道具です。今のコードも、書いた当初のものとはかなり違います。「動かす → 不便を見つける → 削るか足す」 のループを、半年〜1年かけて繰り返した結果が今のシンプルな形です。

途中、自分の作ったものが現場でうまく使ってもらえなかった時期もありました。それでも続けられたのは、「便利になればみんなが楽になる」と信じていたからです。腕力で説得するのではなく、ただ便利なものを置いておくと、いつか誰かが使ってくれる。そう思って積み上げた1年でした。

[おおばこ追記欄3/3] 「便利になればみんなが楽になる」と信じて続けた具体的なエピソードを1つ足してください 記入例:

  • 最初の3ヶ月は自分1人しか使っていなかったが、4ヶ月目に若手が「これ便利ですね」と言ってくれた瞬間
  • 上司に「こんなの誰も使わない」と言われた翌日に、別部署からヘルプ依頼が来た日
  • 1年後、後任に引き継ぐとき「このシート無いと仕事になりません」と言われたとき

まとめ

  • GASスプレッドシート在庫管理のキモは、「在庫値を直接書き換えず、ログに1行残す」 という1点だけです
  • 集計は SUMIFS数式に任せる ので、GASのコード本体は驚くほど短くなります
  • そこさえ自動化すれば、夕方の集計残業（自分の場合は2〜2.5時間）はほぼ消えます
  • タイムアウトしたら、「全データから読む」を「編集された場所だけ読む／数式に任せる」に変える のが最速の直し方です
  • 最初から3,700種類を相手にせず、10種類で動かしてから広げるほうが結果的に速く着地します

この抜粋コードは、過去の自分に渡したい3ブロックそのものです。CONFIG → SUMIFS → ログ追記、の順で自分の現場のシート名と列番号に直すところから、ぜひ始めてみてください。

関連記事

この記事が役に立ったら

同じ悩みを持つ方に届くよう、シェアしていただけると嬉しいです。

次に読む

GAS入門は何から？非エンジニアが3日で動かした3つの手順
GAS自動化

GAS入門は何から？非エンジニアが3日で動かした3つの手順

GAS入門で何から始めればいいか迷っていた非エンジニアの自分が、引き継いだ「数字が合わない業務」をきっかけに、チャット型AI→エージェント型AIと使い分けて最初の動くものまで3日で到達した手順です。3年前にスクリプトエディタを開いて挫折した自分が、今回はたどり着いた記録です。

おおばこ

物流 x AI自動化ブロガー

非エンジニアの会社員。GASとAIで日々の仕事を改善中。 「自分にもできた」を同じ立場の人に届けたいと思い、このブログを始めました。

プロフィール詳細 →
記事一覧に戻る