ビジネス

2019/11/12

【GASデータ活用】Google Apps Scriptによる快適なデータ操作を考えてみた

こんにちは。那須野です。

Google Apps Script、通称GAS（ガス）と言えば、無料で使えるプログラミング・業務改善ツールとして有名になってきた感があります。とりあえずファイルを作って始められて、即実行できるため、「小規模なものであればとりあえずGASで」というシーンは多くなってきたのではないでしょうか。

ただし、GAS自体はただのスクリプトであり、色々なことをやろうとするとデータを保存したり、データを取得したりなどのデータ操作が必要になってきます。GoogleスプレッドシートやGoogleドライブなどの様々なGoogleアプリとネイティブに繋がるGASでは色々なデータ操作ができるので、どういったデータ操作が実際に快適かを検証してまとめてみることにしました。

※なお、この投稿は、2019/11/12(火)開催の【エンジニア交流会】Google Apps Script 活用ミートアップ #5のLT用スライドを作る際の元ネタです。ご留意ください。なおスライド自体はこちらをご参照ください。

ちなみに関連記事はこちらです。

それでは本編をどうぞ。

GASの三大制約

無料で使えるGASを実際に実務で使ううえでの二大制約は、

①実行時間に上限がある

②スクリプトファイル自体にはデータを保存できない（例外あり）

③バージョン管理が弱い

の3つでしょう。それぞれ確認してみます。

①実行時間に上限がある

これは有名な話ですが、スクリプトの関数を実行してから一定以上の時間が経つと、

「起動時間の最大値を超えました。」

などというメッセージと共に関数が強制終了します。ちなみにGoogle無料アカウントやBasicプランの上限は6分、BusinessプランやEnterpriseプランのG-suiteの上限は30分となっており、まあ普通に使っている分には問題ないのですが、ループ処理を繰り返すものや重たいデータを加工する等をしようとすると落ちるので注意が必要です。

時間がかかる関数は、リファクタリング（≒ソースコードを綺麗に書き直す）ことによって高速化を試みたり、また関数を分割して、複数回、段階的に実行するように変えたりなどの工夫が対策としては考えられるでしょう。

②スクリプトファイル自体にはデータを保存できない（例外あり）

これはメリットともデメリットともとれる内容なのですが、GAS自体には原則としてデータを保存できないため、進捗状況や加工したデータを保存しようとするとGoogleスプレッドシートを始めとした他のGoogleアプリに保存することが必要になってきます。（原則の例外については後述します）

どのGoogleアプリに保存するにしても、基本的にはGoogleドライブの海に埋もれかねないので、整理が肝要です。どのファイルにデータを保存しているか、どのファイルからデータを引用しているかといったファイル同士の関連は、フォルダ整理やソースコードの記述などで分かりやすくしておく必要があります。

（この点は、とりわけExcel VBAのようにスクリプトとデータが1つのファイルにまとまった環境に慣れ親しんだ人にとっては大きな壁になりがちです。）

③バージョン管理が弱い

バージョン管理の問題は、GASをウェブアプリケーションや実行可能APIとして公開する場合に発生します。

GASはバージョン管理できるといっても、バージョン管理の本命であるGitHubのようにきめ細やかなバージョン管理ができるわけではなく、ファイル全体での一直線のバージョンが管理できるだけです。複数人での並行開発には辛いものがあり、またバージョンごとに1行メモは残せるものの、具体的にどの部分が更新されたかは分かりません。少し複雑なものを作ろうとすると、このデメリットは一気に顕在化します。

また、関数を定期実行できる便利なプロジェクトトリガーについても、プロジェクトトリガーから実行する関数は本番環境に公開したコードではなく開発環境で書いているコードになるという半分バグみたいな仕様もあります。そのため、開発環境だからと言って、トリガーを使っている場合はトリガーの呼び出す関数や、その関数が呼び出す関数をむやみやたらと書き換えないという注意も必要です。

（このあたりは、Google公式ツールの『clasp』を使えば部分的に解決はできます。この記事では割愛しますが。）

さて、こんな三大制約の前でも、まあ現場発信のプロトタイピングツールみたいに使うのなら良いのではないかと感じますので、初級編、中級編、上級編にわけてデータ操作について見ていきましょう。

初級編　スプレッドシートのやり取りを高速化

GASで扱うデータをどこに保存するかという質問、100人いれば恐らく95人くらいはGoogleスプレッドシートと答えるでしょう。そう言い切れるぐらい『スプレッドシート＝データ』というイメージは一般的だと思います。もちろんGASからも、SpreadsheetAppライブラリを使ってスプレッドシートの作成や更新、削除などのあらゆる処理が可能です。

ただし、GASの特徴として、他アプリへのアクセスに極端に時間がかかるという制約があるため、細かく何度もアクセスすると、とにかく時間がかかります。そのため、まとめて一括でアクセスするというのが高速化のための基本の姿勢となってきます。

まず、シートの全てのセルの文字列を結合する処理で、

①各セルをgetRange()で都度取得し、そのセルの値をgetValue()で都度取得する場合

②getDataRange().getValues()で一括取得する場合

の違いを見てみます。

//①各セルをgetRange()で都度取得し、そのセルの値をgetValue()で都度取得する場合
function getSpeadsheet_each(){

  var spreadsheetId = '{your_spreadsheet_id}';
  var file = SpreadsheetApp.openById(spreadsheetId);
  var sheet = file.getSheetByName('{your_sheet_name}');
  
  var buf = '';
  for (var iRow = 1; iRow <= sheet.getLastRow(); iRow++){
    for (var iCol = 1; iCol <= sheet.getLastColumn(); iCol++){
      buf += sheet.getRange(iRow, iCol).getValue();
    }
  }
  
}

//②getDataRange().getValues()で一括取得する場合
function getSpeadsheet_bulk(){

  var spreadsheetId = '{your_spreadsheet_id}';
  var file = SpreadsheetApp.openById(spreadsheetId);
  var sheet = file.getSheetByName('{your_sheet_name}');
  var table = sheet.getDataRange().getValues();
  
  var buf = '';
  for (var iRow = 0; iRow < table.length; iRow++){
    for (var iCol = 0; iCol < table[0].length; iCol++){
      buf += table[iRow][iCol];
    }
  }
  
}

//①各セルをgetRange()で都度取得し、そのセルの値をgetValue()で都度取得する場合

function getSpeadsheet_each(){

var spreadsheetId = '{your_spreadsheet_id}';

var file = SpreadsheetApp.openById(spreadsheetId);

var sheet = file.getSheetByName('{your_sheet_name}');

var buf = '';

for (var iRow = 1; iRow <= sheet.getLastRow(); iRow++){

for (var iCol = 1; iCol <= sheet.getLastColumn(); iCol++){

buf += sheet.getRange(iRow, iCol).getValue();

}

//②getDataRange().getValues()で一括取得する場合

function getSpeadsheet_bulk(){

var spreadsheetId = '{your_spreadsheet_id}';

var file = SpreadsheetApp.openById(spreadsheetId);

var sheet = file.getSheetByName('{your_sheet_name}');

var table = sheet.getDataRange().getValues();

var buf = '';

for (var iRow = 0; iRow < table.length; iRow++){

for (var iCol = 0; iCol < table[0].length; iCol++){

buf += table[iRow][iCol];

}

今回は11行×1列ほどのスプレッドシートで、それぞれ5回実行して比較した平均値は、

①4.094秒

②0.476秒

という大差で一括の②が圧勝、呼び出し回数を減らす大切さがはっきりと数字で出ました。呼び出し回数は、①が24回、②が2回なので、呼び出し回数にほぼ比例するような傾向も見えますね。

なお、おまけ編として、シートの呼び出し関数を1行で書くのと都度変数に格納しながら呼ぶのとではどう違うのかも検証したかったので、③ということで

//③getDataRange().getValues()で一括取得する場合（短縮版）
function getSpeadsheet_bulk_shortest(){

  var spreadsheetId = '{your_spreadsheet_id}';
  var table = SpreadsheetApp.openById(spreadsheetId).getSheetByName('{your_sheet_name}').getDataRange().getValues();
  
  var buf = '';
  for (var iRow = 0; iRow < table.length; iRow++){
    for (var iCol = 0; iCol < table[0].length; iCol++){
      buf += table[iRow][iCol];
    }
  }
  
}

//③getDataRange().getValues()で一括取得する場合（短縮版）

function getSpeadsheet_bulk_shortest(){

var spreadsheetId = '{your_spreadsheet_id}';

var table = SpreadsheetApp.openById(spreadsheetId).getSheetByName('{your_sheet_name}').getDataRange().getValues();

var buf = '';

for (var iRow = 0; iRow < table.length; iRow++){

for (var iCol = 0; iCol < table[0].length; iCol++){

buf += table[iRow][iCol];

}

を追加で試したところ、5回実行の平均タイムはそれぞれ、

①4.094秒

②0.476秒

③0.490秒　←New!

となり、1行で書く③と都度変数に格納しながら呼ぶ②とでは明確な差がないことも分かりました。親オブジェクトに対する関数の使い道や、コード自体の可読性を考えても、スプレッドシートやシートなどのオブジェクトに格納してからgetDataRange().getValues()をした方がよさそうです。

中級編　スプレッドシートの限界を超える

データを保存するアプリの定番ともいえるスプレッドシートですが、実務面で見ると様々な限界が見えてきます。特にクリティカルな限界を3つ挙げると、

1つのセルに入力できる文字数は半角5万文字が上限となっている。
全シートの合計で、セル数の上限が200万セルになっている。1つのファイル内でシートを増やしても全体の上限は変わらないため、気付かず上限を迎えるリスクが高くなる。
入力先はセルであるがゆえに、「=」から始まる文字列を入力しようとすると高確率で数式エラーになってGASが強制終了する。

があるため、ログ的な大量のデータを追記していったり、ユーザーに入力させた文字列をそのままセルに格納しようとしたりすると一定確率でエラーが発生するため、決して安定したシステムとは言えません。

その場しのぎで問題回避しようとすると、複数のスプレッドシートファイルに拡張保存しようとしたり、半角5万文字を超えるデータは分割保存しようとしたり、ユーザー入力データの頭には「#」などの文字を付けて入力エラーを防ぐようにしたりといった方法はあるにはありますが、実装方法としてお世辞にも綺麗とは言えません。

としたときに、スプレッドシート以外のデータ保存先を考える必要が出てきます。

上級編　スプレッドシート以外のデータ処理

スプレッドシート以外のGoogleアプリの中でデータを保存／取得できるアプリとして、Googleドキュメント、Googleドライブ、プロパティサービスの3つを挙げたいと思います。

Googleドキュメントは、普通に使っている分には文字数の制限がないように感じますが、実際は『半角102万文字／ファイル』という制限があります。多いような少ないような上限値ですが、テキストファイルにして約1.5MBほどのファイルサイズであるため、しっかりテキストを書きこもうとすると上限に達しがちです。

一方のGoogleドライブは、テキストファイルを直接保存することができ、そのファイルサイズには『50MB／ファイル』という上限が設定されています。Googleドキュメントの文字列が約1.5MBほどであるのと比べると、30倍以上の容量が見込めることが分かります。

最後のプロパティサービスは、GAS内部にデータを保存できる仕組みです。GASからは非常に簡単に呼び出せますが、容量の上限は『9KB／プロパティ』,『500KB／ファイル』とかなり小規模であり、かつ文字列としてのみ保存されるため、ずばりパラメータを保存するための機能と言えるでしょう。

さて、半角5万文字を超えるデータは分割保存せざるを得ないスプレッドシートに対して、GoogleドキュメントやGoogleドライブ（テキスト）は一括保存できる文字数の上限が桁違い大きいことが分かりましたが、気になるところは実際の処理速度です。GoogleドキュメントとGoogleドライブについては、以下の2つのコードを使いながら、ファイルサイズを変更しながら比較してみます。

//④Googleドキュメントからのデータ取得
function getDocument(){

  var id = '{your_document_id}';
  var file = DocumentApp.openById(id);
  var text = file.getBody().getText();
  
}

//⑤Googleドライブのテキストからのデータ取得
function getText(){

  var id = '{your_text_id}';
  var file = DriveApp.getFileById(id);
  var text = file.getBlob().getDataAsString();
  
}

//⑥GASのプロパティサービスからのデータ取得
function getProperty(){

  var properties = PropertiesService.getScriptProperties();
  var text = properties['{your_property_name}'];
  
}

//④Googleドキュメントからのデータ取得

function getDocument(){

var id = '{your_document_id}';

var file = DocumentApp.openById(id);

var text = file.getBody().getText();

}

//⑤Googleドライブのテキストからのデータ取得

function getText(){

var id = '{your_text_id}';

var file = DriveApp.getFileById(id);

var text = file.getBlob().getDataAsString();

}

//⑥GASのプロパティサービスからのデータ取得

function getProperty(){

var properties = PropertiesService.getScriptProperties();

var text = properties['{your_property_name}'];

}

肝心のファイルサイズですが、今までスプレッドシートで検証していた文字列と同じもの（716KB）を基準容量としつつ、Googleドキュメントの上限である2倍（≒半角102万文字）、Gooogleドライブ（テキスト）の上限である71倍（≒50MB）も比較検証しました。

その結果がこちら。

9KB未満のような小データの場合は、プロパティサービスが最速であることが分かりました。それ以上だと、Googleドキュメントで保存できる範囲内ではGoogleドキュメントが最速ですが、大容量のデータを扱おうとすると、早々にデータ容量の上限を迎えることも分かりました。

一方のGoogleドライブ（テキスト）は、上限の50MBでも3秒ほどと実務に耐えうる高速さを見せています。データ容量が増えても読み込み速度はあまり増えない傾向にあるのは素晴らしいです。Googleスプレッドシートの分割保存だと13秒ほどと実務的に厳しい遅さになっているところを考えると、Googleドライブの安定感が光ります。

ということでまとめると、

9KB未満の短いパラメータはプロパティサービスに保存
人が手で更新するデータ（あまり大きくないものに限る）は、Googleスプレッドシートに保存
約1.5MB（＝半角102万文字）までならGoogleドキュメントに保存
50MBまでならGoogleドライブ（テキスト）に保存
50MBを超える場合は、Googleスプレッドシートにセル分割保存するか、Googleドライブ（テキスト）にファイル分割保存するかの二択

という結果になると思われます。

ただ、ファイル容量を超えて「ドキュメントにアクセスできません。しばらくしてからもう一度お試しください。」などと意味不明なアラートで頭を悩ませるリスクが見え隠れするGoogleドキュメントは意外と使いづらいので、実務的にはプロパティサービス×Googleスプレッドシート×Googleドライブ（テキスト）の掛け合わせ運用が望ましいと感じます。

なのでファイナルアンサーは、