GoogleAppsScriptのスプレッドシート処理を高速化する

2019/03/282020/04/24

はじめに

スプレッドシート絡みのGoogleAppsScriptの高速化案です。
タイマー実行時間がきつきつなので節約します。

処理時間を確認する

Apps Script - 実行数

GoogleAppsScriptダッシュボードの実行数で、期間として処理時間が表示されている。

※一日の合計時間の取得方法は不明

スリープを使用しない

Utilities.sleep(milliseconds);

スリープした時間も実行時間に含まれます。できるだけ、スリープしない設計とすることで実行時間を節約できます。

スプレッドシートのAPIコールを減らす（getLastRow()）

悪い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  for (var i=0; i<sheet.getLastRow(); i++) {
    Logger.log(i);
  }
  Logger.log(new Date()-start); // 160377.0

良い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var row = sheet.getLastRow();
  for (var i=0; i<row; i++) {
    Logger.log(i);
  }
  Logger.log(new Date()-start); // 780.0

	悪い例	良い例
時間	160秒	1秒

※1000件のデータに対して試験しています

スプレッドシートにセル単位でアクセスしない

悪い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var row = sheet.getLastRow();
  for (var i=0; i<row; i++) {
    var value = sheet.getRange(i+1, 1).getValue();
    Logger.log(value);
    //sheet.getRange(i+1, 1).setValue(value * 2);
  }
  Logger.log(new Date()-start); // 3064.0

良い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var row = sheet.getLastRow();
  var values = sheet.getRange(1, 1, row, 1).getValues()
  for (var i=0; i<row; i++) {
    Logger.log(values[i][0]);
    values[i][0] = values[i][0] * 2;
  }
  sheet.getRange(1, 1, row, 1).setValues(values)
  Logger.log(new Date()-start); // 905.0

	悪い例	良い例
getValue	3秒	1秒
setValue	600秒以上	1秒

※1000件のデータに対して試験しています

スプレッドシートに末尾1行追加ならappendRowを使う

悪い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var row = sheet.getLastRow();
  sheet.getRange(row+1, 1, 1, 1).setValues([['append']])
  Logger.log(new Date()-start); // 623.0

良い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  sheet.appendRow(['append']);
  Logger.log(new Date()-start); // 277.0

	悪い例	良い例
時間	623ms	277ms

キャッシュを利用する

悪い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var row = sheet.getLastRow();
  var values = sheet.getRange(1, 1, row, 1).getValues()
  Logger.log(values[10][0]);
  Logger.log(new Date()-start); // 371.0

良い例  var start = new Date();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var row = sheet.getLastRow();
  var values = sheet.getRange(1, 1, row, 1).getValues()
  var cache = CacheService.getScriptCache();
  cache.put('key', JSON.stringify(values), 21600);
  Logger.log(new Date()-start); // 545.0

  var start = new Date();
  var cache = CacheService.getScriptCache();
  var values = JSON.parse(cache.get('key'));
  Logger.log(values[10][0]);
  Logger.log(new Date()-start); // 18.0

	悪い例	良い例
時間	371ms	18ms

下記の条件に合致する場合、キャッシュが有用です。

元データが変更しないことがわかっている
- キャッシュが期限切れになった場合のみ更新する
元データの変更タイミングを知ることができる
- 変更タイミングでキャッシュの内容も合わせて変更する
最新のデータを常に利用する必要はない場合
- 一定時間毎にキャッシュのデータを更新する

キャッシュに一時保存して、一定期間後にスプレッドシートに書き込む方法も考えられます。ただし、データが消失するリスクは考慮すること。

また、キャッシュにすべてのデータを格納せず、一定時間内に必要な分のみ格納することで、キャッシュの容量問題を回避できます。

逐次処理からバッチ処理にする

読み出しと書き込み処理の回数が減少するため、高速化します。ただし、メモリ上限と処理時間超過が発生する可能性増加します。

バグ取りの日々