テーマを変更中で、表示が崩れているところがあります…！

【bubble】大量データのダウンロード – GASとAPI利用

.bubble
.bubble
APIcsvファイルGASNoCode外部連携大量データダウンロード

すごく久しぶりになってしまって凹みまくりですこんにちはこんばんは。

今回は、「大量のデータをダウンロードしたい」ニーズに対して、GoogleスプレッドシートからApp Scriptを書いて対応したお話です。

せっかくなので、CSVバージョンについても次回書こうと思います。

bubbleのCSVダウンロード

bubbleのデータをCSVダウンロードするには大きく分けて3つの方法があります。

  • エディタのDataタブからExportする方法
  • デフォルトのアクション、Download data as CSVを利用する方法
  • プラグインを利用する方法

ここで上記2つは、基本的に1つのテーブルからしかダウンロードできません。リレーションがあるテーブルは、primary display fieldsで設定した値でダウンロードできますが、その他の値を取得できません。。

primary display fieldsの設定

例えば、申請・承認アプリがあると考えてみましょう。ユーザーが申請する場合、申請データを作成する単純なものとします。

申請データにはCreated Byで申請者が紐づいています。Userテーブルにはユーザー名の他に部署名などの情報が登録されているとします。申請者名はUserテーブルのprimary fieldを「Name」にすれば取得できますが、Userテーブルに保存されている部署名などは先ほどあげた3つのうち上記2つの方法では取得できないです。

そんなとき、以下で書いたプラグインを利用すると取得したい形式が作れたりします。

【bubble】CSV Creatorプラグインが最高だった

ただし、1点問題があります！

データ量が数万件などになった場合、タイムアウトがおきます！

Timeouts – bubble document

これでは大量のデータがある場合、CSVダウンロード出来ないですね…。

toCシステムの場合、それほどCSVダウンロードは必要とされないかもしれませんが、toBシステムの場合は話は別です。

他のシステムと連携するために特定のCSV形式でデータをダウンロードする要件が多々あるのです…！

今回は、その要件をGoogle Spread SheetのApp Script（いわゆるGAS）を使用して、APIでデータを取得し、Google Spread Sheetに書き込むことで解決しました！

まずはAPIを作ろう

ということで、スプレッドシートから呼び出すAPIを作っていきます。

今回はTennis Buddyのデータを利用しますが、こちらのしかも開発環境はデータ量は全然ないのでその辺は見ないふりでお願いします。

想定するフロー

ここでは、ユーザーのメッセージ一覧を取得することを想定とします。

ざっくりテーブル構成は以下の通り。*は、リレーションがあるものです。

User
ユーザー情報		Rooms
メッセージルーム		Message
メッセージ中身
NameLast message(日付)Message(本文)
SexUser(ユーザーリスト)Room *
AreaTo * (User)
FavoriteShotCreated By * (User)
Lebel
: (その他情報など）
3つのテーブル

Messageデータを全部取得するとして、取得したいデータはこんな感じとします。

取得したいデータテーブル
メッセージ作成者名Message.Creator.Name
メッセージ作成者AreaMessage.Creator.Area
メッセージ受信者名Message.To.Name
メッセージ受信者AreaMessage.To.Area
メッセージ内容Message.Massage
最終メッセージ送信日Message.Rooms.Last Message
取得するデータ

このとき、Workflow APIで以下のようにするとデータ件数が多い場合にタイムアウトする可能性があります。

簡単な例（件数が多いとタイムアウト）

1アクションに対し、時間がかかりすぎるとタイムアウトとなってしまうので、上記のAPIを呼び出ししてもSearch for Messages:format as textを処理する時間次第でタイムアウトする可能性があります。

そこで、手順としては以下のようにします。

  1. 取得対象のMessageのunique idのリストを返す
  2. 1の結果から、1つずつMessageデータを取得する

こうするとWorkflow API内のアクションはそれぞれ処理速度がかからなくなります。GASから必要なだけ2.のAPIを呼び出してあげることになります。

まずは取得対象のunique idリストを返すAPIを作成

それでは早速作っていきます。

unique idのリストを返すAPIを、uniqueidという名前にします。ここでは、外部からAPIを呼び出すため、Expose as a public API workflowにチェックをいれます。

APIの作成①

APIが呼ばれたら、いきなり Return data from API でunique idのリストを返してやります。ちなみに、何らかの条件が必要であれば、Search for〜の中で設定してあげてください。今回は全てのMessageにしています。

APIの作成①Return data from API

Workflow APIの返却値をリストにする場合、件数制限があります！
そのため、上記のようにformat as textを利用して「”データ”, “データ”, …」というようなテキストデータで返すと1回で済みます。

もちろん、このデータが多い場合はそれだけでタイムアウトする可能性もあります…。
そんな時は x件ずつリストを返却する、といった形にして、GASから繰り返し呼び出します。

データ内容を1行ずつ取得するAPI

次に、unique idから必要なデータを返却するAPIを作成します。

APIの作成②

ここで、パラメータにidを設定しました。

通常パラメータはtextやdateなどで受け取りますが、テーブルのunique idの場合は直接テーブル名を指定出来ます！

こちらも問答無用でReturnします。

APIの作成②Return data from API

このとき、実際に返却するMessageは1レコードですが、convert to listをしてあげることでリストのように扱って、format as textが利用出来ます。

また、実際のメッセージ内容には改行が含まれますが、改行が含まれているとGASで取得・解析した際エラーになるので、今回はテストなので find & replaceで改行をスペースに変換しました。

APIの呼び出し設定

基本的にBackend Workflowを利用している場合はすでにチェックが入っていると思いますが、Settings＞APIでEnable Workflow API and backend workflowにチェックを入れます。

また、APIトークンを生成して、第三者からアクセス出来ないようにします。（Privacy Rulesについてはここでは割愛します。）

外部からのAPI利用設定

スプレッドシート（GAS）からAPIを呼び出す

それではみんな大好きスプレッドシートに移動します。

Apps Scriptを作成

適当なシートを作成した後、Apps Scriptに移動します。

Apps Scriptを選択

Apps Scriptについては詳しく解説しません。いっぱい転がっているのでググってみてくださいな！

とりあえず、API呼び出しに利用する情報をグローバル変数で書き込んでおきます。

セキュリティ上の理由から、urlやAPIトークンはスクリプトプロパティに設定した方が良いです。

GAS①グローバル関数

ユニークIDリストを呼び出す

GASのAPIコールは以下のようなコードです。

// ユニークIDの取得
function getUniqueId() {
  var headers = {
    'Authorization': 'Bearer '+ token
  };
  var options = {
    'method': 'get',
    'headers': headers
  }
  var requestUrl = url + endpoint1;
  var response = UrlFetchApp.fetch(requestUrl, options)
  var responseCode = response.getResponseCode()
  var responseText = response.getContentText()

  Logger.log(responseCode)
  Logger.log(responseText)

}

実行ログをオンにして、APIを呼んでみましょう。

API呼び出し結果

こんな感じでレスポンスが確認出来ます。

ユニークIDからデータを呼び出す

次に受け取ったレスポンスをJSONパースして、レスポンスのうち list の値を取得します。

このlistに対し、ループ処理をしてMessage取得していきます。

APIがエラーになった時のことを考慮して、先ほどのコードの次に以下のように追加します。


  if( responseCode == 200){
    let json = JSON.parse(responseText);
    let list = (json["response"]["list"]).replace(/"/g,'').split(",")
     listの中身がテキストになっているので、カンマ区切りで分割してリスト化 
     エラーが起きる特殊文字や、unique idを囲う「"」を取り除く

    list.forEach(function(uid){
      Logger.log(uid)
      // data呼び出し
      let res = JSON.parse(getMessage(uid)); API呼び出し用の関数
      let data = JSON.parse(res["response"]["data"][0])

      // 書き込み
      let lastRow = ss.getLastRow() + 1;
      Object.keys(data).forEach(function(key,i){
        ss.getRange(lastRow, i + 1).setValue(data[key]);
      })
    })
  }

MessageデータのAPIを呼ぶ

上記のコードでは getUniqueId() 関数の中で getMessage(uid) 関数を呼んでいたので、この中身を作っていきます。

先ほどのAPI呼び出しと同じようにコードを書いていきます。

// Message取得
function getMessage(){
  var headers = {
    'Authorization': 'Bearer '+ token
  };
  var options = {
    'method': 'get',
    'headers': headers
  }
  var requestUrl = url + endpoint2 + "?id=1686391107984x927578977568030700";
  var response = UrlFetchApp.fetch(requestUrl, options)
  var responseCode = response.getResponseCode()
  var responseText = response.getContentText()
  
  Logger.log(responseCode)
  Logger.log(JSON.parse(responseText))

  return responseText;
}

確認のためにgetMessage()関数の変数を一旦削除し、APIパラメータのidを指定してみます。

テスト：Messageデータ取得

レスポンスで値が取得出来ていることが確認できます。

それではこちらを書き換えて、完成したコードが以下です。
すみません、こちらではステータスコードの確認を忘れてしまっています…みなさんはしてくださいね💦

// API関連の設定
const url = "https://tennisbuddy.info/version-test/api/1.1/wf/"
const endpoint1 = "uniqueid"
const endpoint2 = "data"
const token = "API TOKEN"

// グローバル関数
const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// ユニークIDの取得
function getUniqueId() {
  var headers = {
    'Authorization': 'Bearer '+ token
  };
  var options = {
    'method': 'get',
    'headers': headers
  }
  var requestUrl = url + endpoint1;
  var response = UrlFetchApp.fetch(requestUrl, options)
  var responseCode = response.getResponseCode()
  var responseText = response.getContentText()

  Logger.log(responseCode)
  Logger.log(responseText)

  if( responseCode == 200){
    let json = JSON.parse(responseText);
    let list = (json["response"]["list"]).replace(/"/g,'').split(",")

    list.forEach(function(uid){
      // data呼び出し
      let res = JSON.parse(getMessage(uid));
      let data = JSON.parse(res["response"]["data"])
      
      // 書き込み
      let lastRow = ss.getLastRow() + 1;
      Object.keys(data).forEach(function(key,i){
        ss.getRange(lastRow, i + 1).setValue(data[key]);
      })
    })
  }
}

// Message取得
function getMessage(uid){
  var headers = {
    'Authorization': 'Bearer '+ token
  };
  var options = {
    'method': 'get',
    'headers': headers
  }
  var requestUrl = url + endpoint2 + "?id=" + uid;
  var response = UrlFetchApp.fetch(requestUrl, options)
  var responseCode = response.getResponseCode()
  var responseText = response.getContentText()
  
  Logger.log(responseCode)
  Logger.log(JSON.parse(responseText))

  return responseText;
}

実行してみよう

さて、GASから getUniqueId() 関数を実行してみましょう。

その前に、書き込み先シートにわかりやすいようにヘッダーを追加しておきました。

スプレッドシートにヘッダーを追加

実行します！

データが取れています！

こんな感じで、GASからAPIを呼んでスプレッドシートに書き込むことが出来ました。

最後に

途中でも書きましたが、ユニークIDを返却するAPIでタイムアウトが起きる可能性もあります。そんなときはさらに1段階追加して、以下のような流れにしてみましょう。

  1. 最初に対象データの件数を取得するAPIを作成し、実行
  2. (例)100件ずつ ユニークIDを取得する
    Search for Things:until (n-1)*100 + 1 from n*100
  3. 2のユニークIDから1つずつデータを取得する
タイムアウト時の対応例

または、以下の案もありですね。

  1. 対象件数を取得するAPIを作成
  2. 件数分、データ取得APIを繰り返す
    パラメータで呼び出し回数を送信する
    Search for Things:item (パラメータの呼び出し回数) :format as text とする

こっちの方が簡単そうですね。

自分のアプリにあったやり方でやってみてください！

なお、本Tips記載後、作成したWorkflowは削除しています。

タイトルとURLをコピーしました