Googleスプレッドシートを使っている方は多いかと思います。その利用用途の一つに、マスターデータやキャラクターデータを管理するケースもあるでしょう。そして作成したデータをCSVにしてmBaaSにインポートしている方もいるのではないでしょうか。
CSVインポートだとobjectIdが書き換わってしまう問題があったり、かといってコピー&ペーストでデータをメンテナンスするのはとても面倒です。そこでGoogle Apps Script用SDKを使ってマスターメンテナンスを手軽に行えるようにしました。このメリットとしては例えば以下が挙げられます。
- データの並び替えができる
- カラムの並び替えができる
- 他のシート、セルの値を計算しながら使える(あるセルの7日後など)
- ポインターの修正ができる
ライブラリのインストール
Googleスプレッドシートのツールメニューにあるスクリプトエディタを選択します。
スクリプトエディタのリソースメニューにあるライブラリを選択します。
出てきたモーダルウィンドウで、ライブラリ追加の入力欄に「1yWnb7GfYsBCR-MZvi6r-TOYv_y-AV4le5P7kaG5B3iZ5VNnSk9Q55pSJ」と入力して追加ボタンを押してください。
NCMBというタイトルでライブラリが追加されます。バージョンは24以降の最新版を選んでください。
これでSDKが使えるようになります。
仕様
今回紹介するコードの仕様は次のようになっています。
- 各シート名はmBaaSのクラス名に相当します
- 各シートの一行目はクラスのカラム名とします
- カラム名にcreateDate/updateDate/aclは使えません
- objectIdはA列にあることとします
- 計算式は使えますが、A列を参照していると(つまりobjectId)、自動的にポインターになります
- Googleスプレッドシートを真として扱います。データを更新後、スプレッドシートにないmBaaSのデータは削除されます
- データストア側のデータは1000件まで取得します。ページネーションに対応していないので1000件を越えるデータメンテナンスには対応していません
プロパティを追加
スクリプトエディタにて、ファイルメニューのプロジェクトのプロパティを選択します。
その中のスクリプトのプロパティにて、項目を追加します。これはアプリケーションキーや認証情報をハードコーディングしないよう、環境変数化する機能です。
- APPLICATION_KEY
アプリケーションキー - CLIENT_KEY
クライアントキー - USER_NAME
メンテナンスを行うユーザ名 - PASSWORD
メンテナンスを行うユーザのパスワード
認証を必ず行います。認証していないと認証していないユーザでも書き込み権限がついたデータになってしまうからです。
コードの作成
Googleスプレッドシートのスクリプトエディタに関数を追加します。 PropertiesService.getScriptProperties()
で先ほど登録した環境変数を呼び出します。
function syncData() { var userProperties = PropertiesService.getScriptProperties(); // NCMBの初期化 var application_key = userProperties.getProperty("APPLICATION_KEY"); var client_key = userProperties.getProperty("CLIENT_KEY"); var ncmb = NCMB.init(application_key, client_key); // 認証 var userName = userProperties.getProperty("USER_NAME"); var password = userProperties.getProperty("PASSWORD"); ncmb.User.login(userName, password); // すべてのシートを対象に処理を行います var objSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var i = 0; i < objSheets.length; i += 1) { syncClass(ncmb, objSheets[i]); } }
認証した後は、すべてのシートを対象として syncClass
関数(これから作ります)を処理していきます。処理対象にするシートを作る場合には命名規則を設けるなどして工夫してください。
クラスの同期
クラスの同期処理です。まずクラス名やカラムをシートから取得します。
function syncClass(ncmb, sheet) { // クラス名の取得と、データストアの準備 var className = sheet.getName(); var Item = ncmb.DataStore(className); // 必要な変数の初期化 var fields = []; var coulmn = 1; var objectIds = []; // 一行目を使ってカラム名を取得 while (true) { var field = sheet.getRange(1, coulmn).getValue(); if (field == '') break; coulmn += 1; fields.push(field); } // : }
次にデータの更新および登録処理を行います。注意点としては、関数が埋め込まれていて、かつそれがobjectIdだけを参照している場合にはポインターにしています。また、今回はAdminというロールに読み書き権限を設定していますので、ここは各アプリの設定に合わせて変更してください。
// データの登録および更新処理 var rowIndex = 2; while (true) { // クラスのインスタンスを容易 var item = new Item; // すべての行が空であれば、処理終了とします var range = sheet.getRange(rowIndex, 1, 1, fields.length); if (range.isBlank()) break; // 各行を処理します for (var i = 0; i < fields.length; i += 1) { // 表示されている値と、そこで使われている計算式を取ります var value = sheet.getRange(rowIndex, i + 1).getValue(); var formula = sheet.getRange(rowIndex, i + 1).getFormula(); // 値がない場合には処理対象外です if (value != '') { // 計算式がある場合 if (formula != '') { // A列(objectId)が処理対象か否か var pClassName = formula.replace(/=(.*?)!A[0-9]+.*$/, "$1"); // objectIdが計算式にある場合はポインターとします if (pClassName) { value = { "__type":"Pointer", "className": pClassName, "objectId": value }; } } // 値をセットします item.set(fields[i], value); } } // ACLは決め打ちです。ワークフローに合わせて変更してください item.set('acl', { "*": { "read": true, // 全員に読み込み権限 }, "role:Admin":{ // Adminというロールに書き込み権限 "read": true, "write": true } }); // オブジェクトIDの有無によって登録/更新を変えています var error = item.get('objectId') ? item.update() : item.save(); // エラー判定 if (!error) { // エラーがなければ行のA列の値をobjectIdにします sheet.getRange(rowIndex, 1).setValue(item.get('objectId')); // ここで処理されたobjectIdは削除対象外のデータとします objectIds.push(item.get('objectId')); } rowIndex += 1; }
ここで処理したデータは削除対象外になります。最後に現在データストアにあるデータを取得し、処理対象でなかったデータを削除します。
// 現在クラスにある全データ(最大1000件)を取得します var items = Item.limit(1000).fetchAll(); for (var i = 0; i < items.length; i += 1) { // 処理対象だったかどうかチェックします if (objectIds.indexOf(items[i].get('objectId')) > -1 ) { } else { // 処理対象でなかったのでデータを消します items[i].destroy(); } }
これで処理完了です。全体のコードはGistにアップロードしてありますので参考にしてください。
このコードを使うことで、Googleスプレッドシートを使ったマスタメンテナンスが可能になります。ポインターは管理画面でも編集しづらいですが(ポインターを修正できるブックマークレットを作りました)、Googleスプレッドシートからであれば簡単に修正できます(他のシートを参照するだけです)。
ぜひ皆さんのアプリ運用にお役立てください。