GoogleSpreadsheetでちょっと複雑なJSONを出力する

目的

前回の記事でシンプルなJSONを出力する事はできましたが、JSONにオブジェクト配列だけでなくオブジェクト直下にメンバーも記述したり、複数の配列の記述や配列の中から指定のオブジェクトのみ取得しJSONを作成、さらに複数の異なるJSON作成もしたいと思うかもしれません。その為、データの組み合わせを管理するシートを作成しそれを用いてJSONを作成します。

なにが出来る？

• データの種類別にテーブルの様なシートを作り、それを組み合わせてJSONを生成が可能
• JSONオブジェクトに直下メンバーとオブジェクト配列の複数の混合出力と各個別の出力
• オブジェクト配列から指定のキーと値を持つオブジェクトのみ取得
• 複数のファイル構成を管理シートに記述する事で複数のJSONファイルを連続して出力

出来ない事

• オブジェクト配列内のオブジェクトにオブジェクト配列を入れられない
• 外部シートからデータを取得する場合、キー名は外部シートの名前になり任意に変更出来ない
• オブジェクト配列内のオブジェクトから任意のデータを取り出し、直下メンバーに設定出来ない

対応するJSON

前回のシンプルなJSON以外に下記の様な構造のJSONの出力に対応させます。前回との違いはオブジェクト直下にメンバーが有ることと、配列が複数有る事です。

TrainerData.json

{
	"userId": 1,
	"trainerName": "noco",
	"rank": "D",
	"rating": 30000,
	"money": 500000,
	"jewel": 10000,
	"ownUmaData": [
		{
			"id": 1,
			"userId": 1,
			"umaId": 3,
			"cardName": "サクラバクシンオー"
		},
		{
			"id": 2,
			"userId": 1,
			"umaId": 2,
			"cardName": "マヤノトップガン"
		}
	],
	"ownSupportData": [
		{
			"id": 1,
			"userID": 1,
			"supportCardId": 1,
			"cardName": "桐生院葵"
		},
		{
			"id": 2,
			"userID": 1,
			"supportCardId": 5,
			"cardName": "ダイワスカーレット"
		}
	]
}

(サンプルとして直下メンバーを記述したかったのでこの形にしましたが、rating等のユーザーデータは別にシート作って読み込んだ方がよさそう)

スプレッドシート

上記JSONを作成する為にシートを3つ作成します。データシートの1行目は人が理解する為の項目名、2行目がJSONに記述する為の項目名です。作成する場合、シート名は指定のにしてください。

シート名:ownUmaData

各ユーザーが所有するウマ娘のテーブルです。これはウマのパラメータも保持するつもりでしたがJSONが無駄にながくなるので省略。

ID	ユーザーID	ウマID	カード名
id	userId	umaId	cardName
1	1	3	サクラバクシンオー
2	1	2	マヤノトップガン
3	2	1	ゴールドシップ
4	2	4	スーパークリーク
5	2	5	ハルウララ

シート名:ownSupportData

各ユーザーが所有するサポートカードデータのテーブルです。

ID	ユーザーID	サポートカードID	カード名
id	userID	supportCardId	cardName
1	1	1	桐生院葵
2	1	5	ダイワスカーレット
3	2	2	マヤノトップガン
4	2	4	スイープトウショウ
5	2	3	エイシンフラッシュ

(上のサンプルJSONを見るとわかりますが、ownUmaDataとownSupportDataは"userID"が1のオブジェクトしか取得してません。)

シート名:JsonManager

これが管理シートになり、オブジェクト直下のメンバーはここに記述し配列は外部シートから読取り記述する事になります。

出力ファイル名：そのファイルに格納する全てのデータ行に記述。さらに続けて別ファイル名とデータ構成を設定する事で連続してJSON生成が可能

種類:"data"であれば直下メンバー,"sheet"であれば外部シート読込によるオブジェクト配列

キー名：実際にJSONに記述されるキー名。sheetに関してはシートを検索する際も利用

値 /シート取得値:"data"であればキーに対応する値。"sheet"であれば外部シートから取得する値。"sheet"で値が複数ある場合配列変換する為カンマ区切りにする。なにも書かない場合は全てのオブジェクトを取得する。

シート取得列:外部シートから値をどの列から取得するかの指定
※例では"シートの取得列"が1でownUmaData、ownSupportDataどちらも"userId"。"シート取得値"は1なのでユーザーIDが1のオブジェクトのみ取り込んでいます。

出力ファイル名	種類	キー名	値 /シート取得値	シート取得列
TrainerData	data	userId	1
TrainerData	data	trainerName	noco
TrainerData	data	rank	D
TrainerData	data	rating	30000
TrainerData	data	money	500000
TrainerData	data	jewel	10000
TrainerData	sheet	ownUmaData	1	1
TrainerData	sheet	ownSupportData	1	1

コード

gs2Serialize()

これは前の記事gs2JSON()からJSON出力機能はなくし、実行元から取得対象のシート情報を受取、シリアライズ化したJSONを返す関数になっています。その他に指定列(targetColNum)の指定値(targetValues)を含むオブジェクトを取得する為の処理も追加しています。

function gs2Serialize(sheet, targetValues, targetColNum) {
    // キー名取得
  var keyName = sheet.getSheetName();
  // シート名の最初の文字列を大文字に設定
  var fileName = keyName.charAt(0).toUpperCase() + keyName.slice(1)

  // 項目名取得 jsonで記述する項目名は2行目
  var headRange = sheet.getRange(2, 1, 1, sheet.getLastColumn());
  var headValues = headRange.getValues().flat().filter(String).map(String);
  // 全ての値取得 データは３行目から始まる
  var dataRange = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
  var dataValues = dataRange.getValues();

  // レコード内のデータを格納するオブジェクト
  var dataObj = {};
  //　データオブジェクトを配列として格納
  var dataObjAry = [];

  // 1行ずつデータを取り出し、オブジェクトに設定
  dataValues.forEach((datas)=> {
    var cnt = 0
    dataObj = {}; // dataObjの初期化
    
    targetValues.forEach( (id) => {
      // 取得IDが引数のIDと一致しない場合は処理スキップ
      if(datas[targetColNum] == id || id == "" ){
        headValues.forEach((head)=>{
          // dataObjに項目名に対応した値を設定
          dataObj[head] = datas[cnt];
          cnt++;
        });
        // dataObjをまとめる
        dataObjAry.push(dataObj);
      }
    });
    
  });

　// タブ区切りでオブジェクトをシリアライズ化
  // var dataObjAry = JSON.stringify(dataObjAry, null, '\t')
  return dataObjAry;
}

createJSONManeger()

これが管理用スクリプトになり、これを実行してJSONを生成します。folderIdの<ドライブのフォルダID>を任意のドライブIDに書き換えます。その上で上記3つのスプレドシート、gs2Serialize()とcreateJSONManeger()があれば上のJSONと同じ物をすぐ生成可能です。

function createJSONManeger() {
  // 保存するフォルダIDと名前
  const folderId = '<ドライブのフォルダID>';

  // アクティブスプレッドシートの取得
  var ass = SpreadsheetApp.getActiveSpreadsheet();
  // 管理シートの取得
  var sheet = ass.getSheetByName("JsonManager");

  // 出力ファイル名があるA列から全て選択
  var outPutNameRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1);
　// 重複ありのファイル名一覽
  var outPutNamesOfDup = outPutNameRange.getValues().flat().filter(String).map(String);
  // 重複なしのファイル名一覽
  var outPutNames = outPutNamesOfDup.filter((value, index, self) =>  self.indexOf(value) === index); 
　
　
  outPutNames.forEach( (outPutName) => {
    // 出力ファイル名の最初の行数 ※+2はスプレッドシートの行数の始まりが1なのと、項目名が1行ある為
    var outPutNameRow = outPutNamesOfDup.indexOf(outPutName) + 2;
    // 出力ファイル名の個数カウント
    var outPutNameCnt = outPutNamesOfDup.filter((x) => x === outPutName).length;
  　// 種類のリスト生成
    var typeRage = sheet.getRange(outPutNameRow, 2 , outPutNameCnt, 1);
    var typeValues = typeRage.getValues().flat().filter(String).map(String);

    // データ全体を格納し、出力に用いるオブジェクト
    var jsonObj = {};

    //　単一データの処理
    var rowCnt = 0; // 行数を決定するカウント
    typeValues.forEach((type) =>{
      if(type == "data"){
        // キー名と値を取得　キー名は3列目、値は4列目
        var keyName = sheet.getRange(outPutNameRow + rowCnt,3).getValue();
        var keyValue = sheet.getRange(outPutNameRow + rowCnt,4).getValue();
        // オブジェクトに追加
        jsonObj[keyName] = keyValue;
      }
      rowCnt++;
    });

    // 別シートデータの処理
    var rowCnt = 0; // 行数を決定するカウント
    typeValues.forEach((type) =>{
      if(type == "sheet"){
        // 外部シート取得値
        var targetValue = sheet.getRange(outPutNameRow + rowCnt,4).getValue();
        // 外部シート取得列
        var targetColNum = sheet.getRange(outPutNameRow + rowCnt,5).getValue();
        // 取得対象のIDの配列
        var targetValues = [];
        var pattern = /,/;
        // 単数か複数で配列追加処理の場合分け
        if ( pattern.test(targetValue)) {
          targetValues = targetValue.split(",");
        }else{
          targetValues.push(targetValue);
        }

        // キー名とシートを取得
        var keyName = sheet.getRange(outPutNameRow + rowCnt,3).getValue();
        // シート取得
        var ohterSheet = ass.getSheetByName(keyName);
        // シートが存在しない場合エラー
        if (ohterSheet == null) {
          throw 'シートが存在しません！';
        }

        jsonObj[keyName] = gs2Serialize(ohterSheet, targetValues, targetColNum);
      }
      rowCnt++;
    });

    // タブ区切りでオブジェクトをシリアライズ化
    var jsonObj = JSON.stringify(jsonObj, null, '\t')
    console.log(jsonObj);

    // JSON書き出し
    createJsonFile(folderId, outPutName, jsonObj);
    
  });
}

/**
 * テキストファイル書き出し
 * @param {string} folderId フォルダID
 * @param {string} fileName ファイル名
 * @param {string} contents ファイルの内容
 */
function createJsonFile(folderId, fileName, contents) {  
  // コンテンツタイプ
  const contentType = 'application/json';
  // 文字コード
  const charset = 'UTF-8';
  // 出力するフォルダ
  const folder = DriveApp.getFolderById(folderId);
  // Blob を作成する
  const blob = Utilities.newBlob('', contentType, fileName).setDataFromString(contents, charset);
  // ファイルに保存
  folder.createFile(blob);
}

詳細

gs2Serialize

基本的にシリアルデータ作って返すだけですが、下記の部分でオブジェクトの取得判定を行っています。取得データ(targetValues)は配列なので、例えばidの1と3のみが含まれるオブジェクトが欲しいと言う事が出来ます。また、指定列(targetColNum)でどの列を対象にするかも指定できます。

    targetValues.forEach( (id) => {
      // 取得IDが引数のIDと一致しない場合は処理スキップ
      if(datas[targetColNum] == id || id == "" ){
        headValues.forEach((head)=>{
          // dataObjに項目名に対応した値を設定
          dataObj[head] = datas[cnt];
          cnt++;
        });
        // dataObjをまとめる
        dataObjAry.push(dataObj);
      }
    });

createJSONManeger

処理のながれは下記の様になっています。

1. "出力ファイル名"(outPutNames)を取得し、ファイル名毎に下記処理を回します
2. "種類"からデータタイプを取得(typeValues)しdataタイプ、sheetタイプ毎の処理を行います
3. dataタイプではキー名と値を取得し、出力用のオブジェクトに追加します。
4. sheetタイプでは外部シートで取得する値(targetValues)と対象の列情報(targetColNum)とキー名から外部シート(ohterSheet)を取得し、gs2Serialize()に渡して返り値を出力用オブジェクトに追加します
5. シリアライズ化します
6. JSONを書き出します

---

Unityのデータ管理にJSONを使うので、そのJSONをスプレッドシートで管理出来ると便利そうと思ったのが発端です。もっと改善できそうですが、とりあえずこんなんで良しとします。