# 複数の業務シートに散った「発注し忘れたら困るもの」を1枚に集約し、抜けを機械的に検知する（GAS） 対象: 案件ごとにスプレッドシートが分かれていて、そこから外注先へ発注する運用。 「発注し忘れ」「データの渡し忘れ」が定期的に起きるが、どこに何があるか一覧できていない状態。 **結論を先に**: 集約表を作るだけでは漏れは止まらない。**「表にあるがデータが無い」と「データはあるが表に無い」の両方向を出す**こと。片方向だけだと必ず抜ける。 --- ## 1. 最初に潰すべき落とし穴（実測） ### 1.1 「品名の列」は1本ではない 横に広い業務シートは、右側に別ブロック（追加発注分・別チームの記入欄）を持っていることがある。 ヘッダー検出で「最高スコアの行の品名列を1つ」選ぶ実装にすると、**右側の小さいブロックを掴んで本体を丸ごと読み飛ばす**。 実測: 868 行のシートから **4 行しか拾えていなかった**（本体の品名列は 6 列目、掴んでいたのは 49 列目）。 対策: ヘッダー名に一致した列は**全部使う**。1 行から複数の候補が出てよい。 ```javascript // NG: 1本に絞る if (candidate.score > best.score) best = candidate; // → 本体を取りこぼす // OK: 当たった列を全部集める rows[r].forEach(function (v, col) { if (/^(品名|品目|アイテム名|item|name)$/.test(norm(v))) nameCols.push({ col: col, row: r }); }); ``` 行の一意キーも `<案件ID>-<行番号>-<列番号>` にする。行番号だけだと同じ行の2ブロックが衝突する。 ### 1.2 「カテゴリ別フォルダ」は空のまま運用されていることがある 「ポスター」「バナー」等のカテゴリ別サブフォルダがテンプレとして自動生成される運用だと、 **現場は使わず、実データは別の場所（受領素材フォルダ）に置かれている**ことが多い。 見分け方: フォルダの `createdTime == modifiedTime` が全件で一致していれば**一度も使われていない**。 設計前に必ず実物を数件見る。ここを前提にすると収集結果がゼロ件になる。 対策: 起点フォルダから**深さ3〜4で再帰走査**し、拡張子と名前で候補を拾う。 ### 1.3 セルの中に URL が直書きされている `商品名（印刷して渡す） データ: https://.../folders/xxxx` のように、**品名セルの本文に URL が埋まっている**。 さらにハイパーリンクとして貼られている場合もある。**両方**拾わないと取りこぼす。 ```javascript // 表示文字列の正規表現と、リッチテキストのリンクの両方 var urls = (String(cell).match(/https?:\/\/[^\s<>"']+/g) || []); var rt = range.getRichTextValues()[r][c]; if (rt && typeof rt.getLinkUrl === 'function') { if (rt.getLinkUrl()) urls.push(rt.getLinkUrl()); rt.getRuns().forEach(function (run) { if (run.getLinkUrl()) urls.push(run.getLinkUrl()); }); } ``` URL がフォルダなら**1階層だけ展開**してファイル名を出す。フォルダURLだけ渡されても相手は中身を確認できない。 ### 1.4 `getRichTextValues()` をシート全域に掛けると 6 分制限で落ちる 数MBのシートで全域に掛けると**無応答のままタイムアウト**する（エラーも出ないので原因が分かりにくい）。 **品名・備考など数列だけ**に絞る。他の列の URL は表示文字列の正規表現側で拾えば足りる。 なお `getRichTextValues()` は2次元配列を返す。1列だけ取ったときは `col[row][0]` であって `col[row]` ではない。 間違えると `rich.getLinkUrl is not a function` で**その案件の行が丸ごと消える**。 --- ## 2. 抜けを検知する構造（本質） 集約表と実データを**双方向**で突き合わせる。 ``` [業務シートの品目] ←── 名前で正規化して部分一致 ──→ [実データのファイル] マッチした → 表の「データ」列にリンクを入れる 表にあるがデータ無し → 「データ未入稿」として表に残す（外注先への催促材料） データはあるが表に無い → 「一覧に無いファイル」として別シートに出す ★これが漏れの検知口 ``` 「データはあるが表に無い」を出さないと、**そもそも発注リストに載せ忘れたもの**が永久に見つからない。 名前の正規化は、実務のファイル名に合わせて次を落としてから比較する: ``` NFKC正規化 → 記号/空白除去 → 小文字化 先頭の装飾記号（●★◆【】）/ 「メール添付_」等の接頭辞 日付らしき連続数字6〜8桁（260819 / 20260731） 拡張子 / 「見本」「閲覧用」「_共有」「のコピー」 ``` 2文字以下の断片では一致させない（誤マッチが増える）。 --- ## 3. ノイズと漏れのバランス 「抜けゼロ」を素直に実装すると **1案件254行**になり、外注先が読めなくなる。読めない表は漏れと同じくらい悪い。 原因は「行内のどこかに URL があれば採用」というルール。業務シートには**現物写真の列**や**購入先URLの列**があり、 消耗品のほぼ全行にリンクが入っている。 対策: **URL を採取する列を「品名・備考・注意事項」に限定**し、採用条件を次のどちらかにする。 - 品名・備考が対象キーワードに当たる - 上記の限定列に URL があり、かつ**フォルダ or 入稿データらしい拡張子・名前**である 実測: 254 行 → **43 行**に収束し、残ったものが実際に発注する品目に寄った。 --- ## 4. 人が編集する列を絶対に壊さない 集約表は自動再生成されるが、**人と外注先が書き込む列がある**。ここを上書きすると運用が死ぬ。 - 安定キー（`<案件ID>-<発生源>-<行>-<列>`）で既存行を索引し、**人が編集する列だけ既存値を引き継ぐ** - **行は削除しない**。元データから消えても備考に `[ソース消滅]` を付けて残す - 並べ替えは値の書き直しで行い、保持列がキーに追従することを必ず確認する（ズレたら本末転倒） **「不要な行を消したい」への答えは行削除ではない。** 削除しても次回再生成で元データから復活する。 状態列を「対象外」にして別シートへ退避する形にし、その旨を凡例に明記する。 さらに「毎回出てきてほしくない」に応えるため、**除外ルールを学習させる**: 状態が「対象外」かつ指示欄に「今後も除外」とある行の品名を除外ルールとして登録し、以後は初回から対象外にする。 反映したら指示欄の先頭に `[反映済 日付]` を付けて**消さずに残す**（何を指示したか後から読める）。 --- ## 5. 実行時間とスケジューラの制約 - 1案件あたりの処理が数十秒〜2分かかると、**全案件は1回の実行では終わらない**。 カーソル（次に処理する案件のindex）を永続化し、4分30秒で打ち切って続きから再開する。 - **1案件の例外で全体を止めない**。その案件をスキップして `errors[]` に積み、サマリに出す。 1つの壊れたシートで全案件が出なくなるのが最悪の失敗モード。 - 外部ストレージの走査結果は**24時間キャッシュ**する。実測で 1案件140秒 → 17秒。 - **時間トリガーには上限がある（GASは20本/スクリプト）**。既存プロジェクトでは既に埋まっていることがある。 「1分後に自分を再実行するトリガーを毎回作る」方式は上限で必ず落ちる。 **10分ごとの tick 1本**にまとめ、その中で「カーソルがあれば継続、無ければ日次の開始判定」を分岐させる。 上限に当たったら、まず**重複登録されたトリガーを疑う**。設置処理が既存チェックをせずに毎回作っていると、 同じ夜間処理が何本も走って枠を食う（実測: 同一ハンドラが7本重複＝毎晩7回実行されていた）。 --- ## 6. 検証の型 集約系は「動いたように見えて中身が空」が最も危ない。次の読み取り専用プローブを**必ず作る**。 ``` debugOneCase(caseId) -> { 使ったシート名, 検出したヘッダー行と列, 全行数, 採用行数, 採用理由の内訳 { キーワード: n, リンク: n }, 採用行のサンプル10件, 走査したファイル数と確度別の内訳, 打ち切りの有無, 所要ms } ``` `採用行数 / 全行数` が異常に小さければヘッダー検出が外れている。これが無いと 1.1 の「868行中4行」に気付けない。 --- ## 7. 出力の列設計（外注先に渡す前提） 自社列と**相手が記入する列を色で分ける**。相手の記入欄はチェックボックスとプルダウンにする。 ``` 自社: ID / 状態 / 案件 / 会期 / 締切目安 / 区分 / 品名 / サイズ / 数量 / 仕様 / データリンク / データ有無 / 発生源 / 備考 相手: 受領☑ / データ確認(未確認・OK・要修正・データ不足) / 印刷予定日 / 納品予定日 / 担当者 / メモ ``` - 「締切目安」は機械計算（会期初日-N日）である旨を凡例に明記する。確定納期と誤解されると事故る。 - 自社列は**警告付きの保護**（`protect().setWarningOnly(true)`）。完全ロックは自社の運用まで止める。 - ヘッダー行の**上に行を挿入しない**。固定行数と読み書き位置が全部ずれる。導線は別シートか最終行の下に置く。 --- ## 8. 列見出し検出の細かい罠 - `/^サイズ/` は「サイズ縦」にも当たる。**完全一致 `/^サイズ$/`** にする。当たった列が縦だけを返して `329` のような無意味な値が出る。 - 寸法が1つしか取れないときは空にする（2つ以上取れたときだけ出す）。単位も文脈も無い数値は相手を混乱させる。 - 自社の倉庫ロケーション（`A3-1-3-3` 等）を「仕様」欄に入れない。相手には意味が無い。備考へ回す。 - 期間の判定は `終了日 || 開始日` で行う。**終了日が空のレコードは実務で非常に多い**。終了日だけ見ると過去案件が現役表に残る。 ---