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

はじめに

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

処理時間を確認する

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

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

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

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

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

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

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

関連記事

 コメントを書く