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

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

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

Transcript

1. 2019年 11月 那須野 拓実 Google Apps Scriptによる 快適なデータ操作を考えてみた
2. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 2 自己紹介 那須野 拓実 （なすの たくみ） https://takuminasuno.com/ja/ たなぐら応援大使（福島県棚倉町）。 トリプレッソを勝手に応援する人。 ネイチャーフォト中心の多言語ブログを書いてます。 本業はIT&マーケティング界隈で ナレッジマネジメントとかデータ分析とかの何でも屋。 半年間の育休明けで、家事育児と外働きのバランスを模索中。 ※上記の自己紹介は、2019年11月執筆当時のものです。 コンタクトはこちらからどうぞ！ ⇒ takumi_nasuno
3. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 3 今日お話しすること GASを使うとき データをどこに保存するべきか
4. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 4 今日お話しすること
5. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 5 今日お話しすること 本当にスプレッドシートで 良いのだろうか？
6. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 6 GASの制約 GASの気になる制約
7. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 7 GASの制約 起動時間6分の壁 （※Business, Enterpriseプランは30分です） GASの気になる制約
8. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 8 GASの制約 起動時間6分の壁 （※Business, Enterpriseプランは30分です） GASの気になる制約 少しでも 速くしたい…
9. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 9 少しでも速くしたい… 【初級編】 【中級編】 【上級編】
10. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 10 少しでも速くしたい… 初級編 【初級編】 【中級編】 【上級編】
11. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 11 少しでも速くしたい… 初級編 【初級編】 スプレッドシートの やり取りを高速化
12. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 12 少しでも速くしたい… 初級編 都度取得と一括取得で比較 //①各セルを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]; } } } どちらか速く全セルの値を結合できるか
13. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 13 少しでも速くしたい… 初級編 //①各セルを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() 一括取得 getDataRange().getValues() 今回は、11行×1列のシートを対象に、 5回テストした平均値で比較しました。
14. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 14 少しでも速くしたい… 初級編 //①各セルを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]; } } } 4.094秒 0.476秒 圧勝 都度取得 getRange().getValue() 一括取得 getDataRange().getValues()
15. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 15 少しでも速くしたい… 初級編 スプレッドシートやシート、セルの関数は とにかく遅いのでなるべく使わない。 使うとしても、最小回数にする。
16. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 16 少しでも速くしたい… 中級編 【初級編】 【中級編】 【上級編】
17. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 17 少しでも速くしたい… 中級編 【中級編】 スプレッドシートの 限界を超える
18. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 18 少しでも速くしたい… 中級編 ～悲しいスプレッドシートの限界～ 1. 1セルには半角5万文字が上限。 2. 全シート合計で、200万セルが上限。 3. 入力先はセルなので、 「=」から始まる文字列を入力しよう とすると高確率で数式エラーになって GASが強制終了する。
19. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 19 少しでも速くしたい… 中級編 ～悲しい打開策～ 1. 半角5万文字を超える文字列は、 複数のセルに分割保存 2. 200万セルを超えそうなデータは、 複数のスプレッドシートに分割保存 3. 予期せぬ数式エラーを防ぐため、 冒頭に「#」など適当な文字を挿入 ～悲しい打開策～
20. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 20 少しでも速くしたい… 中級編 ～悲しい打開策～ 1. 半角5万文字を超える文字列は、 複数のセルに分割保存 2. 200万セルを超えそうなデータは、 複数のスプレッドシートに分割保存 3. 予期せぬ数式エラーを防ぐため、 冒頭に「#」など適当な文字を挿入 本当にスプレッドシートで 良いのだろうか？
21. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 21 少しでも速くしたい… 上級編 【初級編】 【中級編】 【上級編】
22. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 22 少しでも速くしたい… 上級編 Google ドライブ （テキスト） Google Apps Script （プロパティサービス） Google ドキュメント
23. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 23 少しでも速くしたい… 上級編 Google ドキュメント //③Googleドキュメントからのデータ取得 function getDocument(){ var id = '{your_document_id}'; var file = DocumentApp.openById(id); var text = file.getBody().getText(); }
24. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 24 少しでも速くしたい… 上級編 //④Googleドライブのテキストからのデータ取得 function getText(){ var id = '{your_text_id}'; var file = DriveApp.getFileById(id); var text = file.getBlob ().getDataAsString (); } Google ドライブ （テキスト）
25. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 25 少しでも速くしたい… 上級編 //⑥GASのプロパティサービスからのデータ取得 function getProperty(){ var properties = PropertiesService.getScriptProperties(); var text = properties['{your_property_name}']; } Google Apps Script （プロパティサービス）
26. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 26 少しでも速くしたい… 上級編 小データ (1KB) 基準容量 (716KB) 基準容量×2倍 (≒102万文字) 基準容量×71倍 (≒50MB) Googleスプレッドシート ・半角5万文字／セル ・200万セル／ファイル 0.376 秒　 0.476 秒　 0.545 秒　 13.539 秒　 Googleドキュメント ・半角102万文字／ファイル 0.084 秒　 0.203 秒　 0.443 秒　 - Googleドライブ （テキスト保存） ・50MB／ファイル 0.425 秒　 0.404 秒　 0.481 秒　 3.394 秒　 プロパティサービス （GAS内部） ・9KB／プロパティ ・500KB／ファイル ・文字列として保存される。 0.001 秒　 - - - 読み込み速度 データ上限 データ容量別の速度がこちら
27. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 27 少しでも速くしたい… 上級編 小データ (1KB) 基準容量 (716KB) 基準容量×2倍 (≒102万文字) 基準容量×71倍 (≒50MB) Googleスプレッドシート ・半角5万文字／セル ・200万セル／ファイル 0.376 秒　 0.476 秒　 0.545 秒　 13.539 秒　 Googleドキュメント ・半角102万文字／ファイル 0.084 秒　 0.203 秒　 0.443 秒　 - Googleドライブ （テキスト保存） ・50MB／ファイル 0.425 秒　 0.404 秒　 0.481 秒　 3.394 秒　 プロパティサービス （GAS内部） ・9KB／プロパティ ・500KB／ファイル ・文字列として保存される。 0.001 秒　 - - - 読み込み速度 データ上限 データ容量別の速度がこちら
28. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 28 少しでも速くしたい… 上級編 小データ (1KB) 基準容量 (716KB) 基準容量×2倍 (≒102万文字) 基準容量×71倍 (≒50MB) Googleスプレッドシート ・半角5万文字／セル ・200万セル／ファイル 0.376 秒　 0.476 秒　 0.545 秒　 13.539 秒　 Googleドキュメント ・半角102万文字／ファイル 0.084 秒　 0.203 秒　 0.443 秒　 - Googleドライブ （テキスト保存） ・50MB／ファイル 0.425 秒　 0.404 秒　 0.481 秒　 3.394 秒　 プロパティサービス （GAS内部） ・9KB／プロパティ ・500KB／ファイル ・文字列として保存される。 0.001 秒　 - - - 読み込み速度 データ上限 データ容量別の速度がこちら
29. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 29 少しでも速くしたい… 上級編 1. 9KB未満の短いパラメータは プロパティサービス 2. 50MB未満なら Googleドライブ（テキスト） 3. 人が手で更新するデータ （あまり大きくないものに限る）は、 Googleスプレッドシート ～速さ重視の使い分け～
30. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 30 まとめ 所感的まとめ
31. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 31 まとめ 結果としてはプロパティサービスの高速さだけでなく、 Googleドライブ（テキスト）の意外な可能性が見えた分析に なりましたが、このテキスト形式はアプリ自体にエディタが なく、データの更新は原則としてGAS経由で行わなければな らないというデメリットもあります。 色々と考えた先には、 (お金払ってGoogle Cloud Platform使うのが最強じゃん…) などという元も子もない結論が垣間見えますが、それはそれ、 これはこれということで、課金なしでできる範囲を検証したの が今回でございます。 あなたの何かの気付きにつながれば幸いです。
32. Copyright © 2019 Takumi Nasuno. All Rights Reserved. 32 まとめ ご清聴 ありがとうございました