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

いなば

2015.09.03

Web制作

こんにちは、フロントエンドエンジニア（Angularおにいさん）のいなばです。
最近は「レッドブルしか入ってない自販機が社内にあればいいのに……」と思いながら仕事をしています。

さて、案件でちょろっとGoogle App Script（GAS）を触る機会がありました。Google App ScriptとはGmailやスプレッドシートなど、Googleが提供しているサービスのAPIをJavascriptで利用するクラウド型スクリプトのこと。Google App Scriptを用いることで、Googleのサービスを自由に拡張できたり、他サービスと連携することができます。

Google App Scriptについて詳しく知りたい方は、こちらをまずご一読ください。

作業効率化！Googleアプリを1000%活かせる「Google Apps Script」の始め方

王

2013.09.25

今回はGoogle App Scriptを使って、スプレッドシートからJSONを生成する手順をご紹介いたします。

はじめに：スプレッドシートを用意しよう

まずはJSONを生成するための元データを用意しましょう。

今回はid、title、descriptionのカラムを持った「LIGの若手フロントエンドエンジニアの一覧」を用意してみました。Googleドライブの任意の場所に新しくスプレッドシートを作り、ポチポチ入力していきます。

3行くらいでいいですかね。
それでは、ここからGoogle App Scriptの出番です。

初級編：Google App Scriptを実際に書いてみよう

Step1. スクリプトを作成する

「ツール」から「スクリプトエディタ…」をクリックします。

クリックすると、別タブでエディタ画面が開きます。その際にダイアログがでてくると思いますが、こちらは閉じてしまって大丈夫です。
デフォルトでmyFunctionという空の関数が書かれています。

function myFunction() {

}

Step2. 関数を実行してみる

空のmyFunction関数の中に、下記の1行を追記してみましょう。

function myFunction() {
  Browser.msgBox('Bye Spreadsheet!');
}

command + s （Windowsの場合は多分 ctrl + s）でファイルを保存した後に「実行」タブから「myFunction」をクリックします。
初回は承認を求めるモーダルがでますが、そのまま「続行」をクリック、その後のモーダルでは「承認」をクリックしてください。

スプレッドシートのタブへ戻ってみると、ダイアログが表示されていますね。

ここで「window.alertじゃダメなの？」と思われた、あなた。

Google Apps ScriptはWebブラウザで実行されるものではないので、windowオブジェクトにアクセスすることができません。かわりに予め用意されているGoogle側のAPIを利用する形でスクリプトを書いていくことになります。

参考：Google Developersリファレンス

実践編：スプレッドシートの内容をGASから取得しJSONにする

スプレッドシートの内容を取得する方法

それでは、いよいよ実践編です。まずは、Google App Scriptでスプレッドシートシートの内容を取得してみましょう。
下記サンプルではスプレッドシートにアクセスをし、そこから指定した範囲を抜きだしてダイアログを表示させています。

function myFunction() {
  //今開いているシートを取得する
  var sheet = SpreadsheetApp.getActiveSheet();

  // シートの一部分を取得する
  var rowIndex = 2;
  var colStartIndex = 1;
  var data = sheet.getSheetValues(rowIndex, colStartIndex, sheet.getLastRow(), sheet.getLastColumn());

  Browser.msgBox(JSON.stringify(data));
}

出力結果はこのようになりました。
取得したデータは2次元配列となります。

[["id","title","description"],[1,"天然なHTML/CSS設計にいさん","CSS設計にこだわりを持っていて、Schooなどにも出演。天然なところがあり、チーム内でのいじられキャラ。今まさにこの時間にSchooに出ている。"],[2,"コツコツ頑張るコスプレ娘","技術力はまだまだだが、コツコツと作業を積み重ねていける性格。人見知りなのであまり表にはでないが、趣味が割と目立つ趣味。"],[3,"時間でカバーする努力家","足りないところは時間でカバーしてでもやり通す努力家。クリエイティブにこだわりが強い。勉強会では出落ち担当。"],["","",""]]

スプレッドシートから取得したデータを整形してJSONにする

ここまで分かれば、あとは取得したデータを整形するだけです。
JavaScriptが書ければどうにでもできます。

function myFunction() {
  //今開いているシートを取得する
  var sheet = SpreadsheetApp.getActiveSheet();

  // シートの一部分を取得する
  var rowIndex = 2;
  var colStartIndex = 1;
  var rowNum = 1;
  var keys = sheet.getSheetValues(rowIndex, colStartIndex, rowNum, sheet.getLastColumn())[0];
  var data = sheet.getRange(rowIndex + 1, colStartIndex, sheet.getLastRow(), sheet.getLastColumn()).getValues();

  var list = [];

  data.forEach(function(elm,index){
    var template = indexBy(keys);
    var member = generate(elm,template);
    if(member.id){
        list[index] = member;
    }
  });

  Browser.msgBox(JSON.stringify(list));

  function generate(elm,obj){
    var i = 0;
    for(var key in obj){
      obj[key] = elm[i];
      i++;
    }
    return obj;
  }

  function indexBy(ary){
    var obj = {};
    for(var i = 0, len = ary.length; i < len; i++){
      var key = ary[i];
      obj[key] = key;
    }
    return obj;
  }
}

出力結果はこうなりました。

[
    {
        "id": 1,
        "title": "天然なHTML/CSS設計にいさん",
        "description": "CSS設計にこだわりを持っていて、Schooなどにも出演。天然なところがあり、チーム内でのいじられキャラ。今まさにこの時間にSchooに出ている。"
    },
    {
        "id": 2,
        "title": "コツコツ頑張るコスプレ娘",
        "description": "技術力はまだまだだが、コツコツと作業を積み重ねていける性格。人見知りなのであまり表にはでないが、趣味が割と目立つ趣味。"
    },
    {
        "id": 3,
        "title": "時間でカバーする努力家",
        "description": "足りないところは時間でカバーしてでもやり通す努力家。クリエイティブにこだわりが強い。勉強会では出落ち担当。"
    }
]

スプレッドシートからJSONを生成できましたね。