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);

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