MongoDB AtlasとGoogle Spreadsheetを連動してみた話

2023年8月8日

こんにちは、新規プロジェクトのバックエンドエンジニアのJです。

今回はMongoDB公式サイトのドキュメントにも記載されているMongoDB AtalsとGoogle Spread Sheetの連動を試してみた話を紹介したいと思います。

※Using the Atlas Data API with Google Apps Script
https://www.mongodb.com/developer/products/atlas/atlas-data-api-google-apps-script/

MongoDB Atlas DataAPIとは

MongoDB Atlasはクラウド基盤のデータベースで簡単なインタフェースを通して、MongoDBクラスターが管理できるサービスです。ココネではインフラストラクチャー管理コスト軽減とアプリケーション開発に集中するために最近のプロジェクトでMongoDB Atalsを導入し運用しています。
その中でAtlas Data APIはMongoDB Atlasの機能の一部であり、開発者がアプリケーションとMongoDBクラウドデータベース間で相互作用するためのRESTful APIです。Atlas Data APIの利用で開発者はクライアントアプリケーションから直接データベースに対するCRUD（作成、読み取り、更新、削除）操作を実行することができます。
MongoDB Atlasは管理画面から3つの手順で活性化できます。

1. Enable the Data API (endpointの獲得）
2. Create a Data API Key (Data API Keyの獲得）
3. Send a Data API Request (RESTful APIへリクエスト）

Data APIを活性化してからData API Keyを利用してendpointへリクエストすることでMongoDBへのCRUD操作ができるようになります。

curl --request POST \
  'https://data.mongodb-api.com/app/<Data API App ID>/endpoint/data/v1/action/insertOne' \
  --header 'Content-Type: application/json' \
  --header 'apiKey: <Data API Key>' \
  --data-raw '{
      "dataSource": "<cluster name>",
      "database": "learn-data-api",
      "collection": "people",
      "document": {
        "name": "John Sample",
        "age": 42
      }
  }'

上記の例ではinsertですが、update、replaceなど様々なResourceが存在するので多様なデータベース操作が可能になります。

※Data API Resources
https://www.mongodb.com/docs/atlas/api/data-api-resources/

Google Spread Sheet からDataAPIを呼び出す

次はGoogle Spread Sheet側でMongoDB Atlas Data APIを呼び出す仕組みを入れます。こちらも公式ドキュメントに詳しく書いてますが、Google Spread Sheet「拡張機能」の「Apps Script」を作成して「図形描画」のボタンとApps Scriptを連動する形になります。

Apps Scriptは主にAtlas Data APIを呼び出す処理とシートのセルデータをコントロールする役割を担当します。

今回は3つのボタンを設置しました。それぞれの機能は下記の通りに実装します。

Clear (シート上データを表示するセルをクリアする)
Find (データベースから取得したデータをセルに表示する)
Apply to DB (セレクトした行のデータをデータベースへ反映する)

例）Apps Scriptのfind、clearはこのような流れになります。
※実際はもっと複雑なコードになっています。「」の部分は設定の部分です。

// データベースからデータを取得してシートに設定する
function find() {

  let payload = {
    dataSource: 「MongoDB Atlas クラスター名」,
    database: 「データベース名」,
    collection: 「コレクション名」,
    filter: 「クエリ(json)」,
    sort: 「ソート(json)」,
    limit:「limit」,
    skip: 「skip」
  };

  let option = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload)
  };

  // APIKEYを設定
  option.headers = { "api-key": 「API Key」 };
  
  // MongoDB Atlas DataAPIへリクエスト
  let response = UrlFetchApp.fetch(' 「endpoint」 ', option);
  // DataAPIからのレスポンス
  let documents = JSON.parse(response.getContentText()).documents;

  //データを表示するセルをクリア
  let dataRange = sheet.getRange('A6:H506');
  dataRange.clearContent();

  let applyRange = sheet.getRange(dataRange.getRow(), dataRange.getColumn(), cellValueRows.length, dataRange.getNumColumns());
  // シートのデータエリアにデータを設定する。
  applyRange.setValues(documents);
}

// データ表示エリアをクリアする
function clearDataRange() {
   let dataRange = sheet.getRange('A6:H506');
  dataRange.clearContent();
}

// 選択した行のデーターを削除＆挿入
function replaceData() {
……省略……
}

各ボタンとApps Scriptの各メソッドの紐付けを設定すればOKです。ボタンを右クリックして出るメニューの「スクリプトを割り当て」でApps Scriptのメソッドと紐付けすることができます。

汎用性を高めるカスタマイズ

こんな感じでMongoDB Atlas Data APIとGoogle Spreadsheetと連動して簡単にMongoDBデータのCRUD操作は確認ができました。しかし、新しくMongoDBのCollectionが追加される度にスクリプトの追加作業が発生するので運用コストやスクリプトの管理が難しくなります。そのため、シートの方へ設定を記入してCollection毎にカスタマイズができるように仕組みを入れてみました。

複数Collectionを一つのスクリプトで対応できるためにシートの特定のセルで「コレクション名」(COLLECTION)、「データを表示するセル範囲」(DATARANGE)、「検索テキスト」…など動的な設定値を入れるようにしました。
図のように一部設定入力のセルを作成してセルのデータをApps Script側で読み取り、Sheetの操作とData APIのリクエストに利用する形に変えました。

例）

// シートの設定セルを指定
const sheetInfo = {
  clusterName : '',
  database : '',
  metaInfoCell : 'B2',
  dataRangeCell : 'D2',
  skipInfoCell : 'F2',
  collectionNameCell : 'B1',
  searchTextNameCell: 'A3',
  searchTextRange: 'B3',
}

……省略……

// データ表示エリアの取得
let dataRange = sheet.getRange(dataRangeCell);
// 操作するコレクション名を取得
let collectionName = sheet.getRange(collectionNameCell).getValue(); 

……省略……

Data APIで利用するデータとGoogle Spread Sheetのデータ変換にはCollectionのタイプを指定する必要があります。Date、Array…など文字列のままにCRUD動作を行うとデータベースに文字列として反映されたりします。
そのため、タイプに関してはシートのテーブルヘッダの「メモ」にタイプとfield名を指定できるjsonを追加して利用する形にしてみました。

例えばテーブルヘッダーの開始日のセルのメモの情報を入れて

{"type": "date", "key": "start_time"}

Apps Script側は下記のようなデータを変換して処理する形になります。

// ejsonへ変換
let ejson = {};
    ejson['$date'] = Utilities.formatDate(new Date(value), 'Asia/Tokyo', "yyyy-MM-dd'T'HH:mm:ss.SSS'+09:00'");

// セルに入れるデータへ変換 
let result = Utilities.formatDate(new Date(document[key]), 'JST', 'yyyy-MM-dd HH:mm:ss.SSS');

このようにApps Scriptに変換処理を入れて動作するようにしておくとタイプを守りながらCRUD操作することができます。Date、ObjectIdなど特殊なタイプに関してはMongoDB Extended JSONを利用しました。

※MongoDB Extended JSON (v2)
https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/

例）MongoDBのObjectId、int32、int64のEjson

{ "$oid": "<ObjectId bytes>" }
{ "$numberInt": "<number>" }
{ "$numberLong": "<number>" }

完成したのはこのようなイメージになります。

最後に

今回の記事ではMongoDB AtalsとGoogle Spread Sheetの連動を試みた話を紹介しました。
Google Spread Sheet側から直接データベースのCRUD操作をする仕組みを構築したことで、例えばデータ管理のためのツールを開発したり、Spread Sheetに入力規則の設定をしたりして、データ作業のミスを減らせるようになります。紹介した以外にも、認証、操作ログの記録…を追加してもっと安定的に運用するようにしています。
またテーブル形式ではないデータの管理も容易になるので今後もいろいろ試しながら楽しく利用しようと思っています。

補足１：CRUDコレクション指定（Data API Access）

MongoDB Atlas Data API活性化するとデフォルトで全てのコレクションが操作できるようになります。
運用のルールでテスト環境のデータベースのみ利用する予定ですが、セキュリティ的に危険になります。
設定画面でCustom Accessを設定することができるので「Default roles And filters」をdenyAllAccessに設定してから、必要なCollectionのみ許可する形で運用しています。

補足２：API Keyは大切に管理

MongoDB Data API設定時に取得したAPI Keyはデータベースのデータを変更することができる大事な情報なので共有したりすることは非常に危険です。MongoDB AtlasにはIP Address制限、認証オプションもありますが、データ操作ができる重要な情報になるため隠す必要があります。Google Spread Sheetを共有すると、Apps Scriptを閲覧することができるので、実際の実装には次のようにApps Scriptを複数作成して運用する形を取っています。

① Properties Script
– Apps ScriptでData API Keyとendpointを取得できるライブラリ
　(Apps ScriptのスクリプトプロパティにAPI Keyとendpointを設定）

② Sheet Script
– Google Spread Sheet + 拡張機能のApps Script
　(シートの操作、Data APIの呼び出し、①のライブラリを追加)

複数のApps Script構成でそれぞれの権限を設定して、Properties Scriptは必要なメンバーのみ編集者権限を与えてAPI Keyを隠すようにしています。他にもいろいろ方法があると思いますが、API Keyは大切に管理する必要があります。

ココネでは一緒に働く仲間を募集中です。

ご興味のある方は、ぜひこちらのエンジニア採用サイトをご覧ください。

→ココネ株式会社エンジニアの求人一覧