経緯
Google Driveで無料で制限なくデータベースを使えないかと思って探していたのがきっかけです。目的を満たす候補としてFusion tableがありましたが、あれは専用のウェブUIがいつまで経っても古めかしく、かつプログラム上の利用に微妙な制限もありいつか廃止されるのではという不安、かといってGoogleが推し出している諸々のデータベースは基本的には課金制でちょっとした利用には手順が多く面倒。ドライブ内であればスプレッドシートを使えば済むことですが、そこからデータをプログラム上で自由に取り出して操作するには煩雑になってしまい、いまいちピンと来ませんでした。
スプレッドシートを使った…という点では以前検索したところ、QiitaにSpreadsheetSQLをいうライブラリを開発し公開なさっている方の記事を見つけ、その方に触発されアイデアを参考にして開発を始めました。
きっと似たようなライブラリが世の中で多く作られているでしょうし自分が何番煎じになっているのかわかりませんが、一応実装完了したのでコッソリと試験的に公開してみようかと。
実装物
| 名前 | スクリプトID |
|---|---|
| SSheetDB | 1m-mnNjwCIxUCgoZxTeHpvICFyntLCs52IjSxfHGA-6MbBiym441opQ21 |
| JsonDB | 1GavkBlPnlyz4uu7GjgczEhsk5SyHOcQPPNQ_67AK-to3ii8X0i0T9MX4 |
| 説明書 |
※各々のGASプロジェクトで上記IDをライブラリにて指定して下さい。あるいは上記GASのプロジェクトを開いた後、ご自身のGoogle Driveにコピーしてお使い下さい。
特に一報などは気にせずご自由にしてくださって構いません。
利用方法
1, GASプロジェクトを開き、「リソース」メニューから「ライブラリ」を開く
2, 「ライブラリを追加」の入力欄に上記IDをコピペし、「追加」ボタンを押下する
ポイント
- 1スプレッドシートあたり複数のテーブル（または1JSONファイルあたり複数のテーブル）
- SQLの構文に近い書き方ができるメソッド群
- テーブル同士のJoinでちょっとだけ複雑なデータ検索が可能
- インターフェース・機能が同じJsonDBも同時実装
- データの制限はスプレッドシートのセル数・ドキュメントの文字数に依存
解説
SpreadSheetSQLの作者様の解説や同ライブラリをご覧になった方はおわかりと思いますが、参考にしただけあって基本の構造はSpreadSheetSQLをベースにさせていただきました。（パクリではなくリスペクトとさせて下さい・・・）
そこから独自の拡張をしたものが、拙作のライブラリです。
データの構造について
スプレッドシート1つにテーブル一つだけというのももったいないと思い、1シート=1テーブルとして見て、複数テーブルを読み出せるようにしています。既存のスプレッドシートのシートもテーブルとして読み出すことが可能です。
スプレッドシートはSSheetDBに、各シートはSSheetTableになります。
また、読み出しの高速化のためスプレッドシートの最初のシートに列数・行数を固定の位置にセットしています（※）。既存のスプレッドシートの場合、シートが追加されます。
※WorksheetオブジェクトのgetLastRow()やgetLastColumnを使って毎回列数や行数を取得していたところ、明らかに遅かったのでこのような仕様にした次第。
SSheetDBおよびJsonDBは次のような構造になっています。
| 最上位 | 下位 | 名前 | 機能 |
|---|---|---|---|
| SSheetDB(JsonDB) | S(スプレッド)シートDB（JSON DB） | スプレッドシートおよびJSONファイルを読み込み、その中のシート及び配列をテーブル化してまとめて管理する | |
| SSheetTable(JsonTable) | S(スプレッド)シートテーブル（JSONテーブル） | スプレッドシートの個々のシートおよびJSONの個々の配列をテーブル扱いにして検索・編集できるようにしたもの |
スプレッドシートおよびJSONを読み込むと～DBというオブジェクトが生成されます。その配下に一つひとつのシートおよび配列をにテーブルとして扱う～Tableというオブジェクトのインスタンスが生成されます。この～TableがSQL風の操作をできるようにしたものです。
JsonDBについて
データの保存には基本的にはスプレッドシートを使いますが、合わせて実装しているJsonDBにてJSONも使えます。こちらの場合、データ保存には生のJSONテキストのためにGoogleドキュメントを使っています。なおメソッド等のインターフェースは同じなので、両方を併用することも可能です。
※違いがあるとすると型でしょう。スプレッドシートで書式を明示すると、GASではそれが型として反映されます。（わかりやすいのは日付型と思われます）
そのため、既存のスプレッドシートを読み込んだりするとSSheetDBには予期せぬ型が存在することがあります。JsonDBではそれがありません。
JSONの保存形式は次のとおりです。
{
"テーブル名" : {
"column" : [a,b,c],
"data" : [["hoge","foo","bar",...], ... ]
},
...
}
プロパティ名を各レコードごとに持つと、テキストとしての長さが増えてドキュメントの制限に達するのが早くなってしまい無駄なので、実際の保存では二次元配列にしています。
テーブルとしての最大の保存数はスプレッドシート・ドキュメントに依存します。万が一制限に達してもライブラリで新しいDBを作成すればすぐに別のスプレッドシートおよびドキュメントに移行できます。
SSheetTable ←→ JsonTable間の移行も、事前に同じ列とレイアウトで作成しておけば可能です。
スクリプト上での使用について
利用上はSQLに慣れた人に配慮しました。メソッド類のインターフェースをSQLのキーワードの使い勝手になるべく近くなるように実装しました。
DBとして開く・テーブルを開く
まずはスプレッドシートおよびJSONをDBとして開くところから始まります。
//新規
var db = SSheetDB.newDB("DBの名前");
//既存
var db = SSheetDB.open("1234568feief");
新規の場合、指定した名前がそのままスプレッドシートおよびJSON（Googleドキュメント）のファイル名になります。既存のを開く場合はファイルIDを指定して下さい。
DBとして開いたら、任意のテーブルを取得します。
var nikki = db.table("日記");
var calendar = db.table("カレンダー");
テーブル名は、スプレッドシートの場合はシート名、JSONの場合は上述の形式の"テーブル名"となります。
新規テーブルを作成する
新規テーブルを作成することができます。
createTable(テーブル名, 列情報 [, データ])
の形式です。
//SSheetDBの場合
db.createTable("アイコン",{
"アイコン名" : "@",
"アイコンデータ" : "@",
"更新日" : "yyyy/mm/dd"
});
//JsonDBの場合
db.createTable("アイコン",["アイコン名", "アイコンデータ","更新日"],データ);
SSheetDBの場合、列の指定にはJSON形式で指定します。列の形式を指定する必要がありますが、スプレッドシートの書式の指定内容をそのまま用いることができます。JsonDBではそれがなく、列名のみの配列です。
また、JsonDBでは初期化目的でデータをセットすることもできます。（不要な場合は空の配列）
検索をする
例）
日記テーブルから日付＝2017/12/01～15、天気＝晴れで抽出し、日付、時間、タイトル、内容を取得する。並びは日付、時間で昇順。
・一般的なSQL
select 日付, 時間, タイトル, 内容
from 日記
where (日付 between '2017/12/01' and '2017/12/15')
and (天気 = '晴れ')
order by 日付, 時間;
・拙作のライブラリ
var 日記帳 = SSheetDB.open("スプレッドシートのID");
var 日記 = 日記帳.table("日記");
日記.select(["日付","時間","タイトル","内容"])
.where({
"日付" : ["between", "2017/12/01", "2017/12/15"],
"天気" : ["==", "晴れ"]
})
.sort([{"日付":true}, {"時間":true}])
.result();
※どちらも紹介用にあえて日本語表記をしています
ポイントはwhereメソッドです。JSONの構文を利用し、「列名 : [ 演算子, 値, (値...)]」という表記にしてSQLに近い書き方と見やすさを実現させました。こうすることでbetweenやinの時の複数の値の表記を実現させています（[や{や:がちげーよというツッコミは除きます）。
なお複数列の場合はANDしか現状使えませんが、ひとまずはこれにより列を複数指定してデータをより複雑な検索することができます。
また、列の抽出には * も使えます。[]の中に列名を記述する代わりに"*"を指定することで全項目を抽出できます。
書式を整えるおよび簡易関数機能（フォーマット文字列）
また、スプレッドシートの場合フォーマットを指定したセルを読み出すとGAS上ではその形式にもとづいた型のデータになります。例えば、日付型にした場合はDateオブジェクトのデータが取得・判定条件になったりします。
whereで判定に用いる場合、日付型の場合は簡単に年月日・時間のみの判定をしやすくするため、独自の書式を実装しました。":%"の後に特定の文字列を付与します。その他、最低限必要な文字列関数的なものも用意しています。
//---年月日だけで判定したい場合
"日付:%ymd" : ["==", new Date(2018,0,15)]
//---時分秒のみ
"日付:%hms" ...
//---フル桁
"日付:%ymdhms" ...
//---指定の位置から桁数分文字列を抽出：「タイトル」を1文字目から５文字分抽出
"タイトル:%substr,1,5" ...
本来であれば関数的な機能を実装してもよかったのですが、そこまで本格的なものにする気はないので、あくまで簡易的な表記による機能としました。
エイリアス（別名）
selectでは列の指定で合わせてエイリアスも指定可能です。次のような表記になります。
var 日記帳 = SSheetDB.open("スプレッドシートのID");
var 日記 = 日記帳.table("日記");
日記.select(["日付","時間",{col:"タイトル",alias:"件名"},"内容"])
.where({...})
.sort([...])
.result();
検索の例で示した内容ですが、この場合「タイトル」を「件名」に置き換えて結果を取得します。
テーブルを結合する
また、テーブル同士をJoinして結合することもできます。
例）日記テーブルを上記例の通り検索、アイコンテーブルを参照し、日記テーブルの天気とアイコンテーブルのアイコン名で結合、アイコンデータを取り出して合わせて取得する。
・一般的なSQL
select 日記.日付, 日記.時間, アイコン.アイコンデータ, 日記.タイトル, 日記.内容
from 日記 left join アイコン on 日記.天気 = アイコン.アイコン名
where (日付 between '2017/12/01' and '2017/12/15') and (天気 = '晴れ')
order by 日付, 時間;
・拙作のライブラリ
var 日記帳 = SSheetDB.open("スプレッドシートのID");
var 日記 = 日記帳.table("日記");
var アイコン = 日記帳.table("アイコン");
日記
.join(アイコン.select(["アイコンデータ"]),["アイコン名,天気"])
.select(["日付","時間","アイコンデータ","タイトル","内容"])
.where({
"日付" : ["between", "2017/12/01", "2017/12/15"],
"天気" : ["==", "晴れ"]
})
.sort([{"日付":true}, {"時間":true}]).result();
ポイントはjoinメソッドです。selectよりも前に指定していますが、実際はどの位置でも問題ありません。そしてjoin内は次のようになっています。
join( 別テーブル（where, sortの指定も可）, 結合する列名 )
1番目の引数には、別のテーブルのselect().where()...の指定がそのままできます。ただしresult()は指定しません。そして2番目の引数で、結合の基準となる列名を指定します。
そして本来のselectで、取得する列名を指定します。この時点で各テーブルにある列をすべて指定可能です。（詳しい使い方は説明書を用意しております）
追加する
例）日記テーブルに2017/12/31の日付でデータを挿入する
・一般的なSQL
insert into 日記
(日付, 天気, タイトル, 内容)
values
('2017/12/31','曇り','散歩してきた','...')
;
・拙作のライブラリ
var 日記帳 = SSheetDB.open("スプレッドシートのID");
var 日記 = 日記帳.table("日記");
日記
.insert([
{
"日付" : "2017/12/31",
"天気" : "曇り",
"タイトル" : "散歩してきた",
"内容" : "..."
},{
"日付" : "2018/01/01",
"天気" : "晴れ",
"タイトル" : "初詣してきた",
"内容" : "..."
}
]);
insertには配列で受け渡すことができ、一度に複数のデータを追加できます。
更新する
例）日記テーブルの2017/12/01～2017/12/15間の"曇り時々雨"の天気をすべて"雨"に変更する
・一般的なSQL
update 日記
set
天気 = '雨'
where
日付 between '2017/12/01' and '2017/12/15'
and 天気 = '曇り時々雨'
;
・拙作のライブラリ
var 日記帳 = SSheetDB.open("スプレッドシートのID");
var 日記 = 日記帳.table("日記");
日記
.update({
"天気" : "雨"
},{
"日付" : ["between", "2017/12/01", "2017/12/15"],
"天気" : ["==","曇り時々雨"]
});
update～whereという形式ではありませんが、update(更新内容, 条件)というように書き方としては近くなるようにしています。
また、削除はdeleteRow()です（delete自体は予約語だったのでそのまま使えず、このようにしました）。引数は条件のみでwhereやupdateと同じ形式です。
update()とdeleteRow()は処理された件数を返します。また実際には更新しないupdateCount()を用意しています。これを使えば事前に処理件数を取得することが可能です。
var 日記帳 = SSheetDB.open("スプレッドシートのID");
var 日記 = 日記帳.table("日記");
var cnt = 日記.updateCount({
"日付" : ["between", "2017/12/01", "2017/12/15"],
"天気" : ["==","曇り時々雨"]
});
実例
社内の様々なユーザーマスタを同ライブラリでスプレッドシートおよびJSONにまとめ、別のGASのウェブアプリで用意した検索画面で検索できるようにしています。よく参照される列のみ抽出して閲覧できるようにするなど一通りの機能をテストする目的の場でもありました。
プライベートでは、家計簿アプリ、体重管理アプリ（いずれもGAS製ウェブアプリ）で使っています。
無制限で簡易的なDBを使うという点では自分の要望を達成しているので満足しています。
今後の実装予定
簡易DBのためのインターフェースという位置づけですが、可能な限り基本を抑えておきたいと思っています。
- 条件のAND/ORの正しい実装
上記のように書き味はSQLに近くなるように工夫していますが、現在は強制的にANDです。ORも実装を考えています。それにともないもう少し複雑・自由度の高い書き方をできるようにと考えています。
- commitの正しい実装（およびトランザクション）
現在、commitはJsonDBにしか実装していません。SSheetDB・SSheetTableは、データの更新をすると実際のスプレッドシートにも即座に反映されます。対してJsonDB・JsonTableはcommitをしない限りは読み出した直後の状態に戻すことが可能です。（内部的な保持やスプレッドシートへの実際の反映のタイミング、動作速度の問題に悩んでいたので・・・）
- group byの実装
グループ化・集計周りも実装を考えています。開発中としてソースにはありますが、まだ非公開のメソッドで動作は保証しません。詳細を検討中です。
- 動作の高速化
ここ2年ほどでGASは着実に動作速度を高めている感じですが、それでも基本的には遅いので、必要以上に遅くならないようにどうにかして工夫中です。
終わりに
有償のDBに課金するなどして本格化に使うほどではないけれど、ちょっとしたウェブアプリで無料無制限でデータベースを使いたい、という場合にGoogle Drive、スプレッドシートの活用を思いついたら、合わせて使っていただけると便利かと。制限はGoogle Driveに依存するので、Google DriveおよびGoogleApps Scriptがサービス終了しない限りは使い続けることができます。
GASを愛する多くの方に見知ってもらえたら幸いです。改造は大歓迎です。また、GAS初心者の方々には学習の素材になれれば幸いです。
この投稿を作成する段階で検索して気づいたのですが、元々はSpreadSheetDBと命名していたのですが、すでに別のサービスで名前が使われていました。そのため慌てて名称変更。とはいえ元のスプレッドシートという名称から離れても仕方ないので、短縮してSSheetとしました。