データベース代わりに使うならドキュメントよりスプレッドシートの方が便利
以前、GoogleドキュメントにWebブラウザからデータをPOSTする記事を書きました。
-
-
ブラウザからGoogle DocumentにPOSTする
ブラウザ（Chrome）からAjaxでGoogle DocumentにデータをPOSTするコードです。ローカルWebアプリのデータ保存とかに使えます。
続きを見る
今度はGoogleスプレッドシートとJSONデータの読み書きができるようにしてみました。Webアプリのデータベース代わりに使うならスプレッドシートの方が便利でしょう。
通常、Webアプリケーションでデータを保存して後から再利用したいとなると、サーバーを立ててMySQLとかを使うのが普通だと思います。
そうすると急にハードルが上がります。
Webアプリはサクっと作れるのがいいところなので、できればサーバーもデータベースを使わずに書きたいのです。
もちろんデータ件数が万単位とか高度な検索や抽出をしたいなら別ですが。
GoogleスプレッドシートのデータをWebアプリから読み書きできれば、結構いろんな要素に使えそうです。
フロントエンドもGoogle Apps Scriptで書くという手もあるのですが、GASで凝ったことをしようとするとちょっと大変なんですよね。
そんなわけで今回のプログラムです。
表全体のデータを一括で読み出し、書き込み
今回はとりあえず、
- GETリクエストで任意のシートの全データを読み込む
- POSTリクエストで任意のシートにデータを書き込む
の2つの機能を付けました。
動作を動画にしてみました。
GETで表全体のデータを取得
GETボタンを押すとGoogleスプレッドシートの1番の表の全データが読み込まれます。
データは、最初の行をキーとしたオブジェクトの配列になっています。
例えば、以下のような表であれば、
| name | age |
| 鈴木 | 18 |
| 佐藤 | 30 |
json
[ { name:"鈴木", age:18} , { name:"佐藤", age:30} ]
というデータが得られます。
取得したデータはtextareaに表示されるほか、「dataObj」というグローバルオブジェクトに入るので、ブラウザ上で自由にデータを扱えます。
POSTでテキストエリアの内容を表に書き込み
POSTボタンを押すとテキストエリアの内容をJSON.parseしてGoogleスプレッドシートに書き込みます。
テキストエリアの内容がJSONかどうか try...catch で簡易チェックをしています。
テストでは上の動画のようにGETボタンで取得したデータを編集して書き戻すのがいいでしょう。
既存のシートの内容は全消去して上書きされます。
GASのレスポンスはものすごく遅いのでやりとりは最小減にする
GASはドキュメントやスプレッドシートなどのデータにアクセスするとものすごく遅くなります。一回の読み書きに秒単位で時間がかかったりします。
データの量にはあまり関係がないようです。単純に1回1回のアクセスが遅い感じです。
ですから検索とか並び替えをGoogleスプレッドシート側でやらせると、レスポンスが長くなりま、結構かったるいアプリケーションになってしまいます。
今回はローカルアプリからGASのWebアプリにリクエストをする形なのでさらに遅くなりそうです。
このため、今回のアプリケーションも、データの処理はローカルのオンメモリでやる前提にして、表のデータを一括で読み込み、一括で書き込むようにしています。
jQueryの$.ajaxでJSONデータをGET/POSTする
実際のコードは割とシンプルです。
まずはブラウザ用のプログラム。短いのでHTML内にscriptタグで記述しています。
お使いになるときは、END_POINTにGASでデプロイしたWebアプリケーションのアドレスを設定してください。
また、SHEET_NOは1から始まるGoogleスプレッドシートの番号です。これも任意に設定してください。
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Get/Post with Google SpreadSheet</title>
</head>
<body>
<p>GET and POST <br>
to Google SpreadSheet</p>
<textarea id="textArea" rows="5" cols="25"></textarea>
<br>
<button id="postButton">POST</button>
<button id="getButton">GET</button>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"
integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
<script>
//GAS WebアプリのURL
const END_POINT = "https://script.google.com/macros/s/*****************************/exec";
//読み書きするスプレッドシート（タブ）の番号
const SHEET_NO = 1;
document.getElementById("postButton").addEventListener('click', postToGAS, false);
document.getElementById("getButton").addEventListener('click', getFromGAS, false);
let dataObj = {};
function postToGAS() {
const Content = document.getElementById("textArea").value.trim();
console.log(Content);
//データがJSONかどうかのチェック
try {
const checkJSON = JSON.parse(Content);
if (checkJSON.length > 0 && Object.keys(checkJSON).length > 0) {
console.log("data is OK");
} else {
throw "data is not array of object";
}
}
catch (e) {
alert("error:" + e);
return;
}
//POST送信
$.ajax({
type: "POST",
url: END_POINT,
dataType: "json",
data: { sheetNo: SHEET_NO, data: Content }
})
.then(
(result) => { // 成功した時の処理
console.log(JSON.stringify(result));
},
(error) => { // 失敗した時の処理
alert('Error:' + JSON.stringify(error));
}
);
}
function getFromGAS() {
$.ajax({
type: "GET",
url: END_POINT,
data: { sheetNo: SHEET_NO }
}).done((result) => { // 成功した時の処理
dataObj = JSON.parse(result);
document.getElementById("textArea").value = result;
console.log("get done:" + result);
}).fail((error) => { // 失敗した時の処理
alert('Error:' + JSON.stringify(error));
}).always((data) => {// 常にやる処理
// do something
});
}
</script>
</body>
</html>
なお、備忘録代わりにPOSTとGETでajaxの成功時、失敗時の無名関数の書き方を変えています。最近はsuccessとかいう書き方はしないそうです。
Googleスプレッドシート側のプログラム
Googleスプレッドシート側のプログラムです。
スクリプト単体ではなく、スプレッドシートに付属のスクリプトとして設定します。
GASのお約束通り、doPost関数でPOSTアクセスを処理し、doGet関数でGETアクセスを処理します。
sheetNoのパラメータを省略した場合は1とみなします。
スプレッドシートへの書き込みには2次元配列が必要なので、POST時にはJSONを2次元配列に変換します。
GETの場合はシートから取得した2次元配列から1行目をキーにしたオブジェクトの配列を作ります。
1行目が見出し（キー）である、というルールで表を作ればプログラムを変更せずに対応できます。
見出しに空白文字が混じるとエラーになるので、実用化するにはアンダースコアに置換するなどの処理が必要ですね。
return ContentService.createTextOutputで返す値をJSONにしないとブラウザ側のエラーになります。
var ss = SpreadsheetApp.getActiveSpreadsheet();
function doGet(e) {
const reqParam = e.parameter;
const sheetNo = parseInt(reqParam.sheetNo) || 1; //シート番号のパラメーターがなければ 1 にする
sheet = ss.getSheets()[sheetNo - 1];
const data = sheet.getDataRange().getValues();
const keys = data[0];
data.shift();
const dataObj = data.map((array) => {
const temp = {};
keys.map((key, index) => {
temp[key] = array[index];
});
return temp;
});
const dataObj2 = {};
keys.forEach((key, index) => {
dataObj[key] = data.map((arr) => {
return arr[index];
});
});
return ContentService.createTextOutput(JSON.stringify(dataObj));
}
function doPost(e) {
const reqParam = e.parameter;
const sheetNo = parseInt(reqParam.sheetNo) || 1;
sheet = ss.getSheets()[sheetNo - 1];
var data = JSON.parse(reqParam.data);
postJsonToSpreadSheet(data);
return ContentService.createTextOutput(JSON.stringify({ result: "post done" })); //JSONを返すとエラーにならない？
}
function postJsonToSpreadSheet(arrObj) {
//受け付けるJSONは、[{key1:data1, key2:data2,....},...]、オブジェクトが配列になっている形式
//オブジェクトのキーがスプレッドシートの項目名として1行目に入力される
sheet.clear();
//タイトル行を書き込み
const keys = [Object.keys(arrObj[0])];//setValuesには必ず2次元配列を渡すので [ ] で囲んで2次元配列にする
sheet.getRange(1, 1, 1, keys[0].length).setValues(keys);//項目名を書き込み
//オブジェクトからデータ書き込み用の2次元配列を作成
const arrToWrite = arrObj.map((obj) => {
const arr = [];
for (const key of keys[0]) { arr.push(obj[key]); }
return arr;
});
const lastColumn = arrToWrite[0].length; //1個め配列の長さ＝カラムの数を取得する
const lastRow = arrToWrite.length; //行の数を取得する
sheet.getRange(2, 1, lastRow, lastColumn).setValues(arrToWrite);
}
作ってる時はつまらないところでたくさん躓きましたが、なんとか上手くいきました。
そのうち語学学習用の短文帳でも作ってみようかと思います。
【追記】その後、もうちょっと高機能なヤツを作りました
-
-
GoogleスプレッドシートをWebアプリのデータベース代わりに使う-その2【シート選択、配列orオブジェクト指定を追加】
Googleスプレッドシートのデータをブラウザから読み書きするアプリにシート選択やデータ形式の選択（配列／オブジェクト）機能を加えました
続きを見る