Googleドライブのフォルダに格納したcsvファイルをGASで読み取りスプレッドシート->json化して出力する

はじめに

What is this

Googleドライブの特定の名称のフォルダ内に格納したcsvファイルを全て読み取り、
シートに出力するGoogle App Scriptと、
スプレッドシートを外部公開することで、URLのクエリパラメータのコピペのみでjsonファイルを公開する手順を示しました。

背景

pythonsスクリプトでcsvをスクレイピングしてjson出力する方法を示しましたが、
csvをサーバーに置く前の段階として、しばらくcsvデータをメールで受領する時期がありました。

• 今後の運用を踏まえるとjsonデータを読みとる方法は変えたくない
• 可能な限りjsonデータ化する手間を少なくしたい
  の2点を解決するため、下記のようなフローを考えました。
• メールで受領したcsvを特定のGoogleドライブフォルダにアップロードする
• 自動でスプレッドシートのデータが更新される
• googleスプレッドシートから出力したjsonファイルから情報取得する

本件ではその説明をしています。

ソースコード

how to use

csvファイルの読み込み

本コードをGoogle App Scriptにコピーし、CSVを格納するフォルダ名を任意の名称に変更してください。
※直下のフォルダでなくとも使用できますが、固有名詞としてください
csvを格納し、本スクリプトを実行するとcsvを読み取り、スプレッドシートにファイル名のシートを作成します。
(すでにシートが作成されている場合はclearします)

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ファイルを出力できる。
    

技術仕様

csv2json_gas.gs

function csv2json() {
  //書き込む対象のSpread Sheetを定義
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  
  // 対象のCSVファイルが置かれているフォルダ名、ファイル名を定義
  var folderName = "data";
  var folders = DriveApp.getFoldersByName(folderName);

 while (folders.hasNext()) {
    var folder = folders.next();
    if (folder.getName() == folderName) {
      var files = folder.getFiles();
      while (files.hasNext()) {
        var file = files.next();
        var sheetName = file.getName();
        sheetName = sheetName.substr(0,sheetName.length-4);
        
        var sh = ss.getSheetByName(sheetName);
        if(sh == null)
        {
          ss.insertSheet(sheetName);
          sh = ss.getSheetByName(sheetName);
        }
        else
        {
          sh.clear()
        }
        
        var data = file.getBlob().getDataAsString("Shift_JIS");
        var csv = Utilities.parseCsv(data);
        sh.getRange(1,1,csv.length,csv[0].length).setValues(csv);
      }
      return;
    }
  }
}

• フォルダ内の全データを対象にしている
• ファイル名からシート名を抽出、シートが無ければ新たに生成、あればシートをcleanする
• コードを簡略化するため、エラーケースは未考慮(csv以外のファイルを入れないこと)

参考

Google Apps ScriptでCSVファイルをGoogleスプレッドシートにインポートする
https://qiita.com/YusukeKameyama/items/5ae840ec8d4382a215db