6分の壁をどう乗り越える？――「配列」を使ったGASのデータ集約スクリプトとは：働く価値を上げる“VBA／GAS”術（10）（3/3 ページ）

GASは「1実行当たり6分」までという制限がある。処理するデータが増えると途中でスクリプトが止まってしまう可能性がある。今回は、過去に作成した「バラバラのデータを1箇所に集約する」スクリプトの処理を高速化する秘訣を紹介する。

[高橋宣成，プランノーツ]

なぜ配列を使うと処理が速くなるのか

　まず、なぜスプレッドシートの処理で配列を使うと、スクリプトの処理速度が向上するのか明らかにします。

　GASのSpreadsheetサービスでは、シート上のセル範囲の値をまとめて2次元配列として取得する「getValues()メソッド」と2次元配列をシート上のセル範囲にまとめて書き出す「setValues()メソッド」が用意されています。

Rangeオブジェクト.getValues()

Rangeオブジェクト.setValues(配列)

　修正前のスクリプトでは、セルの値の取得を、セル一つ一つに対して実行していました。つまり、セルの数の分だけ、スプレッドシートへアクセスしていたことになります。

　一方で、setValues()メソッドを使って、セル範囲をまとめて読み書きすることで、読み書きの回数はそれぞれ1回だけで済みます。セル範囲の大きさに依存してその回数が増えることはありません。

　これが配列を使うことでスプレッドシートの処理速度を向上させるカラクリです。なお、今回のスクリプトでは、以下の変数名で配列を使用しています。

• mainValues：経費データを集め、最終的に「経費収集」シートに書き出すための配列
• basicValues：経費精算書から基本データ（対象月、部署、氏名）を取得するための配列
• values：経費精算書から経費データを取得するための配列

セル範囲を取得する

　データを配列で取得するステートメントについて見ていきます。まず、配列basicValuesを取得しているのが、以下のステートメントです。

var basicValues = sheet.getRange('G1:G4').getValues(); 

　セル範囲を取得する場合は、「'G1:G4'」のようにセル範囲のアドレスをgetRange()メソッドの引数に指定できます。この場合、取得できる配列は、以下のような2次元配列になります。

[
	[Mon Jan 01 17:00:00 GMT+09:00 2018], 
	[Fri Feb 02 17:00:00 GMT+09:00 2018], 
	[総務部], 
	[経費太郎]
]

　getRange()メソッドではセル範囲を取得する別の方法があります。行番号、列番号、そして行数、列数といった整数の組み合わせでセル範囲を指定する方法です。その場合の書式は以下の通りです。

Sheetオブジェクト.getRange(行番号, 列番号 [,行数] [,列数])

　これにより、行番号と列番号で指定したセル位置を起点として、そこから行数分、列数分に広げたセル範囲を取得できます。なお、行数、列数は省略可能です。この方式で、セル範囲の値を配列valuesに取得しているのが、以下のステートメントです。

var values = sheet.getRange(startRow, startColumn, numRows, lastColumn).getValues();

　これにより取得した2次元配列は、例えば以下のようなものです。

[
	[Wed Jan 10 17:00:00 GMT+09:00 2018, 備品・消耗品費, , ABC株式会社, コピー用紙, 2592.0, ], 
	[Fri Jan 12 17:00:00 GMT+09:00 2018, 旅費交通費, 株式会社ディーイー, 東京メトロ, 池袋⇔飯田橋, 400.0, ], 
	[Tue Jan 16 17:00:00 GMT+09:00 2018, 通信費, , 日本郵政株式会社, 定型外郵便送料, 280.0, ]
]

　1行分のデータの集まりが1次元配列で構成されていて、それを複数行分まとめた2次元配列になっていることを確認しておきます。

配列にデータを追加する

　続いて、forループ内の以下のステートメントについて解説しておきましょう。

mainValues.push([
	month, //対象月
	values[i][0], //日付
	department, //部署
	fullname, //氏名
	values[i][1], //科目
	values[i][4], //適用
	values[i][5], //金額
	values[i][6] //備考
]);

　ここで使用しているpush()メソッドは配列、つまりArrayオブジェクトの最後尾に要素を追加するメソッドです。書式は以下の通りです。

Arrayオブジェクト.push(要素)

　mainValuesは2次元配列ですから、その要素は1次元配列です。その1次元配列は、シート上でいうと1行分のデータの集まりになります。今回の例でも、1列目から順番に列挙した配列がpush()メソッドの引数になっています。

　なお、このpush()メソッドは、第8回で紹介したappend()メソッドの配列版と捉えることもできます。役割が類似しているので、合わせて確認しておくといいでしょう。

2次元配列から行数と列数を求める

　もう一つ、最後に生成した2次元配列をシートに書き出すステートメントを見ます。

sheetMain.getRange(2, 1, mainValues.length, mainValues[0].length).setValues(mainValues);

　getRange()メソッドの引数に、lengthというプロパティが使用されています。これは、配列つまりArrayオブジェクトの要素数を表すプロパティです。

Arrayオブジェクト.length

　mainValuesは2次元配列で、1行分のデータを表す配列の集合のため、その要素数は「行数」を表します。また、mainValues[0]は、2次元配列の1番目の要素、つまり1行目のデータの集合なので、その要素数は1行目の値の数、すなわち列数になるのです。

　このようなlengthプロパティの使い方は、setValues()メソッド以外にも、for文などの条件式などで活用できるので、ぜひ使いこなせるようになりましょう。

スクリプト全体を確認

　解説は以上です。再度、関数「collectData()」の内容を確認しましょう。

function collectData() { 
  var sheetMain = SpreadsheetApp.getActiveSheet();
  var mainValues = [];
  var files = DriveApp.getFolderById('-----フォルダID-----').getFiles();
  while (files.hasNext()) {
    var file = files.next();
    var sheet = SpreadsheetApp.open(file).getSheets()[0];
    var basicValues = sheet.getRange('G1:G4').getValues();    
    var month = basicValues[0][0]; //対象月
    var department = basicValues[2][0]; //部署
    var fullname = basicValues[3][0]; //氏名
       
    var startRow = 8, startColumn = 1;
    var lastRow = sheet.getLastRow();
    var lastColumn = sheet.getLastColumn();   
    var numRows = lastRow - startRow + 1;
    var values = sheet.getRange(startRow, startColumn, numRows, lastColumn).getValues();
    for (var i = 0; i < numRows; i++){
      mainValues.push([
        month, //対象月
        values[i][0], //日付
        department, //部署
        fullname, //氏名
        values[i][1], //科目
        values[i][4], //適用
        values[i][5], //金額
        values[i][6] //備考
      ]);
    }
  }
  sheetMain.getRange(2, 1, mainValues.length, mainValues[0].length).setValues(mainValues);
  
}

次回は

　今回は、Google Apps Scriptの経費精算データを収集するスクリプトについて、その速度を向上させるテクニックを紹介しました。配列はイメージしづらかったり、扱いづらかったりするかもしれません。しかしGASでは活用が機会多いため、使いこなすメリットは非常に大きいので、ぜひ覚えましょう。

　次回は、Excel VBAで日報メールを自動作成するマクロを紹介します。

著者プロフィール

高橋宣成

プランノーツ 代表取締役

「ITを活用して日本の『働く』の価値を高める」をテーマに、ExcelやVBA、G Suite、Google Apps Script、クラウドなどによる企業または個人事業主向けのシステムおよびツールの開発やコンサルティング、セミナー講師などを務める。「IT×働き方」をテーマに運営するブログ「いつも隣にITのお仕事」は月間60万PV達成。

書籍紹介

ExcelVBAを実務で使い倒す技術

高橋宣成著 秀和システム 1800円（税別）

動くコードが書けたその先、つまり「ExcelVBAを実務で使う」という目的に特化した実践書。ExcelVBAを楽に効果的に使いこなし続けるための知恵と知識、そしてそのためのビジョンと踏み出す勇気を提供する1冊。

詳解！ Google Apps Script完全入門 〜Google Apps & G Suiteの最新プログラミングガイド〜

高橋宣成著 秀和システム 2600円（税別）

Google Apps Scriptの完全入門書として、JavaScriptの基本から自作ライブラリまでを徹底解説。これ一冊だけで基礎から実践まで体系的にマスターできます。