VBAからkintoneデータをOAuth2.0認証で読み書きする - 実戦編

前回の記事にて、kintoneのOAuth2.0認証周りを実装しました。これにより、あとはAPIを叩いて、データを取得したり、kintone側のデータを更新する事が可能になります。この仕組を使う事で、kintone側で装備したりプラグイン開発といった事をする必要がなく、使い慣れたExcelやAccess側で制御が可能になります。また、JSは使い手なら大量のライブラリを使いこなせますが、そうではない中小企業の場合VBA側のほうがしっかりしたGUIを構築できるメリットもあります。

今回は取得したAccess Tokenを用いて、APIを実際に叩いてみてkintoneのデータの処理をしてみたいと思います。まずは、データの一括取得、そして実際に承認作業をしてkintone側の対象のレコードを更新する、対象のレコードを削除する、新規レコードの追加という最も重要な部分を実装します。

今回使用するファイル等

kintone側へ更新用データを送る為にJSONを組み立てる必要があるため、VBA-JSONを今回は利用しています。また引き続き、暗号化のためのモジュールも前回に引き続き含まれています。

新方式が登場しました

IE11の廃止に伴い、SeleniumやNode.jsやらといった手段を使わず、またPuppeteerと同様の手法(CDPを叩く)でVBAとEdge/ChromeのみでOAuth2.0認証する手段が登場しました。スクレイピングも可能になっています。以下のエントリーを参考にしてみてください。この手法は最も制限が無く、もっともすぐれた選択肢になると思います。

VBAでOAuth2.0認証 - 新方式を試してみた

事前準備

参照設定

本プログラムは今回、DictionaryやOutlookを使う為に参照設定が必要です。

  • Microsoft Outlook 16.0 Object Library – Outlookを遠隔操作するために必要
  • Microsoft Scripting Runtime – Dictionaryを使うために必要

APP IDの取得と保存

今回から予め作成しておいた「アプリ」のAPP IDが別途必要になります。このAPP IDは、アプリのURLにある数字がそれになりますので、これを前回の設定用UIに新たな項目として、レジストリに保存できるように追加してあげましょう。URLは以下のような感じになります。

https://ドメイン名.cybozu.com/k/999/

この999の部分がAPP IDとなり、アプリ毎に異なります。

図:UserFormにはこのように増設しました

シート側の列の設定

一括取得等の為にシートを用意します。シート名はkintoneとし、kintone側のテーブルと同じような列構成で作っておきます。データの一括取得や、処理フラグなどを格納するようにkintone側で作成しておく必要がありますので、テーブル設計はしっかりおこないましょう。

図:テーブル設計はアプリ作成の肝です

図:シート側も同じ設計にしておく

承認用のフォームを用意する

UserForm2として承認用のフォームを新規に用意します。摘要欄だけはプロパティとして

  1. EnterkeyBeheiviorをTrueにする(Enterキーで改行になります)
  2. MultilineをTrueにする(複数行かけるようになる)
  3. ScrollBarsは「2 - fmScrollBarsVertical」とする(縦だけスクロールバー表示)

として設定しています。他はテキストボックスを用意して、初期化時に選択したレコードデータを格納します。また、承認・却下用のボタンを用意し、それぞれに処理を記述する必要があります。

図:承認用のダイアログがこれで用意できました。

新規追加用フォーム

UserForm3として新規追加用のフォームを用意します。使用用途欄のプロパティは

  1. EnterkeyBeheiviorをTrueにする(Enterキーで改行になります)
  2. MultilineをTrueにする(複数行かけるようになる)
  3. ScrollBarsは「2 - fmScrollBarsVertical」とする(縦だけスクロールバー表示)

として設定。他にも細かくデザイン上の設定変更や、数値の欄は右揃えにする等のプロパティ変更をしています。

また今回は、通常VBAのUserForm上では使えないカレンダーコントロールについて、こちらのクラスを利用する事でOS標準で入ってるCommon Controlの1つである「DTPicker」をVBA上から利用する事ができました。詳しくはクラス公開ページを良く読んでから装備してみてください。Visual Basic 6.0 runtimeの別途インストールは不要です(Windows10の場合)

図:データ追加用のダイアログはちょっとだけ豪華

ソースコード

データの一括取得

もっともよく利用する項目がデータの一括取得。ただし、データには既に処理が完了済みのものもあり、これらのデータまで毎回取得するのは合理的ではなく、またコードも複雑怪奇になります。また、kintoneは現在一括取得では最大500レコードぽっちしか取得できない制限があるため(2020年7月には10000件まで増やすようですが・・・・)、例えば、500件以上の未処理データがある場合には、数回に分けて取得するといった事が必要になります。

今回は以下の事を考慮して設計します。

  1. レコード番号を元に、既に取得済みデータについては取得対象とはしない
  2. 承認が未処理のレコードについては取得対象としない

小規模な組織であればこれだけで速度面や制限面もクリアしつつ十分な運用が出来るはずです。kintoneでは「指定したフィールドが空のものを抽出」がAPIでやる方法が無いので、空ではなく、未処理状態のものと定義づけて処理をすると良いです。それが出来ない場合、空で抽出するのではなく一度取得後空かどうか判定して、Excelに貼り付ける事になります。よって、未処理状態は初期値「未処理」が入るようにフィールドに設定しておきましょう。

  • データ取得用のエンドポイントURLは、kingetdataに記してある通りのものになります。cursorと呼ばれるAPIもあります。
  • Access Tokenの期限切れチェック&再取得ルーチンをまず走らせます。
  • 今回より新たに追加したAPP IDをレジストリから取得します。
  • offsetは指定した分のレコード数だけ飛ばして表示するので、クエリの結果5件返ってきた場合、offsetが2の場合、3件目~のデータを受信する。レコード番号とは関係ないので注意。
  • query文字列が嵌るポイントです。ドロップダウン項目は項目名 in (""項目の値"")としてクエリを作る必要があります。複数つなげる場合にはand演算子でつないで条件指定をします。
  • 空文字での検索はできないので、どこか判定になる列で今回は未処理だったらという条件で取得します。
  • レコード番号順でorder byを指定し、レコード番号最大値以上、取得件数は最大値の500をlimitとして指定しています。
  • query文字列は必ず「URLエンコード」してから渡す必要があります。
  • アクセスするURLにAPP IDとtotalCount=trueのオプション、query文字列をつなげて、GETで通信します。
  • VBA-JSONは正直扱いが面倒(特にJSONオブジェクトのキー名に変数が素直に使えない)ので、いつものParseをしています。
  • jsn.recordsが空の場合、取得できるデータがないのでそこで終了です。
  • kintoneシートのカラム列名でCallByName関数にてkeyの指定として利用しています。
  • 配列を用意し、同じレコード数・カラム数で拡張、データをCallByName関数でvalueを取得し流し込んであげます。
  • 最後にシートの最終行以下に新しく取得した配列データを書き込みしています。
  • totalCountですが、これはqueryでフィルタされたあとの件数ではなくフィルタされる前の件数ので注意。これが500を超えている場合にはtotalCount/500の回数だけGETで叩けば全てのデータを取得可能です。

全データを取得する

レコード取得のAPIについては、cursorを使った手法では10,000件まで取得可能ですが、通常のAPIでは500件がMAXです。そのため、kintoneのテーブルに500件以上ある場合、何度か回さないと取得ができません。それほど多く利用するシーンは無いと思いますが、無いと困るので、作りました。

以下のコードはExcelではなくAccess用に作っていますが、適当に関数などを書き直せば動くと思います。必要な部分だけを掲載しています。

  • lastsetがtrueの場合ループから脱出するようにしています。
  • reclimitは取得できる最大値を指定しています(今回は500)
  • テーブルより取得済みレコード番号の最大値を取得しておきます。クエリではこれよりも上のレコード番号を取得させます。
  • POST送信で送り、totalCountが返ってきます。この値は最大値以上の条件での返り値なのでループ毎に減っていきます。
  • totalCountがreclimit以下かどうかで判定させて、lastsetのフラグをtrueにします。
  • また、reclimit以下の場合には残りの件数をloopcntに入れ、そうでない場合はreclimitをloopcntに入れます。
  • loopcntを元にループを回し取得したデータをテーブルに流し込んでいきます。

1ループ毎にレコード番号最大値を取得してクエリを流して、totalCountがreclimit以下になるまで回し続ける事でオフセットなどの計算をせずに全部のレコードを取得可能です。

データの更新

承認フォーム側コード

UserForm2として承認用のフォーム側には、initializeと承認・却下のボタンを用意しています。

  • 初期化では選択してる行のデータを各テキストボックスに反映しています。
  • 承認及び却下のボタンでは、グローバル変数であるappflgに対してそれぞれの値を格納し、グローバル変数であるtekiyouに摘要欄の値を格納しています。
  • この時、摘要欄は「Multilineはtrue」となっていて、「EnterkeyBehaviorはTrue」でEnterで改行されます。改行コードとなっているのでメールで送る場合は注意が必要です。
  • 摘要欄が長くなった時の為に、Scrollbarsは「2 - fmScrollBarsVertical」として縦だけ表示するようにしています。

メインの更新用コード

承認および却下をした場合には、kintone側の対象の列の値を変更するだけでなく、入力値を持ってしてExcelの行の値も変更、その後Outlookにてメールを送る処理を作ります。

  • グローバル変数kinputdataが1行レコードを更新する為のエンドポイントURLになります。recordであってrecordsじゃない点に注意。
  • UserForm2側の摘要欄(tekiyou)承認フラグ(appflg)レコードデータのID(tarrec)、現在選択されている行の番号(selerec)などをグローバル変数で格納します。
  • line_check関数で行が選択されているかどうかを判定します。
  • 今回は複数行まとめてではなく、1行単位で承認をする仕組みなので、1行以上選択して承認は出来ません。
  • 完了日の日付はyyyy-mm-dd形式でなければなりません。
  • 承認後は承認内容である承認列、完了日、摘要の内容をシートの該当の行に反映します。
  • また、承認後その内容を元に、Outlookにてメールを送るコードが入っています。その際に摘要欄は改行コードを<br>に変更しています(HTMLメールで送っているため)
  • kintonePutData関数が、kintone側のレコードを更新する関数になります。
  • 更新するデータはJSONで組み立てて、JsonConverter.ConvertToJsonにてJSON化したものをPUTメソッドにて送信します。
  • JSONはrecordキーだけが複数のkeyの値を持っている状態にする必要があります。
  • 承認でステータスが200が返ってくれば、trueとして、そうでなければfalseとしてflagmanで返す。この返り値を元にメインルーチンでシート書き込みやメール送信を行います。
  • シートでは承認された場合は灰色、却下された場合は赤色でレコードが色分けされるように、条件付き書式設定を行っています。

図:承認用のJSONは上記のようなスタイルで構築する

図:承認されると灰色にレコードが変色する

データの新規追加

本来今回のケースの場合、kintone側へデータをExcel側から入れる予定はなかったのですが、これだけやらないというのも気持ち悪いのと、他で流用できるので、ついでに装備することにしました。新規入力用フォームを用意し、尚且新規追加用のコードを用意する必要があります。追加用コードは、更新用コードと殆ど同じ形式なので

新規追加フォーム側コード

フォーム側では主にプルダウンメニューの動的生成およびDTPickerを呼び出すクラスの初期化などを行っています。また、新規追加ボタンのアクションにつても記述しております。

  • initializeではkibouという名前の希望日プルダウン上にDTPickerを呼び出して被せています(kibouのプルダウンはダミーなので、値を取る時には、DTPCBox2.valueとしてこの場合取得します。
  • 同じく、品目名のプルダウンの初期化を行い、初期値はListIndex = 0のものを指定しています。
  • 品目名プルダウンを選択した時のchangeイベントにて品名プルダウンを生成し、初期値はListIndex = 0のものを指定しています。
  • 新規追加ボタンでは各コントロールの値を取得・Validationを行い、グローバル配列であるdataArray()に追加しています。
  • kintoneInsertを呼び出し、無事に追加が出来た場合には、kintoneGetdataを呼び出して最新のデータを取得させています。
  • メールアドレスの形式チェックではこちらのサイトの関数を利用しています。

メインの追加用コード

データの新規追加は更新のコードと殆ど同じです。ただ、レコード番号の指定が無いだけですね。

  • レコードの塊はそれぞれの列用にDictionaryを用意し、valueをセット、Rec_Jsonに追加する形でJsonConverter.ConvertToJsonを行ってから、JSON形式に仕立てています。
  • JSON化したデータを元に、WinHttpにてPOSTで通信をしています。
  • 無事にデータが追加できたら、200が返ってくるので、flagmanにフラグを格納して返します。
  • 今回はシンプルなフォームなのでこんなもんですが、checkboxなどの場合や、複数のレコードをまとめてバルクインサートなどはまたやり方が異なるので、難易度が上です。
  • 組み立てたJSONは以下のような感じになります。
  • Kintone側からの返り値をCallbyNameで取得すると、新規追加時に割り当てられたレコード番号を取得することが可能です。今回はこの値をrecidに格納しています。

データの削除

Excel上でレコードの削除をしても、kintone上で削除されるわけじゃありません。ですので、レコード削除用のコマンドを用意し、それを持って削除をするようにしましょう。未処理のままの行をただExcel上で削除しても次回データ取得時に当然ですが復活してしまいますので、データの削除は必須です。kintoneの仕様上1度に削除できる件数は最大100件までです。

  • kingetdataのエンドポイントURLを使用します。
  • 一括削除が出来ますが今回は1レコード削除にしてあります。複数行削除したい場合にはids配列を複数つなげればOKです(ids[0]=1&ids[1]=3といった具合に)
  • idsに代入する値はレコードIDです。
  • WinHttpで通信するときのメソッドは「DELETE」になります。
  • 削除が成功すると200のステータスが返ってくるので、これを受信したら、Excelシート側の行をRows(レコード番号).Deleteで削除します。レコードのIDではなくレコード番号な点に注意
  • 次項にあるように、右クリックメニューから削除できるようにコマンドを追加しておくと非常に便利です

右クリックメニューにも承認ボタンを付ける

リボンにすでに承認用ボタンを付けているので、不要といえば不要なのですが、マウス操作では行選択=>即承認作業という流れでは、リボンまでの移動やボタンを選ぶ作業は地味にタイムロスやストレスになります。そこで、右クリックメニューにも承認用ボタンをつけてみます。起動時に自動的にメニューへ動的に追加します。

  • リボンのOnLoadイベント今回は利用しています。Auto_OpenのサブプロシージャでもOK
  • 今回は単一のボタン追加ですが、ネストするサブメニュー追加も可能です。また、ボタンの位置なども変更可能
  • Temporary:=Trueは付けておいたほうが良いです。ブック終了時にメニューが消えます。でないと、次回起動時にもう一個メニューが追加されてしまうため
  • Application.CommandBars("cell")でセルの右クリックメニューになります。今回は行選択なのでrowを指定しています。
  • Application.CommandBars("row").Control("コマンド名").Deleteでメニューを削除可能。コマンド名はCaptionで指定した文字列です。
  • faceidはアイコンです。番号で指定をします。こちらのサイトに対応表があるので、好きなアイコンを指定しましょう。

図:承認作業メニューが追加されました

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)