AppSheetからGoogle Apps Scriptを叩く方法【GAS】
AppSheetは「ノーコードツール」なわけですが、どうしてもそうなると出来る範囲が限られてしまいます。一方でGASは出来る範囲は広いものの「GUIの構築」はHTMLとCSSでゴリゴリ書く必要があります。GUIをAppSheet側で、バックエンドをGASでといった使い方が出来るとニコイチで良いアプリケーションが作れそうです。
そこで今回はこの2つの組み合わせでアプリを構築できるか？挑戦してみることにしました。
今回利用するサービス
今回の取組では3つの仕組みが必要です。AppSheetのアプリとその土台で利用するSpreadsheet、GASが記述されたスタンドアローンなファイル。AppSheetから呼び出せるGASの関数は、スタンドアローンのGASファイルしか指定できない為です（Spreadsheetに記述したものはコンテナバインドなので指定が出来ない）。
事前準備
スプレッドシートの構造
今回のサンプルアプリはdataシートにデータを追加したら、その内容を特定のGoogle Chatにメールで投稿するという仕組みにしています。ただし、スプレッドシート側にはGoogle Apps Scriptでは実装しません（前述の通りスタンドアローンである必要がある為）。
4列だけのシートを元にメニューからA拡張機能 => AppSheet => アプリを作成で作成をします。この時のスプレッドシートのファイルのIDがGASで必要になるので控えておきます。
図：スプシからアプリを直接作成可能
スタンドアローンのGASを用意
今回のアプリの仕様としては
- 複数名で利用することが前提
- 各レコードには連番を振る必要がある
- 連番を元にレコードを特定してGoogle Chatへメールで送信
という形です。しかし、問題は現時点でAppSheetがAccessやMySQLのようにオートナンバー的に自動的に連番を振る機能がありません。一人で利用する前提ならば、「MAX関数 + 1でナンバーを生成」も出来ますが、複数名でアクセスしてる場合これだと不具合が出ます（別の人が先に書いてしまったりするとナンバーが重複する可能性がある）。AppSheetのヘルプにも出来ない旨の記載があります。よって、この部分はUNIQUEID関数を使うことにします。
そこで今回GAS側は引数として与えられたこのUNIQUEIDを元にスプシを探索し、レコードを特定し、Google Chatへ送信を担当しようと思います。
尚、スタンドアローンのGASのファイルを作成する手順は以下の通りです（URLにcopyをつけても複製は出来ません）
- Google Driveを開いて、左上の新規をクリック
- その他 => Google Apps Scriptをクリック
- スクリプトエディタが直接開いてドライブにはスタンドアローンファイルが生成される
昔はChrome Webstoreから拡張機能インストールする必要がありましたが、現在は標準装備されてるようです。
図：新規から作成することが出来る
Google Chat側の準備
レコードが投稿されたことを通知するためのGoogle Chatのスペースとメールで送信する為の設定を施します。2024年5月22日よりGoogle Chatにメールで投稿する機能が装備されたので早速利用したいと思います。
- Google Chatのトップを開く
- 既存のスペースを開き、上部にあるスペース名の横にある「∨」をクリックする
- スペースの設定を開く
- メールを開く
- メールを生成をクリックする
- しばらくするとメアドが生成されるのでコピーする（GASで利用します）
ちなみにこのメアド、実体は特殊なGoogleグループでしてオーナーとスペースが参加してるという不思議な構造をしています。
図：スペースのメアドを生成する
図：実体はグループアドレス
アプリを構築する
GAS側の実装
スタンドアローンスクリプトに対して関数を一個用意します（今回はsendNotifChatとでも命名します）。先にGAS側を準備しておかないと、AppSheet側でスクリプト指定時に関数名が出てこないことになるので、はじめにGASの処理を実装します。今回はAppSheet側でのレコードのIDを引数に取って、スプレッドシートを探索し、Chatのスペースにメールで投稿します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
//Chatに対して通知を送る function sendNotifChat(rec) { //スプレッドシートのIDを指定する let ssid = "ここに探索先スプレッドシートのIDを入力" //Google Chatのスペースのメアドを指定 let spacemail = "ここに通知先スペースのメアドを入力" //スプシをflushしておく SpreadsheetApp.flush(); //スプシデータを取得する let ss = SpreadsheetApp.openById(ssid); let sheet = ss.getSheetByName("data").getRange("A2:D").getValues(); //引数recと一致する行を特定する let target = ""; for(let i = 0;i<sheet.length;i++){ //レコードを一個取り出す let record = sheet[i]; //レコードIDを比較する if(rec == record[0]){ //対象レコードを確保する target = record; break; } } //recordデータに基づいて本文を構築 let body = "レコード番号:" + rec + "として、新規に医薬品 「" + target[2] + "」 が登録されました。<br>" + "担当者はレコードを確認してください💊" //スペースに対してメールを送信する MailApp.sendEmail({ to: spacemail, subject: 'アプリから新規に医薬品登録されました', name:"薬剤部からのお知らせ", htmlBody:body, }); } |
- 探索先のスプレッドシートのIDおよびスペースに設定したメアドを記述しておきます。
- スプシのデータを取得する前に一旦flushしておきます（時々書き込みが反映する前にGASが走ってしまう為）
- 引数のUUIDと一致するレコードを特定しレコードを取得
- そのレコードの内容をもってして本文を構築し、Chatのメアドに対してMailAppでメール送信
必ず一度は手動で関数を実行し、権限の承認を済ませておく必要があります。
AppSheet側の実装
ID列を装備する
スプレッドシートから作成したAppSheetのアプリ作成画面を開いて、まずはID列を改造します。ここにはUNIQUEID関数で自動生成された値が入ります。
- 左サイドバーのDataをクリック
- 同じくID列に於いて、Initial Valueをクリックして、「UNIQUEID("UUID")」を入力する
- 右のほうにスクロールして、Showのチェックはオフにしておく（編集出来ないようにする）
- 右上のSAVEをクリックする
これで事実上のオートナンバーを作成することが出来ました。
図：ID列の設定を変更しておく
GAS連携を装備する
今回のテーマの一番の肝になるのが「レコード追加時にGASを発火させる」部分を構築するのがこのステージになります。GASに対しては引数で前述で指定したUUIDの値を渡して発動させます。
- AppSheetのアプリのトップ画面より、左サイドバーのAutomationをクリックする
- Create my first automationをクリックする
- 左下のCreate a new botをクリックする
- Configure Eventをクリックする
- Create a custom eventをクリックする
- 右サイドバーがSettingsの状態になってるので、Data change typeをAddsのみチェックを入れる
- 画面真ん中の「Add a step」をクリックして次のステップを追加する
- Create a custom stepをクリックする
- 作成されたステップをクリックすると右サイドバーが開かれる
- 右サイドバーよりCall a scriptをクリックする
- Apps Script ProjectのClick to Selectをクリックする
- 前述で作成したスタンドアローンのスクリプトを指定する
- Function Nameにて作成しておいた「sendNotifChat」を選択する
- Function Parametersにて引数recに何をいれるか？を指定します。IDの行のinsertをクリックします。
- 今回はReturn Valueについてはとくに設定しないのでスルーする
- 右上のSAVEをクリックして保存する
これで、レコードが追加されるとIDの値をGASに渡して実行させることが可能になります。
図：イベントの設定
図：GASプロジェクトの設定画面
図：ID列を引数としてinsertする
デプロイする
これでとりあえずアプリは閑静なのでデプロイをします。以下の手順でデプロイしましょう。
- アプリ作成画面の左一番上のボタン（Not Deploy）をクリックする
- Deployment Checkをクリックして、Move app to deployed stateをクリックします。
- これでアプリが使えるようになりました。
図：デプロイしたら完成
実際に動かしてみる
デプロイしたURLでも良いですし、開発画面のViewからでも実行してテストすることが可能です。できればデプロイする前にViewの画面からテスト動作をしてみて、無事にGAS連動できることを確認してからデプロイすると良いでしょう。
無事に動けば、GASが色々バックグラウンドで処理をしてくれてChatにメールで投稿されるハズです。
AppSheetで無理くり装備しようとしたり実装法を調べるよりもバックグラウンドの処理については素直にGASに任せることで、作業分担にもなる上にGASでUIを構築するよりかはずっと楽なのは事実なので、選択肢の一つとして覚えておいて損はないと思います。
図：アプリの画面
図：Chat欄に無事に投稿されました
GAS側からの返り値を受け取って判定
GASに処理を投げて、完了したら返り値（trueやfalseなど）を受取、その内容を元に通知を送る。といった処理を装備したい場合、どうやって返り値を受け取るのか？また判定はどうするのか？。
その手法は以下のようになります。
- Automationプロセスにて、Call a Scriptの下のほうにある「Return Value」のスイッチをオンにする
- 下に説明文にUse the output of this function by usingで出てるフィールドの値をコピーする（例：[最終仕入日更新].[Output]）
- 新しいstepを追加して、Branch on a conditionにしておき、値は2.で取得したものを入れる
- Yes / Noにそれぞれステップを追加し、send a notificationを設定 => 宛先はUSEREMAIL()の数式を指定。
- メッセージ等は適当に入れましょう。
- 実際にGASを発火し、GAS側からの処理の返り値がtrueもしくはfalseの値を受け取って、処理が分岐されます。
GAS側は必ず最後にreturn trueなどの処理を入れておかないと返り値を返せないので注意。
図：返り値を取るフィールド名はコレ
図：返り値を元に処理を分岐するステップ
図：無事通知が表示出来ました
フォーム上のボタンを押して実行できないの？
標準のGAS連携の仕組み
AppSheet標準のGAS連携機能はあくまでも「レコードの追加、更新、削除」のイベントに対してだけ発火する仕組みです。よって、ボタンをクリックして直接GASを叩くみたいな真似は現時点では出来ません。それ故に、既存のレコードを開いて例えば請求書を発行するであったり、インスタントに通知を送りたいといった事が出来ません。
逆を言えば「更新」を掛ければ動くということ。バッドノウハウではありますが、このレコードの更新を意図的にできれば発火させることが出来る。ということで、特定の列を用意し、値としてランダムな値を書き込み、書き込み前と後を比較して変わっていればGASを叩くみたいな仕組みで、ボタンをクリックすると発火するGASを作ってみたいと思います。
※注意点としてボタン1個に1列が必要になるため、複数設置したい場合はその個数分列を用意してAutomation判定を用意する必要があります。列を共用してしまうと、別のボタンの発火も同時に引き起こしてしまいます。
トリガー発火用の列を用意する
以下の手順でトリガー判定用の列を1つ追加して設定を施します。そして、開発画面にてRegenerate Schemeをクリックすると、開発画面にTrigger列が現れるようになります。
フォームに表示されても困るのでこの列のShowはチェックをオフにしておきます。
図：値書き込み用の列を追加する
アクションボタンを一個追加する
GAS発火させるためのアクションボタンをフォームに追加します。
- 左サイドバーからActionsをクリックする
- Actionの横にあるAdd Actionの＋ボタンをクリックする
- Create a new actionをクリックする
- Action Nameは適当に入力する
- For a record of this tableは更新を掛けるテーブルを指定。今回は１個しかないのでスルー
- Do Thisは、「Data: set the value of some column in this row」を選択する
- Set these columnsでは、列はTriggerを指定してとなりの箱をクリックしたら関数として「UNIQUEID("UUID")」でも入れておく
- 右上のSAVEをクリックする
図：UUIDで意図的に値をセットさせる
GAS実行のAutomationを作る
新しいBotを作って、自動化を構築します。この時通常の更新作業でも発火してもらっても困るので、判定を加えます。GASや仕組みは前述のChat送信のものをそのまま流用しています。
- 左サイドバーのAutomationをクリックする
- Botsの横のCreate a new Botの＋ボタンをクリックする
- Create a new botをクリックする
- Configure Eventをクリックする
- Create a Custom Eventをクリックする
- Event Nameは適当に入力
- 発火元のテーブルを選択する
- Data Change TypeはUpdatesのみにする
- Conditionには以下のような数式をいれる
1[_THISROW_BEFORE].[Trigger] <> [_THISROW_AFTER].[Trigger]
※更新前と後を比較して異なる場合にだけ発火という意味の数式になります。 - Add a stepで次のアクションをいれる
- Create a custom stepをクリックする
- Settingsでは、Call a scriptを選ぶ
- Apps Script Projectのボックスをクリックして、スタンドアローンコンテナを選ぶ
- Function Nameでは実行する関数を選ぶ
- Function Parametersでは渡す引数を選択する
- 右上のSAVEをクリックする
すると、フォーム上にボタンがあるのでタップするとTriggerの値が更新されて、同時にActionが発火しTriggerの値が変わった場合にはGASを叩くという動作がこれで作れました。
図：Conditionで値の新旧を判定
図：手動発火用に別のGAS実行を作っておく
図：手動発火をタップするとGASが動く
ダッシュボードにボタンを置いて実行させる
前述までのは、レコード単位のフォーム上にボタンを置いて、対象のレコードを基準にGASを発動させる方法です。一方で、個別じゃなくテーブル全体を基準にGASを発動させたい場合があります。例えば全体の報告レポートをPDF化して送信したい等など。この場合ちょっとしたロジックを必要とします。
図：このタイルをクリックすると発火する
メニュー用のシートを用意する
原理的には前述までのシートを基準にTrigger列を用意して、値を更新してGASを発動させる仕組みです。そこでこのメニュー用のシートを用意して、これを元にDashboardタイプでビューを作ります。
- シートを用意する
- Dataにてこのシートを取り込んでおく
- 名前、アイコン、Triggerの3列でセットする
- 名前とアイコンをRequireとしてチェックを入れておく。（アイコンはimageのTypeを指定する）
- このメニュー用のテーブルのスライスを作り、Read Onlyにしておく
Requireにチェックを入れておかないとフォームにした時に出てこないので注意。Triggerは表示する必要はないのでSearchとShowのチェックは外しておいてオッケー。
図：こんな感じのシートを作る
図：2列だけRequireにチェック
アクションを作成する
ダッシュボードを作る前に、項目クリック時のアクションを先に作っておきます。
- 左サイドバーからActionをクリック
- 前述で取り込んだシート名の隣の＋ボタンをクリックする
- Action Nameは適当にセット（事項で選択時に利用します）
- For a record of this tableは前述で取り込んだシート名を選択する
- Do thisでは「Data: set the value of some columns in this row」を選択します。
- Set these columnsでは、「Trigger列」を選択し、隣のボックスを選択して、UNIQUEID()を入力する
- Behaviorを開く
- Needs confirmation?のスイッチをオンにする
- Confirmation Messageをクリックして、数式として「CONCATENATE([名前], "を実行しますか？")」を入力する。
- 右上のSAVEをクリックする
図：これでクリック時に確認メッセージが出る
図：出てきたメッセージ
ダッシュボードを作る
このメニュー用シートの「スライス」を元にダッシュボードを作ります。これがユーザが使うものになります。
- ViewsよりAdd Viewをクリックして新規追加
- Create a new viewをクリックする
- View Nameは適当にセット
- For this dataを前述で取り込んだテーブルを指定する。
- View TypeをGallaryに変更する
- 下の方にあるBehavierをクリックする
- Event ActionsのRow Selectedでは前述で作成したアクションを選択する
- 右上のSAVEをクリックする
また、ボタンのアイコン指定は作成者だけがセット出来るようにすれば良いので、このシートから直接ビューを作成し、こちらは編集出来るようにしておきます。自分のメアドとイコールの場合だけ表示するように、Show ifにて「"自分のメアド" = USEREMAIL()」を数式として入れておきます。
また、この後この編集用フォームにて画像を指定しておきましょう。
図：アクションを指定する
図：編集用フォームは自分だけが利用する
あとは更新イベントでGASを発動させる
あとは前述のTriggerの値が更新されて、Conditionで更新前と更新後の値が異なる場合で発動するようにセットするだけ。できればこの後のGAS実行後は
- GASからのReturn Valueを受け取るようにする
- 受け取った次のStepを追加しておいて、例えばsend a notificationで自身に通知を送る
といったような処理をしておくと処理が終わったことがわかるので装備しておきましょう。
図：GASを叩く処理を作る
図：処理後の通知を装備する
関連リンク
- 自動化から Apps Script を呼び出す
- ポートフォリオや URL 経由でアプリをテンプレート / サンプル アプリとして共有する方法
- AppSheetの作品公開方法
- 【AppSheet】データモデリングの学習
- AppSheet実践編シリーズ⑯～絶対覚えたい！！Refってなに？～
- AppSheet で請求書アプリ（第6回）請求書テンプレートを作成する
- AppSheetと16行のGoogle Apps Scriptで作るバーコード蔵書管理アプリ（画面の再読み込みが必要）
- AppSheetと25行のGoogle Apps Scriptで作るバーコード蔵書管理アプリ
- Apps Script connector for AppSheet についてのお知らせ: Google Workspace でワークフローを自動化する
- 新しい AppSheet Apps Script コネクタを使用して、Google Workspace 全体でカスタムの統合ソリューションを構築します
- AppSheetではオートナンバーの設定ができない模様
- appsheetで連番を取る方法 max()関数で初心者でもできる！！
- 【AppSheet】リストのインデックス番号を得る
- シーケンシャルキー - AppSheet
- AppSheetを使って簡単な販売管理システムを作ってみた①
- ActionのBehaviorのConfirmation Messageにリンクを貼ることは可能でしょうか
