【GAS】データ取得高速化 Google Sheets API使用

Google Sheets APIを使用したら、処理速度が上がった。そのため、メモ書きとして皆さんと共有する。

以下は私の今までのやり方

/**
*　データの一括取得
* @param [string] ssid - スプレッドシートID
* @param [string] sheetN - シート名
* @return [[]] data - シートの使用範囲のすべてを2次元配列にて抽出
**/
function getSpreadData(ssid,sheetN){
  let sp = SpreadsheetApp.openById(ssid);
  let sh = sp.getSheetByName(sheetN);
  let data = sh.getDataRange().getValues();
  return data;
}

以下、Google Sheets APIを使用した場合、
※【サービスを追加】にて、Google Sheets API を「Sheets」というIDで登録してあるとする。

/**
*　データの一括取得
* @param [string] ssid - スプレッドシートID
* @param [string] sheetN - シート名
* @return [[]] data - シートの使用範囲のすべてを配列にて抽出。2次元だが、凸凹。
**/
function getSpreadData(ssid,sheetN){
  //ranges内はA1notion表記
  //sheetNとすることで、シート全範囲を指定
  let response = Sheets.Spreadsheets.Values.batchGet(
    ssid,
    {ranges:[
     sheetN
     ]
    }
  );
  let data = response.valueRanges[0].values;
  return data;
}

また、複数のシートのデータを同時に取得したい場合は、

/**
*　12カ月分のシートのデータを一括取得
* @param [string] ssid - スプレッドシートID
* @return [obj] 12カ月分のシートの中身
**/
function getSpreadData(ssid){
  //ranges内はA1notion表記
  let response = Sheets.Spreadsheets.Values.batchGet(
    ssid,
    {ranges:[
     "4月",
     "5月",
     "6月",
     "7月",
     "8月",
     "9月",
     "10月",
     "11月",
     "12月",
     "1月",
     "2月",
     "3月"
     ]
    }
  );
  let obj = {};
  obj["4月"] = response.valueRanges[0].values;
  obj["5月"] = response.valueRanges[1].values;
  obj["6月"] = response.valueRanges[2].values;
  obj["7月"] = response.valueRanges[3].values;
  obj["8月"] = response.valueRanges[4].values;
  obj["9月"] = response.valueRanges[5].values;
  obj["10月"] = response.valueRanges[6].values;
  obj["11月"] = response.valueRanges[7].values;
  obj["12月"] = response.valueRanges[8].values;
  obj["1月"] = response.valueRanges[9].values;
  obj["2月"] = response.valueRanges[10].values;
  obj["3月"] = response.valueRanges[11].values;
 
  return obj;
}

複数シートのデータを取得したい場合は、圧倒的に速い。
しかし、一見、便利なGoogle Sheets APIだが、データ量が多い場合は、どうやらエラーになるらしい。そのときは、大人しく、SpreadsheetAppを用いるのが良いみたい。

初期設定として３枚くらいスプレッドシートを開いて(openByIdを３回)データを取得する処理をGoogle Sheets APIに変えたら、起動時間が、6秒から3秒になった。
※WEBアプリ。

ちなみに、複数のシートを一括取得の場合の例として、１２カ月シートとしているが、私の環境のデータ量（１枚60行×30列=1800セルぐらい？）では、１枚（たとえば、４月のみ）の場合と差がほとんどなかった。

やり方をしらないだけかもしれないけど、欲を言えば、複数のスプレッドシートを同時に取得（ssidを複数指定可能）できたら最高だった。

皆さんも快適なGASライフを！！

※他の30回ぐらいopenByIdをする処理を置き変えたが処理速度は変わらなかった。高速化するのは時と場合によるらしい。

※12ヶ月シートのデータを取得するのを従来のやり方でやったら、約18秒。Google Sheets APIなら、約1秒だった。

※1枚で比較してみた。3回試行した結果
従来:約1.2秒
Google Sheets API:約0.7秒