2015年1月23日金曜日

Google Apps Script "getValues"のベストな範囲指定の模索

こんにちは、井下です。
今回もGoogle Apps Scriptについて書いていきます。

以前も書いたことがありますが、スプレッドシートの値を取得する場合は、
厳密に1つずつ取得するより、範囲を指定して1度に取得する方が早くなります。
※Google Apps Scriptの処理速度検証

極力getValueでなく、getValuesを使おうという話ですね。

ただし、取得できるのはN×Mの範囲しか指定できないので、
次みたいな場合はどう取得するのが一番早いんだろうとちょっと悩みませんか？（私だけ？）

※100×100のデータ中、セルが緑色の部分のデータ（A1～CV1、A2～A100、AN2～AN100）が欲しい

3つ範囲を指定し、それぞれの範囲ごとにgetValuesで値を取得する（getValuesを3回実行する）方法が一番早いと予想しています。
無駄なデータは取らないに越したことはありませんしね。

ただ、Google Apps Scriptの仕様で違う結果もあり得るのかもしれないと考えたので、
次のコードで実際に値を取得して比較してみました。

function getValuesTest() {
  var sheet = SpreadsheetApp.openById('XXXXXXXXX').getSheetByName('シート1')
  Logger.log("START getValuesbByRangeOnce")
  getValuesbByRangeOnce(sheet)
  Logger.log("END getValuesbByRangeOnce")
  
  Logger.log("START getValuesbByRangeTwice")
  getValuesbByRangeTwice(sheet)
  Logger.log("END getValuesbByRangeTwice")

   Logger.log("START getValuesbByRangeThrice")  
  getValuesbByRangeThrice(sheet)
  Logger.log("END getValuesbByRangeThrice")  
}

 function getValuesbByRangeOnce(sheet) {
  var values = sheet.getRange(1, 1, 100, 100).getValues()
  return values
}

 function getValuesbByRangeTwice(sheet) {
  var values = new Array();
  var values1 = sheet.getRange(1, 1, 1, 100).getValues()
  var values2 = sheet.getRange(2, 1, 99, 40).getValues()
  
  values.push(values1)
  values.push(values2)
  
  return values
}

 function getValuesbByRangeThrice(sheet) {
  var values = new Array();
  var values1 = sheet.getRange(1, 1, 1, 100).getValues()
  var values2 = sheet.getRange(2, 1, 99, 1).getValues()
  var values3 = sheet.getRange(2, 40, 99, 1).getValues()
  
  values.push(values1)
  values.push(values2)
  values.push(values3)

   return values
}

■getRangeの補足
    getRange(始点となる行位置, 始点となる列位置, 取得する範囲[行], 取得する範囲[列])

    getRange(5, 10, 3, 2)なら、"5行目の10(J)列"を始点として、"3行2列"の範囲を示します。


■getValuesbByRangeOnce、getValuesbByRangeTwice、getValuesbByRangeThriceの補足
    getValuesbByRangeOnce
    範囲指定は1回だけ、100×100のデータを全て取得しています。
    当然、一番遅くなるものと予想。

    getValuesbByRangeTwice
    範囲指定は2回、下の図みたいな取り方です。
    中途半端な取り方ですが、範囲指定1回と3回の中間の速度になるものと予想。

  getValuesbByRangeThrice
    範囲指定は3回、無駄なデータを取得しません。
    一番早くなるものと予想。


■getValuesTestの実行結果
getValuesTestを実行すると、全てのメソッドを実行するようになっているので、
実行後に「実行トランススクリプト」で各メソッド内でどれだけ処理に時間がかかるのか分かるようになっています。

実行トランススクリプトの内容はこちら
[15-01-21 13:42:15:829 JST] 実行を開始しています
[15-01-21 13:42:15:944 JST] SpreadsheetApp.openById([XXXXXXXXX]) [0.091 秒]
[15-01-21 13:42:15:945 JST] Spreadsheet.getSheetByName([シート1]) [0 秒]
[15-01-21 13:42:15:945 JST] Logger.log([START getValuesbByRangeOnce, []]) [0 秒]
[15-01-21 13:42:15:945 JST] Sheet.getRange([1, 1, 100, 100]) [0 秒]
[15-01-21 13:42:16:103 JST] Range.getValues() [0.156 秒]
[15-01-21 13:42:16:111 JST] Logger.log([END getValuesbByRangeOnce, []]) [0 秒]
[15-01-21 13:42:16:112 JST] Logger.log([START getValuesbByRangeTwice, []]) [0 秒]
[15-01-21 13:42:16:113 JST] Sheet.getRange([1, 1, 1, 100]) [0 秒]
[15-01-21 13:42:16:117 JST] Range.getValues() [0.004 秒]
[15-01-21 13:42:16:118 JST] Sheet.getRange([2, 1, 99, 40]) [0 秒]
[15-01-21 13:42:16:132 JST] Range.getValues() [0.013 秒]
[15-01-21 13:42:16:134 JST] Logger.log([END getValuesbByRangeTwice, []]) [0 秒]
[15-01-21 13:42:16:135 JST] Logger.log([START getValuesbByRangeThrice, []]) [0 秒]
[15-01-21 13:42:16:135 JST] Sheet.getRange([1, 1, 1, 100]) [0 秒]
[15-01-21 13:42:16:137 JST] Range.getValues() [0.001 秒]
[15-01-21 13:42:16:137 JST] Sheet.getRange([2, 1, 99, 1]) [0 秒]
[15-01-21 13:42:16:139 JST] Range.getValues() [0.001 秒]
[15-01-21 13:42:16:139 JST] Sheet.getRange([2, 40, 99, 1]) [0 秒]
[15-01-21 13:42:16:141 JST] Range.getValues() [0.001 秒]
[15-01-21 13:42:16:142 JST] Logger.log([END getValuesbByRangeThrice, []]) [0 秒]
[15-01-21 13:42:16:142 JST] 実行が無事終了しました（合計ランタイム 0.29 秒）

赤字がgetValuesbByRangeOnce
青字がgetValuesbByRangeTwice
黄字がgetValuesbByRangeThrice

それぞれの計測結果になります。
予想通り、getValuesbByRangeThriceが一番早い…　と見えるのですが、
よくよく見てみると、次の部分が気になりました。

～略～
[15-01-21 13:42:16:112 JST] Logger.log([START getValuesbByRangeTwice, []]) [0 秒]
[15-01-21 13:42:16:113 JST] Sheet.getRange([1, 1, 1, 100]) [0 秒]
[15-01-21 13:42:16:117 JST] Range.getValues() [0.004 秒]
～略～
[15-01-21 13:42:16:135 JST] Logger.log([START getValuesbByRangeThrice, []]) [0 秒]
[15-01-21 13:42:16:135 JST] Sheet.getRange([1, 1, 1, 100]) [0 秒]
[15-01-21 13:42:16:137 JST] Range.getValues() [0.001 秒]
～略～

同じ範囲を取得してからgetValuesを実行しているのに、
getValuesbByRangeTwiceは0.004秒、getValuesbByRangeThriceは0.001秒になっています。

その後、メソッドの実行順序を変えたりしてみて気付いたのですが、
最初に実行するgetValuesに一番時間がかかり、以降のgetValuesはあまり時間がかからなくなりました。



不思議に思いつつも各メソッド1つずつを個別に実行したところ、次の結果になりました。

getValuesbByRangeOncegetValuesbByRangeTwicegetValuesbByRangeThrice
1回目0.159秒0.17秒（0.165 + 0.005）0.113秒（0.113 + 0 + 0）
2回目0.137秒0.123秒（0.118 + 0.005）0.157秒（0.157 + 0 + 0）
3回目0.124秒0.139秒（0.134 + 0.005）0.178秒（0.178 + 0 + 0）
平均0.14秒0.144秒0.149秒

予想に反してgetValuesbByRangeOnceが一番早い結果になりましたが、他のメソッドと大差ないです。
getValuesbByRangeThriceが最速の場合もありますし、揺らぎの範囲内でしょう。

先述しましたが、getValuesbByRangeTwiceとgetValuesbByRangeThriceのように、
複数回getValuesを実行する場合、最初のgetValuesに時間がかかり、
以降のgetValuesでは時間がかかっていません。（ほぼ0秒）

getValuesで取得する範囲が被っているのであれば、裏でキャッシュなりを適応して
早く処理されるようになっている…　のような想像ができるのですが、
getValuesbByRangeTwiceとgetValuesbByRangeThriceは内部で指定している範囲は被っていません。

"実はgetValuesはシートのある程度の範囲の値を記憶している"と考えれば話は通るのですが、
そんな不思議な仕様にする必要性が感じられないので、いまひとつ理由が分かりません。


最初のgetValuesで時間がかかる理由に対して、合点の行く説明はつかないのですが、
少なくとも最初に予想していた「無駄なデータを取らない＝早い」は成り立っていないようです。

もっと膨大なデータが対象であれば、結果は変わってくるのかもしれませんが、
そんなデータが対象なら、Googleが提供しているFusion Tablesを使うほうが手っ取り早いので、
あまり調べる意味がなさそうです。



今回の疑問の結論として、スプレッドシートからgetValuesで値を取得するときは、
最初に実行するときに時間がかかり、それ以降は範囲に依存せずに時間がかからないため、
どんな取り方をするかはそこまで悩まなくてよさそうです。

他の実装部分と相談して、使いやすい形式で取れる方法を選んでおけばいいと思います。


次回はGoogle Apps Scriptを使っていてのちょっとしたTipsか、
今回少しワードが出てきたFusion Tablesを取り上げようと考えています。
投稿者 時刻:
ラベル:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)