サイトリニューアルで今までExcelからcsv出力で管理してたデータをJSON形式にしたいっていう要件があったので、今回GoogleのスプレッドシートからGoogleAppsScript （GAS） を使ってJSON形式にデータ出力するまでをやってみました。
スプレッドシートの作成と仮データ
まずGoogleドライブでスプレッドシートを作成します。
スプレッドシートの作成
【新規】ボタンから
【Googleスプレッドシート】を選択
仮データ入力
各セルにこんな感じで仮のデータ入れてみます。
今回お客さんがデータ管理しやすいよう、基本のラベルは1行目に日本語で、JSONで使用するラベルは2行目に記載するようにしてみました。
スクリプトエディタでGoogleAppsScript（GAS）とhtmlを書いていく
次にこのデータをJSON形式で出力するために、スクリプトエディタを使用し【GoogleAppsScript（GAS）】を作成していきます。
基本的にはJavascriptで書いて問題ないです。
スクリプトエディタ
スプレッドシートメニューの【ツール】>【スクリプトエディタ】と選択します。
こんな感じでスプレッドシートとは別のページでスクリプトエディタが開くと思います。
ここで拡張子が【.gs】と書いてあるのが【 GoogleAppsScript（GAS） 】用のファイルになり、ここに基本的なコードを書いていきます。
◎参考
https://qiita.com/void_vtuber/items/a0c81392ce57b49dbb61
基本的にこちら参考にしてます。
toJSON.gs
【コード.gs】をリネームしてファイル名をわかりやすいように【toJSON.gs】にして、以下記述します。
//ダウンロードダイヤログ表示
function toJSON() {
//ダイヤログテンプレート読み込み
var dl_html = HtmlService.createTemplateFromFile("dl_dialog").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 = [];
//2行目のkeyの名前取得
//1行目は管理しやすいよう日本語で記述し、
//JSON用のラベルは2行目で指定しているため
//【getRange】の第1引数は【2】
for (var x = 1; x <= maxColumn; x++) {
keys.push(sheet.getRange(2, x).getValue());
}
//データの取得
//実際のデータが3行目からなので【y = 3】から開始
for (var y = 3; y <= maxRow; y++) {
var json = {};
for (var x = 1; x <= maxColumn; x++) {
json[keys[x-1]] = sheet.getRange(y, x).getValue();
}
//データ格納
data.push(json);
}
//整形してテキストにします
return JSON.stringify(data, null, '\t');
}
//スプレッドシート読み込み時に実行
function onOpen() {
//メニューバーにJSON出力用メニューを追加
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "JSONで出力",
functionName : "toJSON"
}];
spreadsheet.addMenu("JSON", entries);
};dl_dialog.html
次にダウンロードダイヤログを表示し、【getData】を実行するためのhtmlテンプレートを作成していきます。
スクリプトエディタのメニュー【ファイル】>【New】>【HTMLファイル】と選択することで新規のhtmlファイルを作成できます。
【dl_dialog】と命名してください。
【dl_dialog.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="jojo.json" onclick="handleDownload()">ダウンロード</a>
</body>
</html>実行してみる
スクリプトエディタで【toJSON.gs】【dl_dialog.html】作成・保存したら、実際に実行してみます。
スクリプトエディタからスプレッドシートに戻って、ページをリロードして少し待つとスプレッドシートのメニューに【JSON】が追加されるのでそこから【JSONで出力】を選択します。
初回実行時は、承認が必要
GASで作成したスクリプト実行する場合、初回の実行時はGoogleアカウントによる承認が必要になります。
表示されたポップアップの【続行】ボタンを押すとアカウント選択画面になりますので、使用しているGoogleアカウントを選択し、
アクセスの【許可】をおします。
【JSONで出力】 の実行
許可をしたらスプレッドシートに戻ってくるので、メニューの 【JSON】> 【JSONで出力】 から再度作成したスクリプトを実行します。
スクリプトが正常に実行されると数秒後にJSONファイルダウンロードのポップアップがでますので【ダウンロ―ド】のリンクから生成したJSONファイルのダウンロードができます。
[
{
"id": 1,
"name": "ジョナサン",
"height": 195,
"weight": 105,
"type": "A"
},
{
"id": 2,
"name": "ジョセフ",
"height": 195,
"weight": 97,
"type": "B"
},
{
"id": 3,
"name": "承太郎",
"height": 195,
"weight": 82,
"type": "B"
},
{
"id": 4,
"name": "仗助",
"height": 185,
"weight": 75,
"type": "B"
},
{
"id": 5,
"name": "ジョルノ",
"height": 172,
"weight": "--",
"type": "AB"
}
]無事JSONになってます。
1つ問題が
今回サンプルで実行したのはわずかなデータ数でしたが、これが数百データになってくるとJSON生成の処理に30秒～1分くらいかかってしまいます。
さらに多くなればより遅くなるのが見えているので対策を行いました。
JSON生成に時間がかかる場合の対策
対策その1：ローディング
【JSONで出力】をおした後にそのままの画面でしばらく待つのがきびしいので、とりあえずローディングを入れてみることにしました。
【toJSON.gs】の【function toJSON】にローディングの記述を追加
//ダイヤログ表示
function toJSON() {
//ダイヤログテンプレート：loader.html読み込み
var loader = HtmlService.createTemplateFromFile("loader").evaluate();
//ローディングダイヤログ表示
SpreadsheetApp.getUi().showModalDialog(loader, "JSON変換処理中です");
//ダイヤログテンプレート：dl_dialog.html読み込み
var dl_html = HtmlService.createTemplateFromFile("dl_dialog").evaluate();
//ダウンロードダイヤログ表示
SpreadsheetApp.getUi().showModalDialog(dl_html, "JSONファイルダウンロード");
}loader.htmlを制作
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
.loader_wrapper {
max-width: 100%;
overflow: hidden;
}
.loader,
.loader:after {
border-radius: 50%;
width: 10em;
height: 10em;
}
.loader {
margin: 60px auto;
font-size: 10px;
position: relative;
text-indent: -9999em;
border-top: 1.1em solid rgba(0, 0, 0, 0.2);
border-right: 1.1em solid rgba(0, 0, 0, 0.2);
border-bottom: 1.1em solid rgba(0, 0, 0, 0.2);
border-left: 1.1em solid #000;
-webkit-transform: translateZ(0);
-ms-transform: translateZ(0);
transform: translateZ(0);
-webkit-animation: load8 1.1s infinite linear;
animation: load8 1.1s infinite linear;
}
@-webkit-keyframes load8 {
0% {
-webkit-transform: rotate(0deg);
transform: rotate(0deg);
}
100% {
-webkit-transform: rotate(360deg);
transform: rotate(360deg);
}
}
@keyframes load8 {
0% {
-webkit-transform: rotate(0deg);
transform: rotate(0deg);
}
100% {
-webkit-transform: rotate(360deg);
transform: rotate(360deg);
}
}
</style>
</head>
<body>
<div class="loader_wrapper">
<div class="loader">Loading...</div>
</div>
完了までこのままお待ちください。<br>
※データ数によっては1～3分ほどかかる場合ございます。
</body>
</html>これでJSON生成されるまでローディングが表示され、生成されると【dl_dialog】のポップアップウィンドウが表示されるようになります。
対策その2：スクリプトの改修
Twitterでつぶやいてみたら記述変えるといいと非常にありがたいアドバイスをいただきました。
ということで【function getData】を改修してみます！
//データ取得
function getData() {
//データ取得するシート（現在開いているシートを指定）
var sheet = SpreadsheetApp.getActiveSheet();
//行（横軸）と列（縦軸）の最大数を取得
var maxRow = sheet.getLastRow();
var maxColumn = sheet.getLastColumn();
//JSON用のkey
var keys = [];
//データ格納配列
var data = [];
//2行目のkeyの名前取得
//1行目は管理しやすいよう日本語で記述し、
//JSON用のラベルは2行目で指定しているため
//【getRange】の第1引数は【2】
for (var x = 1; x <= maxColumn; x++) {
keys.push(sheet.getRange(2, x).getValue());
}
//データの取得
//実際のデータが3行目からなので【y = 3】から開始
//getRange()を使って、3行目1セル ～ 最終行目の最終セルを範囲指定
var _values = sheet.getRange(3, 1, maxRow, maxColumn).getValues();
for(var i = 0; i < _values.length; i++){
var json = {};
for (var k = 0; k < _values[i].length; k++) {
json[keys[k]] = _values[i][k];
}
data.push(json);
}
//整形してテキストに
return JSON.stringify(data , null, '\t');
}こんな感じに。
【getRange()】を使ってシートのデータセルを範囲で一括取得すると2次元配列でデータが返ってくるのでそれをforで回すだけです。
改修前の処理も同様にforで回しているんですが、毎回 【getRange()】 を実行して1セルずつデータを取得していたために処理が遅くなっていたと思われます。
GASの初見殺し的な部分にも引っかかりましたが、無事スプレッドシートからJSON生成できるようになりました。
おしまい。
