AppSheet と GAS の連動
hankei6kmAppSheet の更新結果を GAS で操作(編集)したい。
hankei6kmWebhook などは極力利用しない。
あとに他ような話だと思うが、GAS を API としてデプロイするのもなし。
(GCP プロジェクト、oauth2 などが絡みそうで面倒)
できるだけスプレッドシートとスクリプトエディターの範囲でおさめる。
hankei6kmカスタム関数を利用
追記。
カスタム関数は他のセルの変更をできない。
よって、ログなどを収集するならよいが入力された値を元に変形させた値をどこかにセットするようなことはできない。
ただし、後述のように同一行の他カラムを参照して自身のセルを変更するような場合には使える。
追記ここまで
あらかじめ AppSheet が更新するであろうセルを対象にしたカスタム関数を設定しておく。
カスタム関数を設定
これで AppSheet 側からの変更があればカスタム関数が実行される。
ただし、変更点を把握できない。
上記スクリーンショットの場合だと関数の引数には列 A から D をまるごと渡される。
getActiveRange などからも変更のあった箇所はわからなさそう。
あとは、変更を検出する関数をシートに入力するのが不自然に感じる。
追記したように以下はできない。
シートの内容をまるごと別のシートに転記してしまうならよいのかもしれない。
hankei6kmトリガーを利用
onChange であれば AppSheet の変更にも反応する。
イベントの source で変更箇所も判別できそう。
function onChange(e) {
console.log(e.changeType)
if (e.changeType === 'EDIT') {
console.log('--- change', e.source.getSheetName());
const ar = e.source.getActiveRange()
console.log('--- change getColumn', ar.getColumn());
console.log('--- change getNumColumns', ar.getNumColumns());
console.log('--- change getRow', ar.getRow());
console.log('--- change getNumRows', ar.getNumRows());
}
}
ただしスクリプトエディターからトリガーを追加する必要がある。
これが権限を要求される上に開発者が確認できない云々の警告がでる。
hankei6kmカスタム関数を利用 その 2
ある列のすべてのセルが同一の式だった場合、AppSheet は行の追加を時にその列に該当の式を含める。
If all the cells in a column have the same formula, AppSheet recognizes that the intent is for all new rows to also have that formula. As a result, new rows inserted will include the formula.
これを利用すると「隣のセルを参照しているカスタム関数を設定」しておけば行の編集や追加があったときに反応する。
どのセルが変更されたか特定はできないが、１行だけに絞れるのでさほど問題はないと思われる。
ただし、実際に比較はしていないが、onChange と比べると関数(実際にデータを変形させる処理)の実行回数は可能性はある。
また、スプレッドシート側で列の構成を変更した場合、AppSheet 側では Data > Columnt から Regenerate Structure を実行することになるが、警告が表示されることがある。
これでも期待通りに動作するのだが少し気になる。
以下、試した結果。
AppSheet で入力された値によって計算される
Sync などをしなくとも計算結果は反映される
hankei6kmAppSheet ではレコード削除時にスプレッドシート側に空行ができる。
これを削除するためのライブラリーを作ってみた。
試してみる前は「空行を削除してしまうと AppSheet 側で変更したときに元の位置の行を更新するかな？」と思っていたが、更新時は毎回 ID 用カラムで行を特定しているもよう。
複数端末から削除と編集を行ってみたがデータがよれることはなかった。
また、前述の関数が認識されなくなるようなこともなかった。
厳密にはロックがかかってないので処理を集中させるとよれる可能性は排除できないが、スケジュールで運用するか個人的な利用ならいけそうかと。