# Google スプレッドシートの数式・外部参照を AI に監査させる手順（xlsx エクスポート＋openpyxl） ## 使いどころ - 数十シート・数万数式のスプレッドシートで「この値はどこから来たか」「IMPORTRANGE で何に依存しているか」「集計がズレる原因」を調べたいとき。 - Sheets API や GAS を書かずに、読み取り専用で全数式と計算済み値を一括取得して解析したいとき。 ## 手順 1. **xlsx で丸ごと取得する**。Drive の自然言語読み取り（`read_file_content` 相当）は大きいシートで途中で切れるので使わない。`download_file_content` 相当で `exportMimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet` を指定し、base64 を復号して `.xlsx` に保存する。 2. **数式と値を別々に読む**。 ```python import openpyxl wf = openpyxl.load_workbook('book.xlsx') # 数式 wv = openpyxl.load_workbook('book.xlsx', data_only=True) # 計算済みキャッシュ値 ``` 3. **IMPORTRANGE などの Google 独自関数は `IFERROR(__xludf.DUMMYFUNCTION("…"),キャッシュ値)` に包まれている**。DUMMYFUNCTION 内の文字列（`""` は `"` にデコード）を取り出すと元の数式が復元できる。スプレッドシート ID は `/d//` または 44 文字の ID で正規化し、ID ごとに「参照元シート／範囲／件数」を集計する。ID 文字列が `"…"&"…"` で分割連結されている式もあるので、正規表現が 0 件でも諦めず連結を解く。 4. **開放範囲は末尾行が切り詰め表示される（最大の罠）**。Google 上で `'案件'!$A$2:$A` と書かれた開放範囲は、xlsx では `$A$2:$A28` のように**参照元シートの行数ではなく別の数字**で表示されることがある。これを「28 行固定の集計漏れ」と誤判定しやすい。判定は必ず **キャッシュ値を自分で再計算して突き合わせる**：候補 (a) 切り詰め表示どおりの範囲、(b) 全行、の両方で SUMPRODUCT/SUMIFS を Python で再現し、キャッシュ値と一致する方が真の数式。全行版が全件一致すれば表示上の切り詰めと確定できる。 5. **列の対応は QUERY の SELECT 順で決める**。`QUERY(IMPORTRANGE(...), "SELECT Col1,Col3,Col11,Col59..Col86")` の出力は A=Col1, B=Col3, C=Col11, D=Col59… と詰まるので、元シートの列文字で読まない。年・月の列を取り違えると再計算が全部 0 になる。 6. **エラー棚卸し**は `data_only=True` の値が `#REF!` `#N/A` `#VALUE!` `#DIV/0!` `#NAME?` `#ERROR!` のセルをシート別に数える。IMPORTRANGE 自体が `#REF!` の場合は参照先の削除・権限切れを疑い、参照先 ID のメタデータ（存在・所有者・更新日）を直接照会する。 7. **AI の中間報告は再計算で裏取りする**。解析を小さなモデルに委譲した場合、手順 4 の誤判定を複数回繰り返すことがある。影響が最大の主張（「95% が集計漏れ」など）は監督側が必ず手順 4 の突き合わせをしてから採用する。 ## よくある結論の型 - 「ある人だけ急上昇」→ 評価の累積ラダー（毎月の達成率で ±N を積む）と、1 件の大きな調整値（年間換算を複数月に按分）の組合せ。全員の時系列を並べて個人の話か全体の話かを先に切り分ける。 - 「担当者別の集計が時々ズレる」→ セル単位の行挿入より、**バインドされた GAS が全行を読み込み→長時間処理→全列を丸ごと書き戻す**設計が原因になりやすい。症状は「実行中の手入力が消える」「末尾に同一行が複数残る」「展開したサブ行に隣の案件の担当が入る」。 - バインド GAS を直す前に **必ず `clasp pull` で本番ソースを取る**。ローカルの古いミラーから `clasp push` すると本番の改修が消える。 ## 検証スクリプトの骨子 ```python # 候補範囲ごとに再計算してキャッシュ値と一致数を比べる def calc(y, m, name, max_row): s = 0 for r in range(2, max_row + 1): if month(r) != m or year(r) != y: continue for name_col, amt_col in pairs: if str(cell(r, name_col)).strip() == name: s += num(cell(r, amt_col)) return rounddown(s) # ok_a = キャッシュ値 == calc(..., 28) の件数, ok_b = calc(..., 全行) の件数 ``` ---