【GAS】SpeadSheetをDB代わりに使うWebサービスを作る際に考えたこと

はじめに

現在社内向けにGoogleWorkspace上のWebサービスを制作中で、データ管理をMySQLなどのDBではなくGoogle SpreadSheetで行う形にしています。
シートをデータ検索をしようと思った際、よく記事で

「getValuesで全体を配列に入れて処理すれば高速！」
「QUERY関数の方が更に高速！」

と割と便利そうなことは書かれているのですが、実際にやってみるとgetValuesはデータ量が増えれば比例してかなりの時間がかかるようになりますし、QUERY関数は便利ではあるものの専用にシートを使うので、同時編集されないようにロックなどを考える必要があります。
スプレッドシート操作はそこそこ時間がかかるので、ロックした場合は同時に複数の人がアクセスした際の懸念が残ります。

色々考えた結果、今回は以下の仕様でスプレッドシートを管理することにしました。

Googleアカウント名でユーザ毎に検索用シートを作る

スプレッドシートのQUERY関数はgetValuesと比べると格段に高速です。
データ量が数千件、一万件以上になったときのことを考えるとQUERY関数で検索機能を実装した方が良いでしょう。
参考：スプレッドシート内をGASで検索するのはQUERY関数利用が桁違いに爆速だった

ただこの関数を使う場合、関数を記述するためのシートが必要になります。
今回は、WebサービスにログインしているGoogleアカウント名、つまりメールアドレスでシートを作ることにしました。
初回はシートを作成する分だけ少し時間がかかります（気にするほどではないですが）。

var sheetName = Session.getActiveUser().getUserLoginId();
var ss = SpreadsheetApp.openById(SHEET_ID);
var ws = ss.getSheetByName(sheetName);
if(!ws){
    //末尾にシートを追加する
    ws = ss.insertSheet(sheetName,ss.getNumSheets());
}

スプレッドシート操作時はUserLockを行う

処理がぶつからないようにユーザロックをかけておきます。

var userLock = LockService.getUserLock();
if(userLock.tryLock(5000)){
//スプレッドシートでの検索処理
  userLock.releaseLock();
}

これにより、本人はロックをかけているので処理がぶつかることはなくなり、他のユーザは別のシートになるためQUERY操作しても問題ない状態を作ることができました。

注意点

シートを作った直後にQUERYをsetValueすると、処理の都合でgetValuesした際の中身が空になってしまうことがありました。
なので、QUERYをsetValuesした後はSpreadsheetApp.flush()でシートを更新することをおすすめします。

//QUERYをセット
ws.getRange("A1").setValue(query);

//シートを更新する
SpreadsheetApp.flush();

//結果を取得
var values = ws.getDataRange().getValues();

補足

この形で作ると処理をしたユーザの数だけシートが増えていくことになりますが、今回自分が作るサービスでは多くても100程度だろうというところと、ワークシートはかなりの数を作成できるので、似たような状況の人がいたらこの形で作るのもよいかもしれません。
トリガーで深夜にGoogleアカウント名のシートを棚卸しするなどもいいかもです。

おわりに

身も蓋もないですが、こんな縛りプレイみたいなことをせずにできれば普通にMySQLなり使った方がいいですね。