公開されたcsvファイルをGASで読み取りスプレッドシートに転記・jsonで公開する

はじめに

What is This

外部で公開されたcsvファイルを読み取り、シートに出力するGoogle App Scriptです。

開発の経緯

• 公開されたcsvをGithub ActionでスクレイピングしてGithub Pagesで公開する
• Googleドライブのフォルダに格納したcsvファイルをGASで読み取りスプレッドシート->json化して出力する

の2つを公開したところ、「CSVをGASで読み取ってGoogleスプレッドシートに転記・JSON出力したほうが良いのでは」とアドバイスをいただいたので、作成しました。技術的には上記2記事の合わせ技です。

Product

Github

参考データ

岐阜県オープンデータ
https://data.gifu-opendata.pref.gifu.lg.jp/dataset/c11223-001

how to use

csvファイルの読み込み

• 本コードをGoogle App Scriptにコピーし、読み取りたい任意のCSVファイルのURLに変更してください
• csvを格納し、本スクリプトを実行するとcsvを読み取り、スプレッドシートに指定した名称のシートが作成されます

(備考)APIとしてスプレッドシートを出力する方法

• Googleスプレッドシートのメニューで「ファイル->ウェブに公開」で「公開」を選択する
• https://docs.google.com/spreadsheets/d/xxxxx/edit#gid=0
  • スプレッドシートの「xxxx」部分をコピー
• https://spreadsheets.google.com/feeds/list/YYYY/ZZ/public/values?alt=json
  • YYYY:先程コピーした「xxxx」の部分をペースト
  • ZZ: シートを左から数えた順番。左から2番目なら「2」
  • 置き換えた値で実行するとjsonファイルを出力できる。
• 詳細はGoogleドライブのフォルダに格納したcsvファイルをGASで読み取りスプレッドシート->json化して出力するにて、写真付きで公開しています

前回2記事と何が違うか

今回の開発では下記の2点が求められていました。

• ①自動取得できること
• ②万が一データの更新が行われないとき、スクレイピングを停止し、Googleスプレッドシートでも手動で修正が行えること。(原則は更新しない)

「公開されたcsvをGithub ActionでスクレイピングしてGithub Pagesで公開する」では①はできるが②ができず、「 Googleドライブのフォルダに格納したcsvファイルをGASで読み取りスプレッドシート->json化して出力する」では逆に①はできないが②はできました。
①も②も達成するためには、csvを取得し、最終的にGoogleスプレッドシートに出力できる必要があったため、開発しました。

技術仕様

csv2Gsheet.gs

var ss = SpreadsheetApp.getActiveSpreadsheet();
var REMOTE_SOURCES = [
  {
    'url': 'https://opendata.com/sample1.csv',
    'sheetname': 'sample1',
  },
  {
    'url': 'https://opendata.com/sample2.csv',
    'sheetname': 'sample2',
  },
  {
    'url': 'https://opendata.com/sample3.csv',
    'sheetname': 'sample3'
  },
];
var REMOTE_SOURCES_OPTIONS = {
  method : "get"
};

function CSV2Gsheet(){
  for(var i =0; i<REMOTE_SOURCES.length; i++){    
    response = UrlFetchApp.fetch(REMOTE_SOURCES[i].url, REMOTE_SOURCES_OPTIONS);
    var sheetName = REMOTE_SOURCES[i].sheetname;
    var sh = ss.getSheetByName(sheetName);
    if(sh == null)
    {
      ss.insertSheet(sheetName);
      sh = ss.getSheetByName(sheetName);
    } 
    var data = response.getContentText("Shift_JIS"); 
    var csv = Utilities.parseCsv(data);
    sh.getRange(1,1,csv.length,csv[0].length).setValues(csv);
  }
}

• githubには実際に参照しているデータを掲載しています
• 参照するデータのURLと、転記するGoogleスプレッドシートのシート名を明記する
• シートが無ければ新たにシートを作成。シートがあれば上書きする。(clearしない。万が一の場合は手動で行追加も念頭に置くため)