GASでJSONファイルを出力してみた

やりたいこと

アプリを作るときにデータベースが必要になった時、データテーブルを書くのにExcelを使用したくなかった。
理由は下記。

• Excelがないとみれない（当たり前）
• 編集する環境がころころ変わるといちいちExcelをダウンロードするのが面倒
• VBAが好きではない

そこでメモとしてしか使用していなかったGoogle Spreadsheetに目をつけ、使ってみることにした。

単語表

• スプレッドシート = ExcelでいうExcelファイル
• シート = Excelでいうシート

シートの仕様を決めた

• JSONファイル名はシート名
• 1行目はプロパティ名
  • データベースに入れずにアプリ内でのみ使うプロパティはプロパティ名の後ろに(inApp)と記述する
  • 例）confuse(inApp)
• 2行目はデータ型
  • 開発言語に沿った記述にする
  • 画像ではSwiftをイメージしている（小文字になっているのは愛嬌です許してください）
• 3行目は補足。JSONファイルやコーディングに影響しない部分。
• 配列データは[]で囲わずに入力する
• nullは空欄にせずnullと入力する

完成コード

ExportJson.gs

// メニューバーにアクションを設定する
function onOpen() {
  // 現在開いているスプレットシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // アクション設定
  var entries = [{
    name : "JSONファイルを出力", // タブ内のアクション名
    functionName : "exportJson" //生成する内容
  }];
  // アクション登録
  spreadsheet.addMenu("JSON出力", entries);
};

// JSONファイルをエクスポートします
function exportJson() {
  
  // 現在開いているシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheets = spreadsheet.getSheets()
  // 保存フォルダID設定
  var folderId = "フォルダID";
  // データ取得・既存ファイル削除・新規ファイル作成
  for (var i = 0; i<sheets.length; i++) {
  //for (var i = 2; i<3; i++) { //デバッグ用
    // データを取得
    var dataInfo = getDataInfo_(sheets[i])
    Logger.log(dataInfo)
    // データをJsonデータにパース
    var jsonTxt = parseJson_(dataInfo[0], dataInfo[1], dataInfo[2])
    Logger.log(jsonTxt)
    // 既存のjsonを削除する
    removeFile_(sheets[i].getName() + ".json", folderId)
    // ファイル作成
    makeFile_(jsonTxt, sheets[i].getName() + ".json", folderId)
  }
  // ダイアログ表示
  Browser.msgBox("成功しました")
}

// シートからデータを取得します
function getDataInfo_(sheet) {
  // プロパティ名取得
  var startY = 1
  var startX = 1
  var endY = 1
  var endX = sheet.getLastColumn() - (startX - 1)
  var headerDatas = sheet.getSheetValues(startY, startX, endY, endX);
  var headers = Array.prototype.concat.apply([], headerDatas) // １次元配列に変換
  // データ型取得
  startY = 2
  startX = 1
  endY = 1
  endX = sheet.getLastColumn() - (startX - 1)
  var valiableTypeData = sheet.getSheetValues(startY, startX, endY, endX);
  var valiableTypes = Array.prototype.concat.apply([], valiableTypeData)
  // データ取得
  startY = 4
  startX = 1
  endY = sheet.getLastRow() - (startY - 1)
  endX = sheet.getLastColumn() - (startX - 1)
  var datas = sheet.getSheetValues(startY, startX, endY, endX);
  return [headers, valiableTypes, datas]
}

// シートデータをJsonデータに整形します
function parseJson_(headers, valiableTypes, datas) {
  var jsonTxt = "[\n"
  for (var i = 0; i<datas.length; i++) {
    jsonTxt += "\t{\n"
    for (var k = 0; k<headers.length; k++) {
      // アプリ内のステータスはスキップ
      if (headers[k].indexOf("inApp") !== -1 || headers[k] === "") {
        continue
      }
      // キー設定
      jsonTxt += "\t\t\"" + headers[k] + "\":"
      // 値設定
      if (valiableTypes[k].indexOf("?") != -1 && datas[i][k] == "null") {
        jsonTxt += datas[i][k]
      } else if (valiableTypes[k] == "string") {
        jsonTxt += "\"" + datas[i][k] + "\""
      } else if (valiableTypes[k].indexOf("[") != -1) {
        jsonTxt += "[" + datas[i][k] + "]"
      } else {
        jsonTxt += datas[i][k]
      }
      // 最後のデータなら
      if (headers.length == k + 1) {
        continue
      }
      // 挿入対象外ならスキップ
      if (headers[k+1].indexOf("inApp") !== -1 || headers[k+1] === "") {
        continue
      }
      // まだキーがあるなら,を挿入
      if (k < headers.length - 1) {
        jsonTxt += ",\n"
      }
    }
    jsonTxt += "\n\t}"
    // まだデータがあるなら,を挿入
    if (i < datas.length - 1) {
      jsonTxt += ","
    }
    jsonTxt += "\n"
  }
  jsonTxt += "]"
  return jsonTxt
}

// 既存ファイルを削除します
function removeFile_(fileName, folderId) {
  var folder = DriveApp.getFolderById(folderId)
  var files = folder.getFiles()
  while (files.hasNext()) {
    var file = files.next()
    // 指定したファイル名が存在するなら
    if (file.getName().indexOf(fileName) != -1) {
      DriveApp.getFolderById(folderId).removeFile(file);
    }
  }
}

// ファイルを作成します
function makeFile_(jsonData, fileName, folderId) {
  var contentType = "text/plain";
  var charSet = "UTF-8";
  var blob = Utilities.newBlob("", contentType, fileName).setDataFromString(jsonData, charSet); //blobに変換して、データをsetする。
  DriveApp.getFolderById(folderId).createFile(blob)
}

使い方

メニューバーから実行する。
成功するとダイアログが表示される。

説明

スプレッドシートでスクリプトを実行する方法

自動でメニューバーに表示されます。
表示されない場合、スクリプトエディタでonOpenを実行します。

単体テストコード

DoAction.gs

function doAction() {
  // 現在開いているスプレットシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // アクション設定
  var entries = [{
    name : "スクリプト実行", // タブ内のアクション名
    functionName : "testAction" // 生成する内容
  }];
  // アクション登録
  spreadsheet.addMenu("単体テスト", entries);
}

function testAction() {
  // ダイアログ表示
  Browser.msgBox("成功しました")
}

フォルダIDの確認方法

URLの末尾がフォルダIDです。

作成するファイルデータはstringでOK

ネットを見るとわざわざData型に変換している記事があったがstringで出力できます。
JSON.stringfy等をする必要はありません。たぶん。

既存ファイルを削除する必要がある

同名ファイルが作成できてしまうので、既存ファイルを削除する必要があります。
また初回生成の時のためにファイルが存在するかチェックが必要です。

単体テストコード

DoAction.gs

function doAction() {
  // 現在開いているスプレットシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // アクション設定
  var entries = [{
    name : "既存ファイル削除", // タブ内のアクション名
    functionName : "removeFile" // 生成する内容
  }];
  // アクション登録
  spreadsheet.addMenu("単体テスト", entries);
}

// 既存ファイルを削除します
function removeFile() {
  var folder = DriveApp.getFolderById("フォルダID")
  var files = folder.getFiles()
  while (files.hasNext()) {
    var file = files.next()
    // 指定したファイル名が存在するなら
    if (file.getName().indexOf("削除するファイル名") != -1) {
      DriveApp.getFolderById(folderId).removeFile(file);
    }
  }
}

ファイル作成

ファイルの作成はわりとシンプル。

単体テストコード

DoAction.gs

function doAction() {
  // 現在開いているスプレットシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // アクション設定
  var entries = [{
    name : "新規ファイル作成", // タブ内のアクション名
    functionName : "makeFile" // 生成する内容
  }];
  // アクション登録
  spreadsheet.addMenu("単体テスト", entries);
}

// ファイルを作成します
function makeFile() {
  var contentType = "text/plain";
  var charSet = "UTF-8";
  var blob = Utilities.newBlob("", contentType, "test.json").setDataFromString("{\n\t"message": "test!"\n}", charSet); //blobに変換して、データをsetする。
  DriveApp.getFolderById("フォルダID").createFile(blob)
}

所感

書き方が悪いのかただネットが遅いだけなのか、書き出しに時間がかかる。
書き出しに関しては圧倒的にExcelの方が早い。
コーディングに関しては主観ではあるがVBAよりは遥かに楽。
Chromeがあれば環境に依存しない点もかなりメリット。