Google Apps Scriptで毎日データ報告をRPA自動化｜効率化と正確性向上の秘訣

閲覧数: 4,705+

正體中文 简体中文 English 日本語

本記事は AI による翻訳をもとに作成されています。表現が不自然な箇所がありましたら、ぜひコメントでお知らせください。

記事一覧

---

Google Apps Script を使った毎日データレポートのRPA自動化

Google Apps Script を使った Google Workspace 関連サービスのロボティック・プロセス・オートメーション（RPA）自動化

Photo by Possessed Photography

ロボティック・プロセス・オートメーション

RPA (Robotic Process Automation) は日本語で「プロセス自動化ロボット」と訳されます。人類の発展の歴史を振り返ると、素手の採集から石器時代、そして農耕文明へと進み、20世紀の産業革命から近年の情報化の台頭に至るまで、人間の作業効率と生産能力は指数関数的に飛躍的に成長しました。この過程で、RPAの応用は至る所に見られます。農耕時代の水車（脱穀の自動化）、産業革命の織機（織物の自動化）、工場のロボットアーム（組み立ての自動化）、そして本記事で紹介する情報関連作業の自動化、例えば自動レポート照会や自動通知などの作業です。

言うのは恥ずかしいですが、私も最近になってこの言葉を知りました。最初の仕事を始めてから（7年前）、ずっとRPAのようなことをしてきました。例えば、スクレイピングでのデータ集計、CI/CDプロセスの自動化、データ照会の自動化、安定性データのアラート自動化、毎日のルーチン作業の自動化などです。しかし、これまでは「自動化」という言葉でまとめていましたが、そろそろ正式名称であるRPA（Robotic Process Automation）を使う時です。

以前のRPAは「プログラムを書いて単一の問題を自動化すること」に重点が置かれていました。しかし、事前の完全な評価と分析、No/Low Codeツールの活用、法規制、運用監視、実際のデータ統計、継続的な改善、企業文化の普及などは欠けていました。これらはすべて完全なRPAに必要な要素です。ただし、前述の通り、私も最近この専門分野を知ったばかりなので、まずは実装面に関する記事を書かせてください。

RPA サービスを提供するプラットフォームは多数あります。例えば、Automation Anywhere、UiPath、Microsoft Power Automate、Blue Prism、または Zapier、IFTTT、Automate.io などです。実際に解決したい問題に応じて、適切なサービスを選択する必要があります。

無料でオープンソースのブラウザベースRPAツールをおすすめします: Automa 。

また広義には、人と人や人と事の能動的な依存を、人とプラットフォーム、事とプラットフォームの依存に変えることも一種のRPAです。

例えば：AsanaやJiraなどのプロジェクト管理ツールを統一して作業を管理する。

能動的から受動的への考え方に基づき、もともと手動で新しい通知があるか確認していたサービスも、変化があった際に自動で通知するRPAを実現できます。

例えば、以前実装した Gmail to Slack は特定の通知メールを作業グループに転送します。

Robotic Process Automation の効果評価

以前「2021 Pinkoi Tech Career Talk — 高効率エンジニアチームの秘密」で、積み重ね効果とフローの中断コストについて共有しました。例えば、ルーチンの繰り返し作業が1回15分かかり、週に10回発生すると、年間で約130時間を浪費します。さらに「フロー切り替え」コストを加えると、最終的に年間で約200時間の浪費になる可能性があります。

2021 Pinkoi Tech Career Talk — 高効率エンジニアチームの秘密

心の切り替えとは、重要なことに集中している最中に一時的に他のことを処理し、その後再び元の状態に戻るまでにかかる時間のことを指します。

開発するRPAの効果評価は以下の図を参考にしてください。開発にかかる時間と発生頻度が、無駄にしている時間を上回る場合は、リソースを投入して実現する価値があります：

https://twitter.com/swyx/status/1196401158744502272

• X軸：タスク発生頻度 例：50/日（1日に50回）

• Y軸：タスク完了に必要な工数時間

• 過去5年間の時間範囲で取得し、表の中央に5年間で浪費された時間と人件費を表示する

• 白色は自動化にかかる時間コストが効果を上回るため、改善の価値がありません。

• 緑色は自動化に適した項目を示しています

• 赤色は自動化を強く推奨します

時間を節約するだけでなく、自動化された定型プロセスはヒューマンエラーの可能性も減らし、安定性を高めます。

Robotic Process Automation と AI の関係

AIの台頭に伴い、RPAもよく話題になりますが、私はRPAとAIは直接の関係はないと考えています。AIのない時代からすでにRPAは存在しており、企業がAIを導入する効果は、RPAを完成させる効果ほど高くないかもしれません。RPAはむしろ企業文化や働き方の問題が大きいです。しかし、AIがRPAを次のレベルに進化させるのは否定できません。例えば、従来のRPAは確定的で定型的な作業しかできませんでしたが、AIがあれば曖昧で動的な判断を要する作業も可能になります。

Google Workspace におけるロボティック・プロセス・オートメーション (RPA)

Google Workspace（旧称 G Suite）は、私たちの日常業務協力の良きパートナーです。メールは Gmail で管理し、ドキュメントは Google ドキュメントに保存し、レポートは Google スプレッドシートで作成し、フォームは Google フォームを使用します。これらのサービス間の連携や社内外のシステムとの通信は、RPA を実装して行う必要があります。

しかし、Google は直接的な RPA サービスを提供していませんが、以下のいくつかのサービスを通じて実現できます：

• No Code： App Sheet（有料サービス）で、非開発者がGUIを使ってサービス連携の自動化を直接構築できます。

• Low Code： Google Apps Script（無料サービス）、簡単なコードでGoogleサービスや外部／内部システムと素早く直接連携できます。

• Function as a Service： Cloud Functions （有料サービスで無料枠あり）、完全なコードやサービスを作成し、Google Cloudを通じてデプロイして直接実行。

No Code プラットフォーム App Sheet は使ったことがありませんが、Cloud Functions と Google Apps Script はかなり経験があります。以下は個人的な使用と選択の経験共有です：

Cloud Functions

• 実行のためのデプロイが必要です

• 複数のプログラミング言語に対応：Node.js、Python、Java、Go、PHP、Ruby…

• サードパーティパッケージの依存関係管理、インストールおよび使用のサポート

• 完全な認証機構をサポート

• 最大実行時間の上限：60分

• 使った分だけ：回数、実行時間、異なるプロセッサ、使用メモリに応じて課金

• コールドスタート問題（長期間呼び出されていない場合、再度呼び出すと初回の応答時間が長くなる）

• Google サービスに直接接続できず、認証/API 認証を経由する必要があります

• 無料プランは以下の通りです
  Cloud Functions は実行時間リソースに対して永久無料プランを提供しており、GB/秒やGHz/秒の割り当て方式が含まれています。200万回の呼び出しに加え、この無料プランでは400,000 GB/秒と200,000 GHz/秒の実行時間、さらに月5 GBのインターネットデータ転送量が提供されます。無料プランの使用量は、上記のレベル1価格に相当する米ドル金額で計算されます。関数の実行リージョンがレベル1および/またはレベル2価格を採用している場合でも、同等の米ドル金額が割り当てられます。ただし、無料プランの割引適用時は、関数実行リージョンのレベル（レベル1またはレベル2）が基準となります。
  ご注意ください。無料プランを利用する場合でも、有効な請求アカウントが必要です。

まとめると、Cloud Functions はより完全で複雑な RPA 連携機能や、多くの外部 API 連携が必要な場合におすすめです。

以前に Cloud Functions を使用した事例には以下があります：

• [Slack ChatGPT ボット]： Slack & ChatGPT Integration

• [自動チェックインロボット]： Python+Google Cloud Platform+Line Botを使った定期作業の自動化

すべて Google Workspace サービスと連携し、かつ他の外部サービスと橋渡しが必要な場合に使用します。

Google Apps Script

• 便利で簡単かつ迅速

• 完全無料

• サービス連携に複雑な認証は不要
  (Google Apps Script は実行中のアカウントをそのまま実行権限として使用します)

• 組み込みのスケジュールおよびカレンダートリガー機能

• Google のウェブでネットワークリクエストを実行可能

• Google Apps Script（JavaScript ベース）のみで開発可能

• パッケージ管理ツール非対応、バージョン管理機能なし

• セキュリティ上の理由で、Request User-Agent 情報のカスタマイズはできません

• 実行時間の上限は6分で、それを超えるとスクリプトは強制終了されます。

• その他の制限やクォータについては、公式の GAS 情報 をご参照ください。

以前に Google Apps Script を使った事例は以下の通りです：

• [Slack x Google Form x Google Sheet 連携]： Slackで完全自動のWFH従業員健康状況報告システムを構築

• [Slack と Gmail の連携]： Google Apps Script を使って Gmail メールを Slack に転送する方法

• [Google Analytics と Slack の連携]： Crashlytics + Google Analytics でアプリのクラッシュフリー率を自動取得

• [Firebase Crashlytics x Big Query x Slack の連携]： Crashlytics + Big Query でよりリアルタイムで便利なクラッシュ追跡ツールを作る

• [GithubとLineBotの連携]： Google Apps Scriptを使った3ステップで無料のGithubリポジトリスター通知システムの作成

実行時間やAPIリクエストのカスタマイズ制限があるため、簡単で迅速なサービスにはGoogle Apps Scriptを使用します。また、Googleサービスとの連携が必要な場合は、Google Apps Scriptを優先的に選びます（Cloud FunctionsではGoogleサービスの認証プロセスを完全に実装する必要があるため）。

Google Apps Scriptによるロボティック・プロセス・オートメーション — 勤務日報（Google Sheet x Google Analytics）

ついに本記事のテーマ、Google Apps Script を使った Google サービスの RPA 自動化の実現に入ります。

背景

製品チームは毎日 Google Analytics のデータを取得し、Google Sheet のデータレポートに記入してチームで傾向を分析します。また、毎日のデータ内容をダッシュボード画面に公開し、全メンバーが現在の状況を把握できるようにします。

同僚は毎日会社に着くと、この作業に約30分かかります。他の仕事がある場合は、この定例作業が終わるまで待つか、日次データの配信が遅れることがあります。

RPAの効果を簡単に見積もる：

• 毎年の消費支出：
  1人 x 30分 x 365日（休日のデータも含む） = 182時間

• 自動化のコスト：
  このケースでは、約1人×5日 = 40時間 が必要です。

したがって、私たちはわずか1週間の開発時間を投入するだけで、データ確認を担当する同僚の作業負担を長期的に軽減でき、彼はより重要な仕事に集中できるようになります。

目標

私たちの目標は、Google Apps Script を使って RPA を構築し、Google Analytics と社内システムのレポート API から毎日のデータを自動で取得し、Google Sheet に入力すること、そして Web UI ダッシュボードを構築することです。

最終効果

• Google Sheet:
  https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit?usp=sharing

• Web GUI URL:
  https://script.google.com/macros/s/AKfycbz2Vk-ikU8DSXjpnLq9r6HNAn3zlNAosvDoItG0cxy0bmItRDSVyEzTdwsL2HyFUz99/exec

データはダミーデータで、デモ用です；2024/04/13以降は特に低いか、ずっと0のままです。私の zhgchg.li のGAは本当に「0」トラフィックだからです Q_Q。

完成すべきタスク

1. Google Apps Script の作成とエディタの使い方

2. 対応する日付のシートを取得または作成する

3. Google Analytics と連携してデータを取得する

4. データの入力

5. スケジュールを設定し、毎日自動実行する

免責事項

記事の説明に合わせて、以下のコードは可能な限り抽象度を下げ、説明を増やしています。ご自身の実際のニーズに応じて書き換えてください。

記事の最後に完全公開された Google Sheet と Google Apps Script を添付しています。手順を一つ一つ実行するのが面倒な場合は、記事末尾のサンプルから直接編集してください。

Step 1. Google Apps Script を作成する

自動化したいレポートで「拡張機能」->「Apps Script」を選択するだけで、Google Sheet レポートと連携する Google Apps Script が自動で作成されます。

また、Google Apps Script のホームページから直接 Google Apps Script を作成することもできますが、その場合は Google Sheet と連携しません。

対応する Google Sheet を操作するのにリンクは必須ではありません。どちらの方法でも作成可能ですが、スクリプトの所有権が誰に帰属するかが異なります。リンクされたレポートの場合はレポートの所有者に、直接作成した場合は作成者に帰属します。所有者は、そのアカウントが退職などで無効になった際にスクリプトが動作しなくなったり削除されるかどうかに影響します。

スクリプトを作成したら、まず上部からスクリプトプロジェクトの名前を変更できます。

Google Apps Script 基礎知識

次のステップでプログラムを書く前に、Google Apps Script の基本知識を補足します。

エディターについて

デフォルトでGoogleサービスのSDKが読み込まれている（特別な読み込みなしで呼び出して使用可能）：

• CalendarApp カレンダー

• DocumentApp Google ドライブ

• FormApp Google フォーム

• SpreadsheetApp Google シート

• GmailApp Gmail

• その他…

1. ファイル：
   複数の .gs ファイルを追加して異なるオブジェクトのコードを整理できます。すべてのファイルは同じ Namespace とライフサイクルで実行されるため、オブジェクト名や変数名が重複すると上書きされる可能性がある点に注意してください。
   .gs スクリプトファイル以外に、Web UI のレンダリングに使う .html の HTML テンプレートファイルも追加可能です。（後ほど説明します）

2. 資料ベース：
   他人が作成したデータベース（a.k.a ライブラリ）は、その Script ID を使って導入できます。もちろん、自分で作成したプログラムもデータベースとして他人に提供可能です。
   また、いくつかの有志が封装したツールも利用できますが、欠点は Google で Script ID を検索するしかなく、公式のデータベースリストは存在しません。
   例：HTML Parser Tool Cheer.io Script ID: 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0

3. サービス：
   GoogleサービスのSDKで、デフォルトで読み込まれていないサービスをここで追加して使用できます。
   例：Google Analytics Data

4. 復元、次の操作

5. 保存 または Control + s

6. 実行 または Control + r
   エラーが発生した場合は、コンソールに直接表示してスクリプトを停止します

7. 偵錯
   Break point (10) の場所に到達すると実行が一時停止し、右側のデバッグビューが表示されます。続行ボタンで実行を再開できます。
   エラーが発生すると実行が一時停止し、右側のデバッグビューが表示されます。

8. デバッグおよび実行の対象メソッド（Function Name）
   現在選択しているファイルに存在するメソッドのみ選択可能。

9. エディタの実行ログを確認する

もう一点はレイアウトについてです。一部のブラウザで「Control + [」を押してインデントを縮小すると、前のページに戻る動作が発生するので注意してください！

Google Apps Script GitHub アシスタント Chrome 拡張版 コントロールプラグイン

• この Extension のインストールをおすすめします。これにより Google Apps Script と git が連携し、バージョン管理機能が使えるため、誤ってコードを壊すのを防げます。

• Push/Pull エラーが発生したり、クリックしても反応がない場合は、まず上記の手順「オプション」-> Github に接続 または Google 認証を再度行ってください。

Logger メッセージ

以下のスクリプトを使用してデバッグすると、下のコンソールにデバッグログが表示されます。

Logger.log("Hi")

実行記録、エラー情報

編集画面で実行した際のログやエラーは直接表示されますが、実行履歴や自動実行時のエラーを確認したい場合は、「実行項目」タブで確認できます。

自動トリガー

「トリガー」タブでは、スクリプト内の関数を自動で実行する条件を設定できます。設定可能な自動トリガー条件は以下の通りです：

• Google Sheet：開いたとき、編集したとき、内容が変更されたとき、フォームが送信されたときに実行

• スケジュール周期トリガー：毎X分、X時間、X日、X週、Xヶ月ごとに実行

• 特定の日付トリガー：YYYY-MM-DD HH:MM に実行

• カレンダー：更新時に実行

エラー通知設定では、スクリプト実行時にエラーが発生した場合の通知方法を設定できます。

実行権限を付与するユーザー

初回実行やデプロイ、または新しく追加したサービスやリソースは、再度認証が必要です。認証後の実行は認証したアカウントの権限で行われるため、そのアカウント（通常は現在のアカウント）が該当リソースやサービスの権限を持っていることを確認してください。（例：該当の Google Sheet の権限が必要）

アカウント選択のポップアップが表示された後、実行を許可するアカウントを選択します（通常は現在の Google Apps Script アカウントを選択します）：

「Google hasn’t verified this app」と表示されるのは、開発するアプリが自分用であり、Googleの認証を受ける必要がないためです。

「Advanced」→「XXX（安全ではありません）に移動」→「許可」をクリックしてください：

認証が完了するとスクリプトを正常に実行できます。リソースに変更がなければ再認証は不要です。

2. 対応する日付のシートを取得／作成する

基礎知識を理解したら、最初の機能のプログラムを書き始めましょう。

以下の複数のファイルを作成し、異なるオブジェクトを保存しやすくします。

DailyReportStyle.gs 列スタイルオブジェクト：

class HeaderStyle {
  constructor() {
    this.color = "#ffffff"; // 文字色
    this.backgroundColor = "#e3284b"; // 背景色
    this.bold = false; // 太字かどうか
    this.size = 12; // フォントサイズ
    this.horizontalAlignment = "center"; // 水平方向の配置
    this.verticalAlignment = "middle"; // 垂直方向の配置
  }
}

class ContentStyle {
  constructor() {
    this.color = "#000000"; // 文字色
    this.backgroundColor = "#ffffff"; // 背景色
    this.bold = false; // 太字かどうか
    this.size = 12; // フォントサイズ
    this.horizontalAlignment = "center"; // 水平方向の配置
    this.verticalAlignment = "middle"; // 垂直方向の配置
  }
}

class HeaderDateStyle {
  constructor() {
    this.color = "#ffffff"; // 文字色
    this.backgroundColor = "#001a40"; // 背景色
    this.bold = true; // 太字かどうか
    this.size = 12; // フォントサイズ
    this.horizontalAlignment = "center"; // 水平方向の配置
    this.verticalAlignment = "middle"; // 垂直方向の配置
  }
}

DailyReportField.gs フィールドデータオブジェクト：

class DailyReportField {
  constructor(name, headerStyle, contentStyle, format = null, value = null) {
    this.name = name;
    this.headerStyle = headerStyle;
    this.contentStyle = contentStyle;
    this.format = format;
    this.value = value;
  }
}

DailyReport.gs レポートメインプログラムのロジック：

class DailyReport {
  constructor(sheetID, date) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.date = date;

    this.sheetFields = [
      new DailyReportField("日付", new HeaderDateStyle(), new HeaderDateStyle()),
      new DailyReportField("曜日", new HeaderDateStyle(), new HeaderDateStyle()),
      new DailyReportField("日流量", new HeaderStyle(), new ContentStyle(), "#,##0", '=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&4)+INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&5)'), // =4(PC 流量) + 5(Mobile 流量)
      new DailyReportField("PC 流量", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("Mobile 流量", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("登録数", new HeaderStyle(), new ContentStyle(), "#,##0")
    ]

    // 日流量の数式分解説明：
    // 1. COLUMN() 関数は現在のセルの列番号を返します。
    // 2. ADDRESS(1, COLUMN(), 4) は指定された行番号（`COLUMN()` の結果）と固定の列番号（1）から絶対参照のアドレスを生成します。第三引数の 4 はドル記号なしの相対アドレスを意味します。例えば、3列目のセルで使うと "C1" を返します。
    // 3. SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") は ADDRESS 関数で生成されたアドレスから数字の 1 を取り除き、列のアルファベットだけを残します。例："C"。
    // 4. INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 4) は &4（正しくは &4）で文字列を連結し、"C4" のような文字列を作り、INDIRECT 関数でセル参照に変換します。したがって、列 C のセルでこの数式を使うと C4 を参照します。
    // 5. 同様に、INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 5) は同じ列の5行目のセルを参照します。列 C なら C5。
    // 6. 最後に2つの INDIRECT 関数の参照セルの値を加算します。
  }

  execute() {
    const sheet = this.getSheet();

  }

  // 指定した日付の対象シートを取得
  getSheet() {
    // 月単位でシートを区別し、当月のシートを探す
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // なければ新しい月シートを作成
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // 月シートの命名規則
  getSheetName() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM");
  }

  // 新しい月シートを作成
  makeMonthSheet() {
    // 当月のシートを追加し、最初の位置に移動
    var thisMonthSheet = this.separateSheet.insertSheet(this.getSheetName(), {index: 0});
    thisMonthSheet.activate();
    this.separateSheet.moveActiveSheet(1);

    // 1列目を追加、列名設定、固定、幅200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // 列名を入力
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const text = sheetField.name;
      const style = sheetField.headerStyle;
      
      const range = thisMonthSheet.getRange(parseInt(currentRow) + 1, 1);
      this.setContent(range, text, style);
      range.setHorizontalAlignment("left");
    }

    // 行の高さを設定
    thisMonthSheet.setRowHeights(1, Object.keys(this.sheetFields).length, 30);

    // 1行目と2行目（日時、曜日）を固定
    thisMonthSheet.setFrozenRows(2);

    // 合計列を追加
    thisMonthSheet.insertColumnsAfter(thisMonthSheet.getLastColumn(), 1); // 最後の列の後に1列追加
    const summaryColumnIndex = thisMonthSheet.getLastColumn() + 1;

    // 合計列に値を入力
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const summaryRowIndex = parseInt(currentRow) + 1;

      const range = thisMonthSheet.getRange(summaryRowIndex, summaryColumnIndex);
      const style = sheetField.contentStyle;

      if (summaryRowIndex == 1) {
        // 日付...
        this.setContent(range, "合計", style);
      } else if (summaryRowIndex == 2) {
        // 曜日...結合...
        const mergeRange = thisMonthSheet.getRange(1, summaryColumnIndex, summaryRowIndex, 1);
        this.setContent(mergeRange, "合計", style);
        mergeRange.merge();
      } else {
        this.setContent(range, '=IFERROR(SUM(INDIRECT(SUBSTITUTE(ADDRESS(1, 1, 4), "1", "") & '+summaryRowIndex+'):INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN() - 1, 4), "1", "") & '+summaryRowIndex+')), 0)', style);

        // 1. IFERROR(value, [value_if_error]) 関数は式にエラーがあるかを判定し、エラー時に指定の値を返します。ここでは SUM の計算でエラーがあれば 0 を返します。
        // 2. SUM(range) 関数は範囲内の数値の合計を計算します。
        // 3. INDIRECT(ref_text, [is_A1_notation]) 関数は文字列をセル参照に変換します。ここでは動的に参照範囲を生成するために使います。
        // 4. SUBSTITUTE(text, old_text, new_text, [instance_num]) 関数は文字列内の指定文字を置換します。ここでは ADDRESS 関数の結果から "1" を置換しています。
        // 5. ADDRESS(row, column, [abs_num], [a1], [sheet]) 関数は指定した行列番号からセルアドレスを返します。ADDRESS(1, 1, 4) は1行1列のセルアドレスをドル記号なしで返します。同様に ADDRESS(1, COLUMN() - 1, 4) は現在列の1つ前の列のセルアドレスを返します。
        // 6. COLUMN() 関数は現在のセルの列番号を返します。
        // 7. summaryRowIndex = 対象の行番号     

      }
    }

    return thisMonthSheet;
  }

  setContent(range, text, style) {
    if (String(text) != "") {
      range.setValue(text);
    }

    range.setBackgroundColor(style.backgroundColor);
    range.setFontColor(style.color);

    if (style.bold) {
      range.setFontWeight("bold");
    }

    range.setHorizontalAlignment(style.horizontalAlignment);
    range.setVerticalAlignment(style.verticalAlignment);
    range.setFontSize(style.size);
    range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  }
}

Main.gs をメインプログラムのエントリーポイントとして：

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE"
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

function debug() {
  var report = new DailyReport(targetGoogleSheetID, new Date());
  report.execute(); // レポートを実行する
}

完了したら「 Main.gs 」に戻り、「デバッグ」を選択してデバッグを開始し、実行結果が正しいか、エラーがないかを確認します。

正しく実行すると、レポートに現在の新しい月が表示され、デフォルトの列と合計列が入力されます。すでに存在する場合は何も反応しません。

3. Google Analytics と連携してデータを取得する

まず「AnalyticsData」サービスを追加する必要があります：

GA4 Debug Tool を使ってクエリ条件を構築する：

• https://ga-dev-tools.google/ga4/query-explorer/

• GA 4の管理画面のフィルタ条件を参考に構築可能

• 本記事ではセッション数の取得例を用いて、デバイスごとのグルーピングを区別します

ログイン認証後に対象リソースを選択：

property の下に表示されている数字が、あなたが検索するリソースの GA Property ID です。

クエリパラメータとフィルター条件の設定：

「Make Request」を押してレスポンス結果を取得：

GA 4の管理画面と同じ条件のデータを同期して比較できます。差異が大きい場合は、フィルター条件が適用されていない可能性があるため、再度確認してください。

小記

マーケティングの同僚が見つけた小さな落とし穴を共有します。GAの一部のデータには遅延があり、今日確認した数値と昨日確認した数値が異なることがあります（例：直帰率）。そのため、データは数日分遡って取得し、最終的な数値を確保するのが望ましいです。

GA Debug Tool に問題がなければ、これを Google Apps Script に変換できます。

新しい GAData.gs ファイルを追加：

// Google Analytics Data APIをサービスに追加することを忘れないでください。そうしないと、ReferenceError: AnalyticsData is not definedというエラーが発生します。
// GAデバッグツール: https://ga-dev-tools.web.app/ga4/query-explorer/

class GAData {
  constructor(date) {
    this.date = date;

    const traffic = this.fetchGADailyUsage();
    this.pc_traffic = traffic["desktop"];
    this.mobile_traffic = traffic["mobile"];
  }

  fetchGADailyUsage() {
    const dimensionPlatform = AnalyticsData.newDimension();
    dimensionPlatform.name = "deviceCategory";

    const metric = AnalyticsData.newMetric();
    metric.name = "sessions";

    const dateRange = AnalyticsData.newDateRange();
    // 指定した日の範囲内のデータをデフォルトで取得 e.g. 2024-01-01 ~ 2024-01-01
    dateRange.startDate = this.getDateString();
    dateRange.endDate = this.getDateString();

    // フィルター例:
    // const filterExpression = AnalyticsData.newFilterExpression();
    // const filter = AnalyticsData.newFilter();
    // filter.fieldName = "landingPagePlusQueryString";
    // const stringFilter = AnalyticsData.newStringFilter()
    // stringFilter.value = "/life\\|/article\\|/chat\\|/house\\|/event/230502\\|/event/230310";
    // stringFilter.matchType = "PARTIAL_REGEXP";
    // filter.stringFilter = stringFilter;
    // filterExpression.filter = filter;

    const request = AnalyticsData.newRunReportRequest();
    request.dimensions = [dimensionPlatform];
    request.metrics = [metric];
    request.dateRanges = dateRange;

    // フィルター例:
    // const filterExpression = AnalyticsData.newFilterExpression();
    // filterExpression.expression = filterExpression;
    // request.dimensionFilter = filterExpression;
    // または
    // const notFilterExpression = AnalyticsData.newFilterExpression();
    // notFilterExpression.notExpression = filterExpression;
    // request.dimensionFilter = notFilterExpression;

    const report = AnalyticsData.Properties.runReport(request, "properties/" + gaPropertyId).rows;
    // データなし
    if (report == undefined) {
      return {"desktop": 0, "mobile": 0};
    }

    // [{metricValues=[{value=4517}], dimensionValues=[{value=mobile}]}, {metricValues=[{value=3189}], dimensionValues=[{value=desktop}]}, {metricValues=[{value=63}], dimensionValues=[{value=tablet}]}]

    var result = {};
    report.forEach(function(element) {
      result[element.dimensionValues[0].value] = element.metricValues[0].value;
    });

    return result;
  }

  getDateString() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM-dd");
  }
}

Main.gs にテスト内容を追加：

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  var report = new DailyReport(targetGoogleSheetID, new Date());
  report.execute();
  //
  var gaData = new GAData(new Date());
  Logger.log(gaData);
}

実行またはデバッグを押してプログラムの取得結果を得る：

OK! 照合しました。

このステップが完了すると、ディレクトリとファイルの構造は上図のようになります。

4. データの入力

シートを作成し、データを確認したら、次はデータを各列に入力します。

DailyReport.gs を調整し、日付ごとの新しい列追加とデータ入力のロジックを追加：

class DailyReport {
  constructor(sheetID, date, gaData, inHouseReportData) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.date = date;

    const dateString = Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd");
    const weekString = ["日曜日", "月曜日","火曜日","水曜日","木曜日","金曜日","土曜日"][date.getDay()]; // 曜日を取得、日曜日が0、月曜日が1、以下同様

    this.sheetFields = [
      new DailyReportField("日付", new HeaderDateStyle(), new HeaderDateStyle(), null, dateString),
      new DailyReportField("曜日", new HeaderDateStyle(), new HeaderDateStyle(), null, weekString),
      new DailyReportField("日間トラフィック", new HeaderStyle(), new ContentStyle(), "#,##0", '=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&4)+INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&5)'), // =4(PC トラフィック) + 5(Mobile トラフィック)
      new DailyReportField("PC トラフィック", new HeaderStyle(), new ContentStyle(), "#,##0", gaData.pc_traffic),
      new DailyReportField("モバイルトラフィック", new HeaderStyle(), new ContentStyle(), "#,##0", gaData.mobile_traffic),
      new DailyReportField("登録数", new HeaderStyle(), new ContentStyle(), "#,##0", inHouseReportData.registers)
    ]
  }

  execute() {
    const sheet = this.getSheet();
    const dateColumnIndex = this.makeOrGetDateColumn(sheet); // 既存の列を取得または新しい列を作成

    // 列に内容を入力
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const rowIndex = parseInt(currentRow) + 1;

      if (rowIndex != null) {
        const range = sheet.getRange(rowIndex, dateColumnIndex);
        const text = sheetField.value;
        const style = sheetField.contentStyle;
        this.setContent(range, text, style);
        this.setFormat(range, sheetField.format);          
      }
    }
  }

  // 指定した日付の対象シートを取得
  getSheet() {
    // 月別にシートを区別し、当月のシートを取得
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // なければ新しい月のシートを作成
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // 月シートの名前を取得
  getSheetName() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM");
  }

  // 新しい月シートを作成
  makeMonthSheet() {
    // 当月のシートを追加し、最初の位置に移動
    var thisMonthSheet = this.separateSheet.insertSheet(this.getSheetName(), {index: 0});
    thisMonthSheet.activate();
    this.separateSheet.moveActiveSheet(1);

    // 最初の列を追加し、列名を設定、固定し幅を200に設定
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // 列名を入力
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const text = sheetField.name;
      const style = sheetField.headerStyle;
      
      const range = thisMonthSheet.getRange(parseInt(currentRow) + 1, 1);
      this.setContent(range, text, style);
      range.setHorizontalAlignment("left");
    }

    // 行の高さを設定
    thisMonthSheet.setRowHeights(1, Object.keys(this.sheetFields).length, 30);

    // 1行目と2行目（日時、曜日）を固定
    thisMonthSheet.setFrozenRows(2);

    // 合計列を追加
    thisMonthSheet.insertColumnsAfter(thisMonthSheet.getLastColumn(), 1); // 最後の列の後に1列追加
    const summaryColumnIndex = thisMonthSheet.getLastColumn() + 1;

    // 合計列に内容を入力
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const summaryRowIndex = parseInt(currentRow) + 1;

      const range = thisMonthSheet.getRange(summaryRowIndex, summaryColumnIndex);
      const style = sheetField.contentStyle;

      if (summaryRowIndex == 1) {
        // 日付...
        this.setContent(range, "合計", style);
      } else if (summaryRowIndex == 2) {
        // 曜日...結合...
        const mergeRange = thisMonthSheet.getRange(1, summaryColumnIndex, summaryRowIndex, 1);
        this.setContent(mergeRange, "合計", style);
        mergeRange.merge();
      } else {
        this.setContent(range, '=IFERROR(SUM(INDIRECT(SUBSTITUTE(ADDRESS(1, 1, 4), "1", "") & '+summaryRowIndex+'):INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN() - 1, 4), "1", "") & '+summaryRowIndex+')), 0)', style);
      }
    }

    return thisMonthSheet;
  }

  // 日付の列を作成または取得
  // 直近の日付から1列追加
  makeOrGetDateColumn(sheet) {
    const firstRowColumnsRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // 1行目（日時）の範囲を取得
    const firstRowColumns = firstRowColumnsRange.getValues()[0]; // 範囲の値を取得 0 = 1行目
    
    var columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(this.date, "GMT+8", "yyyy/MM/dd"))); // 対応する日付の列インデックスを検索

    if (columnIndex < 0) {
      // 見つからなければ、前日を探す
      var preDate = new Date(this.date);
      preDate.setDate(preDate.getDate() - 1);

      while(preDate.getMonth() == this.date.getMonth()) {
        columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(preDate, "GMT+8", "yyyy/MM/dd")));
        if (columnIndex >= 0) {
          break;
        }

        preDate.setDate(preDate.getDate() - 1);
      }

      if (columnIndex >= 0) {
        columnIndex += 1;
        sheet.insertColumnsAfter(columnIndex, 1); // 前日の列の後に1列追加
        columnIndex += 1;
      }
    } else {
      columnIndex += 1;
    }

    if (columnIndex < 0) {
        sheet.insertColumnsAfter(1, 1); // デフォルトで1列目の後に1列追加
        columnIndex = 2;
    } 

    // 列幅を設定
    sheet.setColumnWidths(columnIndex , 1, 100);

    return columnIndex
  }

  // 列のフォーマットを設定
  setFormat(range, format) {
    if (format != null) {
      range.setNumberFormat(format);
    }
  }

  // 内容を列に入力
  setContent(range, text, style) {
    if (String(text) != "") {
      range.setValue(text);
    }

    range.setBackgroundColor(style.backgroundColor);
    range.setFontColor(style.color);

    if (style.bold) {
      range.setFontWeight("bold");
    }

    range.setHorizontalAlignment(style.horizontalAlignment);
    range.setVerticalAlignment(style.verticalAlignment);
    range.setFontSize(style.size);
    range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  }
}

Main.gs を調整してデータ連携を追加し、ビルド段階で値を設定：

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  const date = new Date();
  const gaData = new GAData(date);
  const inHouseReportData = fetchInHouseReportData(date);
  
  const report = new DailyReport(targetGoogleSheetID, date, gaData, inHouseReportData);
  report.execute();
  
}

// 一部のデータは他のプラットフォームのAPIから取得する可能性を模擬しています。
function fetchInHouseReportData(date) {
  // 例のリクエスト:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // または
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // セキュリティ上の理由により、ユーザーエージェントのカスタマイズはできません。

  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // モックデータ 30〜180のランダム値
}

完了したら「Main.gs」に戻り、「デバッグ」を選択してデバッグを開始し、実行結果が正しいか、エラーがないかを確認します。

Google Sheet に戻る！成功です！指定した日のデータを自動で追加することに成功しました。

5. スケジュール設定、毎日自動実行

スクリプトが完成したら、自動トリガーの設定をするだけで、毎日自動的に実行されます。

Main.gs を調整し、cronjob() 関数を追加します:

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  cronjob();
}

// 実際の状況に合わせて、通常は今日に昨日のデータを取得します。それが完全なデータです。
function cronjob() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const gaData = new GAData(yesterday);
  const inHouseReportData = fetchInHouseReportData(yesterday);
  
  const report = new DailyReport(targetGoogleSheetID, yesterday, gaData, inHouseReportData);
  report.execute();
}

// 一部のデータは他のプラットフォームのAPIから取得する場合を想定しています。
function fetchInHouseReportData(date) {
  // 例のリクエスト:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // または
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // セキュリティ上の理由により、ユーザーエージェントはカスタマイズできません。

  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // モックデータ 30〜180のランダム値
}

エディタで「トリガー」タブに切り替え、右下の「トリガーを追加」を選択します：

• 実行したい機能を選択してください：新たに追加された Main.gs の関数 cronjob

• 実行するデプロイ作業を選択：ヘッド（Head、最新版を意味します）

• アクティビティのソースを選択：時間駆動

• 時間ベースのトリガータイプを選択：日付タイマー

• 選択時間帯：午前4時〜午前5時（GMT+08:00）
  通常、午前4時ちょうどに実行されます。

• エラー通知設定：スクリプトでエラーが発生した場合、すぐに通知するか、毎日まとめて通知するか

設定を保存しました。

あとで「実行項目」タブで実行履歴の結果を確認できます：

これで自動化されたクエリ、データ追加、データ入力のレポートRPA機能が完成しました。🎉🎉🎉

Web GUI ダッシュボードの構築

次に、補助的な要件として、毎日のデータを表示するシンプルなWeb画面（戦況センターのようなイメージ）を作成し、チームのバックオフィスの大型スクリーンに直接表示できるようにします。

効果は以下の図の通りです：

Web_DailyReport.gs を新規作成し、Google Sheet を読み込み、列やスタイルを HTML 形式で表示するコードを書く：

class WebDailyReport {
  constructor(sheetID, dayCount) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.dayCount = dayCount;
    this.sheetRows = [
      "日付",
      "曜日",
      "日間トラフィック",
      "PCトラフィック",
      "モバイルトラフィック",
      "登録数"
    ];
  }

  allData(startDate) {
    var sheetRowsIndexs = {};
    var count = this.dayCount;
    var result = [];
    while (count >= 0) {
      const preDate = new Date(startDate);
      preDate.setDate(preDate.getDate() - (this.dayCount - count));
      const sheetName = Utilities.formatDate(preDate, "GMT+8", "yyyy-MM");
      const targetSheet = this.separateSheet.getSheetByName(sheetName);
      if (targetSheet != null) {
        const firstRowColumnsRange = targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()); // 1行目（日付）のデータ範囲を取得
        const firstRowColumns = firstRowColumnsRange.getValues()[0]; // データ範囲の値を取得 0 = 1行目
        var columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(preDate, "GMT+8", "yyyy/MM/dd"))); // 対応する日付の列インデックスを検索
        
        if (columnIndex >= 0) {
          columnIndex = parseInt(columnIndex) + 1;
          if (sheetRowsIndexs[sheetName] == undefined \\|\\| sheetRowsIndexs[sheetName] == null) {
            sheetRowsIndexs[sheetName] = this.sheetRows.map((sheetRow) => this.getFieldRow(targetSheet, sheetRow));
          }

          if (result.length == 0) {
            // 最初の列を追加
            const ranges = sheetRowsIndexs[sheetName].map((rowIndex) => (rowIndex != null) ? (targetSheet.getRange(rowIndex, 1)) : (null));
            result.push(this.makeValues(ranges));
          }

          const ranges = sheetRowsIndexs[sheetName].map((rowIndex) => (rowIndex != null) ? (targetSheet.getRange(rowIndex, columnIndex)) : (null));
          result.push(this.makeValues(ranges));
        }
      }

      count -= 1;
    }

    var transformResult = {};
    for (const columnIndex in result) {
      for (const rowIndex in result[columnIndex]) {
        if (transformResult[rowIndex] == undefined) {
          transformResult[rowIndex] = [];
        }

        if (columnIndex == 0) {
          transformResult[rowIndex].unshift(result[columnIndex][rowIndex]);
        } else {
          transformResult[rowIndex].splice(1, 0, result[columnIndex][rowIndex]);
        }
        
      }
    }

    return transformResult;
  }

  // フィールド属性の値を表示用オブジェクトに変換
  makeValues(ranges) {
    const data = ranges.map((range) => (range != null) ? (range.getDisplayValues()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const backgroundColors = ranges.map((range) => (range != null) ? (range.getBackgrounds()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const colors = ranges.map((range) => (range != null) ? (range.getFontColorObjects()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const sizes = ranges.map((range) => (range != null) ? (range.getFontSizes()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const bolds = ranges.map((range) => (range != null) ? (range.getFontWeights()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const horizontalAlignments = ranges.map((range) => (range != null) ? (range.getHorizontalAlignments()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const verticalAlignments = ranges.map((range) => (range != null) ? (range.getVerticalAlignments()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));

    var result = [];
    for(const index in data) {
        const row = data[index];
        result.push({
          "value": row,
          "backgroundColor": backgroundColors[index],
          "color": this.colorStripper(colors[index]?.asRgbColor()?.asHexString()),
          "size": sizes[index],
          "bold": bolds[index],
          "horizontalAlignment": this.alignConventer(horizontalAlignments[index]),
          "verticalAlignment": verticalAlignments[index]
        });
    }

    return result;
  }

  colorStripper(colorString) {
    if (colorString == undefined \\|\\| colorString == null) {
      return null
    }

    if (colorString.length == 9) {
      return "#"+colorString.substring(3, 9);
    } else {
      return colorString;
    }
  }

  alignConventer(horizontalAlignment) {
    if (horizontalAlignment == undefined \\|\\| horizontalAlignment == null) {
      return null
    }

    return horizontalAlignment.replace('general-', '')
  }

  getFieldRow(sheet, name) {
    const firstColumnRowsRange = sheet.getRange(1, 1, sheet.getLastRow(), 1); // 1列目（フィールド）のデータ範囲を取得
    const firstColumnRows = firstColumnRowsRange.getValues(); // データ範囲の値を取得
    const foundIndex = firstColumnRows.findIndex((firstColumnRow) => firstColumnRow[0] == name);

    if (foundIndex < 0) {
      return null;
    } else {
      return foundIndex + 1;
    }
  }
}

Main.gs Webリクエストハンドルの追加：

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  cronjob();
}

function cronjob() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const gaData = new GAData(yesterday);
  const inHouseReportData = fetchInHouseReportData(yesterday);
  
  const report = new DailyReport(targetGoogleSheetID, yesterday, gaData, inHouseReportData);
  report.execute();
}

function doGet(e) {
  return HtmlService.createTemplateFromFile('Web_DailyReport_ Scaffolding').evaluate();
}

function getDailyReportBody() {
  const html = HtmlService.createTemplateFromFile('Web_DailyReport_Body').evaluate().getContent();
  return html;
}

// FOR POST
// function doPost(e) {
//  ref: https://developers.google.com/apps-script/guides/web?hl=zh-tw
// }


// 一部のデータは他のプラットフォームのAPIから取得した可能性があります。
function fetchInHouseReportData(date) {
  // 例のリクエスト:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // または
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // セキュリティ上の理由により、ユーザーエージェントのカスタマイズはできません。
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // モックデータ 30〜180のランダム値
}

Web_DailyReport_ Scaffolding.html Webダッシュボードの骨組みを追加しました。戦情室の画面で内容を自動更新する必要があるため、Ajaxを使って定期的にHTMLコンテンツを取得するWeb骨組みを作成しました：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      function onSuccess(html) {
        if (html != null) {
          var div = document.getElementById('result');
          div.innerHTML = html;
        }
     }
     setInterval(()=>{
       google.script.run.withSuccessHandler(onSuccess).getDailyReportBody()
     }, 1000 * 60 * 60 * 1); // 1時間ごとに実行
     google.script.run.withSuccessHandler(onSuccess).getDailyReportBody(); // 初回実行
    </script>
  </head>
  <body>
    <div id="result">読み込み中...</div>
  </body>
</html>

新規作成 Web_DailyReport_Body.html 実際にデータをレンダリングしてHTMLを生成する部分：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
    table {
        border-collapse: collapse;
        width: 100%;
        text-align: center;
    }
    th, td {
        border: 1px solid #000000;
        padding: 8px;
        text-align: center;
        font-size: 36px;
    }
      </style>
  </head>
  <body>
      <h1 style="text-align:center">ZHGCHG.LI</h1>
      <table id="dataTable">
        <tbody>
          <?
          // 過去7日間のデータを表示
          const dashboard = new WebDailyReport(targetGoogleSheetID, 7);
          // 昨日から開始
          const yesterday = new Date();
          yesterday.setDate(yesterday.getDate() - 1);
          const data = dashboard.allData(yesterday);
          for(const rowIndex in data) {
            const row = data[rowIndex];
            ?>
            <tr>
              <?
              for(const columnIndex in row) {
                const column = row[columnIndex];
                ?>
                <td style="background-color: <?=column["backgroundColor"]?>; color: <?=column["color"]?>; text-align: <?=column["horizontalAlignment"]?>;">
                  <?=column["value"]?>
                </td>
                <?
              }
              ?>
            </tr>
            <?
          }
          ?>
        </tbody>
      </table>
      <script>
  </body>
</html>

ご注意ください、昨日から遡って7日間のデータを比較しており、本日のデータは表示されません。

以上の手順を完了した後のプロジェクトファイル構成は以下の通りです：

テストデプロイ：

プロジェクト右上の「デプロイ」->「テストデプロイ作業」をクリックしてください

• デプロイ完了後、リンクをクリックしてテスト結果を確認できます。

• このURLは一度限りのテスト用です。コードを変更した場合は、再度テストデプロイを実行してください。

Loading…で止まったり、サーバーエラーが発生した場合は、エディタの「実行項目」タブに戻ってエラーメッセージを確認してください：

正式なデプロイの完了：

テストに問題がなければ、本番環境へのデプロイを完了し、URLを公開します。

プロジェクト右上の「デプロイ」->「新しいデプロイを作成」-> 左上の「タイプを選択」->「ウェブアプリ」：

• 実行ユーザー：デフォルトは現在のアカウント（Google Apps Scriptのユーザーと同じ）

• アクセス可能なユーザー：リンクを知っている全員に公開することも、組織内のみアクセス可能にすることもでき、後者の場合はアクセス時にGoogleログインが必要です。

• デプロイ完了、URLを取得しました。

コードを変更した場合は、再デプロイが必要です：

コードに変更があった場合は、再デプロイが必要です（URLは変わりません）。変更を反映させるためには再デプロイしないと、常に古い状態のままになります。

プロジェクト右上の「デプロイ」->「デプロイ管理」をクリック：

右上の「ペン🖊️アイコン」→「バージョン」→「新しいバージョンを作成」→「デプロイ」をクリックしてください。

デプロイ完了後、URLをクリックするか、元のURLを再読み込みすると、新しい変更結果が表示されます。

🎉🎉完了！これで私たちのすべてのRPA要件が完了しました。🎉🎉

最終成果：

(プログラムを修正して今月のデータを補完、さもなければ新規データでは昨日の分しかない)

https://script.google.com/macros/s/AKfycbz2Vk-ikU8DSXjpnLq9r6HNAn3zlNAosvDoItG0cxy0bmItRDSVyEzTdwsL2HyFUz99/exec

完全な Google Sheet デモ：

• Google Sheet:
  https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit?usp=sharing

• Web GUI URL:
  https://script.google.com/macros/s/AKfycbz2Vk-ikU8DSXjpnLq9r6HNAn3zlNAosvDoItG0cxy0bmItRDSVyEzTdwsL2HyFUz99/exec

• Google Apps Script:
  https://script.google.com/home/projects/1vHgXPTV_q8MC75FVzAEtzD2JPVnPBpMfFXbjZR7SLMVjoEH1FcjKlo8l/edit

最後に、他の日常生活での応用例：

Google Apps Scriptによるロボティック・プロセス・オートメーション — Githubリポジトリスター通知をLineへ

Google Apps Scriptによるロボティック・プロセス・オートメーション — Notionデータベースからカレンダーへ

以前に簡単に実装した Notion to Calendar 機能。

実装方法は、Notion API を使ってデータベースのデータを取得し、ICS形式のウェブページを生成して公開ウェブとしてデプロイします；このURLを Apple カレンダーに追加するだけです。

Main.gs :

// 定数変数
const notionToken = "XXXXX";
const safeToken = "XXXXX";

function doGet(e) {
  const ics = HtmlService.createTemplateFromFile('ics');

  if (e.parameter.token != safeToken) {
    return ContentService.createTextOutput("アクセス拒否！");
  }

  ics.events = getQuickNote();
  
  return ContentService.createTextOutput(ics.evaluate().getContent()).setMimeType(ContentService.MimeType.ICAL);
}

function debug() {
  const ics = HtmlService.createTemplateFromFile('ics');
  ics.events = getQuickNote();
  Logger.log(ics.evaluate().getContent());
}

function getQuickNote() {
  // フィルター条件：
  const payload = {
    "filter": {
      "and": [
        {
          "property": "Date",
            "date": {
            "is_not_empty": true
          }
        }
        ,
        {
          "property": "Name",
            "title": {
            "is_not_empty": true
          }
        }
      ]
    }
  };
  const result = getDatabase(YOUR_DATABASE_ID, payload);
  var events = [];
  for (const index in result.results) {
    const item = result.results[index]
    const properties = item.properties;

    const id = item['id'];
    const create = toICSDate(item["created_time"]);
    const edit = toICSDate(item["last_edited_time"]);
    const startDate = properties['Date']['date']['start'];
    const start = toICSDate(startDate);
    var endDate = properties['Date']?.['date']?.['end'];
    if (endDate == null) {
      endDate = startDate;
    }
    const end = toICSDate(endDate);
    const type = properties['Type']?.['multi_select']?.[0]?.['name'];

    const title = "["+type+"] "+properties?.['Name']?.['title']?.[0]?.['plain_text'];
    const description = item['url'];
    
    events.push(
      {
        "id":id,
        "create":create,
        "edit":edit,
        "start":start,
        "end":end,
        "title":title,
        "description":description
      }
    )
  }
  return events;
}
// UTC日時に変換
function toICSDate(date) {
  const icsDate = new Date(date);
  icsDate.setHours(icsDate.getHours() - 8);
  return Utilities.formatDate(icsDate, "GMT+8", "yyyyMMdd'T'HHmmss'Z'");// 20240304T132300Z
}

// Notion API呼び出し
function getDatabase(id, payload) {
  const url = 'https://api.notion.com/v1/databases/'+id+'/query/';
  const options = {
    method: 'post',
    headers: {
      'Authorization': 'Bearer '+notionToken,
      'Content-Type': 'application/json',
      'Notion-Version': '2022-06-28'
    },
    payload: JSON.stringify(payload)
  }; 
  const result = UrlFetchApp.fetch(url, options);
  return JSON.parse(result.getContentText());
}

ics.html :

BEGIN:VCALENDAR
PRODID:-//Google Inc//Google Calendar 70.9054//EN
VERSION:2.0
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:NotionCalendar
X-WR-TIMEZONE:Asia/Taipei
BEGIN:VTIMEZONE
TZID:Asia/Taipei
X-LIC-LOCATION:Asia/Taipei
BEGIN:STANDARD
TZOFFSETFROM:+0800
TZOFFSETTO:+0800
TZNAME:CST
DTSTART:19700101T000000
END:STANDARD
END:VTIMEZONE
<?
  for(const eventIndex in events) {
    const event = events[eventIndex];
    ?>
BEGIN:VEVENT
DTSTART:<?=event["start"]?>

DTEND:<?=event["end"]?>

DTSTAMP:<?=event["edit"]?>

UID:<?=event["id"]?>

CREATED:<?=event["create"]?>

LAST-MODIFIED:<?=event["edit"]?>

SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:<?=event["title"]?>

DESCRIPTION:<?=event["description"]?>

TRANSP:OPAQUE
END:VEVENT
<?
  }
?>
END:VCALENDAR

前述と同様に、ウェブサービスとしてデプロイするには、プロジェクト右上の「デプロイ」→「新しいデプロイ」→ 左上の「種類を選択」→「ウェブアプリケーション」をクリックします：

• 誰でもアクセスできるようにしてください。カレンダーを追加する際にGoogleログイン認証ができないためです。

カレンダーの購読にURLを追加完了 🎉🎉🎉🎉 ！

企業タイム

もしあなたやあなたのチームが自動化ツールやプロセス連携のニーズをお持ちであれば、Slackアプリ開発、Notion、Asana、Google Sheet、Google Form、GAデータなど、あらゆる連携のご要望に対応いたします。ぜひ開発相談からご連絡ください。

ご質問やご意見がございましたら、お問い合わせ ください。

Post は ZMediumToMarkdown によって Medium から変換されました。

---

🍺 Buy me a beer on PayPal

👉👉👉 Follow Me On Medium! (1,053+ Followers) 👈👈👈

本記事は Medium にて初公開されました（こちらからオリジナル版を確認）。ZMediumToMarkdown による自動変換・同期技術を使用しています。

Improve this page on Github.