LoginSignup
28
14

More than 1 year has passed since last update.

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

Last updated at Posted at 2021-06-11

やりたいこと

スプレッドシートから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ファイルを追加する。

スクリーンショット_2021-05-24_16_45_13.png

gasとhtmlの準備

gasエディタを開くと、すでに「コード.gs」のgasファイルが存在する。
スクリーンショット 2021-05-24 17.00.13.png

「コード.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で出力から実行する ※初回実行は、認証が必要になるので許可する。

スクリーンショット_2021-05-24_17_14_00.png

test_-_Google_スプレッドシート.png

ダウンロードから、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/

28
14
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
28
14