ゼロからはじめるExcel VBA＋Webサービス(19) kintoneデータからワークシートを自動生成してみよう【kintone REST API】

連載第19回の目的

連載第18回と第19回では、Excel VBAにおけるkintone REST APIの活用について紹介します。サイボウズが提供するこのAPIは、同社のサービスkintoneの操作のための機能を無償で提供します。今回は、このkintone REST APIを使用して、kintoneアプリからデータを読み取り、手元のExcelワークシートに反映して活用するサンプルを作成します（図1）。今回は、APIを使ってアプリからデータを取得し、データからワークシートを自動生成するサンプルを作成していきます。なお、取得されるデータはサンプルとして用意されたダミーです。実在するものではありませんのでご注意ください。

図1：完成サンプルイメージ

▼完成サンプルのExcelファイル
https://github.com/wateryinhare62/mynavi_excelvba_webservice

なお、本連載では動作確認をWindows 10 Pro（64bit）、Microsoft 365（Excel 16.0、VBA 7.1）で行っています。旧バージョンや単体のExcelで試す場合にはご注意ください。

ワークシートの準備

ここからは、ワークシートを用意し、基本的なデザインを行っていきましょう。今回は、ワークシートを3個使います。シート1に配置した2つのボタンを使って、シート2にあるAPIアクセス用のデータを用いて顧客リストを取得し、そのデータとシート3のひな型を使って各顧客に向けたワークシートを生成します。配置および書き込むものは、それぞれ以下の通りです（カッコ内はワークシートやコントロールの名前など）。

・シート1：リストシート（List）：ボタンや取得結果を格納
　　アプリケーションのタイトル（セルのテキスト）
　　「データを取得する」ボタン（GetCommandButton）
　　「ワークシートを生成する」ボタン（GenerateCommandButton）
　　取得結果を収納するテーブル（顧客リスト）
・シート2：固定データのシート（Data）：kintoneにアクセスするための情報
　　アプリケーションのタイトルなど（セルのテキスト）
　　サブドメイン、アプリID、APIトークンを記載した表（セルのテキスト）
・シート3：テンプレートシート（Template）：生成するワークシートのひな型
　　文面など（セルのテキスト）
　　会社名、部署名、担当者名、日付などのキー文字列（セルのテキスト）

上記のテキストおよびコントロールを、図2～図4を参考に書き込み、配置してください。具体的な手順は、第6回などを参考にしてください。ワークシートの名前はスクリプトから参照されますので、名前の付け忘れに注意してください。

図2：リストのワークシート（List）が準備された状態

図3：固定データのワークシート（Data）が準備された状態

図4：テンプレートのワークシート（Template）が準備された状態

シート1：リストのワークシート（List）

1個目のシート（リストのワークシート）には、「List」という名前を付けておきます。

・コントロールを配置する
コントロール（コマンドボタン×2）をワークシート上部に配置して、プロパティを設定します。これらのプロパティの内容は、表1のとおりです。

▲表1：コントロールに設定するプロパティ

コントロール	プロパティ	値
「データを取得する」ボタン	Name	GetCommandButton
「データを取得する」ボタン	Caption	データを取得する
「ワークシートを生成する」ボタン	Name	GenerateCommandButton
「ワークシートを生成する」ボタン	Caption	ワークシートを生成する

・取得結果を収納するテーブル
取得結果を収納するテーブルは、以下の構成とします。テーブルにすることで、取得後にフィルタなどの操作が可能になります。テーブルとするには、以下の見出しを入力したセルを選択し、［ホーム］―［テーブルとして書式設定］を選択します。デザインはお好みのものを選んでください。先頭行を見出し行にする指定も忘れないでください。テーブル名は、「顧客リスト」としておきます。なお、ここで設定した見出し項目をキーと見なして、アプリからデータの取得が行われます。

レコード番号、会社名、部署名、担当者名、顧客ランク

シート2：固定データのワークシート（Data）

固定データのワークシートには、「Data」という名前を付けておきます。

・固定データを記載した表
表は、以下の構成とします。普通にセルのテキストとして配置します。データの位置はスクリプトで決め打ちしているので、配置を変更する場合には注意してください。
・「No」（番号、プログラム上は意味はありません）
・「項目」（項目名、プログラム上は意味はありません）
・「内容」（項目のデータ。これらをスクリプトから取得します）

シート3：テンプレートのワークシート（Template）

3個目のワークシートには、「Template」という名前を付けておきます。ワークシートの内容自体は何でもよく、スクリプトから書き換える項目をキー文字列として配置しておくだけです。ここでは、顧客に送付する案内状をイメージして、会社名、部署名、担当者名と日付を配置することにします。

ここで、ブックを保存します。ブック名は何でもよいですが、形式を「Excel マクロ有効ブック (*.xlsm)」にしてください。マクロを有効にしておかないと、VBAのスクリプトを実行できないからです。このあとも、適当なタイミングでブックを保存してください。

スクリプトを書いていく

ここから、Visual Basic Editorを使って、以下の順番でスクリプトを書いていきます。左ペインの「Microsoft Excel Objects」の「Sheet1 (List)」をダブルクリックして開くファイルに、以下のスクリプトを記述していきます。

・共有する変数を定義するスクリプト
・APIにアクセスするスクリプト
・ボタンクリックでデータを読み込むスクリプト
・ボタンクリックでワークシートを生成するスクリプト

［NOTE］VBA-JSON
kintone REST APIは、結果をJSONデータで返します。このようにVBAからJSONデータを使いますので、そのために必要なライブラリを準備します。第3回などと同様に、「VBA-JSON」というサードパーティのライブラリを使用します。第3回の記事を参照して、「Microsoft Scripting Runtime」への参照設定を有効にすることを含めて、VBA-JSONを準備してください。

共有する変数を定義するスクリプト

サブドメインやAPIトークンといった、利用者ごとに異なる情報は専用のワークシートに記載しておくことにしました。ここでは、それぞれの変数を定義し、そこに格納しておきます。これらの変数は、Dataワークシートから取得して値が設定されます。なお、変数の定義とは直接の関係はないですが、安全のために冒頭でOption Explicit文によって未定義の変数を使えないようにしています（リスト1）。

［リスト1］共有する変数を定義

' 未定義の変数を使えないようにする
Option Explicit

' サブドメインを保持
Dim SubDomain As String
' アプリIDを保持
Dim AppId As String
' APIトークンを保持
Dim ApiToken As String

APIにアクセスするスクリプト

APIにアクセスするスクリプトは、拡張性を考慮して関数として独立させます。APIを呼び出す関数KickWebServiceは、引数にAPIパスとクエリパラメータを受け取って、結果としてのJSON文字列を返します（リスト2）。

［リスト2］KickWebService関数

' APIを呼び出す（引数はAPIの種類とパラメータ、戻り値はJSON文字列）
Private Function KickWebService(ByVal Path As String, _
                                ByVal Param As String) As String
    ' (1)URLを作成する
    Dim Url As String
    Url = "https://" & SubDomain & ".cybozu.com/k/v1/" & Path & "?" & Param
    ' (2)リクエストを送信する
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        ' (3)GETメソッドで送信する
        .Open "GET", Url, False
        ' (4)リクエストヘッダにAPIトークンを含める
        .setRequestHeader "X-Cybozu-API-Token", ApiToken
        .send
        ' (5)リクエスト結果を取得する
        KickWebService = .responseText
    End With
End Function

変数Urlに、エンドポイント、APIパス（path）、クエリパラメータ（param）をセットします（1）。HTTPアクセスに必要なMSXML2.XMLHTTPモジュールのオブジェクトを作成後（2）、HTTPメソッドとURLを指定してOpenメソッドを呼び出し（3）、必要なリクエストパラメータを設定した後、sendメソッドで実際に呼び出します（4）。呼び出した結果はResponseTextプロパティで取得できます（5）。これが空なら何らかのエラーが発生していますが、これをそのまま関数の戻り値として返します。

ボタンクリックでアプリのデータを読み込むスクリプト

次に、ボタンクリックでアプリのデータを読み込むスクリプトを書いていきましょう。このスクリプトは、［データを取得する］ボタンをクリックした際に呼び出されるイベントハンドラGetCommandButton_Clickとして記述します（リスト3以降）。以下の流れで、アプリからデータを取得して結果をセルに書き込んでいます。

①固定データを取得し、書き込むセル位置を決定する
②APIを呼び出し、結果がJSON文字列であればディクショナリに変換する
③ディクショナリ内の配列から各レコードを取り出してアプリデータ配列に格納する
④アプリデータ配列の内容をテーブルに書き込む

以下は、リストの解説になります。上記の流れに合わせて適宜区切っています。

［リスト3］アプリからデータを取得してセルに書き込むスクリプト（その1）

' (1)各種固定データを取得する
Dim DataWorksheet As Worksheet
Set DataWorksheet = Worksheets("Data")
SubDomain = DataWorksheet.Range("C4").Value
AppId = DataWorksheet.Range("C5").Value
ApiToken = DataWorksheet.Range("C6").Value

' (2)データを書き込むセル位置の起点を決定する
Dim Row As Integer, Col As Integer
Row = 6
Col = 1

①に相当するスクリプトです。（1）からは、サブドメインやアプリIDなどをDataワークシートから取得しています。セル位置は決め打ちとしているので、必要に応じて修正してください。結果は、それぞれの変数に格納します。
（2）からは、変数Row、変数Colを宣言して初期化しています。これは、アプリのデータを書き込むセル位置（テーブル）の起点です。もし、テーブルの位置を変えた場合には、それぞれ変更してください。

［リスト4］アプリからデータを取得してセルに書き込むスクリプト（その2）

' (3)パラメータ文字列を構築する
Dim Param As String
Param = "app=" & AppId

' (4)APIを呼び出す
Dim Result As String
Result = KickWebService("records.json", Param)

' (5)JSONデータが戻ってこない場合は処理を終了する
If Left(Result, 1) <> "[" And Left(Result, 1) <> "{" Then
    Cells(Row, Col) = "取得結果不正"
    Exit Sub
End If

' (6)JSON文字列をディクショナリ形式に変換する
Dim Json As Object
Set Json = JsonConverter.ParseJson(Result)

' (7)recordsキーがなければ処理を終了する
If Not Json.Exists("records") Then
    Cells(Row, Col) = Json("message")
    Exit Sub
End If

②に相当するスクリプトです。
（3）からは、KickWebService関数に渡すクエリパラメータを生成しています。ここでは、アプリIDを指定するのみの「app=AppId」をクエリパラメータとして生成しています。
（4）からは、生成したクエリパラメータに基づき、複数レコードの取得のためのAPIパス"records.json"を指定し、APIを呼び出しています。結果がJSON文字列でない場合には、エラーとしてその旨をセルに書き込んで処理を中止しています（5）。
（6）からは、取得したJSON文字列をディクショナリ形式に変換し、recordsキーがなければ問題が発生しているとして、messageキーのエラーメッセージをセルに書き込んで処理を中止しています（7）。

［リスト5］アプリからデータを取得してセルに書き込むスクリプト（その3）

' (8)データ配列と要素数を取り出す
Dim Items As Variant, ItemCount As Integer
Set Items = Json("records")
ItemCount = Items.Count

' (9)テーブルと列リストを取得しておく
Dim Table As Variant, ColumnList As Variant
Set Table = ActiveSheet.ListObjects("顧客リスト")
Set ColumnList = Table.ListColumns

' (10)アプリデータ配列を初期化する
Dim ItemList As Variant
ReDim ItemList(1 To ItemCount, 1 To ColumnList.Count)

' (11)取得した全レコードについて処理する
Dim Count As Integer
For Count = 1 To ItemCount
    Dim Item As Variant
    Set Item = Items(Count)
    ' (12)テーブルの各列について処理する
    Dim Index As Integer
    For Index = 1 To ColumnList.Count
        Dim Key As String, Value As Variant
        Key = ColumnList(Index)
        ' (13)列名がキーに存在すればデータを取得する
        If Item.Exists(Key) Then
            Set Value = Item(Key)
            ItemList(Count, Index) = Value("value")
        Else
            ItemList(Count, Index) = "データなし"
        End If
    Next
Next

③に相当するスクリプトです。（8）からは、JSONデータからrecordsキーの値を配列Itemsとして取得して、レコード数を変数ItemCountに格納しています。
（9）からは、テーブルをオブジェクトTableとして取得し、列リストをColumnListとして取得しています。この列リストは、JSONデータから各項目を取得する際のキーとして使用します。
（10）からは、レコード数に基づき、アプリデータ配列の大きさを設定しています。アプリデータを配列で保持するのは、セルへの書き込みをまとめることで、パフォーマンスの悪化を防ぐためです。（11）からは、各レコードについての処理です。各レコードは、（12）からテーブルの各列について処理します。処理内容は、テーブルの列名をキーとしてレコードに存在するか確認し、存在すれば値を取得してアプリデータ配列に格納するというものです（13）。存在しなければ、「データなし」をアプリデータ配列に格納します。

［リスト6］アプリからデータを取得してセルに書き込むスクリプト（その4）

' (14)配列をセルに書き込む
Table.Resize Table.Range.Resize(ItemCount + 1)
Range(Cells(Row, Col), _
    Cells(Row + ItemCount - 1, Col + ColumnList.Count - 1)).Value = ItemList

④に相当するスクリプトです。（14）からは、テーブルの行数を取得したレコード数に合わせて調整し、アプリデータ配列の内容を書き込んでいます。