これを読めば思い出す

漫画好きWebデザイナーのTipsブログ

テクノロジー

Googleスプレッドシートの内容をjsonデータとして利用する

Googleスプレッドシートで気軽に編集できるデータをwebサイト上で利用できたら色々と便利だなと思って調べたら出来そうだったのでチャレンジしてみたのが今回のテーマ。

Google Apps Script という Googleが提供するサービスに対してアレコレできるスクリプトがあり、それを使って今回の目的を実現させます。

スマホで見る

Googleスプレッドシートの内容をjsonデータとして利用する

目次

はじめに

今回使用する技術等の処理の流れを図で表すと上図のようになる。
ここでは詳細説明省きますが、本記事の最後まで実践したあとにこの図を見て関係性の整理に役立ててもらえると幸いです。

以下、関連用語の参考リンクです。

用語集

  • Google Apps Script … Googleが提供するサービスに対してアレコレできるスクリプト
  • Google Spreadsheets … Google版 Excelみたいなもの
  • jQuery … JavaScriptを便利に使いやすくするためのライブラリ
  • ajax … DHTML(JavaScript + CSS) と XMLHttpRequest にサーバーサイドのウェブアプリケーションとを加えたあわせ技の総称
  • jsonp … scriptタグを使用してクロスドメインな（異なるドメインに存在する）データを取得する仕組みのこと（JSON with padding の略称）
  • json … 様々な場面や環境でデータの受け渡しができるよう設計されたデータファイル（JavaScript Object Notationの略称）

Google Apps Script に関しての参考サイト

01. 事前準備

Googleアカウントを持ちスプレッドシートを作れるようにする

これが無いと始まらないのでちゃちゃっと準備します。

テキストエディタ用意

はっきり言って何でも良いけど最低限使いやすいもの（シンタックスハイライトがされる、履歴が複数回戻れる – winのメモ帳以外）が良いと思います。個人的には Sublime Text が使いやすくてオススメ。

ブラウザ用意

モダンブラウザ であれば特に問題は無いだろうけど、Chrome であればスプレッドシート等の相性がばっちりなので安心。

02. 作業手順

  1. 02-01. スプレッドシート作成
  2. 02-02. スクリプトエディタ準備 （Google Apps Script）
  3. 02-03. ウェブアプリケーションとして導入 （公開）
  4. 02-04. ローカルhtmlからデータ取得を試す

02-01. スプレッドシート作成


Googleドライブ – マイドライブ で右クリック → 空白のスプレッドシートを作成（https://docs.google.com/spreadsheets/create から直接作成でもok）


データを入力（仮にidとname列を入力）し、スプレッドシートの名前やシート名を変える

02-02. スクリプトエディタ準備 （Google Apps Script）


「ツール ＞ スクリプトエディタ」からエディタを立ち上げる


エディタ遷移後の最初の画面


エディタ内の記述をまるっと下記のスクリプトに書き換えて保存。スプレッドシートのURLとシート名だけ先ほど作成した参照対象のものを記入。スクリプトの内容はスプレッドシート内容をjsonデータ化するもの

GStoJSONのスクリプト


function getData(id, sheetName) {
  var sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
  var rows = sheet.getDataRange().getValues();
  var keys = rows.splice(0, 1)[0];
  return rows.map(function(row) {
    var obj = {}
    row.map(function(item, index) {
      obj[keys[index]] = item;
    });
    return obj;
  });
}

function doGet(request) {
  var func = 'jsondata';
  var data = getData('xxx_スプレッドシートのURL_xxx', 'Sheet1');
  return ContentService.createTextOutput(func + '(' + JSON.stringify(data, null, 2) + ')')
  .setMimeType(ContentService.MimeType.JAVASCRIPT);
}

02-03. ウェブアプリケーションとして導入 （公開）


「公開 ＞ ウェブアプリケーションとして導入」を選択


プロジェクトの説明は適当に書いて、実行者は自分、アプリケーションにアクセスできるユーザーは「全員（匿名ユーザーを含む）」を選択


「導入」ボタン押下後、承認を求められたら「許可を確認」に進む（求められないときもある…？）


対象のアカウントを選択


「詳細」を選択


直前まで触っていた「スクリプト（安全ではないページ）に移動」を選択


「許可」を選択


ちなみにインフォメーションマークではこんな注意書きが表示される


以上の手順でスクリプトが有効になり、スプレッドシート内のデータ参照 → json化が可能になる。表示されているURLや「最新コード」から実行画面が見れる


アクセスするとこのようにスプレッドシート内のデータがjson形式で表示されれば成功


「自分のプロジェクト」からもスクリプトの確認ができる

02-04. ローカルhtmlからデータ取得を試す


ウェブアプリケーションのURLをコピーする （この画面を閉じてしまったあとでも「公開 ＞ ウェブアプリケーションとして導入」からURLを確認できる）


エディタは何を使ってもよいので以下のコードをコピペして「url: ‘[ココ]’」の値を自分で用意したウェブアプリケーションのURLを入力し、「index.html」として保存・実行する

index.html


<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>Googleスプレッドシートの内容をjsonデータとして利用する</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
</head>
<body>
<div id="whole">
<!-- // -->
</div>
<script>
// ページ読み込み後の処理
window.onload = function(){
	// 【main-script】 を実行
	getJsonp_GAS();
}

// 【main-script】 スプレッドシート内の記述をjsonデータとして読み込み html 内へ入れ込む
function getJsonp_GAS() {
	$.ajax({
		type: 'GET',
		url: 'https://script.google.com/macros/s/xxxxxxxxxxxxxxxxxxxx/exec',
		dataType: 'jsonp',
		jsonpCallback: 'jsondata',
		success: function(json){
			var len = json.length;
			var html = '';
			for(var i=0; i < len; i++){
				html += json[i].id + ' ' + json[i].name + '<br>';
			}
			document.getElementById('whole').innerHTML = html;
		}
	});
}
</script>
</body>
</html>

以前jsonデータを参照するのに jQuery 使用せず にやってたから今回の JSONP ケースでも同じようにやりたかったけど仕様に対して理解が足らず jQuery 使った方がすんなりいけたので素直に頼ることに。（そのうち jQuery 使わない方法が出来て気が向いたらまとめます）

03. 結果


前項で作成した index.html を実行後、ブラウザ内にこんな感じでスプレッドシートの内容が表示されれば成功

04. 発生したエラー

振り返りのためのエラーメモ。

実行環境はhtmlソースがローカルにある状態で「Google Apps Script | スプレッドシート」のjsonデータ参照しようとしてました（時にサーバー上にjsonファイル置いて参照できるか試したり、とか）

◆jsonファイル参照するソースで試した時のエラー

(index):25 [Deprecation] Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience. For more help, check https://xhr.spec.whatwg.org/.

getJSON @ (index):25
onclick @ (index):34
(index):26 Failed to load 

https://script.google.com/macros/s/xxxxxxxxxx/exec: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:3000' is therefore not allowed access.

getJSON @ (index):26
onclick @ (index):34
(index):26 Uncaught DOMException: Failed to execute 'send' on 'XMLHttpRequest': Failed to load 'https://script.google.com/macros/s/xxxxxxxxxx/exec'.
    at getJSON (http://localhost:3000/:26:9)
    at HTMLInputElement.onclick (http://localhost:3000/:34:81)

昔の記事 の方法でGASを参照をしようとして表示されたエラー。たぶん参照先にアクセスできないよーという内容（※後述のjsonp対応で合わせて改善）

◆Refused to execute script

2018.04.25wed 00:02
Refused to execute script from 'https://script.googleusercontent.com/macros/echo?user_content_key=xxxxxxxxxx' because its MIME type ('application/json') is not executable, and strict MIME type checking is enabled.

Refused to execute script from '<スクリプトのURL>' because its MIME type ('text/html') is not executable, and strict MIME type checking is enabled.
… 「MIMEタイプ(text/html)が実行不可能であり、厳密なMIMEタイプの設定が有効な為、スクリプトの実行は遮断されました。 」

Google Apps Script – function doGet内末尾 の方の記述変更で回避（※後述のjsonp対応で合わせて改善）

◆Google Apps Scriptの公開権限や無理やりな参照を繰り返してて発生したエラー

このスクリプトの OAuth ID は削除されたか、無効になっています。利用規約違反が原因である可能性があります。

たぶんガチャガチャいじり過ぎてロック的なものがかかってしまったのではないかと予想。別の Google Apps Script（GAS）使うというその場しのぎの対処法を取った。

【＊＊＊ 具体的なjsonp対応とは、mb20等の制御（!important） ＊＊＊】

◆jsonp対応後、ChromeでうまくいったけどFirefoxでエラー

＜script＞ のソース “https://script.google.com/macros/s/xxxxxxxxxx/exec?callback=jsondata&_=1524589803880” の読み込みに失敗しました。

・jsonデータに問題あるのか調べるためにkoreyome.com上にファイル置いて参照すると成功。Google Apps Scriptの方はダメ
・直で実行URLたたくとGoogleログインを求められる
└ Google Apps Script に 適当なGoogleアカウントでログイン状態にしてても権限を求められる → 全員に開放がされてない？
↓
結果、Scriptコード変更内容が更新されてなかった様子。
プロジェクト バージョンを新規作成して新しいウェブアプリケーションURL発行したもので試したらうまくいった。

05. jsonp作業の内容

  • jsonデータに「callback()関数」を付ける
  • jquery を使い jsonp の callback に合わせた書き方をする

大きくは上記の2点。細かい仕様までは理解できていないけど、とりあえずこうすれば別ドメイン間でもjsonデータのやり取りができるようになるんだなーと覚えた。

jsonデータに「callback()関数」を付ける

jquery を使い jsonp の callback に合わせた書き方をする

この jsonp、別ドメインからアクセス可能だし、今回の Google Apps Script は不特定多数のアクセスも許可の設定にしてるから重要なデータは扱わないようにするとか、用途に合わせてセキュリティ対策するなど使用時のリスクも合わせて考えといた方が良いかもですね。

参考サイト

Share