目次
全クライアント見積り自動集計スクリプト
1️⃣ 概要
本スクリプトは、全クライアントの見積りデータを自動で集約し、1つのスプレッドシートにまとめるための
Google Apps Script（GAS）です。
各クライアントごとに存在する「管理表一覧シート」から最新データを取得し、
1枚の集計シート（QuotationTaskList）に統合します。
これにより、複数スプレッドシートに分散しているデータを 手作業でまとめる必要がなくなり、 作業時間の削減とデータ整合性の向上になります。
データについては、図のように欲しい情報のみを各クライアントシートから取得しています。
🗂 実行先スプレッドシート（見積り管理表）
2️⃣ 主な特徴
| 機能 | 内容 |
|---|---|
| ✅ 一括集計 | 全クライアント分の見積りを自動取得・統合 |
| ✅ スキップ処理 | URL空欄・連続重複URL・必須キー（見積管理No.／サイト・プロジェクト名）を欠くシートや行はスキップ |
| ✅ 安定設計 | 処理中にエラーが出ても他URLの処理を継続 |
| ✅ UI付き | 「見積り集計 → スプレッドシート取り込み」メニューを自動追加 |
| ✅ 処理表示 | 実行中に「見積り取り込み中です…」を表示し、完了後に自動で閉じる |
| ✅ 手動・自動両対応 | 手動実行も、トリガーによる深夜自動実行も対応可 |
3️⃣ 処理の流れ
1️⃣ スクリプトを実行する
- メニュー「見積り集計 → スプレッドシート取り込み」をクリック
- または、トリガーにより自動実行
2️⃣ 管理表一覧からURLを読み取る
- 3行目のヘッダーから「見積もり管理表URLサマリ取得用」列を動的検出。URL空欄や直前重複はスキップ。
- URLが空欄または前の行と同じURLはスキップ
3️⃣ 各クライアントの見積りを取得・集計
- URL先のスプレッドシートを開く
- クライアント名一致のシート（なければ先頭）を選択
- D列（案件名など）が空の行は除外
- 4行目の英語ヘッダーをキーに列をマッピングし、6行目以降〜最終行を取得。
- 行採用条件は見積管理No.（ EstimateManagementNo） とサイト・プロジェクト名（ SiteProjectNameAndDetails )の両方が存在していれば取得
- 必要な列のみを抽出して
QuotationTaskListに転記
4️⃣ 処理が完了する
- 取り込んだ件数と所要時間を表示
- 完了後、完了メッセージのポップアップを表示
4️⃣ ロジック概要（技術者よりの詳細）
🧩 全体構成
| 層 | 関数 | 役割 |
|---|---|---|
| ① UI層 | onOpen(), importAndSummarize() | メニュー生成とUI制御（ポップアップなど） |
| ② 処理層 | runImport_() | データ取得・集計・出力を行うメイン処理 |
| ③ 補助層 | openSpreadsheetBySmartUrl_(), extractSpreadsheetId_(), prepareOutputSheet_(), buildHeaderIndexMap_() | URL解析・出力準備・列番号変換などの共通処理、英語ヘッダー→列インデックスのマップ生成 |
⚙️ 技術的処理フロー
| ステップ | 処理内容 |
|---|---|
| ① | 「管理表一覧」シートを開き、3行目の見出しからURL列（D列）とクライアント列を特定 |
| ② | 4行目以降のURLを順に取得。空欄・直前URL重複はスキップ |
| ③ | 「QuotationTaskList」シートを開き、2行目以降のデータを削除 |
| ④ | 各URLのスプレッドシートを開き、対象シートを選定（クライアント名一致または先頭） |
| ⑤ | D列が全て空のシートはスキップ。各クライアントの見積り管理シートから 特定の英語ヘッダー項目 を基準にデータを取得 |
| ⑥ | すべてのデータを一括 setValues() で出力 |
| ⑦ | 処理時間と行数を算出し、完了アラートを表示後にポップアップを閉じる |
🧠 技術上のポイント
| 特徴 | 説明 |
|---|---|
| 冪等性設計 | 毎回クリア → 最新データ再構築のため、常に正しい状態で保持 |
| 重複回避 | 連続した同一URLはスキップし、処理時間を短縮 |
| 汎用対応 | クライアント名列がなくても処理継続可能 |
| 高速化 | データをまとめて配列転送 (setValues()) することでセル操作を最小化 |
| 安全処理 | try...catch によりアクセス不能シートを自動スキップ |
| 保護設計 | QuotationTaskListの出力シート1行目（ヘッダ部分）はヘッダー保護の為、上書きしない |
📊取得データ項目一覧
| 項目カテゴリ | 代表例 | 説明 |
|---|
| 基本情報 | EstimateManagementNo / ReleaseDate / SiteProjectNameAndDetails / DeliverableType / BillingMonth | 案件の基本識別情報、制作物の分類、請求月などを管理します |
| 金額情報 | ClientProposedAmount / EstimateCalculation / ExternalCostExcluded / ExternalCostIncluded / AdditionalCostTaxIncluded / AdditionalCostTaxExcluded | 見積金額、外注費、追加費用などの算出に関わる数値です |
| 工数合計 | TotalWorkHours / DirectorWorkTotal / DesignerWorkTotal / EngineerWorkTotal | 工数集計および作業負荷や原価算出に利用します |
| 詳細作業項目 | Direction / Writing / Design / Coding / Staging / Release / Product Registration / Shooting など多数 | 制作進行における各作業ステップの履歴管理・進捗把握に使用します |
※G〜N列は取得していません。下図参照
⚠️ スキップ・例外条件
| 状況 | 動作 |
|---|---|
| 管理表一覧シートのD列URL空欄 | スキップ |
| 同一URL（直前と同じ） | スキップ |
| 管理表一覧シートなし | 処理中止（エラー） |
| 各クライアントスプレッドシートのD列（タスク）が全て空の場合 | スキップ |
| アクセス権なし（権限がない場合） | スキップ |
| URL列が見つからない | 処理中止（エラー） |
⏱ 実行性能（目安）
| 処理内容 | 実際の継続時間（観測値） |
|---|---|
| 軽処理（10件未満のURL読み込み） | 約1〜3分 |
| 中規模処理（20〜30件） | 約5〜10分 |
| 大規模処理（40〜50件） | 約12〜18分（まれに20分以上） |
| 超過時（>20分） | 停止または Exceed maximum execution time エラー |
手動実行と自動実行でも上限時間が異なる。上限時間が過ぎるとタイムアウトする可能性はあります。
※上限実行時間はアカウント種別・エディションに依存します。値は実測の目安であり保証ではありません。
| 実行方法 | 上限時間（目安） |
|---|---|
| 手動実行 | 約 10〜12分 |
| 自動実行（トリガー方式） | 約 30分 |
現在は、33URL、実測値 約172秒で取り込みが完了しています。
6️⃣ トリガー実行について
毎日午前0時から1時の間に、自動でGAS関数が実行され、サマリデータが更新されます。
| 項目 | 内容 |
|---|---|
| 実行関数 | importAndSummarize |
| トリガー種別 | 時間主導型 |
| 実行時刻 | 午前０～１時に設定 |
| 頻度 | 毎日 |
| エラー発生した場合 | エラー時は自動でメール通知が届く cynosura@cnsr.jp |
7️⃣ 運用ルール・注意点
- 「管理表一覧」シートの3行目に
見積もり管理表URLサマリ取得用の見出しが必ず存在していること。
・クライアント名とシート名は一致させる。【安易に変更しない】
※クライアント名を含むシートがあれば優先、無い場合は先頭シートを使用するロジックとなっています。
名称変更時は想定シートの選択に影響する可能性があるため注意。
- 各見積りシートの構造は統一フォーマットを維持。
- 実行権限（参照先スプレッドシートのアクセス権）を持つユーザーでトリガーを設定。
- 処理途中で停止しても他URLへの影響はなし。次回実行で最新データに上書きされる。
参考：仕様の要点
- URL列検出：3行目ヘッダーから「見積もり管理表URLサマリ取得用」を含む列を動的検出。
- 英語ヘッダ基準：参照元は4行目の英語ヘッダーをキーに REQUIRED_FIELDS の順で詰め替え。
- データ範囲：6行目〜最終行を処理（5行目は見出し想定でスキップ）。
- 行採用条件：見積管理No.（EstimateManagementNo） とサイト・プロジェクト名（ SiteProjectNameAndDetails ）の両方に記載があれば取得。
- スキップ：URL空欄／直前重複／必須ヘッダー欠落シート／上記採用条件を満たさない行。
- 出力：QuotationTaskList の 2行目以降を毎回クリアし、配列一括 setValues。1行目（ヘッダー）は維持。
- 未定義項目：REQUIRED_FIELDS に無い項目は取得しない（空で出力）。
- シート選定：クライアント名を含むシートがあれば優先、無ければ先頭シート。
追加機能 サマリ手動作成（手動取得用）
「サマリ手動作成」は、
指定した業務報告スプレッドシートだけを手動でサマリ化するための機能 です。
通常の自動サマリ（全件／最新3ヶ月）とは異なり、
手動で URL を指定したファイルのみを対象にできます。
2. 使用するシート
手動取得用スプレッドシートリスト
このシートに、
サマリ化したい 業務報告スプレッドシートの URL を入力します。
- シート名
手動取得用スプレッドシートリスト - 必須ヘッダー（1行目）
手動取得用業務報告スプレッドシートURL - 2行目以降
対象とする業務報告の URL またはスプレッドシートIDを記載
3. 操作手順
- 管理用スプレッドシートを開く
- メニューから
「サマリ手動作成」 → 「手動取得用リストから取り込む」
を選択 - 実行が開始され、指定URLの業務報告ファイルのみがサマリ化されます
4. 出力ファイル
処理結果は BASE フォルダ内に以下の形式で生成されます：
- 手動取得用業務報告スプレッドシート-1
- 行数が上限に近い場合
→ -2, -3 … と自動的に分割作成
出力内容は通常のサマリ作成と同じ形式です。
（業務範囲・対応者分類は自動で VLOOKUP により補完されます）
5. 利用シーン例
- 特定クライアントの業務報告だけ先にサマリ化したい
- テスト用に一部の月だけ取り込んで確認したい
- 大量データの全件取り込みを避けたい場合
6. 注意点
- ヘッダー名 「手動取得用業務報告スプレッドシートURL」 は変更しないでください
- URL が不正・権限なしの場合、該当行はスキップされログに残ります
- 業務報告スプレッドシート内に「サマリ」シートがない場合は処理対象外です
