2018年4月6日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

GoogleスプレッドシートのデータをJSONで取得する【GAS】

Google Apps Scriptにてウェブアプリを作る場合、通常doGet()でHTMLを表示し、HTML側からスプレッドシートのデータをリクエストした時に、スプレッドシートデータを普通は二次元配列形式でデータを受け取ります。その後、配列を例えば ss[2][3]といった具合に配列の場所を指定して、データを処理します。

しかし、この手法だと行を指定する部分は数字でも良いのですが、列を指定する部分まで何列目として指定しなければならず、少々面倒です。列指定はタイトルの文字で指定できたら楽ですよね。そこで、スプレッドシートのデータをJSON形式でHTML側へ送ってあげて、JSONデータとして扱えば、HTML側の処理が少し楽になります。

今回はこれに加えて、スプレッドシートのデータをJSON形式として、Web APIとして配信もしてみたいと思います。

1 今回使用するスプレッドシート
- 1.1 スプレッドシートサンプル
- 1.2 注意事項
2 これまでの処理方法
- 2.1 GAS側のサンプルコード
- 2.2 HTML側のサンプルコード
3 JSONで処理する方法
4 JSONで配信する方法
- 4.1 GAS側のサンプルコード
- 4.2 SheetAsJsonを使ってみる
5 関連リンク

今回使用するスプレッドシート

スプレッドシートサンプル

今回使用するスプレッドシートのサンプルには3つのプロジェクトが入っています。それぞれ、JSONでデータを処理するサンプルと、JSONデータを配信するサンプルとなっています。

注意事項

今回のスプレッドシートを使用する為には、スプレッドシートをコピーした後に一度セットアップが必要です。このセットアップは２つのプロジェクトがあるので、それぞれセットアップを実行が必要です。

スプレッドシートを開くとメニューに「JSON配信セットアップ」と「JSON処理セットアップ」が表示されます。
それぞれの中にある「スタート」を実行してセットアップは完了です。
この処理で、それぞれのプロジェクトプロパティにスプレッドシートのIDが格納され、スクリプト内で利用されます。

図：セットアップを実行しましょう

これまでの処理方法

これまでは、スプレッドシートのデータをガッツリ取得してそのまま、HTML側へ渡してあげてました。その為データは２次元配列の形式で取得する事になります。二次元配列ですので、データはdata[行番号][列番号]で指定する事で、そのセルの値を取り出す事が出来ます。

GAS側のサンプルコード

//HTMLで作ったフォームを表示する
function doGet(){
  var output = HtmlService.createHtmlOutputFromFile("index").setSandboxMode(HtmlService.SandboxMode.IFRAME);
  return output;
}

//スプレッドシートデータを返してあげる
function sheetdata(){
  //スプレッドシートデータを取得する
  var Properties = PropertiesService.getScriptProperties(); 
  var id = Properties.getProperty("mysheetid");
  var sheet = SpreadsheetApp.openById(id).getSheetByName("data");
  var ss = sheet.getRange("A2:D").getValues();
  
  //データを返す
  return JSON.stringify(ss);

}

//HTMLで作ったフォームを表示する

function doGet(){

var output = HtmlService.createHtmlOutputFromFile("index").setSandboxMode(HtmlService.SandboxMode.IFRAME);

return output;

}

//スプレッドシートデータを返してあげる

function sheetdata(){

//スプレッドシートデータを取得する

var Properties = PropertiesService.getScriptProperties();

var id = Properties.getProperty("mysheetid");

var sheet = SpreadsheetApp.openById(id).getSheetByName("data");

var ss = sheet.getRange("A2:D").getValues();

//データを返す

return JSON.stringify(ss);

}

単純に取得したデータを最後にJSON.stringifyにて返してます。

HTML側のサンプルコード

<head>
  <script type="text/javascript">
  //シートデータ(二次元配列)を格納する為のグローバル変数
  var json = "";
  
  //スプレッドシート側からデータを取得する
  google.script.run.withSuccessHandler(onSuccess).sheetdata();
  
  //入力補完を作成する
  function onSuccess(data){
    //JSONデータを取得する
    var json = JSON.parse(data);
    
    //１行目のpointデータだけ表示する
    var html = "1行目のpointの値は" + json[0][3] + "です。";
    
    //データの反映
    alert(html);
    document.getElementById("jsonman").innerHTML = html;
  }
  </script>
</head>

<!-- スプレッドシートデータの反映 -->
<div id="jsonman"></div>

<head>

//シートデータ(二次元配列)を格納する為のグローバル変数

var json = "";

//スプレッドシート側からデータを取得する

google.script.run.withSuccessHandler(onSuccess).sheetdata();

//入力補完を作成する

function onSuccess(data){

//JSONデータを取得する

var json = JSON.parse(data);

//１行目のpointデータだけ表示する

var html = "1行目のpointの値は" + json[0][3] + "です。";

//データの反映

alert(html);

document.getElementById("jsonman").innerHTML = html;

}

</script>

</head>

HTML側表示時に、sheetdataを実行して返ってきた値をjson[0][3]として指定し、値を取り出しています。今回はpointの列の値を表示しているので、4列目（スクリプトでは0から数えるので3を指定する）の値をこれで取り出せています。pointが4列目にあることを知っていなければならないのです。ここがちょっと面倒な部分ですね。

JSONで処理する方法

JSON形式で返して上げてそこから値を取り出してみましょう。JSONの場合のHTML側での値の取得方法は、json[0].pointにて値を取り出す事が可能です。ですので列番号ではなく列の名前で取得が出来るのが特徴です。addressの列の値が欲しいならば、json[0].addressとなります。また[0]はレコードの番号なので、2レコード目ならば、json[1].addressとなります。

GAS側のサンプルコード

//HTMLで作ったフォームを表示する
function doGet(){
  var output = HtmlService.createHtmlOutputFromFile("index").setSandboxMode(HtmlService.SandboxMode.IFRAME);
  return output;
}

//JSONでデータを返す
function backjson() {
  //スプレッドシートデータを取得する
  var Properties = PropertiesService.getScriptProperties(); 
  var id = Properties.getProperty("mysheetid");
  var sheet = SpreadsheetApp.openById(id).getSheetByName("data");
  var ss = sheet.getDataRange().getValues();
  
  //タイトル行を取得する
  var title = ss.splice(0, 1)[0];
  
  //JSONデータを生成する
  return JSON.stringify(ss.map(function(row) {
    var json = {}
    row.map(function(item, index) {
      json[title[index]] = item;
    });
    return json;
  }));

}

//HTMLで作ったフォームを表示する

function doGet(){

var output = HtmlService.createHtmlOutputFromFile("index").setSandboxMode(HtmlService.SandboxMode.IFRAME);

return output;

}

//JSONでデータを返す

function backjson() {

//スプレッドシートデータを取得する

var Properties = PropertiesService.getScriptProperties();

var id = Properties.getProperty("mysheetid");

var sheet = SpreadsheetApp.openById(id).getSheetByName("data");

var ss = sheet.getDataRange().getValues();

//タイトル行を取得する

var title = ss.splice(0, 1)[0];

//JSONデータを生成する

return JSON.stringify(ss.map(function(row) {

var json = {}

row.map(function(item, index) {

json[title[index]] = item;

});

return json;

}));

}

取得したデータをJSON形式に加工してあげています。そのデータをJSON.stringifyにてHTML側へ渡しています。その為別途タイトル行のデータはtitleとして取得させています。これがポイントです。

HTML側のサンプルコード

<head>
  <script type="text/javascript">
  //シートデータ(JSON)を格納する為のグローバル変数
  var json = "";
  
  //スプレッドシート側からデータを取得する
  google.script.run.withSuccessHandler(onSuccess).backjson();
  
  //入力補完を作成する
  function onSuccess(data){
    //JSONデータを取得する
    var json = JSON.parse(data);
    
    //１行目のpointデータだけ表示する
    var html = "1行目のpointの値は" + json[0].point + "です。";
    
    //データの反映
    alert(html);
    document.getElementById("jsonman").innerHTML = html;
  }
  </script>
</head>

<!-- スプレッドシートデータの反映 -->
<div id="jsonman"></div>

<head>

//シートデータ(JSON)を格納する為のグローバル変数

var json = "";

//スプレッドシート側からデータを取得する

google.script.run.withSuccessHandler(onSuccess).backjson();

//入力補完を作成する

function onSuccess(data){

//JSONデータを取得する

var json = JSON.parse(data);

//１行目のpointデータだけ表示する

var html = "1行目のpointの値は" + json[0].point + "です。";

//データの反映

alert(html);

document.getElementById("jsonman").innerHTML = html;

}

</script>

</head>

HTML側表示時にbackjson()を実行して、JSONデータを取得しています。データの１行目のpointデータは、json[0].pointとして直接取得出来ます。pointの列を直接列の名前で指定が出来るので楽ちんですね。コードがわかりやすくなります。こうする事で、今後のGoogle Apps Scriptでのウェブアプリケーション作成が捗ります。

ポイント

特にスプレッドシートの列を入れ替えたりした場合には、コードの修正が不要になる点と、列を追加しても追加部分の対応だけコードを修正すれば済むので、列の位置を意識する必要がないので、アプリを拡張しやすくなります。配列では全てのコードを修正しなければならないので、結構これが苦労します。

また、申請フォームのような場合、HTML側でJSON形式にするコードで整形してあげられれば、GAS側でデータ書き込み時に配列を組む時にも、コードがわかりやすくなります（データの追加などは配列形式でなければならない為）。

JSONで配信する方法

Google Apps Scriptでフォームを作らず、別の外部アプリケーションから利用出来るようにWeb APIとして配信する事も可能です。スプレッドシートのデータを他のアプリから参照がしやすくなるので、便利です。この場合、doGet()だけでなくdoPost()なども利用する事で、Web APIがより設計しやすくなります。

また、Web APIなので通常はdoGet()などに引数を渡して、特定の行でマッチするレコードの特定の値を返せ　といった事も可能になります。詳細についてはまた別途doGetとdoPostの項目で取り上げたいと思います。今回は単純に全データを返すようにしています。

GAS側のサンプルコード

//JSONデータをWeb APIとして配信する
function doGet(){
  var json = backjson();
  return ContentService.createTextOutput(JSON.stringify(json, null, 2))
  .setMimeType(ContentService.MimeType.JSON);
}

//JSONでデータを返す
function backjson() {
  //スプレッドシートデータを取得する
  var Properties = PropertiesService.getScriptProperties(); 
  var id = Properties.getProperty("mysheetid");
  var sheet = SpreadsheetApp.openById(id).getSheetByName("data");
  var ss = sheet.getDataRange().getValues();
  
  //タイトル行を取得する
  var title = ss.splice(0, 1)[0];
  
  //JSONデータを生成する
  return ss.map(function(row) {
    var json = {}
    row.map(function(item, index) {
      json[title[index]] = item;
    });
    return json;
  });

}

//JSONデータをWeb APIとして配信する

function doGet(){

var json = backjson();

return ContentService.createTextOutput(JSON.stringify(json, null, 2))

.setMimeType(ContentService.MimeType.JSON);

}

//JSONでデータを返す

function backjson() {

//スプレッドシートデータを取得する

var Properties = PropertiesService.getScriptProperties();

var id = Properties.getProperty("mysheetid");

var sheet = SpreadsheetApp.openById(id).getSheetByName("data");

var ss = sheet.getDataRange().getValues();

//タイトル行を取得する

var title = ss.splice(0, 1)[0];

//JSONデータを生成する

return ss.map(function(row) {

var json = {}

row.map(function(item, index) {

json[title[index]] = item;

});

return json;

});

}

今回はHTML側が存在しません。doGetでアクセスした場合、HTML ServiceではなくContent Serviceクラスを使って文字列を出力する仕組みになっています。出力をする時に予め作っておいたデータをJSON.stringifyにてアウトプットしています。その為、backjson()の中では、JSON.stringifyで渡す必要はありません。

この時、.setMimeTypeにてJSONのMIME TYPEを指定する必要があります。JSONのMIME TYPE指定は「ContentService.MimeType.JSON」となります。

図：JSON形式でWeb APIとして配信するとこう見えます

SheetAsJsonを使ってみる

結構前からリリースされてる、綺麗に整形されたJSONファイルとして出力してくれるものが、SheetAsJsonです。使い方はとても簡単で以下のような手順です。現在はGithubでもメンテされています。

スプレッドシートのスクリプトエディタ画面で、SheetAsJsonのコードを貼り付ける
ウェブアプリケーションとして導入し、公開する
ウェブアプリケーションのURLにGETパラメータをつなげてURLを叩く
JSON形式でデータが返って来る

そのGETのパラメータですが、以下のようなURLを組み立てて送信します。公開URLにスプレッドシートのIDと対象のシート名をつなげて投げてあげると、JSONに変換されて返ってくるという前項とは少しタイプの違う仕組みですね。

https://script.google.com/macros/s/ウェブアプリケーションのID/exec?id=スプレッドシートのID&sheet=シート名

よって、ウェブアプリケーションとして導入をしただけでは、何もしてくれませんので、必ずパラメータを後ろにつなげて送ります。WebAPIのようなものですね。すると、JSONデータが返って来る仕組みです。これをNode.jsなどでfetchして、値を取り出すわけです。返ってくるデータは以下のような感じになります。

{"data":[
	{"id":1,"name":"hiro","address":"東京","point":2},
	{"id":2,"name":"taro","address":"青森","point":3},
	{"id":3,"name":"saburo","address":"北海道","point":6},
	{"id":4,"name":"shiro","address":"神奈川","point":3},
	{"id":5,"name":"kuro","address":"岐阜","point":45},
	{"id":6,"name":"rokurou","address":"愛知","point":12},
	{"id":7,"name":"hanako","address":"徳島","point":4},
	{"id":8,"name":"reiko","address":"鳥取","point":4},
	{"id":9,"name":"miki","address":"鹿児島","point":2},
	{"id":10,"name":"yukiyo","address":"沖縄","point":1},
	{"id":11,"name":"miyo","address":"長野","point":4},
	{"id":12,"name":"shouko","address":"アメリカ","point":3}
]}

{"data":[

{"id":1,"name":"hiro","address":"東京","point":2},

{"id":2,"name":"taro","address":"青森","point":3},

{"id":3,"name":"saburo","address":"北海道","point":6},

{"id":4,"name":"shiro","address":"神奈川","point":3},

{"id":5,"name":"kuro","address":"岐阜","point":45},

{"id":6,"name":"rokurou","address":"愛知","point":12},

{"id":7,"name":"hanako","address":"徳島","point":4},

{"id":8,"name":"reiko","address":"鳥取","point":4},

{"id":9,"name":"miki","address":"鹿児島","point":2},

{"id":10,"name":"yukiyo","address":"沖縄","point":1},

{"id":11,"name":"miyo","address":"長野","point":4},

{"id":12,"name":"shouko","address":"アメリカ","point":3}

]}

GoogleスプレッドシートのデータをJSONで取得する【GAS】

今回使用するスプレッドシート

スプレッドシートサンプル

注意事項

これまでの処理方法

GAS側のサンプルコード

HTML側のサンプルコード

JSONで処理する方法

GAS側のサンプルコード

HTML側のサンプルコード

ポイント

JSONで配信する方法

GAS側のサンプルコード

SheetAsJsonを使ってみる

関連リンク

コメントを残すコメントをキャンセル

Google Apps ScriptでRSSリーダーを作る【GAS】

Chromeは拡張機能でより便利になる

今回使用するスプレッドシート

スプレッドシートサンプル

注意事項

これまでの処理方法

GAS側のサンプルコード

HTML側のサンプルコード

JSONで処理する方法

GAS側のサンプルコード

HTML側のサンプルコード

ポイント

JSONで配信する方法

GAS側のサンプルコード

SheetAsJsonを使ってみる

関連リンク

コメントを残す コメントをキャンセル

Google Apps ScriptでRSSリーダーを作る【GAS】

Chromeは拡張機能でより便利になる

コメントを残すコメントをキャンセル