google apps script sheet2jsonの高速化に挑戦


しかだよ。
google apps scriptで書いたsheet2jsonを某所で使って頂いてるのですが、「重すぎて話にならないわ」と改善要望を頂いたのでとりあえず調査してみることにしました。

sheet2jsonを処理の流れ

  1. まずsheetの文字が書いてあるところを範囲選択。
  2. 範囲選択した箇所を１行ずつJSONに変換。
  3. 行の中に参照(#hoge or #[]hoge)があればそのシートに移動して1の処理を繰り返す(再帰)。

処理時間を見てどこが重いのか判断する

とりあえず
Logger.log(new Date().toLocaleString());
を要所に記述して処理時間を見てみました。

sheet2json:2011/03/06 15:34:10 JST
generateSheet2Json start:2011/03/06 15:34:10 JST
 getKeyValueRanges start:2011/03/06 15:34:10 JST
 getKeyValueRanges end:2011/03/06 15:34:11 JST
 generateJsons start:2011/03/06 15:34:11 JST
   generateSheet2Json start:2011/03/06 15:34:11 JST
    getKeyValueRanges start:2011/03/06 15:34:11 JST
    getKeyValueRanges end:2011/03/06 15:34:13 JST
    generateJsons start:2011/03/06 15:34:13 JST
      generateSheet2Json start:2011/03/06 15:34:13 JST
       getKeyValueRanges start:2011/03/06 15:34:13 JST
       getKeyValueRanges end:2011/03/06 15:34:22 JST
       generateJsons start:2011/03/06 15:34:22 JST
         generateSheet2Json start:2011/03/06 15:34:42 JST
          getKeyValueRanges start:2011/03/06 15:34:42 JST
          getKeyValueRanges end:2011/03/06 15:35:08 JST
          generateJsons start:2011/03/06 15:35:08 JST
          generateJsons end:2011/03/06 15:35:09 JST
以下略

getKeyValueRangesの時間が長いですね。１シートのJSON生成処理に約１０秒かかっていて、その内の９秒が範囲選択です。(最大で３０秒)
どうやら範囲選択にけっこう時間がかかってるみたいです。しかも再帰するたびに呼ばれているのでオワタ感じです。

原因

sheetの中からJSONにするセルを範囲選択する必要があるのですが、今はloop処理で「隣(下)に文字があるか？」を見ながら進んでいって範囲を決めています。ここらへんが重そうですね。

対策

対象となるセルをあらかじめ名前付けして、getRangeByName()で一発で範囲を指定する。

欠点

シート作成時に範囲選択しなくてはいけないので、うっかり指定を忘れてsheetと出力されたjsonで不一致が起きそうです。

ソースコード

変更前は while でループ処理やってました。

var ranges = getKeyValueRanges(sheet);
var keys = ranges.keyRange.getValues()[0];
var values = ranges.valueRange.getValues();

/*
 *keyとvalueの範囲を指定する。
 */
function getKeyValueRanges(sheet){
  var range = {};
  var colIndex = 1;
  while(true){
    range = sheet.getRange(1, colIndex, 1, 1);
    var value = range.getValue();
    if(!value){
      break;
    }
    colIndex++;
  }
  var valueRange = getValueRange(sheet, colIndex);
  var keyRange = sheet.getRange(1, 1, 1, colIndex-1);
  return {keyRange:keyRange,valueRange:valueRange};
}
/*
 *valueの範囲を指定する。
 */
function getValueRange(sheet,colIndex){
  var range = {};
  var rowIndex = 1;
  while(true){
    range = sheet.getRange(rowIndex+1, 1, 1, 1);
    var value = range.getValue();
    if(!value){
      break;
    }
    rowIndex++;
  }
  return sheet.getRange(2, 1, rowIndex-1, colIndex-1); 
}

変更後
getRangeByNameにしました。

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ranges = ss.getRangeByName(sheet.getName());
  var keys = ranges.getValues()[0];
  var values = ranges.getValues().slice(1);

１秒未満になりました。可読性も上がったし、やったねたえちゃん。