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

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

Google Workspaceのサービスを活用し、Google Apps Scriptで毎日のデータ報告を自動化。手作業のミスを削減し、業務効率を大幅に改善する方法を解説します。

by ZhgChgLi 2024-04-14 113 分 660+ 閲覧

Authentic Writing

Written by human not AI

本記事は AI による翻訳です。お気づきの点があればお知らせください。

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

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

Photo by Possessed Photography

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

RPA（Robotic Process Automation）は日本語で「プロセス自動化ロボット」と訳されます。人類の歴史を振り返ると、素手での採集から石器時代、農耕文明へと進み、20世紀の産業革命からここ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年間で浪費した時間と人件費を表示します。

• 白色は、自動化にかかる時間コストが得られる効果を上回るため、改善の価値がないことを示しています。

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

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

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

ロボティック・プロセス・オートメーションとAIの関係

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

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

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分で、スクリプトは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 自動で App のクラッシュフリー率を取得

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

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

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

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

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

背景

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

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

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

• 年間消費支出：
  1人 × 30分 × 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. ライブラリ：
   他人が作成したライブラリ（別名 Lib）は、その 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. デバッグ
   ブレークポイント (10) の箇所に到達すると実行が一時停止し、右側のデバッグビューが表示されます。続行ボタンで実行を再開できます。
   エラーが発生すると実行が一時停止し、右側のデバッグビューが表示されます。

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

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

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

Google Apps Script GitHub Assistant Chrome 拡張版 コントロールプラグイン

• 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(モバイルトラフィック)
      new DailyReportField("PCトラフィック", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("モバイルトラフィック", 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) は SUBSTITUTE の結果に数字の4を連結し、"C4" のような文字列を作成し、INDIRECT 関数でセル参照に変換します。つまり、列Cの4行目のセルを参照します。
    // 5. 同様に INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 5) は同じ列の5行目のセルを参照します。
    // 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]) 関数は指定した行列番号のセルアドレスを返します。abs_num=4 は絶対参照なしの形式です。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の管理画面のフィルター条件を参照しながら構築可能

• 本記事では、Sessions数の照会を例に、デバイスごとのグルーピングを区別します

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

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;
    // または Not
    // 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);

    // 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"))); // 対応する日付の列Indexを検索

    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、最新版を意味）

• アクティビティのトリガーを選択：時間駆動

• 時間型トリガーの種類を選択：日付タイマー

• 選択時間帯：AM 4:00 〜 AM 5:00 (GMT+08:00)
  通常はAM 4:00ちょうどに実行されます。

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

設定を保存しました。

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

ここまでで、自動化によるデータ検索、追加、入力のレポート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);
     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要件が完了しました。🎉🎉

最終成果：

（月間データを補完するためのプログラム修正。そうしないと新規データでは昨日の1件だけになる）

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データベースからカレンダーへ

■■■■■■■■■■■■■■

ZhgChgLi @ Twitter は次のように述べています：

Notionのカレンダーは使いにくく、Appleカレンダーの代わりにはなりません。
そのため、NotionカレンダーとAppleカレンダーの両方を確認する必要があります。

Notion公式は.icsカレンダーのエクスポート機能を提供していないため、Google Apps ScriptのWebアプリとNotion APIを使って自作の.icsを作成し、すべてAppleカレンダーにインポートしています。

実装結果は以下の図の通りです。POC のソースコードはこちらです:
gist.github.com/zhgchgli0718/4… https://t.co/Lfd4AHLcHE

2024-03-10 07:01:33 にツイートされました。

■■■■■■■■■■■■■■

以前に簡単に実装した 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("Access Denied!");
  }

  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
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"]?>  // ユニーク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 Mediumから変換 by ZMediumToMarkdown.

分類

#iosアプリ開発 #ロボティックプロセスオートメーション #rpaソリューション #google-apps-script #googleスプレッドシート #japanese #ai-translation

GitHub で編集

この記事を改善

本記事は Medium で初公開

オリジナルを読む

この記事をシェア

リンクをコピー · SNS でシェア

著者

ZhgChgLi

An iOS, web, and automation developer from Taiwan 🇹🇼 who also loves sharing, traveling, and writing.

Medium でフォロー 1K+ Followers

コメント

こちらもおすすめ

ZRealm ロボティックプロセスオートメーション · 2024-09-26

Github Action｜ZReviewTenderで無料即時App評価監視ロボットを簡単3ステップで構築

ZRealm ロボティックプロセスオートメーション · 2024-02-16

SlackとChatGPT連携｜OpenAI APIで自作SlackアプリをPythonとGoogle Cloud Functionsで実装

ZRealm開発 · 2026-05-10

AI 協作で資料構造RFC作成｜多言語実装と技術設計の深掘り