Google Apps Script入門 | 第35回.スプレッドシートが非常に遅い、高速化するには | Google Apps Script(GAS)の入門解説です



最終更新日:2017-01-17

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

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

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

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

スプレッドシートに比べればExcelはかなり速いですが、VBAの書き方によっては処理時間がかかってしまう場合もあります。

Excelでの処理速度対策は、
マクロVBAの高速化・速度対策の具体的手順と検証
こちらを参照してください。
最終的には、配列を使用することになります。

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

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

つまりは、
getRange
getValue
setValue

これらの回数を減らすという事です。

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



データは、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入門」の記事

第31回.入力規則
第32回.グラフ
第33回.表示の固定
第34回.シート保護
第36回.フォルダとファイルを扱う(DriveApp)
第37回.フォルダの一覧取得・作成・削除(Folder)
第38回.ファイルの一覧取得・削除(File)

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • スプレッドシートが非常に遅い、高速化するには

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


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

    ↑ PAGE TOP