【営業向け】マクロの実行速度を100倍ぐらい早くできたので忘備録

やりたいこと＆その理由

以前、以下の記事で、SF用の取り込み用フォーマットに整形するスクリプトを作成したが、とにかく処理が遅く、処理中にネットフリックスを見て暇つぶししていたぐらいなので、このプログラムを高速にできないか、っと思っていた。

【営業向け】スプレッドシートに記録した行動管理シートをSFの取り込み用フォーマットに整形する

↑これを↓こんな感じに整形するマクロなのだが、色々模索した結果100倍ぐらい早くできました。

実装方法

１：　対象のスプレッドシートを取得
２：　取得したいシートの名前を指定して取得
３：　2で取得したシートにある情報をまとめて取得
４：　3でまとめて取得した情報を繰り返し処理を利用して配列を生成
５：　setValuesでまとめて出力

完成系

ということで、完成形は以下。

gas.js

function SalesforceImportMacro(){
  const SpreadSheet    = SpreadsheetApp.openById("スプレッドシートID");
  var caldListSheet    = SpreadSheet.getSheetByName("コールドコールリスト");
  var caldListForMacro = SpreadSheet.getSheetByName("出力先シート");
  var ownerId          = "SFオーナーId";
  var lastRow          = caldListSheet.getLastRow();

  var data             = caldListSheet.getDataRange().getValues();
  var arrForMacro      = [];

  for(i = 1; i <= lastRow - 1; i++){
    var columnNum  = 0;
    for(num = 1; num <= 7; num++){
      var check = data[i][9 + columnNum];
      if(check == ""){
        break;
      }else{
        arrForMacro.push([data[i][0],data[i][12 + columnNum],data[i][13 + columnNum],data[i][9 + columnNum],data[i][15 + columnNum],ownerId]);
        columnNum += 8;
      }
    }
  }


  caldListForMacro.getRange(2,1,tenkisaki.length,6).setValues(arrForMacro);

}

改善点

アルゴリズムの改善も行い、メソッドを呼ぶ量をできるだけ少なくしたが、一番はsetValues()がとにかく優秀すぎるw
今まではappendRow()をfor分の中で何回も呼ぶことで最終行にデータを挿入していたが、setValues()の引数に、配列を渡してあげるとまとめてデータをセットしてくれるので、速度は100倍以上速く、しかもコードの量を短くすることができた。

  var arrForMacro      = [];

  for(i = 1; i <= lastRow - 1; i++){
    var columnNum  = 0;
    for(num = 1; num <= 7; num++){
      var check = data[i][9 + columnNum];
      if(check == ""){
        break;
      }else{
        arrForMacro.push([data[i][0],data[i][12 + columnNum],data[i][13 + columnNum],data[i][9 + columnNum],data[i][15 + columnNum],ownerId]);
        columnNum += 8;
      }
    }
  }


  caldListForMacro.getRange(2,1,tenkisaki.length,6).setValues(arrForMacro);

プログラムはただ動けばいいと思っていたが、アルゴリズムを改善して、必要最低限のコード量にするとこんなに早くなるのかと実感。
データ構造とアルゴリズムをまじめに学びたいな。