GoogleスプレッドシートからAPIを呼び出して結果をシートに反映させる

目的と背景

デイリーで報告する必要があるGoogleスプレッドシートで作成されているレポートにシステムが計測をしているデータを貼り付ける必要があるが、それを毎日人力でやるのは現実的でないのでシートを開いた時に自動で最新のデータを取得できるようにしようと思った

検討した案

CSVでデータを取得するAPIが既にシステムに実装されていたので、そのAPIをシートが開いた時に呼び出して、取得結果をシートに保存すればよいのでは、と考えた

手順

1. 該当のスプレッドシートで拡張機能 > App Scriptをクリックし、App Scriptのエディタを開く
2. 関数を実装する
3. コードを保存すると実行ボタンが有効化するのでクリックし、実行してみる。意図したシートにデータが反映されていることを確認する
4. トリガーで該当の関数を選択して、スプレッドシート起動をトリガーとして設定する（設定する時に権限の確認がある場合あり）

実装内容

function updateSheet(){
  // CSVを取得する
  const api_url = 'APIのURL';
  const response = UrlFetchApp.fetch(api_url).getContentText();

  // CSVデータを変換
  const csv = Utilities.parseCSV(response);

  // 保存先のCSVを取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = '保存先のシート名';
  const sheet = spreadSheet.getSheetByName(sheetName);

  if(!sheet){
    // シートの内容を削除
    sheet.clear();    
  }
  else{
    // 該当シートが無い場合は作成する
    spreadSheet.insertSheet(sheetName);
  }

  // シートにデータを反映
  sheet.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}

結果

意図通りにシートを開いた時にデータが自動で更新されるようになった

注意点

数式を扱う場合

CSVのデータに加えて、そのデータを数式で加工した列がある場合、数式の中に範囲指定があると計算に時間がかかり、タイムアウトすることがあった。

例えば、

ArrayFormula(if(C2:C="","",datevalue(substitute(left(C2:C,10),"-","/"))))

これはC列にある日時データから年月日だけを取り出して、-を/にしてYYYY/MM/DD形式に変換する式だが、C2:Cのような範囲の指定が入っていると処理に時間がかかり、タイムアウトしてしまった。

この問題はおそらくCSVのデータが挿入されるたびに範囲内のデータの再計算が実行されてデータ量に応じて処理速度が低下するのはないかと思う。

これに関しては、下記のように範囲指定をやめることで解決した

ArrayFormula(if(C2="","",datevalue(substitute(left(C2,10),"-","/"))))

C2の部分は行によって変更する必要はあるが、単にコピペしていっても行数がちゃんと変更されるので、案外楽だった。

ファイルを複製した場合の注意

ファイルを複製するとトリガーが外れるので、再設定する必要があった