スプレッドシートからjsonを作る #GAS

やりたいこと

スプレッドシートからjsonを作りたい。
見出しをkeyにするパターンと
{id:001, name=aaa, flg=true},{id:002, name=bbb, flg=false}
idをkeyにしてその中に見出しをkeyにするパターンを今回は作る。
{001:{name=aaa, flg=true},002:{name=bbb, flg=false}}

スプレッドシートでgasを書くには

以下の図のように、スプレッドシートを用意し、1行目に見出し、2行目以降にデータを適当に入力する。
そのあと、ツール＞スクリプトエディタをクリックすると、別窓でapps scriptのwebエディタが出てくる。
ここから、新規gasファイルとhtmlファイルを追加する。

gasとhtmlの準備

gasエディタを開くと、すでに「コード.gs」のgasファイルが存在する。

「コード.gs」ファイルを好きな名前に変換し、以下のソースコードを貼り付ける。

//ダウンロードダイヤログ表示
function toJSON() {
  /* 実行結果イメージ
    [
      {flg=true, name=google, id=A001, url=https://www.google.com/},
      {flg=true, name=yahoo, id=A002, url=https://www.yahoo.co.jp/},
      {flg=false, name=aaaa, id=B001, url=https://www.aaaaaa}
    ]
   */ 

  //ダイヤログテンプレート読み込み
  var dl_html = HtmlService.createTemplateFromFile("dl").evaluate();

  //ダイヤログ表示
  SpreadsheetApp.getUi().showModalDialog(dl_html, "JSONファイルをダウンロード");
}

//データ取得
function getData() {
  //データ取得するシート（現在開いているシートを指定）
  var sheet = SpreadsheetApp.getActiveSheet();

  //行（横軸）と列（縦軸）の最大数を取得
  var maxRow = sheet.getLastRow();
  var maxColumn = sheet.getLastColumn();

  //JSON用のkey
  var keys = [];

  // ★ここから

  //データ格納配列
  var data = [];

  //1行目のkeyの名前取得 keyの行を変更したい場合はxと引数を変更
  //JSON用のラベルは1行目で指定しているため【getRange】の第1引数は【1】
  for (var x = 1; x <= maxColumn; x++) {
    keys.push(sheet.getRange(1, x).getValue());
  }

  //データの取得
  //実際のデータが2行目からなので【y = 2】から開始
  for (var y = 2; y <= maxRow; y++) {
    // var json_shop_id = {};
    var json = {};
    
    for (var x = 1; x <= maxColumn; x++) {
      json[keys[x-1]] = sheet.getRange(y, x).getValue();
    }
    //データ格納
    data.push(json);
    Logger.log(data);
  }

  //整形してテキストにします
  return JSON.stringify(data, null, '\t'); // ★ここまで
}


//スプレッドシート読み込み時に実行
function onOpen() {
  //メニューバーにJSON出力用メニューを追加
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "JSONで出力",
    functionName : "toJSON"
  }];
  spreadsheet.addMenu("JSON", entries);
};

次に、ダウンロードするための画面を作成するために、新規でhtmlファイルを用意する。
htmlファイルの名前は何でも良いが、上記の12行目でdlファイルを読み込んでいるため、今回はdl.htmlをつくり、以下のソースを貼り付ける

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
    <script type='text/javascript'>
        //ダウンロード実行
        function handleDownload() {
            //JSONデータの取得
            var content = <?= getData(); ?>;
            //ダウンロード用URL生成
            var blob = new Blob([content], { "type": "application/json" });
            document.getElementById("download").href = window.URL.createObjectURL(blob);
        }
    </script>
</head>
<body>
    <!-- JSONダウンロードボタン：json名は適宜設定してください-->
    <a id="download" href="#" download="download_file.json" onclick="handleDownload()">ダウンロード</a>
</body>
</html>

ファイル実行

上記のファイルの用意ができたら、スプレッドシートにもどって実行してみる。
JSON＞JSONで出力から実行する ※初回実行は、認証が必要になるので許可する。

ダウンロードから、jsonをダウンロードすると、

[
	{
		"id": "A001",
		"name": "google",
		"flg": false,
		"url": "https://www.google.com/"
	},
	{
		"id": "A002",
		"name": "yahoo",
		"flg": true,
		"url": "https://www.yahoo.co.jp/"
	},
	{
		"id": "B001",
		"name": "aaaa",
		"flg": false,
		"url": "https://www.aaaa.co.jp/"
	}
]

のjsonファイルがダウンロードできる。

idをkeyにするパターン

gasに貼り付けたソースの「★ここから」「★ここまで」を下記のように修正

// ★ここから

  //データ格納配列
  var data = {};

  //1行目のkeyの名前取得 keyの行を変更したい場合はxと引数を変更
  //JSON用のラベルは2行目で指定しているため【getRange】の第1引数は【1】
  for (var x = 1; x <= maxColumn; x++) {
    keys.push(sheet.getRange(1, x).getValue());
  }
  
  //データの取得 
  //実際のデータが2行目からなので【y = 2】から開始
  for (var y = 2; y <= maxRow; y++) {
    var json = {};
    
    for (var x = 2; x <= maxColumn; x++) {
      json[keys[x-1]] = sheet.getRange(y, x).getValue();
    }
    //データ格納
    var id = sheet.getRange(y, 1).getValue();
    data[id] = json;
   
  }
  Logger.log(data);
  //整形してテキストにします
  return JSON.stringify(data, null, '\t');  // ★ここまで

すると、idをkeyにしたjsonをつくることができる

/* jsonの形
{
	"A001": {
		"name": "google",
		"flg": true,
		"url": "https://www.google.com/"
	},
	"A002": {
		"name": "yahoo",
		"flg": true,
		"url": "https://www.yahoo.co.jp/"
	},
	"B001": {
		"name": "aaa",
		"flg": false,
		"url": "https://www.aaaaaa"
	}
}
*/

ソースコードは以下のgitにあります。
https://github.com/minami-373/other_code
参考
https://kuwk.jp/blog/spreadsheet2json/