GoogleスプレッドシートのGoogle Apps Scriptでcsv, json, yawl出力

シート内のデータをcsv, json, yawlなどで出力

Googleスプレッドシート内のデータをcsv, json, yawl, md(Markdown)などで書き出したいことがある。

Googleスプレッドシートはファイル=>形式を指定してダウンロードを使用すればcsv, tsvファイルであればダウンロードできるが若干面倒だ。

しかも、json, jsonp, yawl形式には対応していない。

Google Apps Scriptで書き出す

Google Apps Scriptを使用すればcsv, tsv, json, jsonp, yawl, md(Markdown)形式で書き出すことが可能だ。

やり方はまずシート内に適当なデータを入力する。

https://docs.google.com/spreadsheets/d/1CaVjrqVbWz330LKMqRs07jnDJZo4tO98a0IGFVmWess/edit?usp=sharing

入力したらメニューのツールからスクリプトエディタを開いて下記のスクリプトを貼り付ける。

var TYPE = 'json'; // csv, tsv, json, jsonp, yawl, md(markdown) を指定
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getActiveSheet();

function doGet(e) {
  var result;
  var cco = function(s) { return ContentService.createTextOutput(s) };
  if (TYPE === 'csv') {
    result = cco(String(toXSV(sheet))).setMimeType(ContentService.MimeType.CSV);
  } else if (TYPE === 'tsv') {
    result = cco(String(toXSV(sheet)).replace(/,/g, '\t'));
  } else if (TYPE === 'md') {
    result = cco(String(toXSV(sheet)).replace(/,/g, ' | '));
  } else if (TYPE === 'json') {
    result = cco(JSON.stringify(toXSV(sheet))).setMimeType(ContentService.MimeType.JSON);
  } else if (TYPE === 'jsonp') {
    result = cco('callback(' + JSON.stringify(toXSV(sheet)) + ');');
  } else if (TYPE === 'yawl') {
    result = cco(JSON.stringify(toXSV(sheet))
      .replace(/,/g, '\n')
      .replace(/:/g, ': ')
      .replace(/{/g, '- ')
      .replace(/^"/gm, '  ')
      .replace(/[\[\]}"]/g, '')
    );
  } else {
    result = cco('ファイルタイプが指定されていません');
  }
  return result;
}

function toXSV(sheet) {
  var jIndex = (TYPE === 'json' || TYPE === 'jsonp' || TYPE === 'yawl') ? 1 : 0;
  var colIndex = 0 + jIndex;
  var rowNum = 0 + jIndex;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  var jsonArray = [];
  for(var rowIndex=1+jIndex; rowIndex<=lastRow; rowIndex++) {
    var colIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }
  if (jIndex) {
    for(var i=0; i<rowValues.length; i++) {
      var line = rowValues[i];
      var json = {};
      for(var j=0; j<titleColumns.length; j++) {
        json[titleColumns[j]] = line[j];
      }
      jsonArray.push(json);
    }
    return jsonArray;
  } else {
    if (TYPE === 'md') {
      var spr = [];
      titleColumns.forEach(function(x, k) {
        spr.push('------');
      });
      rowValues.splice(1, 0, spr.join(' | '));
      rowValues.forEach(function(x, l) {
        rowValues[l] = String(rowValues[l]).replace(/(.+)/g, '| $1 |');
      });
    }
    return rowValues.join('\n');
  }
}

書き出すファイル形式は1行目のvar TYPEで指定する。(今回はjsonを指定)

あとはスクリプトエディタのメニューの「公開」から「ウェブアプリケーションとして導入」を選択してウェブアプリケーションのURLを発行すれば、このURLにアクセスすることで指定した形式で表示される。