MENU
  1. ホーム
  2. 社内
  3. 業務集計PRJーLookerStudio連携用スプレッドシートー全体サマリ作成

業務集計PRJーLookerStudio連携用スプレッドシートー全体サマリ作成

社内
ooihikaru

📘 LookerStudio連携用サマリ自動作成（業務報告・見積管理表対応）

用途：Looker Studio（データポータル）へ連携するための「業務報告＋見積管理表」サマリデータを自動生成

目次

🔰 1. 概要

Google Apps Script（GAS）を用いて、Google Drive 上のフォルダに保存された「業務報告スプレッドシート群（業務報告YYYYMM形式）」を自動で結合・整形します。

また、**見積り管理表（QuotationTaskList）**を同時にコピーして
Looker Studio 連携用サマリとして1つのスプレッドシート群を作成・更新します。

🗂 1.1 実行先スプレッドシート

本スクリプトは、以下のスプレッドシート上で実行・管理されます。

📄 実行管理用スプレッドシート
👉 LookerStudio連携サマリ自動作成 管理シート（Google スプレッドシート）

https://docs.google.com/spreadsheets/d/1XB3W_oNnTVltjrpsw4itCYxDPkXlYlnPxlgr5I3OvO4/edit?gid=830594613#gid=830594613

🗂 2. 処理の全体構成

業務報告フォルダ（BASE_FOLDER_ID）
├─ 業務報告202508
├─ 業務報告202509
├─ 業務報告202510
│
└─ LookerStudio連携用サマリ-1（出力先）

このスクリプトの目的：

  • 各「業務報告YYYYMM」ファイル内の サマリ シートを読み取り
  • 除外列を除いたデータを結合し、「業務報告サマリ」シートとして出力
  • 「見積り管理表サマリ」も同ファイルにコピーして保持
  • 処理ログをスクリプト実行シートに記録し、完了メールを送信

    ＜各スプレッドシート格納先 Googleドライブ＞
    https://drive.google.com/drive/folders/1ZtOY_N01DFN7jfhibryfPoy9SxD91Hhd

🧭 3. 実行方法（手動メニュー）

スプレッドシート上部メニューに以下を追加しました。

メニュー名機能概要
フォルダ内のスプレッドシートを全部取り込む全ての「業務報告YYYYMM」を結合（全期間対象）
最新月を含む3ヶ月分までを取り込む直近3ヶ月分（例：10月実行時 → 10・9・8月）を差し替え更新

⚙️ 4. 各モードの動作概要

🔹 A. 全件取込モード（menuImportAll）

  • フォルダ内の全ての「業務報告YYYYMM」を取得
  • 8→9→10→11 の昇順で結合
  • 「取込月」列を自動付与
  • Looker Studio用スプレッドシート（複数分割）に出力
  • 1枚目のファイルに「見積り管理表サマリ」を同時出力

🔹 B. 最新3ヶ月モード（menuImportLatest3）

  • フォルダ内でもっとも新しい「業務報告YYYYMM」を検出し、その月・1ヶ月前・2ヶ月前の 3ヶ月のみ を対象にします。
  • 対象月の既存行を 削除 → 新行を追記 → シート側ソート で差し替え更新し、他月のデータは保持します。
  • 出力先は、最新番号の 「LookerStudio連携用サマリ-◯」 ファイルです。
  • 追記時に各行へ 「取込月」 と 「取込時間（JST）」 を付与します（取込時間は取込月の右隣列）。
  • 並び順：取込月 昇順（8→9→10→11） → 生年月日 昇順（サーバ側ソート）。
  • 除外列：チケットフラグ／チケットチェック日付／チケット番号 は常に取り込み対象外。
  • 更新順序：負荷分散のため 見積り管理表サマリ → 業務報告サマリ の順に書き込み。
  • 安定化：LockService による直列化／指数バックオフで一時エラーを再試行。autoResizeColumns は新規時のみ。
  • ログ：YYYY_log に結果・行数・処理時間を記録（最大8年分保持、古い年は自動削除）。

🧩 5. 主な内部関数と役割

関数名役割概要
importAndWrite_()全期間の結合処理。昇順で連結して出力。
importLatest3_()直近3ヶ月のみを更新。既存データを維持。
writeQuotationToOutput_()QuotationTaskList を読み取り、「見積り管理表サマリ」として出力。
writeRunLog_()実行ログを記録（例：「業務報告サマリ：OK（1000行）/ 見積り管理表サマリ：OK（573行）」）。
sendReportMail_()実行結果をメール通知。スキップ・エラーも含め日本語で報告。
collectSummaryFiles_()フォルダ内の「業務報告YYYYMM」ファイルを収集し、最新年月を特定。
upsertMonthsToSingleSheet_()対象月データを差し替え（取込月・年月日昇順）。
buildLatest3Yms_()指定年月から3ヶ月分の年月配列を生成（例：['202510','202509','202508']）。

📄 6. ログ出力仕様

📘 ログシート構成例（自動生成）

実行日時結果内容
2025-10-31 12:30:25正常終了業務報告サマリ：OK（1000行） / 見積り管理表サマリ：OK（573行） / 処理時間：2.8秒
  • 各年ごとに YYYY_log シートとして出力
  • 最新8年分まで保持（古い年は自動削除）
  • 成功・スキップ・エラーを判定して結果を明記

📬 7. メール通知内容

件名：

サマリ取り込み完了通知（LookerStudio連携用）

本文（例）：

📊 サマリ取り込み結果
実行日時：2025-10-31 12:30:25

【出力ファイル】
・LookerStudio連携用サマリ-1（2458行／32列）
　https://docs.google.com/spreadsheets/d/xxxxxxxx/edit

【見積り管理表サマリ】
・OK（573行／17列）
　元データ：https://docs.google.com/spreadsheets/d/xxxxxxxx/edit

【スキップされたファイル】
・業務報告202507：シート「サマリ」が存在しません。

下記のように実行された結果がメールで通知がきます。
通知先：cynosura@cnsr.jp

⚠️ 8. サマリ取り込み：運用ルール & 注意点（社内共有用）

1. フォルダ／ファイル＆シート名（固定・禁止事項）

  • 取り込み対象フォルダ：BASE_FOLDER_ID 直下に配置（サブフォルダ不可）
  • 元ファイル名：業務報告YYYYMM（例：業務報告202511）
    └ 命名を1文字も変更しないこと（異なると取り込み対象外）
  • 出力ファイル名：LookerStudio連携用サマリ-1、-2、…
    └ 改名・統合・削除をしない（スクリプトが再利用・分割管理）
  • シート名（固定）
    • 元データ：サマリ
    • 出力先：業務報告サマリ、見積り管理表サマリ

2. 除外列（常時削除）

  • 業務報告サマリを作成する際、下記列は常に除外（Looker Studio 連携に不要）：
    チケットフラグ / チケットチェック日付 / チケット番号
  • 元データ側の列構成・ヘッダーは全月で同一・同順を維持すること（不一致は対象月スキップ）

3. ソートルール（出力の並び順）

  • 取込月（昇順：8 → 9 → 10 → 11 …）
    → 年月日（生年月日）（昇順）
  • 出力側には補助列「取込月」を自動付与（手動編集しないこと）

4. 分割出力の制御（セル上限）

スプレッドシートは 1ファイル＝合計セル数の上限（行×列の総和、全シート合算）があるため、上限を超えたらスプレッドシートを複製するよう設定しています。
※合計 1,000万セル（または 18,278 列）」という“固定”の上限がある。

  1. 現状の総セル数を軽量に取得
    • 全シートの (MaxRows × MaxColumns) を合計して「使用済みセル数」を算出。
  2. 安全余白（バッファ）を確保
    • 上限セル数 － 使用済みセル数 － バッファ ＝ 実残セル。
  3. 実残セルから“本当に入れられる最大行数”を決定
    • 最大追記行数 = 実残セル ÷ 列数（ヘッダー分は差し引き）。
  4. その上限でデータを分割し、複数ファイルへ順次書き込み
    • 超過時は LookerStudio連携用サマリ-2、-3 … を自動生成
      既存の -1、-2 などはスクリプトが再利用するため、改名・削除禁止

＜計算式＞
使用済みセル数 = Σ(シートごとの MaxRows × MaxColumns)
実残セル = 上限セル数 − 使用済みセル数 − バッファ
最大追記行数 = ⌊ 実残セル ÷ 列数 ⌋ − ヘッダー行

スプレッドシートのハード上限と安全バッファ

変数名設定値意味備考
SPREADSHEET_CELL_HARD_LIMIT10,000,0001つのスプレッドシートファイルに入れられるデータ量の上限Google側で決まっている上限
SPREADSHEET_CELL_BUFFER1,000,000上限ギリギリまで使うと失敗の原因になるため、あらかじめ空けておく余裕トラブル防止のための「安全マージン」
MIN_ROWS_PER_FILE_FALLBACK1000空き容量が少なく見えても、最低この行数は入れるという安全ラインファイルが細かく分かれすぎるのを防ぐ

5. ログの保持（年単位）

  • 実行ログは出力ブック内の YYYY_log シートに1行追記（実行日時／結果／サマリ）
  • 最大8年分を保持し、古い年のログシートは自動削除
    ※「業務報告サマリ読み込みマスタ」のログシートを指す

6. トリガー設定（自動実行）

  • 深夜帯（AM 1:00〜2:00） の間に 1日間隔で自動実行トリガーを設定
    └ 同時多重実行を避けるため、連打・重複トリガーは不可

7. 見積り管理表サマリ（QuotationTaskList）

  • 参照元スプレッドシートIDとシート名は固定
    └ ID／QuotationTaskList の名称変更や構造変更は不可（NGとしてメール／ログに記録）

以下は、「リストシート」に関する注意点として、社内資料にそのまま記載できる形式の内容です。
（本スクリプトの仕様に基づいた正式説明文）

🔖 8-2. リストシートに関する注意点

1️⃣ リストシートとは

  • 「リスト」シートは、業務報告サマリで使用される VLOOKUP 参照用のマスタです。
  • 主に「作業リスト」列をキーとして、以下の情報を自動で展開するために利用します。
    • 業務範囲（列）
    • 対応者分類（列）
=VLOOKUP(作業リスト, 'リスト'!C5:C7, 2, 0)  // 業務範囲
=VLOOKUP(作業リスト, 'リスト'!C5:C7, 3, 0)  // 対応者分類

2️⃣ 自動削除・上書き対象外

  • リストシートは、スクリプト実行時に削除・上書きされません。
  • 業務報告サマリ や 見積り管理表サマリ のような出力対象シートのみが上書きされ、
    「リスト」シートは常に保護対象として残されます。
  • そのため、「リスト」内の設定やマッピングは安全に保持されます。

3️⃣ 複製時のリストシート自動コピー

  • 出力スプレッドシート（例：LookerStudio連携用サマリ-2 など）が新規作成または複製された場合、
    元の LookerStudio連携用サマリ-1 に存在するリストシートが自動で複製されます。
  • 複製後のファイルにも同名「リスト」シートが自動的に追加され、
    VLOOKUP 参照が途切れないように維持されます。

4️⃣ 手動での削除・リネーム禁止

  • 「リスト」シートの削除やシート名変更は行わないでください。
    （リスト という名前で存在していることがスクリプトの動作前提）
  • 誤って削除した場合、次回スクリプト実行時に自動で再生成されますが、
    マッピング内容は失われます。

5️⃣ まとめ（運用ルール）

内容対応
リストシートの削除❌ 禁止（スクリプト対象外）
リストシートの上書き❌ 禁止（保持される）
リストシートの複製⭕ 自動（サマリ複製時にコピー）
シート名変更❌ 禁止（リスト 固定）
マッピング編集⭕ 手動編集OK（式の参照元として使用）

🧠 9. 技術メモ（保守担当者向け）

項目内容
フォルダIDBASE_FOLDER_ID = '1ZtOY_N01DFN7jfhibryfPoy9SxD91Hhd'
出力スプレッドシート名LookerStudio連携用サマリ-◯
ソースファイルQuotationTaskList（ID: 1LF93m9DlMd7XWZmT9uJ8Rj-mPAZpRq3wrkU0o7n3Pm4）
タイムゾーンAsia/Tokyo 固定
メール送信先cynosura@cnsr.jp
GAS 実行制限約6～10分（処理量に注意）

✅ 10. トラブルシューティング

症状想定原因対応策
メールに「ヘッダー不一致」と表示一部の業務報告ファイルで列名が異なるシートの列名を統一（余計な空白・改行も削除）
「データが空です」と出力シート内に値が存在しない該当月のスプレッドシートを確認
「シートが存在しません」サマリ シート名が変更されている元データ側で名称を修正
「見積り管理表サマリ：NG」QuotationTaskListの構造変更や空データ元スプレッドシートを再確認

🕐 11. トリガー設定（自動実行）とバッチ処理

11.1 トリガー設定（例：毎日 1:00–2:00 JST）

  • 実行する関数：menuImportAll
  • デプロイ時に実行：Head
  • イベントのソース：時間主導型
  • 時間ベースのトリガーのタイプ：日付ベースのタイマー
  • 時刻：午前 1 時〜 2 時（GMT+09:00）
  • エラー通知設定：毎日通知を受け取る（GAS の標準通知）

目的：日の切り替わり直後に全件取込を実施し、Looker Studio 側の集計を常に当日朝までに最新化します。処理結果はスプレッドシートの YYYY_log シートへ追記され、同時にメール通知されます。

トリガー設定画面のキャプチャ

11.2 バッチ処理の考え方（負荷分散・安全設計）

本スクリプトは「大量データでも安定して完了できる」よう、以下のバッチ処理方針を採用しています。

  1. 入出力の分割（スプリット出力）
  • 1 スプレッドシートあたりのセル上限（9,000,000）を超えないよう、
    列数から安全行数を算出し、LookerStudio連携用サマリ-1 / -2 / -3 … と自動分割して出力。
  • 実装：MAX_CELLS_PER_SPREADSHEET と chunkRows_() により、書き込み単位をバッチ化。
  1. 差し替え対象の限定（アップサート）
  • 最新3ヶ月モード（menuImportLatest3）では、対象月の行だけを既存データから除外し、
    対象月の新データをまとめて追記することで書き込み回数を削減。
  • 実装：upsertMonthsToSingleSheet_() で、
    • 既存行から 取込月 が 今回3ヶ月に該当するものだけを除外
    • 追加する行は月単位に収集→整列後に一括書き込み（= バッチ）
  1. I/O の最小化（まとめ読み・まとめ書き）
  • 各「業務報告YYYYMM」については getDataRange().getValues() で一括取得し、
    除外列をメモリ上で処理してから最少回数の setValues() で書き戻し。
  • 実装：loadMonthlyData_() → writeToSingleSheet_() による集約→一括書込。
  1. ソートと整形もメモリで集約
  • 並び順は**取込月（昇順：8→9→10→11）→ 年月日（昇順）**をメモリ上で完結。
  • 実装：rowsWithMonth.sort(...) / allRows.sort(...) で整列完了後に1 回の書き込み。
  1. ログと通知で監視（失敗の早期検知）
  • 実行ログは年ごとに YYYY_log シートへ 1 行追記（writeRunLog_()）。
  • 成否・件数・処理時間を日本語で整形。
  • 併せてメール通知（sendReportMail_()）でスキップ・エラー理由を明示。

11.3 運用上のポイント

  • 定期実行：毎日 1–2 時に menuImportAll を自動実行。
    週次・月次主体の運用なら menuImportLatest3 を週 1 回で十分。
  • 通知：GAS 管理画面で「エラー通知：毎日」を有効化。
  • 保守：列名の揺れ（空白・改行など）を月初チェック。
  • 拡張：列追加時は MAX_CELLS_PER_SPREADSHEET の再調整を推奨。

実行テスト 処理時間

フォルダ内のスプレッドシートを全部取り込む：5万8000行でテストトライ。処理時間：約3分50秒（230秒）

最新月を含む3ヶ月分までを取り込む：処理時間：約1分14秒（74秒）

社内

この記事を書いた人

ooihikaruのアバター ooihikaru

関連記事

目次