Google Apps Script入門
スプレッドシートが非常に遅い、高速化するには

Google Apps Script（GAS）の入門解説です
公開日：2017-01-17　最終更新日：2019-11-21

第35回.スプレッドシートが非常に遅い、高速化するには

Google Apps Script で、スプレッドシートのセルを扱うと、とても処理時間がかかります、


あまりにも遅く、目で動作がゆっくり見えるレベルです、

データ量が多くなっても、実用に耐える速度で処理する方法を解説します。

スプレッドシートに比べればExcelはかなり速いですが、VBAの書き方によっては処理時間がかかってしまう場合もあります。
Excelでの処理速度対策は、
マクロVBAの高速化・速度対策の具体的手順と検証
マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。
こちらを参照してください。
最終的には、配列を使用することになります。

Google Apps Script で、スプレッドシートを扱う場合も、
処理速度を速くしたいのなら、配列を使う事になります。

VBAでも、GASでも、時間のかかる処理は、セルへのデータ読み書きになります。
配列を使用することで、セルへのデータ読み書き回数を減らします。

つまりは、
getRange
getValue
setValue
これらの回数を減らすという事です。

以下の表を使って解説します。

google apps script 画像

データは、200行あります。
金額（D列） = 単価（B列） * 数量（C列）
この計算を行います。


普通に書くと

function mySample35_1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var tannka,suuryou
  var lastRow = sheet.getLastRow();
  for (i=2; i<=lastRow; i++) {
    tannka = sheet.getRange(i, 2).getValue();
    suuryou = sheet.getRange(i, 3).getValue();
    sheet.getRange(i, 4).setValue(tannka * suuryou) ;
  }
}

概ね、
15秒～17秒程度の時間がかかります。

さすがに時間がかかりすぎです。
Excelなら一瞬で終わる処理ですね。


配列を使って書くと

function mySample35_2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var tannka,suuryou
  var lastRow = sheet.getLastRow();
  var ary1 = sheet.getRange(2, 2, lastRow-1, 3).getValues();
  for (i=0; i<=ary1.length-1; i++) {
    ary1[i][2] = ary1[i][0] * ary1[i][1]
  }
  sheet.getRange(2, 2, lastRow-1, 3).setValues(ary1);
}

概ね、
0.12秒～0.14秒程度の時間で終わります。
なんと、100倍の違いです。

ただし、この例では非常に単純な処理で、
ほぼセルへのデータ読み書きだけの処理なので、極端に違いが出ていることは承知してください。


配列の解説

セル範囲を配列へ
var ary1 = sheet.getRange(2, 2, lastRow-1, 3).getValues();

これで、セル範囲を配列に入れています。
getValue
ではなく、
getValues
を使う事で、セル範囲を一括で配列化する事が出来ます。

ary1
は、2次元配列になります。
ary1[0]：[296,16,] ・・・ B2～D2
ary1[1]：[926,8,] ・・・ B3～D3
つまり、
ary1[0][0]：296 ・・・ B2
ary1[0][1]：16 ・・・ C2
ary1[0][2]： ・・・ D2
ary1[1][0]：926 ・・・ B3
ary1[1][1]：8 ・・・C3
ary1[1][2]： ・・・D3


配列の処理
forでループさせるだけですが、
注意点としては、
ary1.length
これは、配列の要素数になります。
配列は、0から始まります。
10個の配列なら、
0,1,2,3,4,5,6,7,8,9
となります。
この場合、lengthは10となりますので、
forでは、0からlength-1までループするようにします。

ary1.lengthは1次元目のようそすうですが、2次元目の要素数を取得する場合は、
ary1[0].length
これで取得できます、今回の場合は3となります。

配列をセル範囲へ
sheet.getRange(2, 2, lastRow-1, 3).setValues(ary1);

これで、配列をセル範囲に入れています。
setValue
ではなく、
setValues
を使う事で、配列をセル範囲に一括で入れる事が出来ます。


スプレッドシートが数百行以上になり、その全行を処理するようなスクリプトを書く場合は、
必ず今回の配列を使うようにして下さい。
あまりにもGASでのセル操作は遅すぎます。
そんなに急いだ処理ではないとしても、物事には限度というものがあります。
配列を使わない場合の速度は、さすがに限度を超えた遅さだと思います。



同じテーマ「Google Apps Script入門」の記事

第30回.並べ替え

GoogleAppsScriptで、スプレッドシートを並べ替えます、書き方のバリエーションがいろいろありますが、並べ替えキー列、昇順・降順、これらをどのように指定するかだけの違いになります。書き方のバリエーションごとに順に見ていきましょう。
第31回.入力規則
GoogleAppsScriptで、スプレッドシートに入力規則を設定します、入力規則は機能がとても多く、それにメ対応したソッドも多数あります。ここでは、メソッドの一覧と、代表的な使い方を参考スクリプトを掲載します。入力規則の作成方法の概要 クラスSpreadsheetAppのメソッドnewDataValidatio…
第32回.グラフ
GoogleAppsScriptで、スプレッドシートにグラフを挿入します。スクリプトであまり凝ったレイアウトのグラフを作成しようとすると、結構大変になりますので、そのような場合は、手動で作成したグラフをスクリプトで変更する等の工夫も必要です。
第33回.表示の固定
GoogleAppsScriptで、シートの表示（スクロール）の固定を設定します。表とは一般に、横に項目、縦にデータが連続して入っているものです、スクロールした場合に、見出しが見えなくなってしまう事のないように設定しておきます。表示（スクロール）の固定に関するメソッド Sheetのメソッドを使います。
第34回.シート保護
GoogleAppsScriptで、スプレッドシートを保護します、スプレッドシートの保護に関しては、少々面倒なスクリプトだと感じます。ここでは理屈抜きでサンプルコードの提示に徹します。保護する場合の実際のスクリプトのサンプルを見ていきましよう。
第35回.スプレッドシートが非常に遅い、高速化するには
第36回.フォルダとファイルを扱う（DriveApp）
GoogleAppsScriptで、Googleドライブのファイルやフォルダを扱う場合は、DriveAppクラスを使います。DriveAppクラスの各種メソッドの戻り値（FolderクラスやFileクラス）のメソッドを使って各種操作を行います。
第37回.フォルダの一覧取得・作成・削除（Folder）
GoogleAppsScriptで、フォルダ内のサブフォルダ一覧を取得したり、フォルダ内にサブフォルダを作ったり削除したりするスクリプトの書き方を解説します。使用するクラスは、DriveApp Folder File FolderIterator FileIterator これらのメソッドを使います。
第38回.ファイルの一覧取得・削除（File）
GoogleAppsScriptで、フォルダ内のファイル一覧を取得したり、フォルダ内にファイルを作ったり削除したりするスクリプトの書き方を解説します。使用するクラスは、DriveApp Folder File FolderIterator FileIterator これらのメソッドを使います。
Excel流の最終行の取得
Excelでは、xlUpやxlDownで指定列の最終行を取得することが多いです。GoogleAppsScriptにもどうようのものは用意されています。かなり記述が複雑になっててますが、このGASの書き方を紹介します。
Twitter Bot 作成
GoogleAppsScriptで、TwitterのBotを作成します、全て作成していたら大変ですし、今の段階では私にその技量はありませんので、ネットに公開されているものを参考に作成しました。従って、ここでは手順の紹介がメインで、コード等はリンク先を参照してください。


新着記事NEW ・・・新着記事一覧を見る

TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ
同じテーマの記事
新着記事
アクセスランク


↑