FJCT_ニフクラ mobile backend(mBaaS)お役立ちブログ

スマホアプリ開発にニフクラ mobile backend(mBaaS)。アプリ開発に役立つ情報をおとどけ!

Google Apps Script用SDKを使ってマスターデータをGoogleスプレッドシートでメンテナンスする

f:id:mbaasdevrel:20180405222526p:plain

Googleスプレッドシートを使っている方は多いかと思います。その利用用途の一つに、マスターデータやキャラクターデータを管理するケースもあるでしょう。そして作成したデータをCSVにしてmBaaSにインポートしている方もいるのではないでしょうか。

CSVインポートだとobjectIdが書き換わってしまう問題があったり、かといってコピー&ペーストでデータをメンテナンスするのはとても面倒です。そこでGoogle Apps Script用SDKを使ってマスターメンテナンスを手軽に行えるようにしました。このメリットとしては例えば以下が挙げられます。

  • データの並び替えができる
  • カラムの並び替えができる
  • 他のシート、セルの値を計算しながら使える(あるセルの7日後など)
  • ポインターの修正ができる

ライブラリのインストール

Googleスプレッドシートのツールメニューにあるスクリプトエディタを選択します。

f:id:mbaasdevrel:20180405222503p:plain

スクリプトエディタのリソースメニューにあるライブラリを選択します。

f:id:mbaasdevrel:20180405222515p:plain

出てきたモーダルウィンドウで、ライブラリ追加の入力欄に「1yWnb7GfYsBCR-MZvi6r-TOYv_y-AV4le5P7kaG5B3iZ5VNnSk9Q55pSJ」と入力して追加ボタンを押してください。

NCMBというタイトルでライブラリが追加されます。バージョンは24以降の最新版を選んでください。

f:id:mbaasdevrel:20180405222526p:plain

これでSDKが使えるようになります。

仕様

今回紹介するコードの仕様は次のようになっています。

  • 各シート名はmBaaSのクラス名に相当します
  • 各シートの一行目はクラスのカラム名とします
  • カラム名にcreateDate/updateDate/aclは使えません
  • objectIdはA列にあることとします
  • 計算式は使えますが、A列を参照していると(つまりobjectId)、自動的にポインターになります
  • Googleスプレッドシートを真として扱います。データを更新後、スプレッドシートにないmBaaSのデータは削除されます
  • データストア側のデータは1000件まで取得します。ページネーションに対応していないので1000件を越えるデータメンテナンスには対応していません

プロパティを追加

スクリプトエディタにて、ファイルメニューのプロジェクトのプロパティを選択します。

f:id:mbaasdevrel:20180405222617p:plain

その中のスクリプトのプロパティにて、項目を追加します。これはアプリケーションキーや認証情報をハードコーディングしないよう、環境変数化する機能です。

f:id:mbaasdevrel:20180405222629p:plain

  • 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スプレッドシートからであれば簡単に修正できます(他のシートを参照するだけです)。

ぜひ皆さんのアプリ運用にお役立てください。

中津川 篤司

中津川 篤司

NCMBエヴァンジェリスト。プログラマ、エンジニアとしていくつかの企業で働き、28歳のときに独立。 2004年、まだ情報が少なかったオープンソースソフトの技術ブログ「MOONGIFT」を開設し、毎日情報を発信している。2013年に法人化、ビジネスとエンジニアを結ぶDXエージェンシー「DevRel」活動をスタート。