[Tool] Google SpreadSheetでマクロを使って作業を効率化

こんにちは、@yoheiMuneです。
Googleのスプレッドシート（Excelみたいなやつ）を使ってますか？ そして、スプレッドシートにマクロ機能が付いていることは知ってますか？？ 私最近知ったのですが、とっても便利な機能で使っているので、今回はブログで紹介したいと思いました。

Google SpreadSheetのマクロ機能とは

SpreadSheetは、Microsoft Excelみたいなやつで、表とかを図形とか簡単に描画できるオンラインツールです。
個人的にはExcelより気に入っていて、複数人での共有がすごく楽だったり、異なるPCでも同じ内容にアクセスできたりと便利なのでございます。

そして今回はそのSpreadSheetのマクロ機能（=Script）の紹介です。
SpreadSheetのメニューバーで、「Tools -> Script manager」と開くとScriptが選択して実行できるのです。便利です！
JavaScriptで記述できるので、フロントエンドディベロッパとか使いやすいと思います。

Google SpreadSheetのScriptを定義する

それではさっそくScriptを定義してみたいと思います。 今回は、以下のような表形式のデータからJSON形式のテストデータを生成するスクリプトを作成します。


スクリプトを新規作成するには、メニューバーから「Tools -> Script editor」を選択すると、

以下のようなスクリプト編集画面が表示されます。



上記のスクリプト編集画面でfunctionを定義すると、スクリプトとして利用することが出来るようになります。

続いて、テーブル情報からJSONを生成する関数を定義します。 今回は、popToJsonという関数名で関数を作成しました。

function popToJson() {

  // 定数
  var HEADER_COLUMN_START_INDEX = 1;
  var HEADER_ROW_INDEX = 2;
  var DATA_ROW_START_INDEX = 3;
  
  // Bookやシートを取得する
  var sheet = SpreadsheetApp.getActiveSheet();
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  
  
  // ヘッダ情報を取得する
  var headers = [],
      headerColumnPos = HEADER_COLUMN_START_INDEX;
  while (true) {
    // sheet.getRangeでRangeオブジェクトを取得できます。
    var val = sheet.getRange(HEADER_ROW_INDEX, headerColumnPos++).getValue();
    if (!val) {break;}
    headers.push(val);
  }
  // ログ出力はこんな感じ。
  // ScriptエディタのメニューバーのView -> Logsで確認できます。 
  Logger.log(headers);
  
    
  // 1行ずつ読み込みます。
  var rowIndex = DATA_ROW_START_INDEX;
  var rowData = [];
  while (true) {
    
    // if no data, exit loop.
    var checkVal = sheet.getRange(rowIndex, 1).getValue();
    if (!checkVal) {
      break;
    }
    
    // cerate data row by row
    var columnPos = HEADER_COLUMN_START_INDEX;
    var valueObject = {};
    for (var i = 0, len = headers.length; i < len; i++) {
      var key   = headers[i];
      var value = sheet.getRange(rowIndex, columnPos++).getValue();
      valueObject[key] = value;
    }
    rowData.push(valueObject);
    
    // next row.
    rowIndex++;
  }
  
  // Logger.log(rowData);
    
  
  // JSObject to JSON
  var json = JSON.stringify({
    status_code: 200,
    status_message: 'success',
    datas: rowData,
  });
  Logger.log(json);
    
  // メッセージボックスにJSONを表示して終了します
  Browser.msgBox(json);
}

コードの解説が雑で申し訳無いのですが、JSとVBAを組み合わせたような感じで利用することが出来ます。
定義済みの変数やクラスについては、後述の参照先をご覧ください。

Scriptを利用してみる

では作成したスクリプトを早速利用します。 利用するのは簡単で、スプレッドシートに戻って、メニューバーで「Tools -> Script manager」を選択すると、
以下のような画面が表示されます。

上記画面では、先ほど定義したpopToJsonという関数が表示されており、それを選択して、実行することが出来ます。
実行してしばらく待つと、以下のようなダイアログが表示され、メッセージ部分にJSONが表示されます。

ほほほ、全部入りきってないw。ちょっと使い勝手はだめですが、こんな感じでスクリプトを実行することが出来ます。

SpreadSheetのスクリプトに興味を持ったあなた！

以下にリファレンスサイトを記載します。日本語サイトもあったり、Google本家のチュートリアルサイトもあったりと、学びやすい環境が整っていていい感じです。

Google Apps ScriptでGoogleドキュメントのスプレッドシートを操作する@CodeZine

http://codezine.jp/article/detail/5036
日本語で詳し目に解説している記事。日本語で理解できるので良いです！でも記事は2010年のもので少し古い。API変わってるかも！

Google本家のチュートリアルサイト（英語）

https://developers.google.com/apps-script/
5-Minites QuickStartやIntermediate Tutorialなど、記事が豊富です。最新情報はここから。

Google SpreadSheet Service Class Reference

https://developers.google.com/apps-script/reference/spreadsheet/
脱チュートリアルをするならやっぱりクラスリファレンス。SpreadSheet、Sheet、Rangeなどの詳細が記載されています。

最後に

今回紹介した機能は、Google App Scriptsのごく限られた機能です。 SpreadSheetのScriptを紹介しましたが、Gmailなど他のサービスも利用できますし、グラフ描画とかも出来るようです。
今後他の機能も使う機会がありましたら、ぜひ取り組んでみたいと思う機能でした。

最後までご覧頂きましてありがとうございました。