【スプレッドシート】スプレッドシートからJsonデータを作成する。

2018年9月16日2018年9月22日

流石にガバ記事３連続はやばいのでちゃんとしたの書きます。

スプレッドシートを使っててその表をJsonデータにしたいってこと、ありますよね？（trivago並発音）

ついこないだどうしてもJsonで書き出したかったのでまとめておきます。

1.初めに

お世話になった参考サイト

Google App Scriptを用いてGoogleスプレッドシートからJSONを生成してみよう

使うもの : GAS(GoogleAppScript),スプレッドシート

まずこんなシートがあるとします。

このシートを例えばオブジェクトでくるみ社員番号がキーの物、社員名をキーとした物、部署をキーにした物。有給をキーにした物。給与をキーにした物を入れたいとします。

つまり

[
  {
    社員番号 : 1,
    社員名 : 佐藤,
    部署 : 総務部,
    有給 : 10,
    給与 : 20万
  }
]

のような感じの物をどんどん作って行きたい。

となるとこれぐらいの量であれば正直手打ちのほうがもしかしたら早いかもしれませんが、これが100件、1000件となると時間の無駄ですよね。

そこでGASを使って表を配列で読み込み、forでぶん回してオブジェクトを作って行きたいと思います。

2.GASをつかってみよう

そもそも使ったことがない人もいるかも知れないので、とりあえず使ってみましょう。

スプレッドシートの　ツール　-> スクリプトエディタ　を押すとスクリプトを作成できる画面が出てきます。

こんなのが新規タブで出たかと思います。

ココにJavaScriptベースでドゥンドゥン書いていきます。

とりあえず初めにjsで言うアラートのような物を出して見ましょう。

そのためには Browser.msgBox('hogehoge’);を使います。

function myFunction(){
  Browser.msgBox('hogehoge');
}

このように記載後、 ctrl + s で保存するとプロジェクト名を入れてくださいとでますので、今回はjsonTestとしておいてください。これはあくまでもプロジェクトの名前なので、用途に合わせて名前は変えてわかりやすくしてくださいね。

しましたら、再生ボタンみたいなボタンを押してみてください。

最初はスクリプトの実行に承認が必要なので許可を確認を押してください。すると、ポップアップでGoogleアカウントへのログインが出ると思いますので、ログインしてくださいね。

ログインボタンを押したあと、このアプリは確認されていません。という警告ページが出るかと思いますが、こちらは詳細ボタンを押して、

jsonTest(安全ではないページ)に移動を押してください。

押すことでようやく許可ボタンがでます。多分スクリプトを動かすにあたって、危険なコードが含まれていたりすることも考えてこのような警告が出るのだと思います。アプリを確認してもらって安全であることを証明できるのかどうかは知らないですが、新しくスクリプトを作るときは毎回これが出る気がします。

はい、ということで許可ボタンを押したら開いてるスプレッドシートの画面を見てみましょう。

このようにアラートがでていれば成功です。多分でないってことはないと思いますが、もし出なければスクリプトがエラーを吐いているか、一旦Chromeを再起動することをおすすめします。

確認できましたら、OKボタンを押すことでアラートのような物は閉じます。

3.Jsonを吐き出す準備をしよう

Jsonを吐き出させるためには、シートの情報を元にデータを作成します。ですので、まず開いているシートの情報取得します。

そのためには SpreadsheetApp.getActibeSheet() という関数を呼び出して現在ブラウザで開いているシートを取得します。

function() myFunction(){

var sheet = SpreadsheetApp.getActiveSheet();

}

これで変数sheetに現在開いているシートが格納されました。

sheet変数に格納したスプレッドシートのデータは SpreadsheetClassなので、SpreadsheetClassの関数を使います。

このデータに対して getSheetValues()をすることでシートのデータが取得できます。

ちなみにリファレンスはこれ

リファレンスを見てもらえれば分かるかと思いますが、getSheetValues()は

開始行,開始列,何行取得するか,何列取得するか

というのを指定します。なので、最終行と最終列を毎回打つの面倒ですよね。そこで、 getLastRow()　と　getLastColumn()　を使います。名前の通りシートの最終行と最終列の値を返してくれます。

なのでこんなになります。

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;
  var startColumn = 1;
  var rowLength = sheet.getLastRow();
  var columnLength = sheet.getLastColumn();
  
  var data = sheet.getSheetValues(startRow, startColumn, rowLength, columnLength);
}

data変数に sheet.getSheetVlues(startRow,StartColum,rowLength,columnLength)することでシートのデータが入りました。

中身を確認するために

Browser.msgBox();

を使って確認してみましょう。

その際にdataをJSON.stringfyすることで２次元配列として変換してでてきます。

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;
  var startColumn = 1;
  var rowLength = sheet.getLastRow();
  var columnLength = sheet.getLastColumn();
  
  var data = sheet.getSheetValues(startRow, startColumn, rowLength, columnLength);
  
  Browser.msgBox(JSON.stringify(data));
}

では早速実行。

このように2次元配列で出力されましたね。これを使ってJSONとして吐き出せるものを作っていきます。

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;
  var startColumn = 1;
  var rowLength = sheet.getLastRow();
  var columnLength = sheet.getLastColumn();
  
  var keys = sheet.getSheetValues(startRow, startColumn, 1, columnLength)[0];
  var data = sheet.getSheetValues(startRow + 1, startColumn, rowLength - 1, columnLength);
  
  var jsonData = [];
  var listIndex = 0;
  var valueIndex = 0;
  for(listIndex in data){
    jsonData.push({});
    for(valueIndex in data[listIndex]){
      jsonData[listIndex][keys[valueIndex]] = data[listIndex][valueIndex];
    }
  }
  
  Browser.msgBox(JSON.stringify(jsonData));
}

んで、ガッツリ書いたのがこれ。

変数keysに一旦key部分を格納。

その後for in でdataを回し行ごとのリストを取得。更にソレをfor inで回し、リストごとの要素。（社員番号の値など）を取得。

それらを変数jsonDataに入れてあげる。

変数jsonData　は最初に var jsonData = [];で初期化しておき、

一度目の for in でjsonDataに対し空のオブジェクトを追加し、

二度目のfor in で使用するという形にしています。

これが最善かどうかはわかりませんがこんな感じになりました。早速実行してみましょう。