連載第19回の目的

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

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

    図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)が準備された状態

    図2:リストのワークシート(List)が準備された状態

  • 図3:固定データのワークシート(Data)が準備された状態

    図3:固定データのワークシート(Data)が準備された状態

  • 図4:テンプレートのワークシート(Template)が準備された状態

    図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)からは、テーブルの行数を取得したレコード数に合わせて調整し、アプリデータ配列の内容を書き込んでいます。

ボタンクリックでワークシートを生成するスクリプト

この記事は
Members+会員の方のみ御覧いただけます

ログイン/無料会員登録

会員サービスの詳細はこちら