カテゴリー内の他の記事

第2回 Excelとkintoneを連携させよう

フォローする

(著者:株式会社ジョイゾー 四宮靖隆

第1回ではExcelからkintoneに接続し、レコードデータを取得するところまでを紹介しました。
今回は、取得したレコードデータの取り扱い方法を紹介したいと思います。
前回も書きましたが、kintoneから取得したデータはJSON形式で取得されます。JSONデータは文字列として取得されるため、そのままではフィールドの値を取得するロジックを書くのが大変です。
そこで、取得したJSONをパースしてオブジェクトにし、取り扱い易くします。
今回はパースする方法を2つ紹介します。

ScriptControlを利用する方法

1つ目がScriptControlを使用してJavascriptを呼ぶ方法です。ググるとこの方法がよく紹介されていますが、第1回でも説明したように64bitExcelではScriptControlが使えないためこの方法は利用できませんのでご注意下さい。
32bit
版であれば全く問題ありません。

では実際のコードです。
まず、パースするための関数(parseJSON)を作成します。

次に取得したJSONをパースし、レコードの値を取得します。例えば、フィールドコードが「title」の値を取得する場合は以下のようになります。

上記の例で行くと「record.フィールドコード.fieldvalue」といった形式で取得することができます。 ちなみに実際のJSONではfieldvalueではなくvalueに値が入っていますが、VBAで「record.フィールドコード.value」と書こうとするとvalueがValueプロパティと認識され先頭大文字に自動的に変換されてしまい値が取得できなくなるため、parseJSON関数の中でvalueをfieldvalueに置換しています。同様の理由でidをappIdに置換します。

JSONLibを利用する方法

2つ目がJSONパース用のライブラリを利用する方法です。この方法であれば、32/64bit版関係なく利用できるため、こちらの方法をお勧めします。 まず、以下のサイトからJSONLibライブラリをダウンロードします。

https://code.google.com/p/vba-json/

ダウンロードしたjson.xlsのVisual Basic Editorを開き、クラスモジュールにあるjsonlibをエクスポートします。

そして、エクスポートしたライブラリを開発しているExcelファイルに読み込ませます。
読み込ませたら以下のようにパース用関数を作ります。

 

ScriptControlを使う方法に比べるととてもシンプルですね。
後は、実際にパースをするだけですが、値の取り方が少々異なります。

JSONLibでパースした場合は連想配列として扱い、プロパティがKey項目となるので、record.item(“フィールドコード”).item(“value”)といった形式で取得します。 この場合はvalueやidをプロパティとして指定しないので置換する必要はありません。

第1回と今回のコードを参考にして頂ければkintoneからデータを取得する事ができるようになります。 次回以降はkintoneにデータをアップロードする方法を紹介したいと思います。

※JSONLibは、Google Project Hostingによって提供されているオープンソースとなっています。
注意事項をよく読み、自己責任でご利用ください。

このTipsは、2014年4月版で確認したものになります。2017年10月現在、GoogleからのJSONLibのVBA版の提供は停止されています。

 

<<第1回 Excelとkintoneを連携させよう

記事に関するフィードバック

直接的に記事と関連がないご質問はcybozu developer コミュニティをご活用ください。

Avatar
Yasutaka Shinomiya

JSONLibですが

http://vba-json.googlecode.com/svn/trunk/

こちらからjson.xlsを直接取得できます。

Avatar
皆川 真人

有益な情報ありがとうございます! JSBONLibを使って利用しているのですが、サブテーブル内のフィールド値の取得方法が知りたいです。

Avatar
可児展孝

>サブテーブル内のフィールド値の取得方法
私も同様の処理が必要だったので、レスポンス情報を見て実装しました。
サブテーブルの値は以下のように返ってくるので、サブテーブルの項目に対して主テーブルと同様の取得処理を行ってやれば取得できます。

{"records":[
{
"会社名":{"type":"SINGLE_LINE_TEXT","value":"戸田ネットソリューションズ"},
"レコード番号":{"type":"RECORD_NUMBER","value":"40"},
"TEL":{"type":"SINGLE_LINE_TEXT","value":"092-123-XXXX"},
"先方担当者名":{"type":"SINGLE_LINE_TEXT","value":"浜崎 孝"},
"活動履歴":{"type":"SUBTABLE","value":[
{"id":"142","value":{"活動内容":{"type":"DROP_DOWN","value":"導入相談"},
"活動日":{"type":"DATE","value":"2014-08-01"},
"添付ファイル":{"type":"FILE","value":[]},
"メモ":{"type":"SINGLE_LINE_TEXT","value":"導入相談に申し込みあり。電話にて現状・要件のヒアリングを行った。"}}},
{"id":"143","value":{"活動内容":{"type":"DROP_DOWN","value":"セミナー"},
"活動日":{"type":"DATE","value":"2014-08-06"},
"添付ファイル":{"type":"FILE","value":[]},
"メモ":{"type":"SINGLE_LINE_TEXT","value":"ハンズオンセミナーに来場いただき、実際の画面をみながらご説明。担当者の反応はよく、社内提案のための資料提供などのサポートをしていく。"}}},

Avatar
皆川 真人

可児展孝さん、レスありがとうございます! 色々と試したのですが、どうしてもサブテーブルの取得がうまくいきません。このページのサンプルで言うと、パース後の取得処理で主テーブルと同様の取得処理を行うという事でしょうか? よろしければ、サンプルコードを共有頂けますでしょうか?

Avatar
Kenta Mizuochi

横から失礼します。
アイティー・プラス・ワンの水落です。

既に解決されているかもしれませんので、ご参考までに

Dim str as String
Dim record As Variant
For Each record In objJSON("records")
Dim table As Variant
' サブテーブルのレコード数分ループ
For Each table In record("サブテーブル名")("value")
str = table("value")("フィールドコード名")("value")
Next table
Next record

Avatar
皆川 真人

Kenta Mizuochiさん、バッチリ取得出来ました!
ありがとうございました!! 助かりました。

Avatar
Kenta Mizuochi

皆川さん
取得できたようでよかったです(^^

Avatar
吉岡 三四郎

ScriptControlを利用してアプリのフォーム情報を取得しようとしておりますが、パースがうまくいきません。
なにかいい方法はございませんでしょうか?

Avatar
山下真史

お世話になります。
恐れ入りますが、checkboxの取得方法をお教えいただけると幸いです。
JSONLibを使用しています。
"CB1":{"type":"CHECK_BOX","value":["CB1_1","CB1_2","CB1_3"]}
が返ってくるのはわかりましたが、どのようなコードで判定すればよいのでしょうか。

よろしくお願いします。

Avatar
にしかわ

山下真史さん

>JSONLibでパースした場合は連想配列として扱い、プロパティがKey項目となるので、record.item(“フィールドコード”).item(“value”)といった形式で取得します。
とあるので、

record.item(“フィールドコード”).item(“value”)[0]
とかでとれませんか?(すみません。試せてないです。)

もしだめであれば、

Dim hoge As Variant
For Each hoge In Object

でループで取得するしかなさそうです。

参考:フィールド形式
https://cybozudev.zendesk.com/hc/ja/articles/202166330

Avatar
山田智司

お世話になります。

私も、checkboxの値が取得できなくて困っております。

あるチェックボックスの1番目の項目を取得したい場合、

record.item("フィールドコード").item("value[0]")

とするとプログラム自体はエラーが出ないのですが、どのレコードも値はすべてNULLになってしまいます。

なにか、こうするとうまくいったというような例はございませんでしょうか。

現状は、そもそものクエリーに "フィールドコード" in ("項目")とnot in ("項目")の両方でレコードを取得し、EXCEL上で合成しているのですが、なにか効率が悪い気がします。

ご指導のほど、よろしくお願い申し上げます。

 

山田智司により編集されました
Avatar
Yasutaka Shinomiya

山田さん

record.item("フィールドコード").item("value").item(1)

で1つ目のチェックボック項目が取得できると思いますのでお試しください。

Avatar
山田智司

Yasutaka Shinomiya様

ご回答頂きましたこと、感謝申し上げます。

やってみましたが、「プロシジャーの呼び出し、もしくは引数が不正です」と表示され、残念ながらうまく動きませんでした。

Avatar
Yasutaka Shinomiya

山田さん

 

どのようなコードを書かれたが抜粋で結構ですのでご提示いただけますでしょうか?

また、取得したレコードのチェックボックスがひとつも選択されていないとエラーになるかもしれません。

Avatar
山田智司

Yasutaka Shinomiya様

ありがとうございます。抜粋したソースを提示させて頂きます。

JSONLibを利用するの方法でクラスモジュールを設定し、レコード取得の手続きまではそのまま記載コードを利用されて頂いております。

Sub Main()

    Call hoge(" iday =THIS_MONTH() order by inday asc limit 250") ' 日付のフィールドiday条件にクエリを設定

End Sub

Sub hoge(strQuery as String)

    i=1

   for each record In objJSON(records)

        Cells(i,"F").Value = record.Item("フィールドコード").Item("Value").Item(1) 'これだと「プロシジャーの呼び出し、もしくは引数が不正です」と表示される。

        i=i+1

   next record

End Sub

ただ、ご指摘の通りこのチェックボックスはボタン代わりに利用しているため、値がひとつも選択されないレコードもあります。

その場合は、取得は難しいでしょうか。

ご指導のほど、よろしくお願いいたします。

Avatar
Yasutaka Shinomiya

record.Item("フィールドコード").Item("Value").Item(1)

「Value」のVが大文字が原因かもしれません。

Item("value").Item(1)

でいかがでしょう?

Avatar
山田智司

Yasutaka Shinomiya様

お世話になっております。

変更をしてみましたが、残念ながらこちらも同じエラーが出ました。

Avatar
やーさん

上記の「JSONLibを利用する方法」を行いたいのですが、JSON Lib のリンクが切れていて、

どこから入手すればいいのか分からず困っております。

ご教示いただけないでしょうか。

 

 

 

Avatar
cybozu Development team

やーさん 様

お世話になっております。cybozu developer network運営事務局です

こちらで確認したところ、確かにライブラリのダウンロードの提供が停止されているようです。

したがって、「JSONLibを利用する方法」が使えなくなります。

ご迷惑をおかけしまして申し訳ございませんが、当記事で紹介している「ScriptControlを利用する方法」をご参考ください。

 

ちなみに、参考までですが、個人ページなとでJSONLibのダウンロードが提供しているようです。
例、https://github.com/VBA-tools/VBA-JSON/releases
よろしくお願いいたします。

Avatar
小山剛司

「Excelとkintoneを連携させよう」勉強になります。

第2回以降のアップロード(サンプル)早く拝見したいです。

よろしくお願いいたします。

 

Avatar
cybozu Development team

小山様

ご要望のご連絡ありがとうございます。

第3回以降の執筆について、検討してまいります。

Avatar
Yuto Kawai

お世話になります。

複数のレコードを取得して、それらの同じフィールドの値を一覧としてExcelに落とし込みたい場合は

どのようにVBAを記述したらよろしいでしょうか。

Avatar
cybozu Development team

Yuto Kawai様

お世話になっております。

cybozu developer network 事務局です。

 

ご質問いただいた件ですが、

こちらのコメント欄は記事へのフィードバック用となります。

機能拡張のカスタマイズ方法等についてはぜひコミュニティをご活用ください。

https://developer.cybozu.io/hc/ja/community/topics

 

よろしくお願いいたします。

Avatar
持田善行

いつもお世話になっております。
「ScriptControlを利用する方法」に記載されているサンプルスクリプトについて、基本的なことで恐縮ですが以下3点ご教示ください。

(1)「まず、パースするための関数(parseJSON)を作成します。」直下のコード
29行目はparseJSON変数に値を入れていますが、コメントにもあるようにこれはobjJSON変数ではと推測しておりますが、誤ってますでしょうか。
(2)「次に取得したJSONをパースし・・・」直下のコード
サンプルコード通り16行目でobjJSON.recordsを指定すると「オブジェクト変数またはWithブロック変数が設定されておりません」と表示されます。
プログラムに応じて書き直しが必要ということでしょうか。
(3)現状、GoogleからJSONLibの提供が停止されているため、ScriptControlにて実装しようと考えておりますが、他のJSONパース用ライブラリの候補
としてはcJobjectになりますでしょうか。

以上よろしくお願いいたします。

Avatar
持田善行

度々申し訳ありません。

上記の(1)については、VBAによる戻り値の仕様でしたので、取り下げさせていただきます。

お手数ですが、(2)、(3)は引き続きよろしくお願いいたします。

Avatar
cybozu Development team

持田善行 様

お世話になっております。
cybozu developer network 事務局です。

(2)技術的な質問につきましては、コミュニティをご活用いただければ幸いです。
   有志の技術者からフィードバックを頂けると思います。

(3)他パース用のライブラリですが、ご指摘の通りcJojectがあるようです。
   cJobjectを使ったJSONのパース方法を紹介しているQiitaの記事がありましたので、ご参考いただければと思います。
   https://qiita.com/rex0220/items/b29b8e260a9b5c96432f

宜しくお願い致します。

cybozu Development teamにより編集されました
Avatar
持田善行

cybozu developer network 事務局様

 

いつもお世話になっております。

ご連絡ありがとうございました。

ご回答いただきました件、承知いたしました。

 

ログインしてコメントを残してください。