2016.04.05みなさん、こんにちは!タカハシ(@ntakahashi0505)です。Scriptでスプレッドシートの操作をしていて、こう感じたことはありませんか?実行速度が遅い...遅いぞ!スプレッドシートへの読み書きが多いと、どうやらすごく遅くなってしまいます。エクセルVBAの場合は画面表示の更新を停止するなどをして高速化をすることができましたが遅い...重い...そんなエクセルVBAプログラムの処理速度を劇的に改善する方法エクセルVBAの実行速度が遅い・重いって時ありませんか?そんな時にエクセルVBAのプログラムの処理速度を速くするテクニックを紹介します。プログラムの実行時間を測定する方法も合わせてお伝えします。Scriptでスプレッドシートを操作する場合に処理速度を格段に速くする方法をお伝えしていきます。Scriptでメルマガシステムを作っちゃおう!どれくらい遅いか?例えば、以前紹介しましたこちらの記事のスクリプト。Scriptでメルマガ配信システムを作りました。今回はスプレッドシートにインポートした名刺管理アプリのデータを使ってメルマガ配信リストを自動更新する方法を紹介いていきます。tonari-it.com2016.03.29名刺管理サービスCAMCARDから出力した連絡先リストを取り込んで、メルマガ配信リストを更新するものです。このスプリプトに時間を測定する処理をちょちょっと追加します。);}例えばCAMCARDのデータが50行あったとして、測定しながら実行してみますとこのように約42秒もかかります。これが500件、5000件となったら...ちょっと実用に耐えないですよね。今回はこの課題を解決していきます。APIの呼び出し回数が多いと遅くなるなぜこのスクリプトが遅いか?ということなのですが、ヒントはAPIの呼び出し回数にあります。ここで言うAPIは他でもないSpreadsheetAppです。getActiveSpreadsheet()getSheetByName()getDataRange()getLastRow()getRange()getValue()setValue()などを使ってスプレッドシートにアクセスをするたびにAPIが呼び出されています。処理}このifの条件比較には一気に4つのAPI呼び出しがあります。Script的にはたまったもんじゃないわけです。Scriptの高速化の方法をお伝えしていきます。シート1にこのようなデータを用意します。ランダムの数値が横3列×縦50行分並んでいまして、見出しを入れると全部で51行です。これをシート2にコピーしたいと思います。高速化を考えない場合はこのようなスクリプトになります。j=1;j<=3;j++){sheet2.getRange(i,j).setValue(sheet1.getRange(i,j).getValue());}}}わかりやすさのために実行時間測定の部分は省いています。このスプリプトを実行してみますと21.8秒もかかっていますね。API呼び出しが多いところを確認する遅くなる原因でいうとスプレッドシートのAPI呼び出し回数ということになります。前半の2~8行でシート1やシート2の準備で合計4回のAPI呼び出しがありますが、なんといっても12行目ですね。入れ子になっている二つのfor文の中に、たったの一行で4つもAPIを呼び出すメソッドが含まれています。行数が51、列数が3、読み書きのAPI呼び出しが1周あたり4回ですから、全部で612回のAPI呼び出しが発生していることになります。セル一つ一つに対して読み書きをするメソッドであるgetValueやsetValueを何も考えずに使いまくると必然的に遅くなってしまうということになります。APIリクエストを減らす方法セル一つ一つに読み書きをすると遅くなるということですから、例えばデータのある範囲全体を一気に読み書きしたらよいのではないか?ということになります。その路線で検証してみましょう。シート範囲を配列にまとめて読み書きするgetValuesとsetValuesシートの範囲をまとめて読み書きをするgetValues、setValuesというメソッドがあります。Rangeオブジェクト.getValues()Rangeオブジェクト.setValues(配列)これを使えばセル一つ一つではなくて、シート範囲のデータをまとめて読んてきたり、まとめて書き出したりすることができますね。getValuesで取得した範囲のデータは二次元配列に入り、setValuesでシートに書き出す場合は二次元配列を指定しなければいけません。getValuesで取得したデータの二次元配列について二次元配列...ちょっと不慣れな場合もあるかも知れませんね。var1=sheet1.getDataRange().getValues();とします。以下続くとした場合と同じになります。それぞれの行に対してまず一つ目の配列が用意されて、その中にそれぞれのセルの値が配列で格納されているというわけです。ここで注意すべき点として、行も列も配列番号は「0」からスタートするということです。1行目のデータはvar1[0]に格納され、1行目の1列目のセルはvar1[0][0]に格納されます。getValues、setValuesを使ったスクリプトに修正では、前述のスクリプトをgetValues、setValuesを使ったスクリプトに書き直してみましょう。*/sheet2.getRange(1,1,row1,3).setValues(var1);}...入れ子のfor文だったあたりが、12行目に集約されました。超シンプルなたったの一行になっちゃいましたね。getRangeで範囲を指定する15行目のgetRangeですが引数が4つあります。一つのセルの場所を指定する場合は引数は2つでしたが、getRangeで範囲を表す方法としては、範囲の開始セルの行数、列数とともに、その範囲の行数、列数も指定をします。範囲の列数)今回の場合は、開始セルは(1,1)、範囲の行数はシート1の行数、範囲の列数は3になりますのでJavaScriptsheet2.getRange(1,1,row1,3).setValues(var1);1sheet2.getRange(1,1,row1,3).setValues(var1);となったわけです。Scriptの高速化の結果getValues、setValuesを使って高速化をしたスクリプトを実行してみますと0.57秒...!実行速度はなんと38倍に。Scriptでスプレッドシートを操作する際に処理速度を格段に速くする方法についてお伝えしました。二次元配列の取り扱いは慣れないと少しわかりづらいところもあるかも知れませんが、プログラムの処理速度を考えると絶対に使うべきですよね。次回ですが、今回の高速化のテクニックを活用してメルマガ配信リストの更新のスクリプトを修正していきたいと思います。Scriptを使ってメルマガ配信システムを作成しています。今回はスプレッドシートのAPI呼び出し回数を抑えることで、メルマガ配信リストの自動更新スクリプトの高速化を図ります。ScriptでGmail、スプレッドシート、ドキュメントを連携させて操作することでメルマガシステムが簡単に実現できます。思ったより2割くらい簡単にできますよ。ちなみに無料です...さすがGoogle。プランノーツ代表、コミュニティ「ノンプロ研」主宰。ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ★フォロー頂ければ嬉しいです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。!ノンプログラマーがプログラミングスキルを身に着ける支援ををするコミュニティ。セミナー・もくもく会・Facebookグループのサポートで「自らで学び続ける力をつける」支援、「教え合うことで学びの価値を上げる」場の提供をしています。Scriptで日付&時刻の便利ライブラリMoment.jsを使う方法についてお伝えしています。今回は日時の書式フォーマットを指定するformatメソッドの使い方です。Scriptを使って、作成したレポートをPDFにして、メールで送付するテクニックを紹介します。初心者の方でもグラフ作成から、レポート作成、メールでの共有まで、自動化することができるようになります。SQLのデータベースにアクセスする方法をシリーズでお伝えしています。SQLインスタンスに接続する方法です。AppsスプレッドシートのQUERY関数を使う最初の一歩!クエリを理解するGoogleスプレッドシートのQUERY関数を紹介するシリーズ。第一回目は、「クエリ」とは何か?クエリでデータを抽出するイメージ・QUERY関数を使うメリットをお伝えします。Scriptの超入門ということで、GASプログラミングの基礎中の基礎を学んでいくシリーズです。今回、プログラミングで重要なテクニックである変数の使い方とデータ型についてお伝えします。Scriptのシリーズとして、名言Botの作成の方法をお伝えしています。今回は、時限式のイベントトリガーを設置して決まった時刻にチャットワークにBot送信する方法です。コメントさせていただきます。グーグルスプレッドシートの高速化をしたいと考えていたところ、この記事にたどり着きました。処理}など、入力する箇所がわからず途方にくれています。具体的な入力箇所もしくはそれがわかる記事を紹介していただければ幸いです。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。「コミュニティ+講座」形式で継続性&教え合える環境も提供。詳細・お申込はこちら隣ITを応援する当ブログを「応援したい!」「役に立ったよ!」というお気持ちを、コチラからお支払いただくことができます。日々更新の活力倍増いたします。※PayPalアカウントが必要です。※金額は変更可能です。Platform23HTML・CSS32iPhone・アプリ29Monaca・スマホアプリ開発7PC全般・Windows86PowerPoint・VBA28Python87RPA1VBScript15Vue.js・JavaScript13WEBサービス・クラウド68WordPress・ブログ88Word・VBA42コミュニティ・イベント108セミナーのお知らせ11チャットワーク72データ分析・アクセス解析40仕事・生産性・働き方73動画配信8商業出版21起業・フリーランス72電子書籍・Kindle3運営会社プランノーツは"IT×働き方改革"をお手伝いする会社です。RowSourceプロパティとAddItemメソッド2020.01.21GASでGoogleスライドのSlideオブジェクトの概要とその取得方法2020.01.162020.01.24Facebookページいつも隣にitのお仕事タイトルとURLをコピーしました。