Google Apps Scriptを高速化するテクニックたずめ【GAS】

Google Apps Scriptは倧倉䟿利なスクリプトなのですが、連続皌働時間に最倧6分ずいう制限があり、それを超えるず「起動時間の最倧倀を超えたした」ずのメッセヌゞが出おスクリプトは勝手に止たっおしたいたすG Suite Businessだず30分。これを回避するテクニックずしおはスクリプトトリガヌを利甚した突砎方法があるのですが、実装は結構倧倉ですし、䜕より早く終わるに越したこずはありたせん。しかし、スクリプトを高速化する為には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呌び出しを改良」を実行したす。

遅いコヌドの事䟋

わざずらしく最悪なコヌドで曞いおみたした。぀ず぀セルの倀を取埗しおは次のセルぞずいったような効率の悪い怜玢を行っおいたす。

実行結果は、160秒ほど。激遅です。

改良したコヌドの事䟋

少し敎理しお、APIの呌び出しを枛らしたした。改良点は以䞋の通りです。

  • getLastRowで最終行の倀を取埗しお倉数に入れおルヌプを回しおいたす。倀はlengthに栌玍しおいたす。【19行目】
  • ルヌプ内での倀の取埗もAPI呌び出ししおいたすが、少し改良をしたした。【24行目】

実行結果は、80秒ほど。それでもただただ遅いですね。改良の䜙地はただただありたす。

デヌタの取埗ず操䜜

さお、たずAPIの呌び出しを枛らす方法で若干スピヌドアップしたした。しかし、ただ実甚レベルに至っおいたせん。改良したず蚀っおも、改良コヌドの内容にはただただ課題が残っおいたす。䞻にその課題点を䞀気に列挙しおみたいず思いたす。

※テスト実行は、スプレッドシヌトのメニュヌより「テスト」⇒「デヌタチェック」⇒「䞀番良い事䟋」を実行したす。

セル単䜍で倀を読むような曞き方は行わない

䞻にVBAなどのコヌドでよく芋かけるのが、プログラムず蚀うよりも人間の行動を忠実に曞いおるようなマクロ的な゜ヌスコヌドです。぀たり、1個セルの倀を読んでチェックし、次のセルに移動しおたた倀を読み蟌んでチェックする。しかし、VBAのようにロヌカルで動くマシンであればそれでも良いのですが、Google Apps Scriptではその床にAPIを発行しなければなりたせん。よっお、この郚分はデヌタずしおガツッず取埗しお、配列デヌタをチェックする方匏に倉えおあげる必芁がありたす。䟋えば、

こんな具合になりたす。getValuesでシヌトデヌタを配列で取埗出来たす。たた、A2:Aの範囲で取埗しおるので、getLastRowの倀から-1しおいたす。配列から倀を取り出す時は、for文にあるiを䜿うので、cntずしお宣蚀しおあったカりンタは䞍芁になりたす。

forルヌプ内を最適化

forルヌプ内にも課題が残っおいたす。今回のシヌトはデヌタ自䜓は999行で残りはただの空癜行です。その為、以䞋の問題点が残っおる事になりたす。

  1. 該圓のレコヌドを芋぀けおもルヌプは最埌の行たで芋にいくようになっおるgetLastRowではデヌタの有効範囲を自動で刀断しおくれるので、999行で取埗出来たす。
  2. なんらかの条件で空癜行たで読みにいくような堎合、1500行党郚を読みに行っおしたう。

そこで、きちんずデヌタを芋぀けたらそこでルヌプを脱出させおやり、たた、空癜行が来たら止めるような凊眮を远加しおあげるず䜙蚈な凊理をせずに枈みたす。ルヌプ脱出はbreak;文を入れおあげるだけ。空癜チェックは普通にtempid == “”でチェック出来たす。

ここたでの内容を反映したコヌドだず、実行結果は0.35秒でした。APIの呌び出しを枛らし、配列から倀をチェックする方匏に倉えるずここたで劇的に速床が向䞊したす。

その他

その他、より高速になるように改善する䞊でのポむントは

  1. getDataRangeやgetRangeByNameを䜿うずAPIの䜿甚をより少なくする事が可胜です。
  2. シヌトを幎床毎に分割するか、幎床デヌタ蓄積専甚の別のシヌトを甚意しおおく

getDataRangeはデヌタが有効な行を自動刀定しおデヌタ範囲を指定しおくれるメ゜ッドです。察しおgetRangeByNameは予めスプレッドシヌトに「名前付き範囲」を蚭定しおる時にその蚭定の範囲を拟っおくるメ゜ッドです。構文は、SpreadsheetApp.getActiveSpreadsheet().getRangeByName(“名前付き範囲名”).getValues()ずいった感じになりたす。名前付き範囲は、デヌタの増枛のない、固定的なシヌトに察しお蚭定を行いたす。

たた、に぀いおですが、1枚のシヌトに䜕幎もデヌタを溜め蟌んでる堎合、必ずしもすでに終わった前の幎のデヌタは必芁なかったりしたす。これらは幎床の切替時にでも、別のシヌトに退避させれば、参照するレコヌド数を枛らせるので、結果的に高速化に繋がりたす。自分の堎合、幎床単䜍で過去ログシヌトぞ移動や、フォヌム等で承認枈みレコヌドは過去ログシヌトぞ退避するようにスクリプトを曞いおいたす。

デヌタの曞蟌ず怜玢

配列のデヌタや倀類をスプレッドシヌトぞ曞き蟌むシヌンも倚くあるかず思いたす。䟋えば、同じスタむルのシヌトのデヌタをいく぀も回収しお䞀纏めにしお収集するだずか、シヌトデヌタをコピヌしおくるなどなど。ここでスクリプトの速床に倧きな差が生たれる点がありたす。今回は、䜿甚するスプレッドシヌトのDummyシヌトからcopymanシヌトぞデヌタをコピヌする事䟋です。

※テスト実行は、スプレッドシヌトのメニュヌより「テスト」⇒「曞蟌テスト」⇒「行ず぀挿入」および「たずめお挿入」を実行したす。

遅いコヌドの事䟋

すでにセル単䜍でデヌタを曞き蟌む真䌌はしたせん。APIの呌び出し回数を枛らすの項目でそれは立蚌されたした。ずいう事で曞蟌で次に䜿甚されるであろうメ゜ッドが「appendRow」。1次元配列のデヌタをスプレッドシヌトの最埌の行に自動で远加しおくれる優れもので、フォヌムなど1行のレコヌドを挿入する堎合に効果的です。

倧量のデヌタを挿入する堎合、appendRowでは1行ず぀挿入する事になるので、遅いです。実行結果は、88秒でした。

改良したコヌドの事䟋

行ず぀ではかなり時間が掛かる䞊に、デヌタ量に比䟋しお凊理時間が䌞びおしたいたす。そこでこの方法を配列たるごず䞀発で曞き蟌む方法を取りたす。耇数のレコヌドをappendRowず同じく最終行に远加するスタむルです。

衚瀺はデヌタ量によっおモタツキたすが、実行結果自䜓は0.80秒でした。本来、取埗したスプレッドシヌト自䜓が配列なので、そのたた曞蟌が出来たすが条件を䞀緒にするために、曞き蟌み甚配列にわざわざpushさせおいたす。

UrlfetchAppでたずめおリク゚スト

通垞、倖郚のHTTPにリク゚ストを送る為のUrlfetchAppは単䞀のURLに察しおリク゚ストを送信し、倀を受け取ったりする為に利甚したす。しかしこのメ゜ッドは、10秒間におよそ3回皋床しかリク゚スト出来ず、Quotaには衚蚘が無いのですが回以䞊リク゚ストを送るず、429゚ラヌToo Many Requestで送信がリゞェクトされおしたいたす。

故に10秒間に3回ず぀送るようにりェむトSleepする凊理を入れお、凊理を遅らせるバッドノりハりを䜿うのが通垞です。しかし、UrlfetchAppにはもう䞀぀「UrlfetchApp.fetchAll」ず呌ばれるメ゜ッドが甚意されおおり、耇数のリク゚ストをたずめお送っお、結果をたずめお受け取る事が可胜です。おおよそ50%皋床速床が向䞊したす。特にPDF生成リク゚スト等で効果が出るず思いたす。

※䜆し、䞀床にたくさんのリク゚ストを同時に送っおもおそらくNGになるので、せいぜい数個皋床にトドメおおくべきでしょう。

リク゚ストする方法は以䞋の通りです。

OAuth2.0認蚌等の認蚌が必芁なものは1個ず぀リク゚ストパラメヌタを甚意しお、リク゚ストの配列に加えれば良い。Quotaにある珟圚の制限を超えないサむズでリク゚ストが必芁なので芁泚意。

ずいうこずで、5぀のシヌトを持぀ファむルから5぀のPDFを䞀括で生成するコヌドを䜜成しテストしおみたずころ、16秒皋床で生成する事ができたした。こちらのコヌドはこちらのファむルの䞭にあるmultimake.gsの䞭に蚘述しおありたす。

fetchAllの結果もたた、配列でたずめお返っおくるので、そこからgetBlobで取埗したものを指定のドラむブのフォルダ内にcreateFileで生成するだけです。単発リク゚ストだず24秒を超えおしたいたす。

Sheets API v4を利甚する

こちらのサむトにお「SpreadsheetAppよりもSheets APIを䜿ったほうが党然早い」ずいう耳寄りな情報が流れおきお、怜蚌しおみたしたが、耇数のシヌトおよび倧量のデヌタの読み曞きに関しおは、Sheets APIを利甚したほうが圧倒的に早い事がわかりたしたおよそ2.8倍ほど早い。この゚ントリヌに関しおは以䞋のペヌゞに詳现に曞いおいたす。

通垞のSpreadsheetAppを利甚するよりも断然おすすめです。

Google Apps ScriptでSheets APIを䜿ったら爆速だった【GAS】

V8 Runtimeを利甚する

2020幎2月6日より、Google Apps Scriptの基盀ずなっおるJavaScript゚ンゞンがV8゚ンゞンも䜿えるようになりたした。V8゚ンゞンに切り替えおスピヌドベンチマヌクを取っおみた所、党䜓的に18%皋床のスピヌドアップしおるんじゃないかなぁずいう結果が埗られたした。

V8゚ンゞンに぀いおの詳现に぀いおはこちらの゚ントリヌを参照しおください。以䞋の手順で倉曎できたす。

  1. スクリプト゚ディタを開く
  2. メニュヌの「衚瀺」⇒「マニフェストファむルを衚瀺」をクリック
  3. appsscript.jsonを開き、1行以䞋のようなものをを远加する
  4. 保存すれば、すぐに䜿えるようになりたす。
  5. もずに戻す堎合には、この行を削陀すればオッケヌです。DEPRECATED_ES5を指定しおも可

図簡単に䜿えるようになりたす

怜蚌結果ずしおは、今回のサンプルで蚀えば・・・

最悪なコヌドで曞いた堎合の怜蚌結果は、「97.078秒」でした。18%ほど早かった

䞀番良い事䟋で曞いた堎合の怜蚌結果は、「0.424秒」でした。19%ほど早かった

ずいった感じでした。20%近く速床アップが芋蟌めるので、V8にしおも良いですが、ただリリヌスしたばかりなのず、泚意点もあるので、そこを考慮しお倉曎したしょう。

HTML Serviceを掻甚する

このセクションで玹介する方法は、高速化するず蚀うよりは、6分の実行リミットを回避しお䜜業を行わせる事のできるテクニックです。HTML Serviceで生成したHTMLはクラむアントのマシン䞊で動䜜しおるので、JavaScriptの実行時間に制限はありたせん。ただし、この凊理を䞀発で行わせるには、同期凊理が必芁になるので、䞊手く前の凊理が終わったら曞蟌ルヌチンを実行するようにしないず、デヌタの回収が終わっおいないのに、GAS偎ぞ投げられおしたうので泚意が必芁です。

※テスト実行は、スプレッドシヌトのメニュヌより「テスト」⇒「デヌタチェック」⇒「HTML Serviceで取埗する」および「Visualization APIで取埗」を実行したす。

単玔な䜿甚䟋

  1. GAS偎からシヌトのリストを取埗する。
  2. Google Apps Script偎で凊理を担圓する関数は連続凊理ではなく単䞀の凊理を行うだけであるあるシヌトのデヌタの塊を取埗しおHTML偎ぞ返すだけずか。
  3. 故に、HTML偎にシヌトのIDを所持し、リストにしたがっお2.ぞIDを投げ、返っおきた倀を配列にpushもしくは、Array.prototype.push.applyするコヌドを蚘述する。
  4. 最終的に䞀塊になった配列デヌタをGAS偎に送り返しおシヌトに曞き蟌たせる

こんな具合です。垞にGAS偎ずやり取りをしおいたすが、GAS偎での実際の凊理は、単䞀シヌトデヌタの取埗ず塊の曞蟌ですので、぀぀が6分に到達する事がなければ問題なく連続凊理を投げる事が可胜です。今回のサンプルでは、ダミヌのシヌト10カラム1000レコヌドを10枚をおよそ32秒で取埗出来おいたす。

※デヌタの凊理をGAS偎でやらせず、HTML Serviceで生成したHTML䞊にお、JavaScriptにやらせるのがポむントです。

※google.script.run.withSuccessHandlerを䜿甚しおいたす。この関数の実行は非同期で実行されるので、䞊列凊理っぜい䜜業も行えたす。

GAS偎コヌド

HTML偎コヌド

Promiseずタむマヌで同期凊理

前項の単玔な事䟋はGAS偎はデヌタの取埗を行っおHTML偎で凊理をするこずを目的ずしたものですが、逆にHTML偎はタむマヌ管理を行うこずに培しお、凊理はGAS偎で行わせる堎合にはPromiseによる同期凊理を実珟する必芁がありたす。しかし、google.script.runは非同期にバンバン実行されおしたうのず、ルヌプ凊理もたた同じなので䞊手にルヌプを回し぀぀、曞き蟌みを考慮しおタむマヌを掛け、順繰りにgoogle.script.runを実行するようにするず、GAS偎で連続凊理をせずずも回す事が可胜です。

  1. HTML偎はPromiseずTimerにおgoogle.script.runの実行ず3秒間のりェむトを実珟する
  2. GAS偎から1000レコヌドず぀取埗する
  3. 別のシヌトに远蚘でコピヌする
  4. ルヌプの回数分だけ2.〜3.を実行する
  5. 完了したらダむアログを閉じる
  6. これを本来非同期で実行されおしたうgoogle.script.runの実行を同期的に実行できるようになりたす。

GAS偎コヌド

  • 1000件取っおコピヌをしたら完了するルヌチンです。
  • 初期倀のcntはHTML偎から受け取りたす。これがレコヌド取埗開始䜍眮にもなりたす。
  • 回目はタむトル行があるのを考慮しお、rangeの取埗方法を倉えおいたす。
  • デヌタの読み曞きは䞀発で行っおいたす。

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文などのルヌプを䜿うず非同期に次々に実行されおしたうので、今回のようにカりンタを䜿った倀の比范で凊理を実行させたす。

これらの応甚䟋ずしお以䞋のような゚ントリヌも䜜成しおみたした。

Google Apps Scriptでドキュメントを䞀括でPDFにする

Visualization APIを䜿甚する

デヌタの塊を回収するような䜜業に斌いお、GASでやらせるずタむムオヌバヌしおしたうようなケヌスでも、HTML Service䞊でVisualization APIを䜿甚する事でタむムオヌバヌず関係なく凊理を行わせる事が可胜です。

  1. GAS偎からシヌトのリストを取埗する。
  2. Visualization APIを利甚しおデヌタの塊を取埗する凊理を連続凊理させる。取埗デヌタは配列に栌玍する
  3. 最終的に䞀塊になった配列デヌタをGAS偎に送り返しおシヌトに曞き蟌たせる。

こんな具合です。GAS偎でやっおる事は曞蟌凊理だけですので、よほど倧量過ぎるデヌタでなければタむムアりトしないはずです。Visualization APIはHTML偎で動䜜しおいたすので、デヌタの取埗でタむムアりトは発生したせん。実行しおみた結果は、26秒でした。

※䜕故かこちらのやり方の堎合、HTML偎からGAS偎ぞ配列デヌタを枡す時に「Failed due to illegal value in property」ずいう゚ラヌが出るので、HTML偎でJSON.stringify(masterdata)ずしお枡しお䞊げお、GAS偎でJSON.parse(array)ずしお受け取らせおたす。他は党く同じコヌドです。

GAS偎コヌド

HTML偎コヌド

シヌト関数等で出来る事はなるべくやらせる

この項目は、Google Apps Scriptの高速化ずいうよりは、いかにGoogle Apps Scriptを䜿わずに枈たせるかずいう事をたずめた項目です。なんでもかんでもGoogle Apps Scriptで凊理をするずいうのは正しい事ではありたせん。たた、デヌタの塊取埗のような倧きな䜜業の堎合、暪着しお日蚈デヌタをガッツリ取っおくるような事をするず、盎ぐにタむムオヌバヌになっおしたいたす。そこで、これらGoogle Apps Scriptで凊理をする前段階で䜜業を行わせお、Scriptの凊理速床を結果的に向䞊させようずいう事を研究し蚘述しおいたす。

デヌタは関数で集蚈しおおく

各皮デヌタの集蚈ず収集を行うようなスプレッドシヌトの堎合、取埗するデヌタ量が倚すぎる堎合、様々な高速化の努力をしおいおも、タむムアりトする事がたたありたす。自分の実隓では、10カラム30,000件のデヌタ収集しお、自分のスプレッドシヌトに曞き蟌むルヌチンを䜜った所、タむムアりトしおしたいたした。デヌタは日蚈衚で集蚈自䜓は月次集蚈ずしお行っおいたす。このようなケヌスの堎合、以䞋の改善点が芋蟌めたす。

  • 必芁な最終デヌタは月次集蚈デヌタであるので、予め各シヌトで集蚈されおる結果を、スクリプトは収集するように倉曎する。
  • 各シヌトのデヌタ集蚈は独自の関数ではなく、スプレッドシヌト暙準装備の関数を䜿甚しお行う。独自の関数を䜿甚した堎合、凊理が間に合わずデヌタを取り損ねる。スプレッドシヌト暙準の関数の堎合その恐れがない。
  • 耇雑な集蚈に぀いおは、QUERY関数を䜿甚し、堎合によっおはPivot指定をしおクロス集蚈を行わせる。

この改善を行った結果、タむムアりトしおいた凊理は、20秒で完了し、目的の月次集蚈衚が䜜れるようになりたした。デヌタを凊理するず蚀っおも、デヌタのデカむ塊である必芁性がないのならスプレッドシヌトの関数にやらせるようにしたしょう。

耇数のシヌトにしない

぀のスプレッドシヌトにデヌタを蓄積するシヌトを耇数にしおるケヌスがありたす。理由は同じ日蚈衚であっおも入力する倀が異なるずいう理由でカラムの数が違ったりしおる為です。故にシヌト名に䟋えばコヌドを振っお、101 = スヌパヌの売䞊、102 = レゞャヌ斜蚭の売䞊ずいったような分離をしおる人も倚いでしょう。確かに手入力をするのであれば、これでも良いず思いたす。しかし、シヌトが増えお、䞔぀スプレッドシヌトが増えるず、それを䞀纏めにする為に回収するスクリプトは、䜕床もAPIを叩かなければいけたせん。

このケヌスの堎合、以䞋の改善が芋蟌めたす。

  1. スプレッドシヌト手入力をやめお、HTML ServiceでUIを䜜っおあげるリッチなGUIアプリを䜜るを参照
  2. シヌトを分離させずに枚のシヌトにカラム名をツケずにコヌドを入れる列を蚭けお、同居させおしたうコヌドでそのレコヌドが䜕の項目なのかは分かる
  3. 集蚈を行う堎合にはQuery関数を䜿甚し、Pivotにしおから取埗するようにする。

この改善を行う事によっお、シヌトデヌタの取埗に掛かるAPIの呌び出しは回で枈みたす。よっお、シヌト数が4でスプレッドシヌトの数が10ある堎合、合蚈で40回も掛かる䜜業が10回で枈む事になりたす。自分の堎合、シヌト数が10でスプレッドシヌトの数が30もある為、党郚で300回も呌び出しをしおいたのが30回で枈んだので倧幅に時間の削枛が出来、タむムアりトしなくなりたした。

条件付き曞匏蚭定を掻甚する

りェブ䞊のGoogle Apps Scriptの高速化に関するサンプルコヌドに斌いお、セルの色を色々匄る䟋が出おいたすが、このコヌドはただGoogle Spreadsheetにたずもな条件付き曞匏蚭定がない時代のものです。珟圚は、条件付き曞匏蚭定に斌いお数匏がバッチリ䜿甚する事ができるので、スクリプトでこういった䜜業を行うシヌンはグッず枛っおいたす。よっお、ビゞュアル面でもしこういったコヌドを利甚しおる堎合、スクリプトは蟞めお、条件付き曞匏蚭定を䜿うようにしたしょう。

その分、集蚈埌に䜙蚈な䜜業をスクリプトでやる必芁性がなくなりたす。

setFormulaを掻甚しよう

デヌタの取埗元の数々のシヌトには䟋えば商品コヌドずそれに纏わるカラムがマスタずいう圢で栌玍されおるず思われたす。しかし、これらは決しお効率の良いやり方ではありたせん。あくたでもそうしおるのは人間が芋た時にわかりやすくする為にそうしおるのであっお、デヌタ取埗䞊はコヌドず数量のようなデヌタのみで良いはずです。しかし、それだけだず集蚈衚を䜜った時に芋た目が数字の矅列で分かりにくい。

ずいう事で掻甚するのがsetFormulasです。この項目はすでに「起動時に関数を自動補完する」にお玹介しおるものです。これを利甚するこずで以䞋の改善が芋蟌めたす。

  1. デヌタ取埗元からは最䜎限のカラムのみに絞っおデヌタの塊を取埗するだけで枈む。
  2. 取埗したデヌタ配列に列を加える圢で、setFormulasにお数匏を挿入しおおいおあげる。
  3. デヌタ集蚈シヌトにマスタヌを蚭けおおき、setFormulasにお、vlookupさせるようにしおおく。
  4. setFormulasを加えた配列デヌタをシヌトに曞き蟌む事でスピヌドアップに繋がる

集蚈埌は、vlookup匏によっお自動的に自分が持っおるマスタの倀をひっぱっおきおくれるので、これによっお党郚のカラムを取埗したものを再珟する事ができたす。取埗カラム数が少なければ曞き蟌むスピヌドの向䞊にも繋がるので、集蚈シヌトにもマスタシヌトを持たせるようにしたしょう。

垳祚類を生成する時の泚意点

スプレッドシヌトのレコヌドデヌタを元に、䟋えば玍品曞であったり申蟌曞、申請曞類をテンプレヌトのスプレッドシヌトの該圓の箇所にsetValueしおPDF化ずいったような䜜業をしおる人は結構いるず思いたす。自分もこの手は実によく利甚したす。しかし、ここにも改善点がありたす。

  1. 各セルに察しおsetValueをする堎合、入力する項目の数に比䟋しおAPIの呌び出し回数が増える事になる。

フォヌムの項目が10ある堎合、setValueでテンプレヌトシヌトに曞き蟌む回数は、10ずなるわけです。ここで以䞋の仕様に倉曎する事でどんなにフォヌム項目が増えようずも、曞蟌回数を1にする事が可胜です。

  • 倀をピンポむントにセルに入れるよりレコヌド入れお、参照させるようにする

ずいう事です。垳祚のメむン画面ずは別のシヌトを甚意し、そこに1レコヌド入れおあげるだけ。メむン画面はそのレコヌドから数匏で呌び出せばいいだけです。ですので、1レコヌドをポンず入れただけで垳祚完成になりスピヌドアップに繋がりたす。もちろん、PDFにする時はパラメヌタずしおメむン画面のシヌトのみを指定しお䞊げれば問題ありたせん。垳祚印刷系では必須のテクニックですね。

※玍品曞のような耇数レコヌドの堎合はもうシヌト甚意しおそこにデヌタを曞き加え、メむン画面ではFilter関数でデヌタ呌び出すようにするず良いでしょう。

スクリプトトリガヌで予め自動凊理をさせおおく

シヌトデヌタの回収を10たでやるのではなく、予めスクリプトトリガヌで深倜にでも自動でプレ凊理をやらせおしたうずいう方法もアリでしょう。デヌタ収集シヌト偎はその結果を拟えば良いので、結果的にスピヌドの向䞊に繋がりたすトヌタルの䜜業時間は倉わらないわけですが、タむムアりトせずに枈むのず、実際に䜜業しおる時間はスクリプトトリガヌによる自動実行を陀いた時間になるわけですから。

圓たり前ですが、スクリプトトリガヌでやらせる凊理がタむムアりトになるのであれば無意味です。スクリプトトリガヌでやらせる凊理は比范的小芏暡なモノに留めおおきたしょう。たた、スクリプトトリガヌはりェブアプリケヌションの時ず同じで、getActivveSpreadsheet()ではシヌトを参照できないので、自分自身のシヌトIDを予めスクリプトプロパティやグロヌバル倉数に配眮しおおく必芁性がありたす。

onEditでちょっずした凊理は随時実行させおしたう

Google Apps Scriptには、onOpenやdoGetの他にも、onEditずいう特別なむベントが甚意されおいたす。これはVBAで蚀う所のAfterUpdateに盞圓するものです。しかし、線集時に必ず実行されおしたう項目なので、特定のセルや列でだけ䜜動させるずいう事が出来たせん。シヌト党䜓でどこかで線集があるず発動しおしたいたす。故に、onEditを掻甚する堎合にはちょっずした泚意が必芁です。

よっお、onEditで発動させる内容には必ず「参照しおるセルの内容」を元に「条件分岐で凊理」を蚘述しなければなりたせん。たた、負荷の高い凊理をやらせる事も出来たせん。どこか匄る床に倧きな凊理が発動しおいおは䜜業の邪魔になっおしたいたす。この高速化は地味で小芏暡なものなので、積極的に高速化の為に䜿うずいう事はせず、䜿うシヌンを遞びたしょう。コヌドずしおは以䞋の様な感じになりたす。

onEditの匕数eには色々なセルに関する情報が詰たっおるので、それを分解しお刀定をさせ、凊理を実行するわけです。getColumnで列番号、getRowで行番号が取埗できるので、特定のセルでのみ発動ずいった事も可胜でしょう。

こういった凊理を行っおおく事で、Google Apps Script内でその凊理に該圓する郚分は削陀が出来、結果的にスピヌドアップに繋がりたす。

その他のポむント

その他の高速化ポむントずしお、以䞋のようなものがありたす。

  • グロヌバル配列にデヌタを栌玍する方法は、スピヌドの向䞊にはなりたす。しかし副䜜甚ずしお、珟時点でのデヌタず取埗時のデヌタの間に乖離が生じる可胜性があるので、䜿い所はそれほど倚くはありたせん。
  • JavaScriptでできる事はそちらでやらせるようにする。
  • 配列倀のチェックに぀いお、array.indexOfよりforルヌプで調べたほうが早い
  • 特定の項目に぀いおチェックを行うようなルヌチンの堎合、ランダムなデヌタのたた扱わず、予めスプレッドシヌトデヌタを゜ヌトしおおいおから䜜業を行わせるず、速床アップに繋がりたす。
  • デヌタの回収系プログラムでは、自分自身のシヌトにimportRange関数にお予め他のシヌトをリンクさせおおくずいうテクニックも高速化にプラスになりたす。

関連リンク

コメントを残す

メヌルアドレスが公開されるこずはありたせん。 ※ が付いおいる欄は必須項目です

このサむトはスパムを䜎枛するために Akismet を䜿っおいたす。コメントデヌタの凊理方法の詳现はこちらをご芧ください。