Zenn
🔥

✏️【GAS】詳解!スプレッドシートからJSON出力した話✏️

2023/02/02に公開
HTML
JavaScript
JSON
GAS
Google Sheets
tech

🤔 Why? （なんでやろうと思ったの？）

ズバリ！チームで開発中のモバイル（flutter）アプリ「資格習得支援アプリ」で必要になったから！です！

資格試験の過去問掲載、Yahoo!知恵袋のような質問コミュニティを「基本情報技術者試験」等の技術系資格に絞って提供する学習アプリです✏️

過去問管理にスプレッドシートを使用することに
▼
アプリでの過去問情報取得・表示にインクルードファイルとしてJSONを使うことに
▼
スプレッドシートからJSONを出力できるスクリプトを実装できるらしい。。。
▼
調べて実装してみよう！👍

ざっとこんな流れです！

インクルードファイルとは？ ▼
https://wa3.i-3-i.info/word12024.html
JSONとは？ ▼
https://products.sint.co.jp/topsic/blog/json
スクリプトとは？ ▼
https://wa3.i-3-i.info/word11220.html

🔧 How? （どうやってやるの？）

🏁 Goal

以下のようなJSONを出力、.jsonファイルでローカルにダウンロードまでやっていきます💪

key ・ value の内容の説明は省きますが、「ネスト構造のJSON」を出力するために少々工夫が必要です。

{
	"2019": {
		"17": {
			"year": 2019,
			"year_question": 17,
			"season": "秋",
			"category": "ストラテジ",
			"category_Detail": "経営戦略マネジメント",
			"q_statement": "statement_sample1",
			"choices": {
				"0": {
					"answer_text": "answer_sample1",
					"judge": " false"
				},
				"1": {
					"answer_text": "answer_sample2",
					"judge": " false"
				},
				"2": {
					"answer_text": "answer_sample3",
					"judge": " false"
				},
				"3": {
					"answer_text": "answer_sample4",
					"judge": " true"
				}
			}
		}
	},
	"2020": {
		"1": {
			"year": 2020,
			"year_question": 1,
			"season": "全",
			"category": "テクノロジ",
			"category_Detail": "法務",
			"q_statement": "statement_sample2",
			"choices": {
				"0": {
					"answer_text": "answer_sample5",
					"judge": " true"
				},
				"1": {
					"answer_text": "answer_sample6",
					"judge": " false"
				},
				"2": {
					"answer_text": "answer_sample7",
					"judge": " false"
				},
				"3": {
					"answer_text": "answer_sample8",
					"judge": " true"
				}
			}
		}
	},
	// 略
}

🗒 スプレッドシートの用意

１行目が JSON の key 、それ以下が value となります！

⚠️ 注意

こちらでは、choices以前の value を key として0,1,2,3以下のネスト構造を出力するため、year~q_statementをchoicesの0,1,2,3に合わせて4回記述する冗長な書き方になっております。。　
よりスマートな実装方法を模索中です🔎

🍲 GASの準備

いよいよコーディング、スクリプトを書いていく作業に入ります！
その前に。。。

⛽️ GASって何？

GAS(Google Apps Script)はGoogleのクラウドプラットフォーム上で動作するスクリプト言語です。
実行環境の構築が不要なので、簡単にプログラミング学習、WEBアプリを実装することができます。
https://products.sint.co.jp/topsic/blog/gas

GASのプロジェクト画面を開く

まず、上部タブバーの 拡張機能 ▶︎ Apps Script をクリックして下さい

すると、こんな画面が開きます


こちらがプロジェクト画面です！

✏️ コーディング

では、実際にコードを書いていきましょう！

まずはファイル名を変更します

大まかな流れは以下になります！

  1. 材料の準備
    ▼
  2. データの取得ルールを定義
    ▼
  3. 分類
    ▼
  4. JSON形式に変換して出力
    ▼
  5. JSONダウンロードボタン・ダイアログを実装

ここでのゴールはこちらです！はい、ドン！

toJson.gs
function toJSON() {
  //ダイアログテンプレート読み込み
  var download_html = HtmlService.createTemplateFromFile("download_dialog").evaluate();

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

// シートからデータを取得し、JSON形式に整形する関数
function getJSONFromSheet() {
  // 読み込むシートを指定
  var sheet = SpreadsheetApp.getActiveSheet();
  // シートのデータを取得
  var data = sheet.getDataRange().getValues();
  // JSON出力をマップに格納
  var output = {};
  
  for (var i = 0; i < data.length; i++) {
    // 取得した'data'を列ごとに分類
    var row = data[i];
    // 'year'列の全要素を取得
    var year = row[0];
    // 'year_question'列の全要素を取得
    var year_question = row[1];
    // ③ で''choices' : {}' に格納するためにに'choices'列の全要素を取得
    var choices = row[6];
    
    // ① : key,'year'でvalueを分類
    if (!output[year]) {
      output[year] = {};
    }
    // ② : さらに、key,'year_question'でvalueを分類
    if (!output[year][year_question]) {
      output[year][year_question] = {
        'year': year,
        'year_question': year_question,
        'season': row[2],
        'category': row[3],
        'category_Detail': row[4],
        'q_statement': row[5],
        'choices' : {}
      };
    }
    // ③ : ①, ② で分類したvalue、'choices'をkeyとして、さらにvalueを格納
    if (!output[year][year_question]['choices'][choices]) {
      output[year][year_question]['choices'][choices] = {
        'answer_text': row[7],
        'judge': row[8]
      }
    }
  }
  // ① ~ ③ で作成された'output'をJSONに変換して整形
  return JSON.stringify(output, null, '\t');
}


//スプレッドシート読み込み時に実行
function onOpen() {
  //メニューバーにJSON出力用メニューを追加
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "JSONファイルを出力",
    functionName : "toJSON"
  }];
  spreadsheet.addMenu("toJSON", entries);
};
download_dialog.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script type='text/javascript'>
      //ダウンロード実行
      function runDownload() {
        //JSONデータの取得
        var content = <?= getJSONFromSheet(); ?>;
        //ダウンロード用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="pastQuestions.json" onclick="runDownload()">JSONファイルをダウンロード</a>
  </body>
</html>

細かい解説をしていきます🔎

1. 材料の準備

toJson.gs
function getJSONFromSheet() {
  // 読み込むシートを指定
  var sheet = SpreadsheetApp.getActiveSheet();
  // シートのデータを取得
  var data = sheet.getDataRange().getValues();
  // JSON出力をマップに格納
  var output = {};
}

ここでは、

  • 読み込むスプレッドシートの指定
  • 指定したシートからデータの取得
  • JSON出力を最期に格納するための箱を用意

こちらを行っています。

  • SpreadsheetApp.getActiveSheet()
    Google Apps Script の API の一部で、現在開いているシートを返します。
    ここではsheet変数に代入して5行目で使う準備をしています。

  • sheet.getDataRange().getValues()
    こちらもGoogle Apps Script の API の一部で、3行目で用意したsheetのデータが存在する範囲に含まれる値を配列として取得する関数です。
    なんと、配列は、行ごとに配列に格納されるという有能さんです！

  • var output = {}
    最終的に JSON データを格納する変数（箱）です。
    JSON は key , value のマップ形式なので、定義も　{}でマップにしてあります。

2. データの取得ルールを定義

toJson.gs
for (var i = 0; i < data.length; i++) {
    // 取得した'data'を列ごとに分類
    var row = data[i];
    // 'year'列の全要素を取得
    var year = row[0];
    // 'year_question'列の全要素を取得
    var year_question = row[1];
    // ③ で''choices' : {}' に格納するためにに'choices'列の全要素を取得
    var choices = row[6];
}

先ほど用意したdataをfor文で上から調べていきます。

  • var i = 0
    0番目から調べます、つまり、データの「1行目」から調べるという意味です。
🍵 ちょっと休憩 🍵 なんで順番が「0」からはじまるの？

🤔 for文などで配列の[i]番目にアクセスする際、なぜ0から始めるのでしょう？

答えはズバリ、
コンピューターにとって、0 から始まるインデックス番号の方が計算が高速だから！
です。

✍️ 詳しく書くと...
これは、コンピューターがメモリに格納されているデータにアクセスする際の計算方法に由来します。

コンピューターは、メモリにデータを格納する際に、各データ項目に割り当てられたメモリアドレスを使用します。このメモリアドレスは通常、0 から始まる数値で表されます。

配列の要素にアクセスする場合、インデックス番号に基づいてメモリアドレスを計算する必要があります。0 から始まるインデックス番号を使用する場合、この計算は非常に簡単で効率的です。一方、1 から始まるインデックス番号を使用する場合、この計算はより複雑で低速になります。

このため、0 から始まるインデックス番号を使用することで、コンピューターは配列やリストなどの要素に高速にアクセスできるため、一般的に速い計算を実現することができます。

他、歴史的背景などは以下の記事が面白かったです！
https://qiita.com/kazurego7/items/aeaec6c0b8449569e58c

  • i < data.length
    dataの値の数だけ探索を続けますよ という意味
  • i++
    何番目を見ていますよ という意味のiが「1ずつ増えていきますよ」という意味。
    つまり1個ずつ探索してもらっている。
  • var row = data[i]
    JSONを取得するにあたって、列ごとに key を分類したいので、dataを列ごとに細切れにしている。
  • var year = row[0]・var year_question = row[1]
    列に名前をつけている。
    1行目がyear列、2行目がyear_question列にしますという意味。
    (厳密には「変数に代入している」という表現が正しい)

3. 分類

toJson.gs
// ① : key,'year'でvalueを分類
    if (!output[year]) {
      output[year] = {};
    }
    // ② : さらに、key,'year_question'でvalueを分類
    if (!output[year][year_question]) {
      output[year][year_question] = {
        'year': year,
        'year_question': year_question,
        'season': row[2],
        'category': row[3],
        'category_Detail': row[4],
        'q_statement': row[5],
        'choices' : {}
      };
    }
    // ③ : ①, ② で分類したvalue、'choices'をkeyとして、さらにvalueを格納
    if (!output[year][year_question]['choices'][choices]) {
      output[year][year_question]['choices'][choices] = {
        'answer_text': row[7],
        'judge': row[8]
      }
    }
  }

  • ①
    dataをまずはyearで分類しています。
    if (!output[year])、つまり、最初用意したoutputに2.で用意したyearの値が入っていない場合、outputにyearを key とした空のマップを格納しています。
    今回の場合は"2019"を key とした空マップを格納しています。

    {
  "2019": {
  // ここが空のマップ。ここに value がどんどん入っていくよ！
  },
},

  • ②
    ① と同様の手順で、さらにyear_question（この場合は"17"）で分類し...

    {
  "2019": {
  	"17": {
  	// 残りの value もここに入っていくよ！
  	},
  },
},

    中身をまとめて格納します

    {
  "2019": {
  	"17": {
  		"year": 2019,
  		"year_question": 17,
  		"season": "秋",
  		"category": "ストラテジ",
  		"category_Detail": "経営戦略マネジメント",
  		"q_statement": "statement_sample1",
  		"choices": {}, // ③ で　value を格納するための空マップです
  	},
  },
},

    • ③

      • ② で作成したマップ"choices": {}に value としてさらにマップchoices（0 ~ 3のインデックス）を格納
        ⚠️ "choices"（key として設定したマップ）とchoices（スプレッドシートのchoices列を格納した配列）は別物です！
      {
"2019": {
	"17": {
		"year": 2019,
		"year_question": 17,
		"season": "秋",
		"category": "ストラテジ",
		"category_Detail": "経営戦略マネジメント",
		"q_statement": "statement_sample1",
		"choices": {
			"0": {// この中に残りの value が入る},
			"1": {// この中に残りの value が入る},
			"2": {// この中に残りの value が入る},
			"3": {// この中に残りの value が入る},
		}
	}
},
      • ↑で作成したマップchoicesを key とするマップに残りの value を格納
      {
"2019": {
	"17": {
		"year": 2019,
		"year_question": 17,
		"season": "秋",
		"category": "ストラテジ",
		"category_Detail": "経営戦略マネジメント",
		"q_statement": "statement_sample1",
		"choices": {
			"0": {
				"answer_text": "answer_sample1",
				"judge": " false"
			},
			"1": {
				"answer_text": "answer_sample2",
				"judge": " false"
			},
			"2": {
				"answer_text": "answer_sample3",
				"judge": " false"
			},
			"3": {
				"answer_text": "answer_sample4",
				"judge": " true"
			}
		}
	}
},

    4. JSON形式に変換して出力

    toJson.gs
      // ① ~ ③ で作成された'output'をJSONに変換して整形
  return JSON.stringify(output, null, '\t');
    • JSON.stringify()
      JavaScript で使用する JSON オブジェクトのシリアライズ（文字列化）メソッドです。このメソッドは、JavaScript のオブジェクトや配列などを JSON 文字列に変換することができます。
      • 第1引数output
        シリアライズする JavaScript のオブジェクトや配列などです。この引数が文字列に変換されます。
      • 第2引数null
        書式を指定するためのオプションです。この引数を指定すると、JSON 文字列に含まれるキーと値に対する書式が指定されます。この場合、第2引数に null が指定されているため、書式は指定されていません。
      • 第3引数'\t'
        書式を指定するためのオプションです。この引数を指定すると、JSON 文字列に含まれるキーと値の間にタブ（\t）が挿入されます。これにより、生成された JSON 文字列が整形されます。

こちらで実際にサンプルを動かすことができます ▼
https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify

5. JSONダウンロードボタン・ダイアログを実装

ここまでで、「JSONを出力」まではできるようになりました 🙌

最後に、「JSONファイルとしてダウンロードする機能を作る」ところをやってしまいましょう！

  • スプレッドシートにtoJSONボタンを追加

    toJson.gs
    //スプレッドシート読み込み時に実行
function onOpen() {
  //メニューバーにJSON出力用メニューを追加
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "JSONで出力",
    functionName : "toJSON"
  }];
  spreadsheet.addMenu("toJSON", entries);
};
    • SpreadsheetApp.getActiveSpreadsheet()
      1.と同じく、ボタン追加を実行するシートを指定
    • entries
      ボタンを押した際に表示されるメニュー、実行する関数を設定
    • spreadsheet.addMenu()
      ボタンを追加。
      第1引数にボタン表示名、第2引数に表示メニュー

公式リファレンス ▼
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=ja#addMenu(String,Object)

  • ダウンロードダイアログを実装
    まずはdownload_dialog.htmlを追加しましょう

    以下のようなダイアログ表示を実装します。

    download_dialog.html
    <head>
  <base target="_top">
  <script type='text/javascript'>
    //ダウンロード実行
    function runDownload() {
      //JSONデータの取得
      var content = <?= getJSONFromSheet(); ?>;
      //ダウンロード用URL生成
      var blob = new Blob([ content ], { "type" : "application/json"});
      document.getElementById("download").href = window.URL.createObjectURL(blob);
    }
</script>
</head>
    • content = <?= getJSONFromSheet(); ?>
      htmlのscriptタグ内でtoJson.gsのgetJSONFromSheet()を実行させています。
    • new Blob([ content ], { "type" : "application/json"})
      JavaScriptによるBlobオブジェクト（バイナリデータを扱うためのオブジェクト）の生成をしています。
      content変数に格納されているデータをもとに、MIMEタイプ"application/json"のBlobオブジェクトを生成しています。
    • document.getElementById("download").href = window.URL.createObjectURL(blob)
      HTMLドキュメントからIDが"download"である要素を取得し、その要素のhref属性（文書のURL指定）を更新しています。更新する値は、blobつまり、getJSONFromSheet()の実行結果です。

MIMEタイプとは　▼
https://www.tohoho-web.com/wwwxx015.htm

href属性とは　▼
https://www.tohoho-web.com/html/link.htm#href
https://e-words.jp/w/href属性.html#:~:text=href属性とは、Web,“hypertext reference” の略。

  • JSONファイルをダウンロードリンクを実装

    download_dialog.html
    <body>
  <!-- JSONダウンロードボタン：jsonファイル名は適宜設定してください-->
  <a id="download" href="#" download="pastQuestions.json" onclick="runDownload()">JSONファイルをダウンロード</a>
</body>
    • id="download" href="#" download="pastQuestions.json"
      id属性（ダウンロードするファイルにつけたい拡張子）に"download"という値を指定し、ダウンロードされるファイルのファイル名を指定しています。
      ここではpastQuestions.jsonとしていますが、こちらは適宜変更して下さい！
      href属性には空のURLを指定しています。
    • onclick
      リンク押下時の動作を指定します。
      ここではdownload_dialog.htmlのrunDownload()を実行し、スプレッドシートからのJSON取得、ダウンロードまでのすべての動作を実行します。

id属性とは ▼
https://www.tohoho-web.com/html/attr/id.htm

これで、JSONファイルをダウンロードリンクを押すだけで
JSONファイルダウンロードまで完結するように実装できました 🙌

🎉 What?（結局GASってなんだ？）

本稿はプログラミング初心者の方でも詰まらないようになるべく細かく、参考文献も豊富に記載するように努力いたしました。

今回のような、「スプレッドシートからJSONファイルをダウンロードする」に留まらず、GASは他にも多種多様な便利機能簡単に実装できてしまう優れた代物です。

GASのすごいところ

  • Googleサービスと連携
    GASは、GoogleサービスのAPIを利用することができます。例えば、Googleスプレッドシート、Googleドライブ、Googleカレンダーなどと連携してスクリプトを作成することができます。

  • 使いやすさ
    GASはJavaScriptをベースとしたスクリプト言語です。JavaScriptに慣れている開発者はGASをすぐに使い始めることができます。

  • 自動化
    GASを使えば、手作業で行う重複するタスクを自動化することができます。

  • 開発者向けのAPI
    GASは、Google API Client Library for JavaScriptを使って、多数のGoogle APIを呼び出すことができます。

しかしながら、筆者は、GASの本当の凄さは
プログラミング初心者の「あんなもの作ってみたい！」実現へのハードルを大きく下げることができる
部分にあると考えています。環境構築不要という点が大きいですね！🏃

モノづくりが基本のプログラミング学習では作りたいものが先にあるべきではないでしょうか？
書籍を読み終えてから。。。学習動画を視聴し終えてから。。。
初学者ですとどうしても手段を先に持ってきがちです。（筆者のことです。。）
楽しむこと・目的を最優先するためにも、「初学者こそGASを使ってみよう！」と勝手に思っています💪

ここまで読んでいただきありがとうございます🙇

皆様の学習、開発の一助となれることを心より願っております！💨

参考にさせて頂いた記事 🙏 ▼
https://zenn.dev/oka_yuuji/articles/8262f0254cf80f
https://qiita.com/mrd-takahashi/items/8757b842250785c8543d
https://teratail.com/questions/230170

Discussion