Google Calendarで実験装置の予約を共有、Google Sheets上で実験の条件を記録できるアプリを作った（Google Apps Script）

TL; DR

• 以前書いた記事の機能拡張版
• https://github.com/BoronSpoon/equipment-reservation
• Google Apps ScriptとはGoogleのサービスをスクリプトで機械的に動かすことができるサービス
• このアプリの用途
  • 複数人での実験装置・コンピュータ等の予約状況の追加・共有
    • Google Calendar上で完結
    • Google Groupsで利用者のみに閲覧権限を付与
  • 実験の条件(温度、時間等)をGoogle Sheets上に記入
    • Google Calendar上の予定と同期される
  • 予約状況、実験条件の自動バックアップ
    • Google Drive上で保存される

このアプリでできること

1. 設定の変更
Google Sheetsにおいて使用する装置を指定することができる

2. 装置の予約
Google Calendarにて装置の予約を行うことができる
予約は自動的に他のユーザーから確認できる状態になる

3. 他ユーザーの予約の確認
Google Calendarで他のユーザーの予約状況を確認することができる
1で選んだ装置のみ表示するようになっている

---

4. 実験に用いた工程(温度・加熱時間・濃度など)の保存
Google Sheetsにて実験の工程を記録することができる
Google Calendarの対応する予約にコメントとして反映される

5. 過去の実験の工程の確認
Google Sheetsにて過去の実験の工程の確認を行うことができる

---

6. すべての予約をログとして保存
毎日、朝4時に2日前の予約をGoogle Sheetsにログとして保存することができる

7. すべての操作をログとして保存
予約の追加・変更・削除履歴をすべてGoogle Sheetsに保存することができる

必要なもの

1. 管理者と各ユーザーがGoogleアカウントを作る必要がある
2. Google CalendarとGoogle Sheetsにアクセスするための端末（スマートフォン・パソコン等）
3. 現時点(2022/02/14)では無料で使用できる

アプリの処理の最大量

Apps scriptのQuotaで規定される

1. 18ユーザー (スクリプトを複数実行することで36ユーザー, 54ユーザー,...と増加させることができる)
2. 50装置
3. 1装置当たり20条件
4. 最大5000件までのログの保存
   5. ログの自動バックアップを用いればより多くのログを保存できる
5. 一日あたり合計150個の予約 (18ユーザーの場合)

アプリ制作を行ったうえでの教訓

sheets APIは特定の状況においてspreadsheetAppよりも高速

• Range.setFormulas()はsheets APIに置き換えると5倍以上の高速化が可能
• 最大実行時間6分に間に合わない場合はsheets APIに置き換えて高速化

spreadsheetAppとsheets APIは共存に注意が必要

• sheets APIでsheetを追加した後にspreadsheetAPPではそのsheetを確認することができない
• spreadsheetAPPにはキャッシュのようなものがあり、sheets APIはそれを経由しないため、spreadsheetAPIでは変化が見えない
• 解決策としてはsheetの追加のみはspreadsheetAPPで行い、高速化が必要なところのみsheets APIを用いる

Range.setValue()をループするよりRange.setValues()で一括で行う

• ほかのナレッジにも頻繁に書かれているがAPIの呼び出しは最も遅い要素なので呼び出し回数を可能な限り減らす

同じ値で埋める場合はRange.setValues()よりもRange.setValue()の方が速い

• TypoでRange.setValues()をRange.setValue()にしたときに気づいた
  • 同じ値で埋める場合はRange.setValue([[value, value], [value, value]])よりRange.setValue(value)の方が実行速度が速い

実行時間6分を超える場合は時間差でトリガーを設定

• 今回はsetup()を3つに分けて30秒後にトリガーをそれぞれ設定した

// timed trigger after 30 seconds
function timedTrigger(functionName) {  
  ScriptApp
    .newTrigger(functionName)
    .timeBased()
    .at(new Date((new Date()).getTime()+30000))
    .create();
  Logger.log(`trigger set for ${functionName}`);
}

インスタンスごとに定数を共有する場合はPropertiesService.getUserProperties()を用いる

const properties = PropertiesService.getUserProperties();
properties.setProperty('timeZone', 'Asia/Tokyo'); // set timezone
const timeZone = properties.getProperty('timeZone');// get timezone

• JSON, Arrayの場合はJSON.parse(), JSON.stringify()を用いる

const properties = PropertiesService.getUserProperties();
properties.setProperty('settings', JSON.stringify({timeZone: 'Asia/Tokyo'})); // set settings
const settings = JSON.parse(properties.getProperty('settings'));// get settings

properties.setProperty('arrayExample', JSON.stringify([1, 2, 3, 4, 5])); // set settings
const arrayExample = JSON.parse(properties.getProperty('arrayExample'));// get settings