Zenn
🏪

オープンなデータをダウンロードし、定期にGoogle Sheetに反映

2021/09/13に公開
Google Apps Script
Google Sheets
tech

はじめに

CSVデータをオープンデータからダウンロードし、Google Sheetに1日数回反映させるものです。
コメントが適当になっているところは、おいおい修正しながら。

シートの構成

設定を記述するシートを用意

  1. ダウンロードするCSVファイルのURLパス
  2. CSVファイルをインポートするタブ名

上記は最初に記載し、後ほどコードからファイルのIDを追記します。

実装

構成

  1. シートに記載したURLをダウンロード、保存したファイルのIDをシートに書き込む（手動）
  2. 自動でファイルをダウンロードしてシートに上書きする（自動 - 日時のTriggerへ設定）
  3. ダウンロードしたファイルは日時で更新（バックアップはしない）

コード

//https://www.mhlw.go.jp/stf/covid-19/open-data.html

const URL_FOLDER_COVID19 ="[フォルダのID]"
const FOLDER_COVID19 = DriveApp.getFolderById(URL_FOLDER_COVID19); //フォルダIDを指定

/**
 * CSVをダウンロードしてテキストを返す
 *
 * @param {String} url ダウンロードサイトのURL
 * @return {String} ContentText
 * @customfunction
 */
function downloadCSV(url) {
  let response = UrlFetchApp.fetch(url);
  return response.getContentText("UTF-8");
}

/**
 * スプレッドシートを読み込む - 手動
 *
 * @param {Sheet} sheet 読み込むシート
 * @param {String} range シートの範囲
 * @return {Object} データの配列
 * @customfunction
 */
function readSheet_manual(sheet, range){
  let datalist = {}
  let sheetdata = sheet.getRange(range).getValues()
  for(let row of sheetdata){
    //空行は捨てる
    if (row != null && row[0]!=""){
          datalist[row[0]] = row[1]
    }
  }
  return datalist
  // Logger.log(datalist)
}

/**
 * スプレッドシートを読み込む - 自動
 *
 * @param {Sheet} sheet 読み込むシート
 * @param {String} range シートの範囲
 * @return {Array} データの配列
 * @customfunction
 */
function readSheet_auto(sheet, range){
  let datalist = []
  let sheetdata = sheet.getRange(range).getValues()
  for(let row of sheetdata){
    //空行は捨てる
    if (row[0]!=""){
        datalist.push(row)
        // datalist[row[2]] = row[1]
    }
  }
  return datalist
}

function readSheet(sheet, range){
  let datalist = []
  let sheetdata = sheet.getRange(range).getValues()
  for(let row of sheetdata){
    //日付がない空行は捨てる
    if (row[0]!=""){
        datalist.push(row)
    }
  }
  return datalist
}

function main(){
  let num_row = 2
  let sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("データ")
  //B:シートのタグ名、C:URL、D:ファイルID(記入がない場合もある)
  let datalist = readSheet(sheet,`B${num_row}:D`)
  for(let d of datalist){
    let fileid = downloadData(d[0],d[1],d[2])
    importCSVFromGoogleDrive(fileid,d[0])
    if (d[2] == ""){
      sheet.getRange(`D${num_row}`).setValue(fileid)
      num_row++
    }
  }
}

/**
 * スプレッドシートを読み込む - 自動
 *
 * @param {String} url URL
 * @param {String} name 名前
 * @return {String} ID
 * @customfunction
 */
function downloadCSV_manual(url,name) {
  let content = downloadCSV(url)
  // Googleドライブへアップロード
  let file = FOLDER_COVID19.createFile(`_${name}`, content, MimeType.CSV);
  return file.getId()
}

function downloadData(name,url,id) {
  let response = UrlFetchApp.fetch(url);
  let content =  response.getContentText("UTF-8");
  // Googleドライブへアップロード
  let file 
  if (id != "" ){
    //フォルダにあるファイルにデータを上書き
    file = DriveApp.getFileById(id)
  } else {
    //フォルダに新規でファイルを作成
    file = FOLDER_COVID19.createFile(`_${name}`, content, MimeType.CSV);
  }
  file.setContent(content)
  return file.getId()
}

function manual(){
  let num_row = 2
  let sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("データ")
  let datalist = readSheet_manual(sheet,`B${num_row}:C`)

  for(let d of Object.keys(datalist)){
    // Logger.log(d)
    // Logger.log(datalist[d])
    let fileid = downloadCSV_manual(datalist[d],d)
    sheet.getRange(`D${num_row}`).setValue(fileid)
    num_row++
  }
}

/**
 * CSVをダウンロードする - 自動
 *
 * @param {String} url URL
 * @param {String} id ID
 * @return {String} ID
 * @customfunction
 */
function downloadCSV_auto(url,id) {
  let content = downloadCSV(url)
  // Googleドライブへアップロード
  let file = DriveApp.getFileById(id)
  file.setContent(content)
  return file.getId()
}

/**
 * CSVをダウンロードする - 自動
 *
 * @param {String} fileid ファイルID
 * @param {String} sheetName シートの名前
 * @customfunction
 */
function importCSVFromGoogleDrive(fileid,sheetName) {
  let file = DriveApp.getFileById(fileid)
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
  //シートがなければ追加する
  if (!sheet){
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName)
  }
  // Logger.log(`${csvData.length}:${csvData[0].length}`)
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

function auto(){
  let num_row = 2
  let sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("データ")
  let datalist = readSheet_auto(sheet,`B${num_row}:D`)
  for(let d of datalist){
    let fileid = downloadCSV_auto(d[1],d[2])
    importCSVFromGoogleDrive(fileid,d[0])
  }
}

さいごに

コロナのPCR検査数などオープンにされていて、日々変動するデータを取得するためにつかっています。
コメントのつけ方はここらへんで
https://developers.google.com/apps-script/guides/sheets/functions#optimization

Discussion