Google Apps Scriptを高速化するテクニックまとめ【GAS】
Google Apps Scriptは大変便利なスクリプトなのですが、連続稼働時間に最大6分という制限があり、それを超えると「起動時間の最大値を超えました」とのメッセージが出てスクリプトは勝手に止まってしまいます。これを回避するテクニックとしてはスクリプトトリガーを利用した突破方法があるのですが、実装は結構大変ですし、何より早く終わるに越したことはありません。しかし、スクリプトを高速化する為にはGoogle Apps Script流の書き方というものがあるので、今回それをまとめてみました。
なるべく普段から意識して書くようにすると、機能追加時に痛い目をみずに済みます（実際、単純なシートデータを集めるスクリプトで、集めるシートが増えてあっという間にスクリプトが破綻して困った思い出があります）。今回使用するスプレッドシートのダミーデータはMockarooを利用いたしました。
目次
今回使用するスプレッドシート
APIの呼び出し回数を減らす
ここでいうAPIとは、SpreadsheetAppであったり、DocumentAppであったりするアレです。それにぶら下がるgetSheetByNameであったり、getRange、getValuesなども同じで、これらGoogle Apps Script特有のAPIの呼び出しは可能な限り削る事、それが高速化では真っ先にあげられることです。どうしてもプログラミング初心者時代には、やってしまいがちなのですが、VBAなどではそれほど速度低下はしないのですが、Google Apps ScriptでAPIの呼び出しを頻繁に行うと物凄く遅くなります。
例えば、SpreadsheetApp.getActiveSpreadsheet().getSheetByName().getRange().getValues()という１文があった場合、これだけで4回APIを呼び出してる事になります。呼び出したら、なるべく取得した値を使うようにするのが今回のキモです。今回は事例として、特定のIDに合致するレコードをシートから探し出し、そのIDに含まれてる企業名を返すものを作ってみました。特定のIDは一番最後のIDを使う事にしました。（シートのレコード数は1500ですが、有効なレコードは1000です。他は空白行である為）。
※実行時間は毎回変わります。また、同じ処理の実行でも2度目の値が良くなるのはキャッシュされてる為と思われます。
※テスト実行は、スプレッドシートのメニューより「テスト」⇒「データチェック」⇒「最悪な事例」および「API呼び出しを改良」を実行します。
遅いコードの事例
わざとらしく最悪なコードで書いてみました。１つずつセルの値を取得しては次のセルへといったような効率の悪い検索を行っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
//APIを呼び出すテストの悪い事例 function badrequestapi(){ //開始時間を取得する var sttime = new Date(); //終了時間用の変数 var edtime = ""; //検索値を格納する var word = 1000; //カウンタを用意する var cnt = 2; //スプレッドシートを取得する var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dummy"); //A列の値を取得してwordと比較して一致したら抜ける for(var i = 0;i<ss.getLastRow();i++){ //セルの値を取得する var tempid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dummy").getRange("A" + cnt).getValue(); //セルの値と検索値が一致するかチェック if(tempid == word){ //終了時間を取得する edtime = new Date(); }else{ cnt = cnt + 1; } } //開始時間と終了時間の差を算出する（秒に変換） var kinoko = (edtime - sttime)/1000; //ダイアログ表示 var message = "発見までに掛かった時間（秒）：" + kinoko; SpreadsheetApp.getUi().alert(message); |
実行結果は、160秒ほど。激遅です。
改良したコードの事例
少し整理して、APIの呼び出しを減らしました。改良点は以下の通りです。
- getLastRowで最終行の値を取得して変数に入れてループを回しています。値はlengthに格納しています。【19行目】
- ループ内での値の取得もAPI呼び出ししていますが、少し改良をしました。【24行目】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
//APIを呼び出すテストの改良事例 function goodrequestapi(){ //開始時間を取得する var sttime = new Date(); //終了時間用の変数 var edtime = ""; //検索値を格納する var word = 1000; //カウンタを用意する var cnt = 2; //スプレッドシートを取得する var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dummy"); //スプレッドシートデータのレコード数を取得する var length = ss.getLastRow(); //A列の値を取得してwordと比較して一致したら抜ける for(var i = 0;i<length;i++){ //セルの値を取得する var tempid = ss.getRange("A" + cnt).getValue(); //セルの値と検索値が一致するかチェック if(tempid == word){ //終了時間を取得する edtime = new Date(); }else{ cnt = cnt + 1; } } //開始時間と終了時間の差を算出する（秒に変換） var kinoko = (edtime - sttime)/1000; //ダイアログ表示 var message = "発見までに掛かった時間（秒）：" + kinoko; SpreadsheetApp.getUi().alert(message); } |
実行結果は、80秒ほど。それでもまだまだ遅いですね。改良の余地はまだまだあります。
データの取得と操作
さて、まずAPIの呼び出しを減らす方法で若干スピードアップしました。しかし、まだ実用レベルに至っていません。改良したと言っても、改良コードの内容にはまだまだ課題が残っています。主にその課題点を一気に列挙してみたいと思います。
※テスト実行は、スプレッドシートのメニューより「テスト」⇒「データチェック」⇒「一番良い事例」を実行します。
セル単位で値を読むような書き方は行わない
主にVBAなどのコードでよく見かけるのが、プログラムと言うよりも人間の行動を忠実に書いてるようなマクロ的なソースコードです。つまり、1個セルの値を読んでチェックし、次のセルに移動してまた値を読み込んでチェックする。しかし、VBAのようにローカルで動くマシンであればそれでも良いのですが、Google Apps Scriptではその度にAPIを発行しなければなりません。よって、この部分はデータとしてガツッと取得して、配列データをチェックする方式に変えてあげる必要があります。例えば、
|
1 2 3 4 5 6 7 8 |
//データを取得する var data = ss.getRange("A2:A").getValues(); //スプレッドシートデータのレコード数を取得する（配列は0から始まる為） var length = ss.getLastRow() - 1; //配列から値を取り出し var tempid = data[i][0]; |
こんな具合になります。getValuesでシートデータを配列で取得出来ます。また、A2:Aの範囲で取得してるので、getLastRowの値から-1しています。配列から値を取り出す時は、for文にあるiを使うので、cntとして宣言してあったカウンタは不要になります。
forループ内を最適化
forループ内にも課題が残っています。今回のシートはデータ自体は999行で残りはただの空白行です。その為、以下の問題点が残ってる事になります。
- 該当のレコードを見つけてもループは最後の行まで見にいくようになってる（getLastRowではデータの有効範囲を自動で判断してくれるので、999行で取得出来ます）。
- なんらかの条件で空白行まで読みにいくような場合、1500行全部を読みに行ってしまう。
そこで、きちんとデータを見つけたらそこでループを脱出させてやり、また、空白行が来たら止めるような処置を追加してあげると余計な処理をせずに済みます。ループ脱出はbreak;文を入れてあげるだけ。空白チェックは普通にtempid == “”でチェック出来ます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//A列の値を取得してwordと比較して一致したら抜ける for(var i = 0;i<length;i++){ //セルの値を取得する var tempid = data[i][0]; //空白行の場合、そこでプログラム終了 if(tempid == ""){ break; } //セルの値と検索値が一致するかチェック if(tempid == word){ //終了時間を取得する edtime = new Date(); break; }else{ } } |
ここまでの内容を反映したコードだと、実行結果は0.35秒でした。APIの呼び出しを減らし、配列から値をチェックする方式に変えるとここまで劇的に速度が向上します。
その他
その他、より高速になるように改善する上でのポイントは
- getDataRangeやgetRangeByNameを使うとAPIの使用をより少なくする事が可能です。
- シートを年度毎に分割するか、年度データ蓄積専用の別のシートを用意しておく
getDataRangeはデータが有効な行を自動判定してデータ範囲を指定してくれるメソッドです。対してgetRangeByNameは予めスプレッドシートに「名前付き範囲」を設定してる時にその設定の範囲を拾ってくるメソッドです。構文は、SpreadsheetApp.getActiveSpreadsheet().getRangeByName(“名前付き範囲名”).getValues()といった感じになります。名前付き範囲は、データの増減のない、固定的なシートに対して設定を行います。
また、２．についてですが、1枚のシートに何年もデータを溜め込んでる場合、必ずしもすでに終わった前の年のデータは必要なかったりします。これらは年度の切替時にでも、別のシートに退避させれば、参照するレコード数を減らせるので、結果的に高速化に繋がります。自分の場合、年度単位で過去ログシートへ移動や、フォーム等で承認済みレコードは過去ログシートへ退避するようにスクリプトを書いています。
データの書込と検索
配列のデータや値類をスプレッドシートへ書き込むシーンも多くあるかと思います。例えば、同じスタイルのシートのデータをいくつも回収して一纏めにして収集するだとか、シートデータをコピーしてくるなどなど。ここでスクリプトの速度に大きな差が生まれる点があります。今回は、使用するスプレッドシートのDummyシートからcopymanシートへデータをコピーする事例です。
※テスト実行は、スプレッドシートのメニューより「テスト」⇒「書込テスト」⇒「１行ずつ挿入」および「まとめて挿入」を実行します。
遅いコードの事例
すでにセル単位でデータを書き込む真似はしません。APIの呼び出し回数を減らすの項目でそれは立証されました。という事で書込で次に使用されるであろうメソッドが「appendRow」。1次元配列のデータをスプレッドシートの最後の行に自動で追加してくれる優れもので、フォームなど1行のレコードを挿入する場合に効果的です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
//appendRowでデータをコピーする function copydataman(){ //開始時間を取得する var sttime = new Date(); //終了時間用の変数 var edtime = ""; //コピー元スプレッドシートを取得する var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("Dummy").getRange("A2:F").getValues(); //コピー先スプレッドシートを取得する var cpsheet = sheet.getSheetByName("copyman"); //コピー先データを全クリアする cpsheet.getRange("A2:F").clearContent(); //コピー元データのレコード数を取得する var length = ss.length; //データを1行ずつコピーしていく for(var i = 0;i<length;i++){ //空白行かどうかチェックする var tempid = ss[i][0]; if(tempid == ""){ break; } //1行取得して挿入する var temprec = ss[i]; cpsheet.appendRow(temprec); } //終了時間を取得する edtime = new Date(); //開始時間と終了時間の差を算出する（秒に変換） var kinoko = (edtime - sttime)/1000; //ダイアログ表示 var message = "挿入完了までに掛かった時間（秒）：" + kinoko; SpreadsheetApp.getUi().alert(message); } |
大量のデータを挿入する場合、appendRowでは1行ずつ挿入する事になるので、遅いです。実行結果は、88秒でした。
改良したコードの事例
１行ずつではかなり時間が掛かる上に、データ量に比例して処理時間が伸びてしまいます。そこでこの方法を配列まるごと一発で書き込む方法を取ります。複数のレコードをappendRowと同じく最終行に追加するスタイルです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
//大量データを一発で書込する function arraydataman(){ //開始時間を取得する var sttime = new Date(); //終了時間用の変数 var edtime = ""; //コピー元スプレッドシートを取得する var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("Dummy").getRange("A2:F").getValues(); //コピー先スプレッドシートを取得する var cpsheet = sheet.getSheetByName("copyman"); //コピー先データを全クリアする cpsheet.getRange("A2:F").clearContent(); //コピー元データのレコード数を取得する var length = ss.length; //書込用配列を用意する var array = []; //データを1行ずつコピーしていく for(var i = 0;i<length;i++){ //空白行かどうかチェックする var tempid = ss[i][0]; if(tempid == ""){ break; } //書込用配列にpushする array.push(ss[i]); } //配列データを最終行に一発書込 var endrow = Number(cpsheet.getLastRow()) + 1; //最終行の位置を取得 var lastColumn = array[0].length; //カラムの数を取得する var lastRow = array.length; //行の数を取得する cpsheet.getRange(endrow,1,lastRow,lastColumn).setValues(array); //終了時間を取得する edtime = new Date(); //開始時間と終了時間の差を算出する（秒に変換） var kinoko = (edtime - sttime)/1000; //ダイアログ表示 var message = "挿入完了までに掛かった時間（秒）：" + kinoko; SpreadsheetApp.getUi().alert(message); } |
表示はデータ量によってモタツキますが、実行結果自体は0.80秒でした。本来、取得したスプレッドシート自体が配列なので、そのまま書込が出来ますが条件を一緒にするために、書き込み用配列にわざわざpushさせています。
UrlfetchAppでまとめてリクエスト
通常、外部のHTTPにリクエストを送る為のUrlfetchAppは単一のURLに対してリクエストを送信し、値を受け取ったりする為に利用します。しかしこのメソッドは、10秒間におよそ3回程度しかリクエスト出来ず、Quotaには表記が無いのですが４回以上リクエストを送ると、429エラー（Too Many Request）で送信がリジェクトされてしまいます。
故に10秒間に3回ずつ送るようにウェイト（Sleep）する処理を入れて、処理を遅らせるバッドノウハウを使うのが通常です。しかし、UrlfetchAppにはもう一つ「UrlfetchApp.fetchAll」と呼ばれるメソッドが用意されており、複数のリクエストをまとめて送って、結果をまとめて受け取る事が可能です。おおよそ50%程度速度が向上します。特にPDF生成リクエスト等で効果が出ると思います。
※但し、一度にたくさんのリクエストを同時に送ってもおそらくNGになるので、せいぜい数個程度にトドメておくべきでしょう。
リクエストする方法は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 |
//一度に3つのリクエストを投げる function multirequest(){ //3つのリクエストを１度に送る let url1 = "リクエストURL"; let url2 = "リクエストURL2"; let url3 = "リクエストURL3"; //fetchAllで一度にリクエスト var response = UrlFetchApp.fetchAll([url1, url2, url3]); } |
OAuth2.0認証等の認証が必要なものは1個ずつリクエストパラメータを用意して、リクエストの配列に加えれば良い。Quotaにある現在の制限を超えないサイズでリクエストが必要なので要注意。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//pdfファイルを複数同時にリクエスト function makeallpdf(token,payload){ //1個目のリクエストメソッド let req1 = { "url" : "1つ目のURL", "method" : "GET", 'headers': {"Authorization": " Bearer " + token} } //2つ目のリクエストメソッド let req2 = { "url" : "1つ目のURL", "method" : "POST", 'headers': { "Authorization": " Bearer " + token, 'contentType': "application/json" }, "payload" : payload } //リクエストの実行 let response = UrlFetchApp.fetchAll([req1,req2]); } |
ということで、5つのシートを持つファイルから5つのPDFを一括で生成するコードを作成しテストしてみたところ、16秒程度で生成する事ができました。こちらのコードはこちらのファイルの中にあるmultimake.gsの中に記述してあります。
fetchAllの結果もまた、配列でまとめて返ってくるので、そこからgetBlobで取得したものを指定のドライブのフォルダ内にcreateFileで生成するだけです。単発リクエストだと24秒を超えてしまいます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
//ページごとにPDF化をまとめて実行 function makeAllPdf() { //アクセストークンを取得 let token = ScriptApp.getOAuthToken(); //シート名の一覧 let ss = SpreadsheetApp.getActive(); let sheets = ss.getSheets(); //リクエストヘッダ let headers = {'Authorization': 'Bearer ' + token}; //リクエストURLを配列にまとめる let array = [] let sheetman = [] let fileid = ss.getId(); for(let i = 0;i<sheets.length;i++){ //シート名を取り出す let sheetname = sheets[i].getName(); sheetman.push(sheetname) //sheetidを取り出す let sheetid = sheets[i].getSheetId(); //URLを組み立て let url = "https://docs.google.com/spreadsheets/d/" + fileid + "/export?gid=" + sheetid + "&format=pdf&portrait=false&size=A4&gridlines=false&fitw=true" //リクエストオプション let req = { "url" : url, "method" : "GET", 'headers': headers } //配列に加える array.push(req); } //リクエストを実行 let response = UrlFetchApp.fetchAll(array); //保存場所を指定 let folder = DriveApp.getFolderById("ここに生成先のフォルダのIDを入れる"); //responseからPDFを生成する for(let j = 0;j<sheetman.length;j++){ //Blobを取得 let blob = response[j].getBlob(); //ファイルを生成（シート名でファイル名をセット） folder.createFile(blob).setName(sheetman[j] + ".pdf"); } } |
Sheets API v4を利用する
こちらのサイトにて「SpreadsheetAppよりもSheets APIを使ったほうが全然早い」という耳寄りな情報が流れてきて、検証してみましたが、複数のシートおよび大量のデータの読み書きに関しては、Sheets APIを利用したほうが圧倒的に早い事がわかりました（およそ2.8倍ほど早い）。このエントリーに関しては以下のページに詳細に書いています。
通常のSpreadsheetAppを利用するよりも断然おすすめです。
発想の転換
差込印刷のGoogle Apps Scriptを開発していていくつかの気付きがありました。それが「正攻法に思えるやり方ではないやり方のほうがスマートで早いことがある」ということ。
特にこの事例では
- 差込処理後に元の状態に戻す必要があるが、雛形のドキュメントデータをコピーして貼り付けは遅い
- そもそもドキュメントを複製して差し込む手法はもっと遅い
多くの課題があり、6分の壁を超える手法も使わざるを得ない状況でした（しかもレイアウト崩れなど別の問題も発生していたり）。そこは発想の転換をし、表形式でレイアウトを組み、中身の文字を元通り入れ替えるという手法。この結果、同じレコード数でも6分を超えていた作業が、3分以下で完了するようになり、レイアウトの崩れも無い。
高速化をするには時には前述のテクニック以外にも「そもそものやり方を変える」ということが必要なケースがあります。とりわけGASではそういった発想力が問われると思います。
V8 Runtimeを利用する
概要
2020年2月6日より、Google Apps Scriptの基盤となってるJavaScriptエンジンがV8エンジンも使えるようになりました。V8エンジンに切り替えてスピードベンチマークを取ってみた所、全体的に18%程度のスピードアップしてるんじゃないかなぁという結果が得られました。
V8エンジンについての詳細についてはこちらのエントリーを参照してください。以下の手順で変更できます。
- スクリプトエディタを開く
- メニューの「表示」⇒「マニフェストファイルを表示」をクリック
- appsscript.jsonを開き、1行以下のようなものをを追加する
- 保存すれば、すぐに使えるようになります。
- もとに戻す場合には、この行を削除すればオッケーです。（DEPRECATED_ES5を指定しても可）
|
1 2 |
//追加する行はこちら "runtimeVersion":"V8" |
図：簡単に使えるようになります
検証結果としては、今回のサンプルで言えば・・・
最悪なコードで書いた場合の検証結果は、「97.078（秒）」でした。（18%ほど早かった）
一番良い事例で書いた場合の検証結果は、「0.424（秒）」でした。（19%ほど早かった）
といった感じでした。20%近く速度アップが見込めるので、V8にしても良いですが、まだリリースしたばかりなのと、注意点もあるので、そこを考慮して変更しましょう。
Proxyを利用する
V8 Runtimeを有効化してる場合様々なES6仕様のJSを利用可能ですが、その中にあるProxyについては高速化に若干貢献できるケースがあります。そのケースとは
- 全く同じ様式のスプレッドシートが複数ファイルある（また同様に複数シートある）
- これらのスプレッドシートのIDとシート名をリスト化シートが用意されている
- 2.のリストを下にシートデータを取得してまとめて1箇所に書き出す
例えば支店ごとの売上であったり施設ごとの利用状況データなどをスプシ毎に用意して、これらのファイルのIDとシート名をリスト化してるようなケースです。結構このパターンを利用してる業務は多いです。しかし、このリストを元に全データを取得する場合、以下の問題が発生します。
- リスト内のスプレッドシートのIDが必ずしもソートされてるとは限らない。
- 仮にソートされていたとしても、GASで回す場合、次のレコードのスプシのIDが前のスプシのIDと同一かどうか？の判定が必要になる。
そうなると、最初のopenByIdでスプシを取得するという動作に於いて、複雑なロジックが必要になり、またそれを嫌ってリストをそのまま回すと、同じシートIDのスプシをopenByIdで取得するという無駄が発生します。これをProxyを利用した手法を用いるとすでに取得済みの場合は、取得済みのスプシとして返す仕組みが構築可能です。
この仕組みで構築した場合、4つのスプシ、7枚のシート（データ件数はそれぞれ10000レコード）の場合は
- 改良せず全部取得で書いた場合の検証結果は、「6.05（秒）」でした。
- Proxyで書いた場合の検証結果は、「3.44（秒）」でした。（56%ほど早かった）
となりました。ネットワークの状況によって上記の数値はだいぶ変わりますが、概ねProxyのほうが早い結果が出ています。以下は通常の取得の場合とProxyで書いた場合のコードになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
//通常のデータ取得 function normal(){ //開始時間を取得する let sttime = new Date(); //シート一覧を取得する let list = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート一覧").getRange("A2:B").getValues(); //リストに基づいてデータを取得する let master = []; for(let i = 0 ;i<list.length;i++){ //レコードを一個取得する let rec = list[i]; //レコードからIDとNAMEを取得する let recid = rec[0]; let recname = rec[1]; //データを取得する let tempdata = SpreadsheetApp.openById(recid).getSheetByName(recname).getRange("A2:E").getValues(); //取得データをmasterに結合する master = master.concat(tempdata); } //終了時間を取得する let edtime = new Date(); //開始時間と終了時間の差を算出する（秒に変換） let diff = (edtime - sttime)/1000; //ダイアログ表示 let message = "発見までに掛かった時間（秒）：" + diff; SpreadsheetApp.getUi().alert(message); } //Proxyで取得する function proxyget(){ //開始時間を取得する let sttime = new Date(); //シート一覧を取得する let list = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート一覧").getRange("A2:B").getValues(); //シートデータをJSON化する let jsonman = []; for(let i = 0;i<list.length;i++){ //レコードを取得 let rec = list[0]; //JSON化 jsonman.push( {ssid:rec[0], sheetName:rec[1]} ) } //スプレッドシートIDを基準にスプシを取得済みの場合は、取得済みの変数から利用する const spreadman = new Proxy({}, { get: (target, ssid) => { if (!target[ssid]) { target[ssid] = SpreadsheetApp.openById(ssid); } return target[ssid]; } }); //jsonmanを基準にスプレッドシートデータを取得する let master = []; for(var j = 0;j<jsonman.length;j++){ //レコードを一個取り出す let rec = jsonman[j]; //spreadmanを使って取得済みかどうか調査してデータを取得 let tempdata = spreadman[rec.ssid].getSheetByName(rec.sheetName).getRange("A2:E").getValues(); //取得データをmasterに結合する master = master.concat(tempdata); } //終了時間を取得する let edtime = new Date(); //開始時間と終了時間の差を算出する（秒に変換） let diff = (edtime - sttime)/1000; //ダイアログ表示 let message = "発見までに掛かった時間（秒）：" + diff; SpreadsheetApp.getUi().alert(message); } |
図：同じSSIDが出現するがソートされていない
filterを利用する
スプレッドシートのデータを取得して、中身を判定し個数を数える。非常に単純な作業なのですが、この部分でもES6のメソッドであるfilterメソッドを使うことで若干高速化可能にできるかもしれません。こちらで紹介されていた内容になります。
実際にダミーデータを用意して、D列（チェック）がTrueのものだけをカウントして返すという作業に於いて通常のForループで判定させる方法と、filterでフィルタリングしてカウントする方法を比較してみました。データはランダム生成の関数を使ってるので開く度に変わりますが、4970個/10000件中がチェック入というケースに於いて
- 通常のforループの場合：0.005秒
- filterでフィルタした場合：0.001秒
という結果になりました。およそ5倍ほど早い。filterメソッドは積極的に使ったほうが良さそうです。以下は検証用のコードです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
//通常のループでカウントした場合 function normalman() { //シートを取得する let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dummy"); let data = ss.getRange("A2:D").getValues(); //計測開始 let sttime = new Date(); //終了時間用の変数 let edtime = ""; //カウンタ用変数 let count = 0; //ループでチェックがTrueならばカウントする for(let i = 0;i<data.length;i++){ //レコードを一個取り出す let rec = data[i]; //チェック列がTrueならばカウント if(rec[3] == true){ count = count + 1; } } //計測時間を算出 edtime = new Date(); let ret = (edtime - sttime)/1000; //ダイアログ表示 let message = "発見までに掛かった時間（秒）：" + ret; SpreadsheetApp.getUi().alert(message); SpreadsheetApp.getUi().alert(count + "個"); } //filterで処理してカウントした場合 function filterman(){ //シートを取得する let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dummy"); let data = ss.getRange("A2:D").getValues(); //計測開始 let sttime = new Date(); //終了時間用の変数 let edtime = ""; //カウンタ用変数 let count = 0; //filter条件で抽出 let fildata = data.filter(function(check) { //D列がTrueかどうかを判定 return check[3] == true; }); //カウントを算出 count = fildata.length; //計測時間を算出 edtime = new Date(); let ret = (edtime - sttime)/1000; //ダイアログ表示 let message = "発見までに掛かった時間（秒）：" + ret; SpreadsheetApp.getUi().alert(message); SpreadsheetApp.getUi().alert(count + "個"); } |
HTML Serviceを活用する
このセクションで紹介する方法は、高速化すると言うよりは、6分の実行リミットを回避して作業を行わせる事のできるテクニックです。HTML Serviceで生成したHTMLはクライアントのマシン上で動作してるので、JavaScriptの実行時間に制限はありません。ただし、この処理を一発で行わせるには、同期処理が必要になるので、上手く前の処理が終わったら書込ルーチンを実行するようにしないと、データの回収が終わっていないのに、GAS側へ投げられてしまうので注意が必要です。
※テスト実行は、スプレッドシートのメニューより「テスト」⇒「データチェック」⇒「HTML Serviceで取得する」および「Visualization APIで取得」を実行します。
単純な使用例
- GAS側からシートのリストを取得する。
- Google Apps Script側で処理を担当する関数は連続処理ではなく単一の処理を行うだけである（あるシートのデータの塊を取得してHTML側へ返すだけとか。
- 故に、HTML側にシートのIDを所持し、リストにしたがって2.へIDを投げ、返ってきた値を配列にpush（もしくは、Array.prototype.push.apply）するコードを記述する。
- 最終的に一塊になった配列データをGAS側に送り返してシートに書き込ませる
こんな具合です。常にGAS側とやり取りをしていますが、GAS側での実際の処理は、単一シートデータの取得と塊の書込ですので、１つ１つが6分に到達する事がなければ問題なく連続処理を投げる事が可能です。今回のサンプルでは、ダミーのシート（10カラム1000レコードを10枚）をおよそ32秒で取得出来ています。
※データの処理をGAS側でやらせず、HTML Serviceで生成したHTML上にて、JavaScriptにやらせるのがポイントです。
※google.script.run.withSuccessHandlerを使用しています。この関数の実行は非同期で実行されるので、並列処理っぽい作業も行えます。
GAS側コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
//ファイルリストを取得する関数 function fileman(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("filelist").getRange("A2:A").getValues(); Logger.log(ss); return JSON.stringify(ss); } //データ取得ダイアログを表示する function datapakupaku(){ var html = HtmlService.createHtmlOutputFromFile('bigdata') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(200) .setHeight(100); SpreadsheetApp.getUi() .showModalDialog(html, 'シートデータ回収君'); } //ファイルIDを元にデータの塊を取得して返す関数 function tokyotower(id){ var sheet = SpreadsheetApp.openById(id); var data = sheet.getSheetByName("apple").getRange("B2:J").getValues(); return JSON.stringify(data); } //データの塊をスプレッドシートに書き込む function insertarray(array){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ui = SpreadsheetApp.getUi(); var ss = sheet.getSheetByName("dataset"); //シートをクリアする ss.getRange("A2:I").clearContent(); //データの書込をする var endrow = Number(ss.getLastRow()) + 1; //最終行の位置を取得 var lastColumn = array[0].length; //カラムの数を取得する var lastRow = array.length; //行の数を取得する ss.getRange(endrow,1,lastRow,lastColumn).setValues(array); //メッセージ表示 ui.alert("書込終了しました。"); } |
HTML側コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
<html> <head> <script type="text/javascript"> var masterdata = []; //シートデータを格納する変数 var filelist = ""; //ファイルリストを格納する変数 var lengthman = 0; var cnt = 0; //ファイルリストの取得 google.script.run.withSuccessHandler(onSuccess).fileman(); function onSuccess(data){ filelist = JSON.parse(data); lengthman = Number(filelist.length); document.getElementById('laputa').disabled = null; } //データ取得用のコマンド function disp(value){ //ボタンエリアを書き換え if(value == 0){ var html = "<img src='https://officeforest.org/wp/library/ProgressSpinner.gif' width='48' height='48'><p><font color='red'><b>データ取得中．．．</b></font></p>"; document.getElementById("spring").innerHTML = html; } //cntにvalueを格納する cnt = value; //lengthとcntを比較して条件分岐 if(cnt<lengthman){ //IDを取得する var fileid = filelist[cnt][0]; //GAS側にデータを取らせて取得する google.script.run.withSuccessHandler(imparray).tokyotower(fileid); }else{ //データを取得し終えたので終了処理を実行 google.script.run.insertarray(masterdata); } } //データを配列に次々に入れるコマンド function imparray(data){ var json = JSON.parse(data); Array.prototype.push.apply(masterdata, json); cnt = cnt + 1; disp(cnt); } </script> </head> <body> <div align="center" id="spring"> <button type="button" id="laputa" onClick="disp(0);" disabled> <img src="https://officeforest.org/wp/library/icons/spinner.gif" width="32" height="32" ><br> <font size="2">データ頂きます</font> </button> </div> </body> </html> |
Promiseとタイマーで同期処理
前項の単純な事例はGAS側はデータの取得を行ってHTML側で処理をすることを目的としたものですが、逆にHTML側はタイマー管理を行うことに徹して、処理はGAS側で行わせる場合にはPromiseによる同期処理を実現する必要があります。しかし、google.script.runは非同期にバンバン実行されてしまうのと、ループ処理もまた同じなので上手にループを回しつつ、書き込みを考慮してタイマーを掛け、順繰りにgoogle.script.runを実行するようにすると、GAS側で連続処理をせずとも回す事が可能です。
- HTML側はPromiseとTimerにてgoogle.script.runの実行と3秒間のウェイトを実現する
- GAS側から1000レコードずつ取得する
- 別のシートに追記でコピーする
- ループの回数分だけ2.〜3.を実行する
- 完了したらダイアログを閉じる
- これを本来非同期で実行されてしまうgoogle.script.runの実行を同期的に実行できるようになります。
GAS側コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
//データ転記ダイアログを表示する function datacopy(){ var html = HtmlService.createHtmlOutputFromFile('promise') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(300) .setHeight(150); SpreadsheetApp.getUi() .showModalDialog(html, 'シートデータ転記くん'); } //1000ずつデータをコピーする関数 function recordcopy(cnt){ //引数から1000個目の位置を決定する var endcnt = cnt + 999; //datasetのレコードデータを取得する if(cnt == 1){ //最初の1回目だけは取得開始位置を1つずらす var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dataset"); //2行目以降からendcntまでのデータを取得する var range = sheet.getRange(2,1,endcnt,9).getValues(); }else{ //指定のcnt値を用いてデータを取得する var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dataset"); var range = sheet.getRange(cnt,1,1000,9).getValues(); } //取得したデータをcpdataに貼り付ける var cpsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("cpdata"); var endrow = Number(cpsheet.getLastRow()) + 1; //最終行の位置を取得 var lastColumn = 9 //カラムの数を取得する var lastRow = range.length; //行の数を取得する cpsheet.getRange(endrow,1,1000,lastColumn).setValues(range); //結果を返す return endcnt; } |
- 1000件取ってコピーをしたら完了するルーチンです。
- 初期値のcntはHTML側から受け取ります。これがレコード取得開始位置にもなります。
- １回目はタイトル行があるのを考慮して、rangeの取得方法を変えています。
- データの読み書きは一発で行っています。
HTML側コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
<!DOCTYPE html> <html> <head> <base target="_top"> <script> var cnt = 1; // ループ内処理（データの取得） function action(i) { return new Promise( (res, rej) => { setTimeout( () => { console.log(cnt); //レコード転記プロセス実行 google.script.run.withSuccessHandler(function(ret) { //処理プログレス表示 document.getElementById("console").innerHTML = cnt + 'レコード目〜処理中'; console.log(i + '回目です') //カウンタに1000を加算する cnt = cnt + 1000; res(); }).recordcopy(cnt); },3000) }) } //ループ処理 function loop( fn, i, end) { console.log(i) return fn(i) .then( () => { if ( i < end ) { return loop( fn, i+1, end) } else { google.script.host.close(); return Promise.resolve('end'); } }); } //データ転記メインプロセス function disp(){ //カウンタ初期化 cnt = 1; //ループ処理実行(2000レコードだけ取得) loop(action, 0, 1); } </script> </head> <body> <div align="center" id="spring"> <button type="button" id="laputa" onClick="disp();"> <img src="https://officeforest.org/wp/library/icons/itigo-jm.gif" width="32" height="32" ><br> <font size="2">データ転記します</font> </button> </div> <div id="console"></div> </body> </html> |
- 今回、Promiseとループ、そして3秒のタイマーを実現するコードはこちらのサイトからお借りしました。
- ボタンクリックでdispが実行され、今回2000レコードなのでループ回数を1として指定（つまり2回実行）。
- loop関数は引数の実行関数名、初期カウンタ、ループ回数を受け取り、if文で判定して再帰的に自分自身を実行しています。
- ループ回数に達するとgoogle.script.host.closeでダイアログを閉じています。
- 実際に処理を行うaction関数にて、setTimeoutでスリープを（msで指定）、レコード転記プロセスにgoogle.script.run.withSuccessHandlerをcallbackする形で組み込んでいます。
- GAS側で処理を完了しcallbackしたら、グローバル変数のカウンタに1000を加えている。
- GAS側関数実行時に引数としてグローバル変数のカウンタを渡しています。
- for文などのループを使うと非同期に次々に実行されてしまうので、今回のようにカウンタを使った値の比較で処理を実行させます。
これらの応用例として以下のようなエントリーも作成してみました。
Visualization APIを使用する
データの塊を回収するような作業に於いて、GASでやらせるとタイムオーバーしてしまうようなケースでも、HTML Service上でVisualization APIを使用する事でタイムオーバーと関係なく処理を行わせる事が可能です。
- GAS側からシートのリストを取得する。
- Visualization APIを利用してデータの塊を取得する処理を連続処理させる。取得データは配列に格納する
- 最終的に一塊になった配列データをGAS側に送り返してシートに書き込ませる。
こんな具合です。GAS側でやってる事は書込処理だけですので、よほど大量過ぎるデータでなければタイムアウトしないはずです。Visualization APIはHTML側で動作していますので、データの取得でタイムアウトは発生しません。実行してみた結果は、26秒でした。
※何故かこちらのやり方の場合、HTML側からGAS側へ配列データを渡す時に「Failed due to illegal value in property」というエラーが出るので、HTML側でJSON.stringify(masterdata)として渡して上げて、GAS側でJSON.parse(array)として受け取らせてます。他は全く同じコードです。
GAS側コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
//ファイルリストを取得する関数 function fileman(){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ss = sheet.getSheetByName("filelist").getRange("A2:A").getValues(); return JSON.stringify(ss); } //データ取得ダイアログを表示する function datapakupaku2(){ var html = HtmlService.createHtmlOutputFromFile('bigdata2') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setWidth(200) .setHeight(150); SpreadsheetApp.getUi() .showModalDialog(html, 'シートデータ回収君・弐式'); } //データの塊をスプレッドシートに書き込む function potato(data){ var json = JSON.parse(data); var sheet = SpreadsheetApp.getActiveSpreadsheet(); var ui = SpreadsheetApp.getUi(); var ss = sheet.getSheetByName("dataset"); //シートをクリアする ss.getRange("A2:I").clearContent(); //データの書込をする var endrow = Number(ss.getLastRow()) + 1; //最終行の位置を取得 var lastColumn = json[0].length; //カラムの数を取得する var lastRow = json.length; //行の数を取得する ss.getRange(endrow,1,lastRow,lastColumn).setValues(json); //メッセージ表示 ui.alert("書込終了しました。"); } |
HTML側コード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
<html> <head> <!-- Google Visualization API関係 --> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> var masterdata = []; //シートデータを格納する変数 var filelist = ""; //ファイルリストを格納する変数 var lengthman = 0; var cnt = 0; var gidval = "apple"; //参照するシート名 var url = ""; //queryに投げるURL //パッケージのロードとデータの取得 google.charts.load("current", {packages:["table"], 'language': 'ja'}); //ファイルリストの取得 google.script.run.withSuccessHandler(onSuccess).fileman(); function onSuccess(data){ filelist = JSON.parse(data); lengthman = Number(filelist.length); document.getElementById('laputa').disabled = null; } //データ取得用のコマンド function disp(value){ //ボタンエリアを書き換え if(value == 0){ var html = "<img src='https://officeforest.org/wp/library/ProgressSpinner.gif' width='48' height='48'><p><font color='red'><b>データ取得中．．．</b></font></p>"; document.getElementById("spring").innerHTML = html; } for(var i = 0;i<lengthman;i++){ //URLの組み立て var spreadman = "https://docs.google.com/spreadsheets/d/" + filelist[i][0] + "/"; var urlData = spreadman + "gviz/tq?sheet="; url = String(urlData + gidval); //検索対象のスプレッドシートURLの設定 var query = new google.visualization.Query(url); //Queryをぶん投げる query.send(imparray); } } //データを配列に次々に入れるコマンド function imparray(response){ //クエリーエラートラップ処理 if (response.isError()) { alert('クエリーエラー: ' + response.getMessage() + ', ' + response.getDetailedMessage()); return; } //データテーブルとレコード数,カラム数を取得する var data = response.getDataTable(); var length = data.getNumberOfRows(); var clength = data.getNumberOfColumns(); //配列を用意しDataTableの中身を取り出す for(var i = 0;i<length;i++){ var array = []; for(var j = 1;j<clength;j++){ array.push(data.getValue(i,j)); } //配列をmasterdataに加える masterdata.push(array); } //カウンタを回す cnt = cnt + 1; //カウンタの値とlengthmanを比較し終了していたら書込作業 if(cnt == lengthman){ //データを取得し終えたので終了処理を実行 var json = JSON.stringify(masterdata); google.script.run.potato(json); } } </script> </head> <body> <div align="center" id="spring"> <button type="button" id="laputa" onClick="disp(0);" disabled> <img src="https://officeforest.org/wp/library/icons/itigo-jm.gif" width="32" height="32" ><br> <font size="2">データ頂きます</font> </button> </div> </body> </html> |
シート関数等で出来る事はなるべくやらせる
この項目は、Google Apps Scriptの高速化というよりは、いかにGoogle Apps Scriptを使わずに済ませるか？という事をまとめた項目です。なんでもかんでもGoogle Apps Scriptで処理をするというのは正しい事ではありません。また、データの塊取得のような大きな作業の場合、横着して日計データをガッツリ取ってくるような事をすると、直ぐにタイムオーバーになってしまいます。そこで、これらGoogle Apps Scriptで処理をする前段階で作業を行わせて、Scriptの処理速度を結果的に向上させようという事を研究し記述しています。
データは関数で集計しておく
各種データの集計と収集を行うようなスプレッドシートの場合、取得するデータ量が多すぎる場合、様々な高速化の努力をしていても、タイムアウトする事がままあります。自分の実験では、10カラム30,000件のデータ収集して、自分のスプレッドシートに書き込むルーチンを作った所、タイムアウトしてしまいました。データは日計表で集計自体は月次集計として行っています。このようなケースの場合、以下の改善点が見込めます。
- 必要な最終データは月次集計データであるので、予め各シートで集計されてる結果を、スクリプトは収集するように変更する。
- 各シートのデータ集計は独自の関数ではなく、スプレッドシート標準装備の関数を使用して行う。独自の関数を使用した場合、処理が間に合わずデータを取り損ねる。スプレッドシート標準の関数の場合その恐れがない。
- 複雑な集計については、QUERY関数を使用し、場合によってはPivot指定をしてクロス集計を行わせる。
この改善を行った結果、タイムアウトしていた処理は、20秒で完了し、目的の月次集計表が作れるようになりました。データを処理すると言っても、データのデカイ塊である必要性がないのならスプレッドシートの関数にやらせるようにしましょう。
複数のシートにしない
概要
１つのスプレッドシートにデータを蓄積するシートを複数にしてるケースがあります。理由は同じ日計表であっても入力する値が異なるという理由でカラムの数が違ったりしてる為です。故にシート名に例えばコードを振って、101 = スーパーの売上、102 = レジャー施設の売上といったような分離をしてる人も多いでしょう。確かに手入力をするのであれば、これでも良いと思います。しかし、シートが増えて、且つスプレッドシートが増えると、それを一纏めにする為に回収するスクリプトは、何度もAPIを叩かなければいけません。
このケースの場合、以下の改善が見込めます。
- スプレッドシート手入力をやめて、HTML ServiceでUIを作ってあげる（リッチなGUIアプリを作るを参照）
- シートを分離させずに１枚のシートにカラム名をツケずにコードを入れる列を設けて、同居させてしまう（コードでそのレコードが何の項目なのかは分かる）
- 集計を行う場合にはQuery関数を使用し、Pivotにしてから取得するようにする。
この改善を行う事によって、シートデータの取得に掛かるAPIの呼び出しは１回で済みます。よって、シート数が4でスプレッドシートの数が10ある場合、合計で40回も掛かる作業が10回で済む事になります。自分の場合、シート数が10でスプレッドシートの数が30もある為、全部で300回も呼び出しをしていたのが30回で済んだので大幅に時間の削減が出来、タイムアウトしなくなりました。
シートを連結しておく
前述にもありますが、データベースのように正規化しておき複数のシートにわけて記録することは多々あります。しかし、GASからデータとして取得するような場合に於いてはあらかじめ、arrayformula関数やQuery関数を利用して複数シートを連結しておき、データ取得用のシートを用意しておくことで、リクエストするAPIの回数を減らすことが可能になります。
このように通常のDBとは異なり、スプレッドシートの関数を利用してあらかじめ準備しておくという作業が高速化に於いて非常に貢献することが多いので、GASのみじゃなく関数も学習しておきましょう。
条件付き書式設定を活用する
ウェブ上のGoogle Apps Scriptの高速化に関するサンプルコードに於いて、セルの色を色々弄る例が出ていますが、このコードはまだGoogle Spreadsheetにまともな条件付き書式設定がない時代のものです。現在は、条件付き書式設定に於いて数式がバッチリ使用する事ができるので、スクリプトでこういった作業を行うシーンはグッと減っています。よって、ビジュアル面でもしこういったコードを利用してる場合、スクリプトは辞めて、条件付き書式設定を使うようにしましょう。
その分、集計後に余計な作業をスクリプトでやる必要性がなくなります。
setFormulaを活用しよう
データの取得元の数々のシートには例えば商品コードとそれに纏わるカラムがマスタという形で格納されてると思われます。しかし、これらは決して効率の良いやり方ではありません。あくまでもそうしてるのは人間が見た時にわかりやすくする為にそうしてるのであって、データ取得上はコードと数量のようなデータのみで良いはずです。しかし、それだけだと集計表を作った時に見た目が数字の羅列で分かりにくい。
という事で活用するのがsetFormulasです。この項目はすでに「起動時に関数を自動補完する」にて紹介してるものです。これを利用することで以下の改善が見込めます。
- データ取得元からは最低限のカラムのみに絞ってデータの塊を取得するだけで済む。
- 取得したデータ配列に列を加える形で、setFormulasにて数式を挿入しておいてあげる。
- データ集計シートにマスターを設けておき、setFormulasにて、vlookupさせるようにしておく。
- setFormulasを加えた配列データをシートに書き込む事でスピードアップに繋がる
集計後は、vlookup式によって自動的に自分が持ってるマスタの値をひっぱってきてくれるので、これによって全部のカラムを取得したものを再現する事ができます。取得カラム数が少なければ書き込むスピードの向上にも繋がるので、集計シートにもマスタシートを持たせるようにしましょう。
帳票類を生成する時の注意点
スプレッドシートのレコードデータを元に、例えば納品書であったり申込書、申請書類をテンプレートのスプレッドシートの該当の箇所にsetValueしてPDF化といったような作業をしてる人は結構いると思います。自分もこの手は実によく利用します。しかし、ここにも改善点があります。
- 各セルに対してsetValueをする場合、入力する項目の数に比例してAPIの呼び出し回数が増える事になる。
フォームの項目が10ある場合、setValueでテンプレートシートに書き込む回数は、10となるわけです。ここで以下の仕様に変更する事でどんなにフォーム項目が増えようとも、書込回数を1にする事が可能です。
- 値をピンポイントにセルに入れるより１レコード入れて、参照させるようにする
という事です。帳票のメイン画面とは別のシートを用意し、そこに1レコード入れてあげるだけ。メイン画面はそのレコードから数式で呼び出せばいいだけです。ですので、1レコードをポンと入れただけで帳票完成になりスピードアップに繋がります。もちろん、PDFにする時はパラメータとしてメイン画面のシートのみを指定して上げれば問題ありません。帳票印刷系では必須のテクニックですね。
※納品書のような複数レコードの場合はもう１シート用意してそこにデータを書き加え、メイン画面ではFilter関数でデータ呼び出すようにすると良いでしょう。
スクリプトトリガーで予め自動処理をさせておく
シートデータの回収を１～10までやるのではなく、予めスクリプトトリガーで深夜にでも自動でプレ処理をやらせてしまうという方法もアリでしょう。データ収集シート側はその結果を拾えば良いので、結果的にスピードの向上に繋がります（トータルの作業時間は変わらないわけですが、タイムアウトせずに済むのと、実際に作業してる時間はスクリプトトリガーによる自動実行を除いた時間になるわけですから）。
当たり前ですが、スクリプトトリガーでやらせる処理がタイムアウトになるのであれば無意味です。スクリプトトリガーでやらせる処理は比較的小規模なモノに留めておきましょう。また、スクリプトトリガーはウェブアプリケーションの時と同じで、getActivveSpreadsheet()ではシートを参照できないので、自分自身のシートIDを予めスクリプトプロパティやグローバル変数に配置しておく必要性があります。
onEditでちょっとした処理は随時実行させてしまう
Google Apps Scriptには、onOpenやdoGetの他にも、onEditという特別なイベントが用意されています。これはVBAで言う所のAfterUpdateに相当するものです。しかし、編集時に必ず実行されてしまう項目なので、特定のセルや列でだけ作動させるという事が出来ません。シート全体でどこかで編集があると発動してしまいます。故に、onEditを活用する場合にはちょっとした注意が必要です。
よって、onEditで発動させる内容には必ず「参照してるセルの内容」を元に「条件分岐で処理」を記述しなければなりません。また、負荷の高い処理をやらせる事も出来ません。どこか弄る度に大きな処理が発動していては作業の邪魔になってしまいます。この高速化は地味で小規模なものなので、積極的に高速化の為に使うという事はせず、使うシーンを選びましょう。コードとしては以下の様な感じになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
function onEdit(e) { //編集されたセルの範囲を取得する var cell = e.range; //B列のセルが編集されたら発動する if (cell.getColumn() == 2) { //別のシートのB列の値と同じ値を持つレコードを処理する var row = cell.getRow(); //レコードナンバーを取得する ・・・ （以下略） ・・・ } } |
onEditの引数eには色々なセルに関する情報が詰まってるので、それを分解して判定をさせ、処理を実行するわけです。getColumnで列番号、getRowで行番号が取得できるので、特定のセルでのみ発動といった事も可能でしょう。
こういった処理を行っておく事で、Google Apps Script内でその処理に該当する部分は削除が出来、結果的にスピードアップに繋がります。
その他のポイント
その他の高速化ポイントとして、以下のようなものがあります。
- グローバル配列にデータを格納する方法は、スピードの向上にはなります。しかし副作用として、現時点でのデータと取得時のデータの間に乖離が生じる可能性があるので、使い所はそれほど多くはありません。
- JavaScriptでできる事はそちらでやらせるようにする。
- 配列値のチェックについて、array.indexOfよりforループで調べたほうが早い
- 特定の項目についてチェックを行うようなルーチンの場合、ランダムなデータのまま扱わず、予めスプレッドシートデータをソートしておいてから作業を行わせると、速度アップに繋がります。
- データの回収系プログラムでは、自分自身のシートにimportRange関数にて予め他のシートをリンクさせておくというテクニックも高速化にプラスになります。
関連リンク
- [GAS][スプレッドシート]処理速度を向上するには: 逆引きGoogle Apps Script
- GASでSpreadsheetを操作する自分的ベストプラクティス
- パフォーマンスの良いGASの書き方 Best Practice
- Google Apps Scriptを速くするためのベストプラクティス
- 配列を高速に探索するTips
- Google Visualization API Reference
- Query Language Reference (Version 0.7)
- Parallel processing in Apps Script
- GASの処理速度が遅いと評判なので計測してみた
- jQuery高速化！処理速度を10倍上げるテクニック20選
- Googleが示すJavaScriptを350倍高速化する秘訣
- JavaScriptのループはどれが一番高速なのか
- 多次元配列での連想配列(key:value)を取得するコード例
- [GAS] onEditでの注意点
- ダイアログを使ってタイムアウトを回避する
- Google Apps Scriptの6分の壁を突破するもう一つの方法
