GlideとGASのWebhookで打刻ボタンを作ろう #GoogleAppsScript

この記事はユニークビジョン株式会社 Advent Calendar 2019 の 19 日目の記事です。

きっかけ

SpreadSheetから簡単にPWAが作れるGlideですが、現在時刻を入力したい時に、わざわざ時刻編集フォームから入力するのが面倒に感じることがあります。特にすでに過去の日付が入力されている場合、時刻編集フォームにその値が入力された状態から始まるので、日時を結構進めないといけなかったりして辛いです。

やりたいこと

どうせ現在時刻しか入力しないならば、入力フォームの代わりにこんな風に打刻ボタンだけおいておき、押された時刻をSpreadSheetに自動入力すればちょっと手間が省けそうです。というわけで作ってみました。

仕組み

GoogleAppScriptには実装したスクリプトをWebhookURLを踏むことで実行する事ができます。今回はURLにパラメータを渡してやることで、任意の行の値が更新されるようなスクリプトを実行します。
※WebhookURLの取り扱いにはご注意ください。

実装手順

シートを用意する

今回は、簡単なランチのお店管理アプリを作ることを想定します。機能は以下のとおりです。

スプレッドシートに登録してあるお店を一覧表示する
お店の一覧は最後に行った日付昇順で表示する
お店の詳細画面にある「打刻」ボタンをタップすると、そのお店の「最後に行った日」が現在時刻で更新される

というわけでまずはシートを作ります。
後々楽なので行番号の列には=ROW()を入力しておきます。2から始まって気持ち悪いですが良しとします。
名前、最後に行った日の列を作り、お店を登録していきます。最後に行った日は空欄でもOKです。
お好みで「画像」や「場所」、「ジャンル」なども足しておくとアプリが華やかになりそうですね。

打刻スクリプトを用意する

URLを踏んだ時に起動するスクリプトを書きます。

stamp.gs

var SEET_ID = '1g7YzmSUI8schzDZfmc11kWlusecVt1K6_bvVqbByZOs'
var TAB_NAME_LUNCH_LIST = 'ランチ'
var COLUMN_INDEX_TIMESTAMP = 3

var sheet = SpreadsheetApp.openById(SEET_ID).getSheetByName(TAB_NAME_LUNCH_LIST)

function doGet(e) {
  var id = e.parameter.id
  if (varidate(id)){
     stamp(id)
     return HtmlService.createHtmlOutputFromFile('success')
  }
  return HtmlService.createHtmlOutputFromFile('invalid')
}

function varidate(id) {
  value = parseInt(id)
  // 数値じゃないとダメ
  if (isNaN(value)) {
    return false
  }
  // ヘッダー行やデータが存在しない行はダメ
  if (value === 1 || value > sheet.getLastRow()) {
    return false
  }
  return true
}

function stamp(id) {
  sheet.getRange(id, COLUMN_INDEX_TIMESTAMP).setValue([new Date()])
}

doGet()がWebhookURLを踏んだ時に実行されます。
e.parameter.{パラメータ名}URLのパラメータの値を名前で指定して取得できます。
今回は、先程のシートの行番号の値を受け取り、stamp(id)メソッドに渡してやります。
stamp(id)メソッドでは渡されたidの値からセルの位置を特定して現在時刻を入力します。

結果出力ページを用意する

doGet()内のreturn HtmlService.createHtmlOutputFromFile()で返すHtmlです。
これは任意なのですが、何も用意しないとWebhookURLを踏んだ際にちょっと残念なメッセージがブラウザ画面に表示されてしまいます。

success.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>打刻しました！</p>
  </body>
</html>

invalid.html

<!DOCTYPE html>
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>打刻できませんでした！</p>
  </body>
</html>

余談ですがGASのエディタでHtmlファイルって作れたんですね。

WebhookURLを設定する

WebhookURLの取得方法はこちらのページに詳しく書いてあるので割愛します。

URLを取得できたら、
https://script.google.com/macros/s/{...}/exec?id=
のように、idパラメータを組み込める形にして適当なセルに入力します。
シートにURL列を追加して、URLの末尾に行番号がつながるように入力します。
例えば=WebhookURL!$A$1&A2のように、=URLを入力したセルの絶対参照 & 行番号のセルになると思います。

アプリを作成する

いつものようにgrideで新しいアプリを作成し、先程のシートを読み込みます。
一覧画面のFEATURESタブでSORTを指定し、最後に行った日順に表示されるようにします。

詳細画面にボタンを配置し、FEATURESタブでdataにURLの列を指定します。
ボタンのラベルを適当に入力し、ACTIONでOpen as linkを指定します。
これでボタンをタップした時にURLの列に入力されたWebhookURLを開きます。

以上で完成です。

気をつけること

WebhookURLを公開すると、誰でも好きなパラメータを入れて叩くことができます。特にglideのボタンに割り当てる場合はアプリ上から簡単にURLを読み取ることができます。parameterにはどんな値が入力されてくるかわかりませんのでご注意ください。
今回の場合、idでセルを特定するので数値以外が渡されるとクラッシュします。また、1が渡されるとシートのヘッダーが書き換わってしまいますし、データがない行の行番号が渡されるとアプリの一覧に名無しの店舗が出現します。この辺りはvalidate()で検証していますが、例えば他の店舗の行番号が渡されるとそのお店の最後の行った日が更新されてしまいます。
最悪の場合シートが壊れる覚悟で計画的に使用しましょう笑。

参考

GAS｜Webhook URL を公開する｜非エンジニアのための「Google Apps Script