やりたいこと
スプレッドシートから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/