😺

GoogleAppsScript チーム利用のための開発アイデア集

に公開
GAS
Google Apps Script
tech

こんにちは、luthです

GASからプログラミングに入門し、Vue/React、Typescriptを勉強していったノンプログラマーですが、チーム内で利用する、社内ツール開発を4年ほどやってきました

GASは手軽に利用でき、小規模なアプリやツールを作るのには便利な言語ですが、いかんせんとにかく処理が遅いことが課題です。

そのため、個人開発の中で感じた、以下のためのポイントをまとめてみました

  • チームで利用するための工夫
  • 処理速度を遅くさせないための工夫
  • 業務利用として安定的に利用するための工夫

＊チーム開発のためのアイデア、こちらにまとめています

https://zenn.dev/luth/articles/gas-team-coding

想定読者

  • 社内やチーム内で共用する、GASツールの開発者
  • GAS 初心者〜上級者
  • ブラウザのGASエディタで開発する環境
    = Node環境を入れない・入れられない（Clasp が利用できない）環境

チーム利用のためのポイント

主にスプレッドシートでの話題で、排他性の確保、処理速度の担保が主になります

繰り返し処理は、メソッド実行は少ない回数で記述

GASは実行時間が遅いことで有名ですが、特にAPI系メソッドを利用する時間が大きいです。

そのため、ユーザー体験向上のためには、「GAS外とやりとりする回数」をできるだけ減らすことが重要です。

悪い例　都度通信しているので、めっちゃ遅い
function getUserData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('ユーザーテーブル');
  for (let i = 1; i <= sheet.getLastRow(); i++) { // API * 1
    const id = sheet.getRange(i, 1).getValue(); // API * 2 * ループ回数
    const name = sheet.getRange(i, 2).getValue(); // API * 2 * ループ回数
    const address = sheet.getRange(i, 3).getValue(); // API * 2 * ループ回数
    console.log({ id, name, address });

    const timeStamp = Utilities.formatDate(new Date(), 'JST', 'yyyyMMddHHmm');
    const myAddress = Session.getActiveUser().getEmail();
    sheet.getRange(i, 4).setValue(timeStamp); // API * 2 * ループ回数
    sheet.getRange(i, 5).setValue(myAddress); // API * 2 * ループ回数
  }
}
いい例
function getUserData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('ユーザーテーブル');
  
  // 取得は1度に済ませる
  const data = sheet.getDataRange().getValues(); // API * 2
  data.forEach(row => {
    const [id, name, address] = row;
    console.log({ id, name, address });
  });

  // 書き込みも1度で済ませる
  const timeStamp = Utilities.formatDate(new Date(), 'JST', 'yyyyMMddHHmm');
  const myAddress = Session.getActiveUser().getEmail();
  const systemData = data.map(() => ([timeStamp, myAddress]));
  sheet
    .getRange(1, 4, systemData.length, systemData[0].length)
    .setValues(systemData); // API * 2
}

スプレッドシートDBは取得用シートを作る

上記の話にも繋がりますが、スプレッドシートをデータベースとして利用する場合、「データベースのテーブル取得」＝「1通信群」です。

そのため、「GASの処理速度向上」のためには、できるだけ少ない回数の通信であることが望ましいです。

通常のデータベース設計であれば、正規化（データの重複がないように、テーブルを分割して管理すること）を行うかと思います。

①ユーザテーブル

ユーザID ユーザ名 アドレス
1 花子 hanako@xxx.com
2 三郎 saburo@yyy.co.jp
3 吾朗 goro.chan@zzz.ne.jp

②投稿情報テーブル
＊投稿したユーザ情報を行に入れず、①ユーザテーブルに分割している

投稿ID 本文 ユーザID
1 ねみぃ 2
2 あの本の発売日今日じゃね？ 3
3 腹減った 1
4 はっ！2時間昼寝してたわ 2

ただ、この程度の情報量だとしても、GASで2テーブル取得するには、計7APIを要します。

  • スプレッドシートファイル全体を取得
  • 対象のシートをシート名で特定して取得　*２回
  • 取得対象のセル範囲を特定　*２回
  • 対象範囲の値を取得　*２回

そのため、取得回数を減らすため、テーブル結合はスプレッドシート側でarrayFormula+vLookUp等で済ませ、
GASは結合後のテーブルを取得すべきです。

③結合後テーブル
＊A1セルに数式 =arrayFormula({'②投稿情報テーブル'!A:C, vLookUp('②投稿情報テーブル'!C:C, '①ユーザテーブル'!A:C, {2, 3}, false)}) が入る想定

投稿ID 本文 ユーザID ユーザ名 アドレス
1 ねみぃ 2 三郎 saburo@yyy.co.jp
2 あの本の発売日今日じゃね？ 3 吾朗 goro.chan@zzz.ne.jp
3 腹減った 1 花子 hanako@xxx.com
4 はっ！2時間昼寝してたわ 2 三郎 saburo@yyy.co.jp

Proxyも利用検討

JavaScriptにはProxyという機能があります。
GASを使っているとあまり触れない技術かと思いますが、これがキャッシュ的な使い方もできるので、処理の効率化に活かせます。

＊Proxyについては参考記事等でご確認を…！

https://zenn.dev/yend724/articles/20220915-t0ws2v8agxiaa8gi

例えば、「繰り返し処理の中で、複数のスプシファイル・シートを取得するが、一部の繰り返し回は同じスプシファイルを取得したい」なんてケースです。
DB設計によっては、権限の渡し方を分けたい、などの都合で、同じ設計のファイルを複数種類用意することもあるでしょう。

getSheetValues.gs
function getSheetValues() {
  // Proxyで、同じスプレッドシートファイルが取得済みだったら、Proxy内部から取得し、未取得だったら新たにファイルを取得
  /** @type {{[key: string]: SpreadsheetApp.Spreadsheet}} */
  const spreadsheets = new Proxy({}, {
    get: (target, spreadsheetId) => {
      if (!target[spreadsheetId]) {
        target[spreadsheetId] = SpreadsheetApp.openById(spreadsheetId);
      }
      return target[spreadsheetId];
    }
  });
  
  const configs = [
    { ssid: 'aaaaaaaa', sheetName: 'シート1' },
    { ssid: 'bbbbbbbb', sheetName: 'シート2' },
    { ssid: 'cccccccc', sheetName: 'シート1' },
    { ssid: 'dddddddd', sheetName: 'シート4' },
    { ssid: 'aaaaaaaa', sheetName: 'シート2' }, // index 0 と同じファイル
    { ssid: 'eeeeeeee', sheetName: 'シート1' },
    { ssid: 'cccccccc', sheetName: 'シート2' }, // index 2 と同じファイル
  ];

  return configs.map(({ ssid, sheetName }) => spreadsheets[ssid].getSheetByName(sheetName));
}

列位置/行位置の特定は、インデックスでなく、キー文字列位置で

スプレッドシートをDBやUIとして利用する場合、ユーザに編集権限も渡すシーンが多いでしょう。
その差異は往々にして、ユーザは意図せずに「行列挿入」「ヘッダー名変更」「列の入れ替え」「フィルターの適用」などをやらかすものです。。。
GAS側でケアしてあげる範囲はユーザのリテラシーに依存するかと思いますが、アイデアとして「意味づけする列位置/行位置に、キー文字を用意しておく」ことを残しておきます。

キー文字列を利用したスプレッドシートDBの例
使用例：運用時にはA列・１行目は非表示にする

上記DBから、GAS側からテーブル取得する例

getTableByIndexKey.gs
function getTable() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("DB");
  const values = sheet.getDataRange().getValues();

  const columnKeyValues = values[0];
  /** 規定の取得対象項目　これらがスプレッドシート1行目にさえあれば位置が変わろうとも取得できる */
  const columnIndexKeys = [
    "Col_number",
    "Col_title",
    "Col_createdBy",
    "Col_responsible",
    "Col_status",
    "Col_memo",
    "Col_genre",
  ].map((key) => ({
    key,
    objectKey: (key.match(/Col_(.+)/) || [, null])[1],
    index: columnKeyValues.indexOf(key),
  }));

  const headerKeyIndex = values.findIndex((row) => row[0] === "Row_index");
  const tableValues = values
    .map((row, rowIndex) => {
      // ヘッダー行を含めて上の行はデータ行ではないので、取得対象外
      if (rowIndex <= headerKeyIndex) return null;

      // データ格納用のオブジェクト
      const object = {};
      columnIndexKeys.forEach(({ objectKey, index }) => {
        object[objectKey] = row[index];
      });
      return object;
    })
    .filter((value) => value !== null);
  return tableValues;
}

function main() {
  const tableValues = getTable();
  console.log(tableValues[0].title);
  // => 'メールチェック'
}

シート取得

以下、旧記載内容です。

旧記載内容

SpreadsheetAppクラスには、シートを取得するメソッドがいくつかあります。

// 現在実行ユーザが表示している（active）シートを取得
SpreadsheetApp.getActiveSheet();

// 対象ファイル中のシートをすべて取得
SpreadsheetApp.getActiveSpreadsheet().getSheets();

// 対象ファイル中の、シート名が完全一致するシートを取得
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');

ただ、ユーザは「勝手にシート名を変える」「シート名規則を守らない」「シートの順番を変えてしまう」ことなんて往々にしてあります。
そのため、完全一致検索のgetSheetByNameだと、シート名側が想定外のものになるとシートを取得できませんし、getSheetsで配列からシートの順番で対象を取得している場合もエラーになる可能性があります。

スプシ側の対策も色々ありますが、GAS側でやるなら、sheetIdを利用する手段があります。
（シートIDからシートを取得するAPIがあればいいのに…！）

const sheetId = 123445676789;
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
const targetSheet = sheets.find(sheet => sheet.getSheetId() === sheetId);

データ行の追加

データ行を追加するとき、range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()や、sheet.getLastRow()で最終行番号を取得するやり方もあるが、フィルターで非表示になっている行がある場合などには注意がいる。
そのため、1行を追加するだけなら、以下メソッドを利用するのがシンプルでよい。
（複数データ行を追加する場合はrange.setValues(array)のほうが処理時間が短いため推奨）

sheet.appendRow(['id', '名前', 'アドレス@gmail.com']);

＊参考サイト

https://tetsuooo.net/gas/2093/

排他処理

複数人で同じツールを利用する場合は、近いタイミングでの複数処理が競合する可能性を考慮する必要があります。
GASでは排他処理をするための組み込みサービス、LockServiceがありますので、複数処理を順番に捌きたい箇所ではロックの取得をできるようにしましょう。

＊LockServiceについて
イメージは、プロジェクト単位やユーザ単位で、「その処理が独占的に処理を進められる権利」を管理しているものです。
参考サイト

https://qiita.com/kyamadahoge/items/f5d3fafb2eea97af42fe

lock.gs
/**
 * 排他処理のラッピング関数。排他処理を適用したい処理をコールバックで記述する。
 * @param {Function} callback
 * @param {number} coolTimeInMillis [Default: 100(ms)]
 * @param {number} timeoutInMillis [Default: 10000(ms)]
 * @return {any} callbackで返却のあった値
 */
function useExclusive(callback, coolTimeInMillis = 100, timeoutInMillis) {
  const lockService = LockService.getScriptLock();
  let usedTime = 0;
  while (!lockService.tryLock(coolTimeInMillis) || usedTime <= timeoutInMillis) {
    // wait
    usedTime += coolTimeInMillis;
  }
  if(lockService.hasLock()) throw Error('timeout: try to get lock, but you could NOT obtain lock.');
  const result = callback();
  lockService.releaseLock();
  return result;
}
addRow.gs
/**
 * データセット。A列はチェックボックス用の列なので、B列以降にセットする
 * @param {any[]} 1次元配列でセットするデータを渡す
 */
function addData(data) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('sheet1');

  // ここは「最終行」を取得してからデータ書き込みをするので、排他処理
  const targetRow = useExclusive(() => {
    const lastRow = sheet.getLastRow();
    sheet.getRange(lastRow + 1, 2, 1, data.length).setValues([data]);
    return lastRow + 1;
  }, 250, 20000);

  // メッセージボックスを表示する場合はGASの処理は「一時停止」になるため、排他処理の外で行う
  Browser.msgBox(`対象データを${targetRow}行目に追加しました。`);
}

例外処理・ログ取得

HtmlServiceを利用することで、WebアプリをGASで提供することも可能です。
ただ、デプロイ設定を「次のユーザーとして実行：ウェブ アプリケーションにアクセスしているユーザー」としている場合は、他アカウントの実行したログは管理者でも見れない罠があります。。セキュリティ保護のためでしょう。
とはいえ、社内ツールであればデバッグに備えてconsole.logなどを配置しておき、実行時の情報を取りたい時もありますよね！

いくつか方法はあるかと思いますが、Webアプリのエラーも考慮するなら、私が社内ツールで使っている手法は以下です。

  • 例外処理(try 〜 catch構文)を可能な限りいれる
  • パラメータ情報などをログ保存する
    • ログ保存用プロパティストアやスプレッドシートへの保存
    • 例外発生時のSlackやGoogleSpaceへのログ送信
    • UIにログ情報と共にエラー表示をさせ、「管理者へスクショ送信をお願いします」メモ
    • ...などなど

GAS障害対策（非機能）

GASはインフラ基盤としてはサービスレベルが定められていないので、一時的な実行エラー(調子が悪い) ぐらいはしょっちゅう発生します。

エラー例
エラー例
We're sorry, a server error occurred. Please wait a bit and try again.
---
Service Spreadsheets failed while accessing document with id xxxxxxxxxxxxxxxxx.
---
Too many simultaneous invocations: Spreadsheets
---
Gmail operation not allowed.
---
We're sorry, a server error occurred while reading from storage. Error code DEADLINE_EXCEEDED.
---
We're sorry, a server error occurred while reading from storage. Error code INTERNAL.
---
We're sorry, there was an unexpected error while preparing top level objects.
---
We're sorry, the JavaScript engine reported an unexpected error. Error code INTERNAL.

また、全体の障害も、ちょこちょこ発生しています。

公式サービス停止ログ

https://developers.google.com/apps-script/guides/support/outage-log?hl=ja

イシュートラッカー(ユーザーがバグ情報をやんや言うところ) - 2024/02/26に発生したエディタ/一部サービスのダウン

https://issuetracker.google.com/issues/326802543

そのため、GASツールが使えなくなった場合の検知方法と、GASを使わないでも業務を回す方法の確立が必要です。

特に後者は、障害が発生する前には手段を確立しておきたいですね。

権限管理

複数シート、シート/範囲の保護、ライブラリなどが絡むと当たる問題が、権限管理です。
めんどくさい、けどやらないとツールが動かなかったり、壊される危険性があったり…と個人的に苦手なポイントです。

私が困ったポイントを書き殴りますね。

ライブラリ

ライブラリを読み込んで利用する場合は、安定稼働のために、「必ずデプロイバージョンを指定して、ユーザー展開」すべきです。
ただし、下記折りたたみの中で解説しているように、「開発モード」(デプロイされている版に関わらず、ライブラリ最新のスクリプトを利用するモード)の利用はアリかもしれません・・・！

ライブラリ読み込み設定: 「開発モード」

通常、バージョン指定を「HEAD」にすると自動で設定される「開発モード」。
有効になると、デプロイバージョンに関わらず、最新のソースコードを取得して、実行されます。
そのため、ライブラリ側で開発作業などが行われることを考慮すると、本番環境での利用は避けるべきです。

ただ、共通ライブラリを読み込む個人ファイルを、チーム内に展開するケースなどは、開発モードにしたくなりますね。
ライブラリの版更新時は、バージョン指定だと都度個人ファイルすべてのスクリプトエディタを開き、バージョン指定を更新する必要があります。
これが開発モードを有効にしていると、自動更新の形になりますね！

下記表中にある「バージョン指定をしたまま開発モードにする」ためには、以下のようにマニフェストファイルappsscript.jsonを表示・手動編集する必要があります。

appsscript.json
{
  "timeZone": "Asia/Tokyo",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "TeamLibrary",
        "libraryId": "xxxxxxxxxxxxxxxxxxxxxxxxxx",
        "version": "1",
-       "developmentMode": false
+       "developmentMode": true
      }
    ]
  }
}

3列目「権限」=「実行者のライブラリファイルへのアクセス権限」
注目すべきは、「版指定＝HEAD　かつ　実行者のライブラリファイルへのアクセス権＝閲覧のみ」の場合は、権限がない状態と同じように、実行エラーになる点ですね。

版指定 開発モード 権限 実行可否
HEAD(0) 有効/無効 なし エラー(実行が始まらない、スクリプトファイルが見つからない、など)
^ ^ 閲覧/閲覧(コメント可) エラー(実行が始まらない、スクリプトファイルが見つからない、など)
^ ^ 編集/オーナー OK
数字指定 有効 なし エラー(ライブラリが見つからない)
^ ^ 閲覧/閲覧(コメント可) OK
^ ^ 編集/オーナー OK
^ 無効 なし エラー(ライブラリが見つからない)
^ ^ 閲覧/閲覧(コメント可) OK
^ ^ 編集/オーナー OK

読み書き対象ファイル

開発者アカウントで編集権限などをもっているとついつい忘れがちですが、ユーザ側のアカウントでも読み書き対象のファイルへのアクセス権があることを常に確認すべきです。
対応策のアイデアとしては、

  • 対象ファイルを取得するメソッドの返り値（const ss = SpreadsheetApp.openById('xxxxxxxxxxxx');）を検証する
  • 取得前にアクセス権を保有しているかチェックする（DriveApp.getFileById().getEditors();等）
  • GoogleGroupsを利用していれば、グループアドレスで権限付与を行う

プロパティストアやキャッシュサービスを活用

プロパティストア（PropertiesService）やキャッシュ（CacheService）はGASの組み込みのデータストレージサービスで、
特にプロパティストアは秘匿性の高い値を保持するのに活用する例が多いです。
これが、小さめの容量制限ですが、API系メソッド（SpreadsheetAppなど）よりかなり高速です。

下記はプロジェクトレベルのストアを利用して、処理済メッセージをID格納し、未処理メッセージのみを対象とするスクリプトです。
Gmailは個人ならスター付きや既読などで処理管理ができますが、グループアドレス宛などは個人ごとにスター/既読の管理となり、またメッセージIDも個人で変わるため、
ヘッダー内の受信サーバが管理するメッセージIDを利用して、一意性を取る必要があります。

props.gs
// Proxyを利用したプロパティストアの利用例
const props = new Proxy({
  store: PropertiesService.getScriptProperties(),
}, {
  get(target, key) {
    if (typeof target[key] !== 'undefined') return target[key];
    target[key] = target.store.getProperty(key);
    if (target[key] === null) return null;
    try {
      target[key] = JSON.parse(target[key]);
    } catch (e) {
      console.warn('can NOT parse as JSON');
    }
    return target[key];
  },
  set(target, key, newValue) {
    target[key] = newValue;
    target.store.setProperty(JSON.stringify(newValue));
    return true;
  },
});

function noticeGmailMessages() {
  const messages = getGmail('to:me subject:(重要)'); // Gmailで検索して一定条件でメッセージを配列にして返す
  const noticedGmailMessageIds = props['noticedGmailMessageIds'];
  
  // storeに`headerMessageId`がないメッセージ（新着）のみフィルター
  const noticeTargets = messages.filter(({ headerMessageId }) => !noticedGmailMessageIds.includes(headerMessageId));

  // ************************* //
  // 　　　　  通知処理   　　　　 //
  // ************************* //

  // storeに通知済メッセージを格納　＊データ容量に注意
  props['noticedGmailMessageIds'] = [...noticedGmailMessageIds, ...noticeTargets.map(({ headerMessageId }) => headerMessageId)];
}

ストアは3つのスコープがありますので、複数ユーザーに利用させるツールであるならば、留意が必要です

スコープ ユーザー範囲
Project 全員共用
Document *コンテナバインドの場合のみ 全員共用
User 個人ごと独立

以上となります！
書きなぐってしまいましたが、どれか一つでもGAS開発者にささって、開発の一助となれれば嬉しいです

Discussion