GoogleAppsScriptの高速化のためAPIの数を減らす

このエントリーはアラタナAdventカレンダー７日目のエントリーです。

昨日は木目沢さんのここが大事だよアジャイルプロセスを"実際の経験"から語ります!でした。
アジャイル開発について実経験しないとわからない難しさや価値が伝わってきました。

---

はじめまして。平素では大変お世話になっております。

アラタナエンジニアの川原です。
今年の４月にプログラム未経験で入社し、それから挑戦と成長の毎日です。その中でも特に、最近扱っているGoogleAppsScript（以下GAS）について書きます。

GASの特性を知らずにスクリプトを書き、時間のかかるプログラムになったのでその調査を行いました。
今回使用しているスクリプトは時間計測が目的のため、1000行✖️26列のシートの全セルに対して、１文字aを入力するスクリプトです。

最初は遅いプログラムを作成していた

　前述の通りプログラム歴が浅くGASの特性を知らない私は、ネットの情報を参考につつ下のようなプログラムを作成しました。すると遅い！30秒もかかるぞ！なんなんだこれは！！

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName( 'シート1' );
  var str = 'a';
  
  for ( var i = 1; i <= 1000; i++ ) {
    for ( var j = 1; j <= 26; j++ ) {
      sheet.getRange( i, j ).setValue( str );
    }
  }
}

GASはAPIの数が増えると遅くなる

　これは不学の至る所の仕業だろうと思い調べました。どうやらGASはAPIを叩く数が多いと処理時間が長くなるようです。上記プログラムはforの二重ループ中にAPIを叩くスクリプト
sheet.getRange( i, j ).setValue( str );
を記述しており、たくさんAPIを叩くプログラムを書いていました。これでは遅いプログラムになっても仕方がなかったのです。

APIを減らすと本当に早くなるのか計測してみた

　『APIを減らすと早くなる』ってどれくらい早くなるの？と不思議に思ったので実際に計測してみました。調査の方法としては以下のようにしました。
　まず初期化した1000行✖️26列のシートを準備します。そのシートに対し文字aを入力するAPI（以下のようなあらかじめsheetをメモ化し、データを入力するスクリプト）を１回とカウントし、以下の4パターンに分類して、計測いたしました。

// パターン1,2,3の１行１列または１シートを対象にしての操作
sheet.getRange( row, column, rowCount, columnCount ).setValues( data );

// パターン4の全セルに対しての操作
sheet.getRange( i, j ).setValue( data );

1.入力するデータを作成し、APIを1回だけ叩いて入力する。
2.１列分のデータを作成し、列数分26回APIを叩いて入力する。
3.１行分のデータを作成し、行数分1000回APIを叩いて入力する。
4.全セル分26000回APIを叩いて入力する。

１０回計測して出た平均が以下の表になります。

1回叩く	26回叩く	1000回叩く	26000回叩く
1.58秒	1.83秒	2.89秒	32.1秒

　実際に測ってみてわかったことはまず、APIをたくさん叩くと明らかに遅くなりました。しかしAPIを叩く数が少ない場合に関しては、目を見張るような変化がありませんでした。1000回と26000回の差に比べ、1回と1000回の差が小さいです。これはなぜだ！？？

APIを叩き方を変えてみる

　APIを叩く回数がどれくらい実行時間に影響しているのか、気になったので再度検証です。以下のスクリプトを使用し、APIを叩く回数を1000と、5000から20000まで5000刻みの4パターンで計5パターンで計測しました。１０回計測して出た平均が以下の表になります。

function myFunction( count ) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(　'シート1'　);
  var str = 'a';
  
  var start = new Date();  
  for ( i = 1; i <= 1000; i++ ) {
    for ( j = 1; j <= count; j++ ) {
      sheet.getRange( i, j ).setValue( str );
    }
  }
  var end = new Date();

  return (　end - start　)　/　1000;
}

1000回	5000回	10000回	15000回	20000回
1.21秒	5.77秒	10.9秒	17.0秒	27.6秒

　APIを叩いた数と処理時間が比例している！しかも処理時間の差が大きい！
　1回APIを叩くたびに約0.001秒かかっています。確かにAPIを叩く数は処理時間に影響がありそうです。

データ量を変えてみる

　１回APIを叩くのに必要な処理時間は0.001秒程度です。ですがシートを全部埋めるために一括でAPIを叩いた場合には、1.58秒ほどかかっています。同じ1回だけAPIを叩いたのにこの差はなぜでしょうか？この理由はデータ量の違いからだろうと予想し、検証しました。
　初期化した1000行✖︎26列のシートに対し入力するデータ量を変更します。一括で入力するデータをaから変更します。文字列長が１のa、文字列長が10のaaaaaaaaaaおよび文字列長が50と100の4パターンで計測しました。１０回計測して出た平均が以下の表になります。

function myFunction( num ) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName( 'シート1' );
  var arr = [], data = [];
  var i, str = '', row = 1000, column = 26;
  
  for ( i = 0; i < num; i++ ){
    str += 'a';
  }
  
  for ( i = 0; i < column; i++ ) {
    arr.push( str );
  }

  for( i = 0; i < row; i++ ) {
    data.push( arr );
  }
  
  var start = new Date();  
  sheet.getRange( 1, 1, row, column).setValues( data );
  var end = new Date();
  
  return ( end - start ) / 1000;
}

1文字	10文字	50文字	100文字
1.58秒	2.17秒	3.58秒	5.37秒
やはりデータ量によってAPIの実行時間は変わるようです。こちらは１文字あたり0.04秒変わるので、APIより影響が大きかったです。

データ量とAPIを叩く回数のバランスを考える

　上記の結果から導き出せることの1つに、データをうまく分割し１つあたりのデータを軽くすると、APIを叩く数が増えるのに早くなる場合があるだろうかと思ったので、再度検証しました。1000行✖︎26列にaを一括で入力するスクリプトに手を加え、入力するデータを何分割かしてAPIを分割数だけ叩く方法に変更して計測しました。１０回計測して出た平均が以下の表になります。

function myFunction( num ){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName( 'シート1' );
  var arr = [], data = [];
  var i, str = 'a', row = 1000, column = 26, rowCount = row / num;

  for ( i = 0; i < column; i++ ) {
    arr.push( str );
  }
  
  for( i = 0; i < rowCount; i++ ) {
    data.push( arr );
  }
  
  var start = new Date();  
  for ( i = 0; i < num; i++) {
    sheet.getRange( rowCount * i + 1, 1, rowCount, column ).setValues( data );
  }
  var end = new Date();
  
  return ( end - start ) / 1000;
}

|1分割|5分割|10分割|20分割|50分割|100分割|
|--:|--:|--:|--:|--:|--:|--:|
|1.62秒|1.53秒|1.47秒|1.55秒|1.59秒|1.65秒|

　変化量は小さいですが、一括で送るよりもいくつかに分割して送る方が処理時間が短くなりました。大きなデータを送る場合は、いくつかに分割して送る方が早いようです。その一方で分割しすぎると今度はAPIを叩く時間が増加するので、結果として処理時間のかかるスクリプトになりました。

わかったこと

　APIを多く叩くと途端に処理時間に差が出てきました。ところが一方でAPIを叩く数が少ない場合に関しては、APIを叩く数を変えたところで処理時間に大きな差は出ませんでした。また一方で大きなデータを送る場合には、分割する方が早い場合もあるようです。google側の扱いやすいサイズに分割して操作するのも１つの手なのかもしれません。

まとめ

　APIを叩く数が増えると処理時間は変わるようです。しかしAPIの処理時間はとても早いので、forループ中に書くなどしない限りは気にしすぎる必要はなさそうです。また大きなデータを送る場合に限っては、分割して送った方が処理時間が小さくなる場合もあるようです。ですがかなり限定的な利用の仕方になる知識だと思います。
　とりあえず迷ったら一括でデータを操作しAPIを叩きましょう！！それが安定して早い方法の１つだと思います。

---

　明日８日目は木目沢さんの『ゆとりに関する記事』です。楽しみにしております！