様々なクラウドシステムがあり、今登場しているこれらウェブサービスの殆どは、既存の業務アプリケーションと連携する為のREST APIを備えています。しかし、通常はこのAPIはサーバサイドからの連携を想定していて、ローカルアプリケーションからの連携を想定していません。例えば、PHPやNode.js、Javaなどを用いています。Google Apps Scriptもサーバサイドですね。

しかし、現実の企業では必ずしも連携元のアプリケーションがウェブアプリケーションとは限りません。むしろ活用する場合には、Excelから使うのがほとんどだと思われます。

今回、Boardと呼ばれるクラウドの案件・顧客管理のシステムを利用する機会があったので、Excelからデータの入出力を実装してみました。ExcelではJSONの扱いは苦手なのですが、今回はいつものようなJSON Parseするやり方ではなく、Callbyname関数を利用した取得法を使っています。

今回利用するファイル等

事前準備

APIトークンとAPIキーの取得

Board APIはよく見かけるOAuth2.0認証を利用した方法ではなく、あらかじめ用意しておいたAPIトークンとAPIキーの2つを送り付けることでAPIの操作を行う事が出来ます。以下にその取得手順を示します。

  1. Boardにログインする
  2. 右上の歯車アイコンをクリックして「API設定」を開く
  3. APIキーが表示されているので控えておく
  4. 新規トークン生成をクリックして、新しいAPIトークンを作成します。
  5. 用途説明を入力、このトークンへ与える権限をチェックして、登録ボタンを押します。権限は必要最低限にて。自分の場合、「顧客リストの取得」「案件の更新」「案件のリストの取得」の3つとしてます。
  6. APIトークンは設定時の1度しか表示されませんので注意が必要です。この2つを控えておきます。VBA中で使用します。

図:Board API Token取得

リクエスト制限

API自体追加料金なしで利用は可能ですが、連続リクエストには制限があります。この制限を理解せずにコードを組んでしまうと、場合によっては制限を超えた分について処理されずにエラーとなってしまいます。上手に制限を回避するようにコードを書く必要があります。主な注意すべきリクエスト制限は以下の通りです。

  • 1日3000リクエストまで。なるべく一発で処理できるようにAPIの利用回数には気を付ける必要があります。
  • 3リクエスト/秒まで。但し、100リクエストまでは制限なく呼び出し可能。ただし、この100は全部で100であり、1回あたりではありません。よって、頻繁に3リクエスト/秒を超えるリクエストを投げてしまうと、この100を消費してしまいます。なるべく、1リクエストは3秒の間隔を守りましょう。
  • 上記の100リクエストは一定期間利用されないと100まで補充される仕組みです。
  • リクエスト基準の1日は、UTC基準でありJSTでないので注意。
  • 制限をオーバーすると、秒間リクエストを超えると429 – Too Many Requestsが返ってきます。1日の制限リクエストを超えるとLimit Exceededが返ってきます。エラー処理も実装しておくと良いでしょう。
  • データの取得などで、1回のリクエストで取得できる件数は最大100件まで。それを超えた場合、ページネーションされ、ページ指定を繰り返してデータを取得する必要があります。なるべく取得する範囲を絞ってリクエストを投げましょう。

ソースコード

データの取得

今回は、Boardの案件リストを取得してみます。但し、指定された日付以降のデータに限り取得するようにします。それでも結構な数になるのでページネーションされる可能性があるため、リクエスト制限回避の為に、1リクエスト毎に3秒間のsleepを入れています。

  • 会社での利用を想定しているので、WinHttpでリクエストを投げる時用にプロキシーのURL設定を加えています。ただし、取得側はそれが不要なxmlhttpで通信させています。データの更新側はWinhttpで記述しています。
  • APIキーやAPIトークンはレジストリに登録し呼び出す形にしている為、ワークブック内には記述しません。
  • dataシートは洗い替えで取得するので既存データは一旦全クリアされます。
  • いきなりデータを取得するのではなく、HEADリクエストX-Total-Countの値を取得させています。
  • per_pageを80にしているので、変更したい場合には最大100まで指定できます。
  • 取得したデータは一旦JSONを記述したtxtファイルとして書き出すようにしています。
  • JSONの最初のパースはVBA-JSONを利用しない方法をつかってパース取得しています。
  • 個別のセクションは各々で、CallByName関数で値を取得する方法を利用しています。VBAで使うならこの方法がもっともベターかも。
  • 途中部門判定をしていますが、この時CallByNameで指定してるgroup_idは表向き知る手段がありません。次項の注意点を参考に書き込み対象にしたいgroup_idを追記しましょう。
  • リクエスト制限対応の為、1ページ実行するごとに3秒間sleepを入れています。
  • ページカウント文だけリクエストを発行して完了です。

group_idの確認について

いわゆる部門毎に区分けした時に着けられるコードなのですが、Board上ではそれを確認する場所がありません。ゆえにそのままでは、全データ取得後全データがExcelに書き込まれてしまいます。特定部門だけに絞りたい場合、具合がよくありません。

このgroup_idですが、一旦この制限部分を取っ払って全データを取得します。すると、対象部門のgroup_idが何なのか?知ることが可能です。再び、制限を加えてこのコードで比較してフィルターすると良いでしょう。

CallByName関数について

通常、JavaScriptではJSON文字列を解析して値を取得する場合、hogehoge.data[1].personといったような形で取得ができます。しかし、VBAでも基本はこれでも取得できなくもないのですが、時として文字列がVBAの予約語とぶつかって勝手に大文字になったり(idなどは代表的)、データの取得上具合がよくありません。

そこで使う特殊な関数がCallByName関数。ただ使い方に癖があるので、JavaScriptのようにスムーズにはいきません。深い階層にあるデータは何度もCallByName関数を使って掘っていく必要があります。使う手順は以下のような感じ。

  1. []内に複数のレコードがJSON形式(jsonobjectという名前でパース済み)で含まれているのでまずはこのデータをobject型変数にCallByNameでセットする
  2. その場合のコードは、Set o = CallByName(jsonobject, i, VbGet)となる。iは1個目,2個目などを指定する
  3. このままでは、n個目の塊が取れただけなので、この塊の中からproject_noを取得してみる。String型変数に格納してみる。
  4. その場合のコードは strpjnum = CallByName(o, “project_no”, VbGet)となる。
  5. さらに深い階層であるclient内の値を取得したい場合は、さらにCallByNameで処理してから同様に値を取り出すことになる。

JavaScriptのようにチェーンで取得できないのは不便ですが、この手法があるからこそVBAでJSONを取り扱えます。もちろん、VBA-JSONを使ったほうがより楽に処理はできます。

データの更新

取得したデータには、その案件を特定できるIDが含まれています。このIDを利用して逆に、Board側にデータを追記してみたいと思います。今回は、Board側案件データの社内メモ欄(in_house_memo)にねじ込みたいと思います。

  • PJ集計シートに案件No.、ねじ込む値、IDの3つを追記しておきます。IDを基準にデータを特定し送り込みます。
  • リクエストはまとめて送れないので、レコード単位で送ることになります。3リクエスト/秒の制限に掛からないようにsleepを加えています。
  • リクエスト用のURLにIDを追記してリクエストを行います。
  • リクエストメソッドはPATCHで行います。
  • 送るデータはJSON形式にして送りつける必要がありますので、今回は素直にVBA-JSONを利用しています。その為、参照設定に「Microsoft Scripting Runtime」を追加しておく必要があります(Dictionaryで利用する為)。
  • Dictionaryで連想配列を作ってデータを構築後、ConvertToJsonにてJSONへ変換し、リクエスト送信します。
  • 無事送信できれば、ステータスとして200が返ってくるので、これで完了です。実際にBoard上で該当の案件の社内メモを確認してみましょう。

APIキーとAPIトークン

前項のコード内にて、APIキーとAPIトークンを利用していますが、今回これらはコード内に記述していません。やはり、これらのキーを含めたままですと、セキュリティ的にはよろしくない(ファイルが流出時にトークンを悪用されてしまう)。そこで、これらの値はレジストリ内に格納し、登録したPCで呼び出して使う形にしたほうが、ナンボもマシです。

そこで今回のファイルにはUserFormを追加し、別途このフォーム上から登録してもらうようにコードを組んでいます。呼び出し側は直接レジストリから読みだします。以下にUserFormの送信コマンドのコードを記述します。

非常に単純なコードで、別途導入済みのレジストリ読み書きのモジュールを使って、レジストリ内に値を格納しています。但し、パスワード入力欄は*印で表示されるよう加工していないので、実用時にはここをどうにかしておいたほうが良いでしょう。

図:適当に作った登録画面

関連リンク

共有してみる: