Googleのスプレッドシートを参照して他のスクリプト内で使用する方法がようやくなんとかなりそうなのでまとめてみました。
結論から言うと、旧スプレッドシートを使用してデータベースを作成し、Google Visualization APIという昔からある仕組みで取り出します。
いきなり脱線しますが、今回は結構ハマりました。(T_T)
元々はGoogle Apps Script のContentServiceの中のJSONを出す機能を使用しようと思ったのですが、どうもリダイレクトされて出力されるらしく、iPhoneから単純にXMLHttpRequestを使用してもダメでした。
そこで、グラフを書く為の仕組みの元となっているGoogle Visualization APIというのを使ってみよう、と思ったのですが
これがスプレッドシートの仕様変更前の情報しかありません。
Google Visualization APIを使用するにはスプレッドシートのURLの1部をスクリプト内で指定する必要があるのですが、そのURLの仕様自体が変わってしまっています。
どーしたものか・・・σ(^_^;)
と途方に暮れていたところ、
「旧スプレッドシートを作成するURL」というのを発見しました！
これは熱い！
ということで、かなりオカルトな手段ですが無事iPhoneのJavaScriptからスプレッドシートの情報を取得できそうです。
旧とつくぐらいですからいつまで使えるかはわかりませんが、ここに記録しておきます。
まずは今回のキモとなるのが旧スプレッドシートです。
新しい方が表計算ソフトとしてはかなりパワーアップしています。ですので普段使いではそのまま使う方が良いんですが。ただ、スプレッドシートのURLの仕組みが変わってしまっています。
こんな感じで
「https://docs.google.com/spreadsheets/d/{スプレッドシートのID}/edit?usp=docslist_api」
IDの表記がスラッシュで挟まれた感じです。皆さんのスプレッドシートもそうなっていると思います。
旧スプレッドシートを作成するにはまずGoogleにログインした状態で、
https://g.co/oldsheets
へアクセスします。
するとどうなるか。URLの感じが違います。
こんな感じで
「https://docs.google.com/spreadsheet/ccc?key={スプレッドシートのキー}&usp=docslist_api」
と、「key=」というのが出ました。これこそがまさにカギとなります。
このシートに使用したいデータベースを記載して、「ファイル」から「ウェブに公開」しておきます。
公開してしまうので機密性の高い場合は使用しない方が良いと思います。
後はスクリプトタグからJavaScriptで参照して行きます。
Google Visualization APIを使用するには定型的な記述をします。jQueryみたいな感じでしょうか。
不思議呪文をつらつら書きまして、使用したい列を指定し(並べ替えや抽出も出来るっぽいです！)、関数を呼び出す、という感じ。
んで、その関数内で欲しい情報を取り出す、という流れです。
今回私が作ったというスクリプトを見てみましょう。
iPhoneでスクリプトを作るのに便利なMyScriptsというアプリを使用しています。
MyScriptsへ登録は→こちら
詳しくは引用したサイト様かリファレンスのGoogleの英文を読むしかないのですが、
dataSourceという変数に先程作成したスプレッドシートの「key=」の部分を入れています。
末尾の方のgid=0の部分でシート番号を指定しています。
queryLanguage = 'select A, B, C'; の部分がそのまま列の指定になります。
使用したい列を記述しています。
関数handleResponseが値取得後に呼び出される仕組みになっています。
ここで、データベースを再度JSONにしてみました。
・・・おそらくは頑張れば取得したオブジェクトから直取り出来るんでしょうが、今のところあんまり情報がないので、引用サイト様にある値の取得に、ラベルの取得を追加した様なスクリプトになっています。
getNumberOfRows()が末尾の行数、getNumberOfColumns()が最大の列数を返してくれる模様ですので、ループの際に活用しています。
どうやらGoogle Visualization APIは勝手に見出し行を取得してくれる模様でして、その見出しはgetColumnLabel()から返ってくる様です。
私でもなんとかなりましたので、皆さんも是非チャレンジしてみては如何でしょうか？
どうやら、Google的にはグラフ機能を使って欲しいっぽいですけどね。リファレンス読む感じでは。
つーかね。
仕様が変わってからのフォローが足りない。
今後もどうなるかは微妙かもしれませんね。
今回のスクリプトもギリギリ動いている感じ。
実用化にはもうちょっと研究が必要な気がしています。
結論から言うと、旧スプレッドシートを使用してデータベースを作成し、Google Visualization APIという昔からある仕組みで取り出します。
ハマりポイント
いきなり脱線しますが、今回は結構ハマりました。(T_T)
元々はGoogle Apps Script のContentServiceの中のJSONを出す機能を使用しようと思ったのですが、どうもリダイレクトされて出力されるらしく、iPhoneから単純にXMLHttpRequestを使用してもダメでした。
そこで、グラフを書く為の仕組みの元となっているGoogle Visualization APIというのを使ってみよう、と思ったのですが
これがスプレッドシートの仕様変更前の情報しかありません。
Google Visualization APIを使用するにはスプレッドシートのURLの1部をスクリプト内で指定する必要があるのですが、そのURLの仕様自体が変わってしまっています。
▲皆さんお困りのご様子・・・。どうやら読み解けば解決している方もいる様ですが・・・。Issue 1476 - google-visualization-api-issues - Bug: New Sheets do not properly process Queries - Google Visualization API bug reports and feature requests - Google Project Hosting
...
どーしたものか・・・σ(^_^;)
と途方に暮れていたところ、
「旧スプレッドシートを作成するURL」というのを発見しました！
これは熱い！
ということで、かなりオカルトな手段ですが無事iPhoneのJavaScriptからスプレッドシートの情報を取得できそうです。
旧とつくぐらいですからいつまで使えるかはわかりませんが、ここに記録しておきます。
旧スプレッドシートを作成するURL
まずは今回のキモとなるのが旧スプレッドシートです。
新しい方が表計算ソフトとしてはかなりパワーアップしています。ですので普段使いではそのまま使う方が良いんですが。ただ、スプレッドシートのURLの仕組みが変わってしまっています。
https://docs.google.com/spreadsheets/d/11tMgEOxGjyf2ffolGrvybA-0zPS5xGA4IqPHgoFpbBg/edit?usp=docslist_api
こんな感じで
「https://docs.google.com/spreadsheets/d/{スプレッドシートのID}/edit?usp=docslist_api」
IDの表記がスラッシュで挟まれた感じです。皆さんのスプレッドシートもそうなっていると思います。
旧スプレッドシートを作成するにはまずGoogleにログインした状態で、
https://g.co/oldsheets
へアクセスします。
するとどうなるか。URLの感じが違います。
https://docs.google.com/spreadsheet/ccc?key=0AvxnySF4O9IzdHlsMFNYR21pRUJ2M1ZFU0MtMnJkTkE&usp=docslist_api
こんな感じで
「https://docs.google.com/spreadsheet/ccc?key={スプレッドシートのキー}&usp=docslist_api」
と、「key=」というのが出ました。これこそがまさにカギとなります。
このシートに使用したいデータベースを記載して、「ファイル」から「ウェブに公開」しておきます。
公開してしまうので機密性の高い場合は使用しない方が良いと思います。
後はスクリプトタグからJavaScriptで参照して行きます。
不思議な呪文
Google Visualization APIを使用するには定型的な記述をします。jQueryみたいな感じでしょうか。
↑こちらのサイトをコピペさせて頂きました。Google Spreadsheets を簡易 SQL DB に！「Google Visualization API」 - WebOS Goodies
皆さん、 Google Docs のガジェット機能はもう使ってみましたでしょうか。データをさまざまな方法で可視化するガジェットをシート上に配置できるというもので、このガジェットは自作することもできま...
不思議呪文をつらつら書きまして、使用したい列を指定し(並べ替えや抽出も出来るっぽいです！)、関数を呼び出す、という感じ。
んで、その関数内で欲しい情報を取り出す、という流れです。
今回私が作ったというスクリプトを見てみましょう。
iPhoneでスクリプトを作るのに便利なMyScriptsというアプリを使用しています。
#HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Google Visialization API テスト</title>
<style type="text/css">
table td { padding: 4px; width: 100px; }
</style>
</head>
<body>
<!-- Google AJAX API の共通ライブラリを読み込む（APIキーは不要） -->
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<!-- Google Visualization API でデータを読み込む一連の処理 -->
<script type="text/javascript">
// Google Visualization API のライブラリを読み込む
google.load("visualization", "1");
// データソース URL
var dataSource = 'https://spreadsheets.google.com/tq?key=0AvxnySF4O9IzdHlsMFNYR21pRUJ2M1ZFU0MtMnJkTkE&gid=0&pub=1';
// Query Language
var queryLanguage = 'select A, B, C';
// ページ読み込みの完了後にデータをリクエスト
google.setOnLoadCallback(function() {
var query = new google.visualization.Query(dataSource);
query.setQuery(queryLanguage);
query.send(handleResponse);
});
// データを取得できると、この関数が呼ばれる
function handleResponse(response)
{
var data = response.getDataTable();
//alert(JSON.stringify(data));
//Labelを取得して配列laへ入れます
var la =[];
for (var cc = 0; cc < data.getNumberOfColumns(); cc++){
la[cc] = data.getColumnLabel(cc);
};
//alert (la);
var csv = [];
for (var row = 0; row < data.getNumberOfRows(); row++)
{
var line = [];
for (var col = 0; col < data.getNumberOfColumns(); col++)
line.push(data.getFormattedValue(row, col));
csv.push(line);
//alert("line = " + line);
//alert("csv = " + csv);
};
//JOSNへ入れ直して配列jsonArrayへ入れます
var jsonArray = [];
for (i = 0; i < data.getNumberOfRows(); i++){
var livalu = csv[i];
var json = new Object();
for (j = 0; j < data.getNumberOfColumns(); j++){
json[la[j]] = livalu[j];
};
jsonArray.push(json);
alert(JSON.stringify(jsonArray));
};
}
</script>
<div id="result">読み込み中...</div>
</body>
MyScriptsへ登録は→こちら
詳しくは引用したサイト様かリファレンスのGoogleの英文を読むしかないのですが、
dataSourceという変数に先程作成したスプレッドシートの「key=」の部分を入れています。
末尾の方のgid=0の部分でシート番号を指定しています。
queryLanguage = 'select A, B, C'; の部分がそのまま列の指定になります。
使用したい列を記述しています。
関数handleResponseが値取得後に呼び出される仕組みになっています。
ここで、データベースを再度JSONにしてみました。
・・・おそらくは頑張れば取得したオブジェクトから直取り出来るんでしょうが、今のところあんまり情報がないので、引用サイト様にある値の取得に、ラベルの取得を追加した様なスクリプトになっています。
getNumberOfRows()が末尾の行数、getNumberOfColumns()が最大の列数を返してくれる模様ですので、ループの際に活用しています。
どうやらGoogle Visualization APIは勝手に見出し行を取得してくれる模様でして、その見出しはgetColumnLabel()から返ってくる様です。
私でもなんとかなりましたので、皆さんも是非チャレンジしてみては如何でしょうか？
どうやら、Google的にはグラフ機能を使って欲しいっぽいですけどね。リファレンス読む感じでは。
つーかね。
仕様が変わってからのフォローが足りない。
今後もどうなるかは微妙かもしれませんね。
今回のスクリプトもギリギリ動いている感じ。
実用化にはもうちょっと研究が必要な気がしています。
MyScripts 2.5
分類: 仕事効率化,ユーティリティ
価格: ￥400 (Takeyoshi Nakayama)
