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回.並べ替え
第31回.入力規則
第32回.グラフ
第33回.表示の固定
第34回.シート保護
第35回.スプレッドシートが非常に遅い、高速化するには
第36回.フォルダとファイルを扱う(DriveApp)
第37回.フォルダの一覧取得・作成・削除(Folder)
第38回.ファイルの一覧取得・削除(File)
Excel流の最終行の取得
Twitter Bot 作成


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

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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