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

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

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

処理速度というスクリプトの問題点

　このスクリプトですが、処理速度という点で、幾つか改善余地があります。まず、前提知識として、以下のように、スプレッドシートへのアクセスを伴う処理は、その実行速度が遅くなります。

• スプレッドシートを開く：SpreadsheetApp.open(file)
• シートを配列として取得する：Spreadsheetオブジェクト.getSheets()
• セル範囲を取得する：Sheetオブジェクト.getRange(a1Notation)
• セルを取得する：Sheetオブジェクト.getRange(row, column)
• シートのデータがある最終行番号を取得する：Sheetオブジェクト.getLastRow()
• シートの最終行にデータ行を追加する：Sheetオブジェクト.appendRow(配列)
• セルの値を取得する：Rangeオブジェクト.getValue()

　これらの処理の特徴を踏まえてスクリプトを見ると、for文の条件式にgetLastRow()メソッドがありますし、forループ内の処理にgetRange()メソッドやgetValue()メソッドが多数含まれています。

　つまり、これらのメソッドの使用回数を極力減らすことで、スクリプト全体の処理速度を向上できます。なお、実行速度が遅い処理はスプレッドシートだけでなく、ドライブなど他のサービスでも同様です。極力、各サービスへアクセスする処理数を減らせるようにスクリプトを組んでいく必要があるのです。

配列を活用した経費データ収集スクリプト

　前述の経費データ収集スクリプトについて、配列を活用したものが以下になります。

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);
  
}

　スクリプト「colectData()」の、処理の流れは以下のようになっています。

1. アクティブシート「経費収集」の取得
2. 収集したデータを格納するための配列mainValuesを用意
3. フォルダ「経費精算書」内のファイルをコレクションとして取得
4. コレクション内の全てのファイルについて繰り返し
   ┣対象となっているファイルを開き、その1番目のシートを取得
   ┣データ「対象月」「部署」「氏名」を配列経由で変数に格納
   ┣経費データを2次元配列として取得
   ┗配列内の全てのデータ行について繰り返し
   　┗配列内の経費データを配列mainValuesに追加
5. 配列mainValuesを「経費収集」シートに書き出す

　おおまかな流れは第8回と同様ですが、途中に配列関連の処理が加わっています。以下で、スクリプトのポイントとなる部分について解説します。