はじめに

今回はGASを用いたスプレットシートの読み書きの時間がどれくらいかかるのかを調べた内容を紹介したいと思います。
GASを使って簡易なwebアプリを使ってスプレットシートを操作することはよくあることだと思います^*1。この記事を書くにあたっての背景としては、簡易なwebアプリを作成した時にレスポンスが想像以上に悪く、それぞれの処理時間を調べてみたと言ったものになります。　

計測する処理について

今回はスプレットシートをDBのテーブルのように見立てて使用します。大雑把な処理としてはスプレットシートのQUERY関数を使用して集計し、結果を取得すると言った流れになります。
GASでシートの内容を集計する場合、シートの値をfor文などで集計するより、SQLのように振る舞うQUERY関数を使用してシート内で直接集計する方が結果的に早くなるらしいです^*2。ということで、計測する処理が以下の関数になります。

シート1にデータが蓄積され、queryシートでシート1の内容を集計するQUERY関数を挿入して使用します。

QUERY関数に関してはココ^*3やココ^*4を参考にしてください。


function getRecord(user = "hoge"){

  var sheets = SpreadsheetApp.openById(SheetId);
  var query_sheet = sheets.getSheetByName("query");  

  query = `=QUERY(シート1!A:B, "select * where A = '${user}'", 1)`

  query_sheet.getRange(1, 1).setValue(query)

  const last_row = query_sheet.getLastRow();
  const last_col = query_sheet.getLastColumn();

  result_record = query_sheet.getRange(2, 1, last_row-1, last_col).getValues()[0]; 
  
  console.log(result_record); // 取得したrecordを表示する

  return result_record
}

スプレットシートの内容はこんな感じ。

シート1にデータが蓄積され、queryシートでシート1の内容を集計するQUERY関数を挿入して使用します。queryシートは関数側で値を書き込むためのシートになるため、何も編集はしません。QUERY関数を書き込むと以下のように、集計結果がシート上に反映されます。

計測方法

計測方法はconsole.time()とconsole.timeEnd()を使って計測します。関数の使い方は測定をそれぞれの関数の引数に計測区間を識別するlabelを設定し、開始したい部分の直前にconsole.time()を挿入し、処理の終了直後にconsole.timeEnd()を挿入するだけでokです。計測結果の時間はmsでコンソール上に表示されます。


var label = "ラベル１"

console.time(label);

//　計測したい処理　
hoge()
  ...

console.timeEnd(label);

今回はこんな感じで計測します。プログラム中にのSheetIdはスプレットシートのIDに相当します。

function getRecord(user = "hoge"){

  // スプレットシートの取得(読み込み)
  console.time("Read sheets");
  var sheets = SpreadsheetApp.openById(SheetId);
  var query_sheet = sheets.getSheetByName("query");  
  console.timeEnd("Read sheets");

  // QUERY関数の挿入(書き込み)
  console.time("Insert QUERY");
  query = `=QUERY(シート1!A:B, "select * where A = '${user}'", 1)`
  query_sheet.getRange(1, 1).setValue(query)
  console.timeEnd("Insert QUERY");

  // QUERY関数の出力範囲を取得(読み込み)
  console.time("Get QUERY result range");
  const last_row = query_sheet.getLastRow();
  const last_col = query_sheet.getLastColumn();
  console.timeEnd("Get QUERY result range");

  // QUERY関数の出力を取得(読み込み)
  console.time("Get QUERY result");
  result_record = query_sheet.getRange(2, 1, last_row-1, last_col).getValues()[0]; 
  console.timeEnd("Get QUERY result");
  
  console.log(result_record); // 取得したrecordを表示する

  return result_record
}

計測区間はコード中のコメントにある通りに以下の4区間です。

スプレットシートの取得(読み込み)
QUERY関数の挿入(書き込み)
QUERY関数の出力範囲を取得(読み込み)
QUERY関数の出力を取得(読み込み)

実際に計ってみた

GASのコンソールで実行すると以下のような出力が返ってきます。

5回程実行し、平均値を求めてみます。結果としては以下のようになりました。

区間	1回目	2回目	3回目	4回目	5回目	平均時間(ms)
スプレットシートの取得 (読み込み)	201	607	181	360	221	314
QUERY関数の挿入 (書き込み)	4	10	5	7	10	7.2
QUERY関数の出力範囲を取得 (読み込み)	295	451	243	479	280	349.6
QUERY関数の出力を取得 (読み込み)	88	148	71	144	80	106.2
合計	588	1216	500	990	591	777

一連の処理時間の平均は777msでした。個人的な印象としては結構時間がかかっているなといった感じです。加えて、書き込みよりも読み込みの方が時間がかかるような結果でした（意外な結果ですね）。

一番時間のかかった処理がシート内の最終行と最終列を取得する"QUERY関数の出力範囲を取得"の区間でした。これはシート上の探索が含まれるので時間がかかる感じでしょうか？（詳しい人教えてください…）

また、"QUERY関数の出力範囲を取得"と"QUERY関数の出力範囲を取得"の区間はシートに関する処理が2つずつあるので、この区間の1処理あたりは150ms~170msと言ったところでしょう。

読み込みの種類によってもバラツキがあると思いますが、総じて読み込みの処理時間は100ms~170ms程度かかるみたいでした。シートの読み込みに関する処理が連続するとかなりの処理時間になりそうですね…。

おわりに

GASを用いたスプレットシートの読み書きの時間がどれくらいかかるのかを調べてみました。結果としては、書き込みよりも読み込みに時間がかかり、その処理時間は100ms~170ms程度でした。処理速度の向上にはスプレットシートの読み込みはなるべく減らすことがコツになるようでした。また、スプレットシートへのアクセスは今回の処理以外にも、SheetsAPIと言うものもあります。今度はSheetsAPIの処理時間と比べてみるたいですね。

*1. よくあることなのかは良く知らない。
*2. https://pineplanter.moo.jp/non-it-salaryman/2021/06/03/gas-query/
*3. https://support.google.com/docs/answer/3093343?hl=ja
*4. https://developers.google.com/chart/interactive/docs/querylanguage?sjid=16238243037045119177-AP&hl=ja