[Google Apps Script]GASでスプレッドシートの内容をXMLで取得する

ものすごい久しぶりに風邪引きました。3年振りくらいだろうか。熱とかはなくて、基本的には鼻水と咳と喉の痛みだけですが、ここまで久しぶりだと新鮮です。

発端

最近、プロジェクトのユニットテスト環境を改善しようと色々やっていたのですが、そろそろS2JUnit4から脱却する必要を感じてきたため、DBUnit + JUnitのRuleアノテーションを使った環境を作ってみました。

DbUnitは名前だけなら前から知っていたのですが、実際に使ったことはなかったため、色々と調べることになりましたが、一番問題になったのは基本的にXMLを読み込む、ということ。
プロジェクトとしては、Gitを使うようになったのと、データをどのように編集したかがわからない、ということもあったので、テキスト形式なのはありがたいのですが、いかんせんXMLは編集しづらいのです。

こういうときのためのVBAだとは思いますが、正直あれは書きたくないです。

ということで、消去法として、Google Apps Scriptでスプレッドシートの内容をXMLとして出力したらいいんでね？という短絡的な思考に落ち着きました。

まずは調べる

Google Apps Scriptは、Google Appsの色々な機能を統合的に利用できるライブラリを利用できる、JavaScriptの実行環境のようです。まぁつまりはただのJavaScriptなので、こっちの方がVBAよりよっぽどやりやすいですね。Web系ではJavaScriptが必須なので、いじるにしても抵抗がありません。

ただ、個人的には生のJavaScriptをいまさらガリガリと書く気にはあまりなれない(やるしかなけりゃやりますが)ので、外部ライブラリとしてお気に入りのUnderscore.jsを追加します。
追加する手順については以下のページを参考にしました。

http://qiita.com/soundTricker/items/5a7e050a2a20f3e3938a

あとはこれで、XMLファイルとしてダウンロードさせることができればとりあえずはOKです。

XMLの作成はXmlServiceを、コンテンツとしてダウンロードさせたければContentServiceを使えばよさそうです。
ここまでわかればあとはリファレンスを読み読みしながらできそうなので作ってみました。

作ってみた

// define underscore.js.
var _ = Underscore.load();

var Apps = {
  convertRowToDataRow : function (tableName, column, rowValue) {
    if (!column || column.length === 0) {
      return null;
    }
    
    if (!rowValue || rowValue.length === 0) {
      return null;
    }
    
    var item = XmlService.createElement(tableName);
    _(column.length).times(function (index) {
      if (!_.isUndefined(column[index]) && !_.isUndefined(rowValue[index])) {
        
        var value = rowValue[index];
        if (rowValue[index] instanceof Date) {
          var moment = Moment.moment(rowValue[index]);
          value = moment.format('YYYY-MM-DD HH:mm:ss');
        }
        
        item.setAttribute(column[index], value);
      }
    });
    Logger.log('created ' + tableName + ' table.');
    return item;
  },
  
  getFilenameByPrompt : function () {
    var ui = SpreadsheetApp.getUi();
    var response = ui.prompt('ダウンロードするファイル名を入力:');

    // Process the user's response.
    if (response.getSelectedButton() == ui.Button.OK) {
      return response.getResponseText();
    } else {
      return '';
    }
  }
  
}

function exportSheetAsTable(sheet, root) {
  
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
  if (numRows < 2) {
    return;
  }
  
  var column = values.shift();
  var numColumns = column.length;
  
  _.each(values, function (row) {
    var child = Apps.convertRowToDataRow(sheet.getName(), column, row);
    if (child) {
      root.addContent(child);
    }
  });
}

function exportSheetsAsXML() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  var root = XmlService.createElement('datasets');
   
  _.each(sheets, function (sheet) {
    exportSheetAsTable(sheet, root);
  });
  
  var document = XmlService.createDocument(root);
  var xml = XmlService.getPrettyFormat().format(document);
  return xml;
}

function createExportedXml() {
  var xml = exportSheetsAsXML();
  Logger.log(xml);
  
  var filename = Apps.getFilenameByPrompt() + '.xml';
  
  var file = DriveApp.createFile(filename, xml);
  
  // Display a modal dialog box with custom HtmlService content.
  var template = HtmlService.createTemplateFromFile('dialog.html');
  template.file = file;
  
  var htmlOutput = template.evaluate()
     .setWidth(400)
     .setHeight(150);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'XMLダウンロード');
  
  file.setTrashed(true);
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "XMLとしてエクスポート",
    functionName : "createExportedXml"
  }];
  sheet.addMenu("変換", entries);
};

実際にやっていること自体は単純と言えば単純で、

  • 各シートをテーブルとみなして、各行をdatasetsの一行として利用
  • 全部を1ファイルにまとめて、一旦Google Driveにファイルを作成する。
    • 作成したファイルはすぐにゴミ箱送りにしておく
  • ダウンロードリンクをはったダイアログを出す

単純にダウンロードさせることが思ったよりも難しかったため、Driveの機能を使いました。こっちの方がシンプルのような気がします。

まだいくつかやっといた方がいいと思われるものはあります。

  • DbUnitの場合、DateとTimestampでフォーマットが異なるので、これをなんとかわかりやすい状態で記述できるようにしたい
    • マークかなんか付けられればいいんやけど・・・。
  • flat datasets形式のXMLをアップロードして、それを展開できるようにしたい
    • いいXML編集環境があればいいんですが
感想

実際に今のプロジェクトでもいくつか使っているものはあるけど、これくらいちゃんと作ろうとしたときでも、結構必要なライブラリが標準で提供されているので、作りやすかったです。

やっぱりUnderscoreとかその他のライブラリをそのまま使えるというのもなかなかよさげです。諸事情から、あまり積極的には使えませんが、ちょっとしたマクロを書くにしても、こっちの方が楽ですね。

こんなことをしているよりかは、プロジェクトの方でDbUnitとかをもっとちゃんと使えるようにした方がいいですね。そうですね。