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

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

最後に、ボタンクリックでワークシートを生成するスクリプトを書いていきましょう。このスクリプトは、［ワークシートを生成する］ボタンをクリックした際に呼び出されるイベントハンドラGenerateCommandButton_Clickとして記述します（リスト7以降）。以下の流れで、テーブルの内容からワークシートを生成し、データをセルに書き込んでいます。

①既存の生成されたワークシートを削除する
②テンプレートを複製して必要箇所を書き換える

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

［リスト7］ワークシートを生成してセルに書き込むスクリプト（その1）

' (1)ワークシート削除時の確認ダイアログを非表示にする
Application.DisplayAlerts = False

' (2)テンプレートのワークシートを取得しておく
Dim TemplateWorksheet As Worksheet
Set TemplateWorksheet = Worksheets("Template")

' (3)既存のワークシートを削除する
Dim Index As Integer
Index = TemplateWorksheet.Index + 1
While Index <= Worksheets.Count
    Worksheets(Index).Delete
Wend
Application.DisplayAlerts = True

①に相当するスクリプトです。（1）では、ワークシートの削除時に表示される確認ダイアログを非表示にしています。削除の終了後には、表示されるように設定を戻しています。
（2）では、テンプレートのワークシートを取得しています。このテンプレートを複製し、新たに生成するワークシートのひな型としています。
（3）からは、既存の生成されたワークシートを削除しています。テンプレートのワークシートより大きなインデックスを持つワークシートを全て削除するだけです。

［リスト7］ワークシートを生成してセルに書き込むスクリプト（その1）

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

' (5)全ての行について処理する
Dim RowIndex As Integer, ColIndex As Integer
For RowIndex = 1 To Table.DataBodyRange.Rows.Count
    ' (6)フィルターされていない行のみ
    If Not Table.DataBodyRange.Rows(RowIndex).Hidden Then
        ' (7)テンプレートシートをコピーして名前を設定する
        Dim NewWorksheet As Variant
        TemplateWorksheet.Copy After:=TemplateWorksheet
        Set NewWorksheet = ActiveSheet
        NewWorksheet.Name = Table.DataBodyRange.Cells(RowIndex, 1)
        ' (8)コピーしたワークシートの内容を書き換える
        Dim Cell As Variant
        For ColIndex = 1 To ColumnList.Count
            Set Cell = NewWorksheet.Cells.Find(What:=ColumnList(ColIndex))
            If Not Cell Is Nothing Then
                Cell.Value = Table.DataBodyRange.Cells(RowIndex, ColIndex)
            End If
        Next
        Set Cell = NewWorksheet.Cells.Find(What:="日付")
        If Not Cell Is Nothing Then
            Cell.Value = Format(Now, "yyyy年mm月dd日")
        End If
    End If
Next

' (9)左端のワークシートをアクティブにする
Worksheets(1).Activate

②に相当するスクリプトです。（4）からは、アプリデータ読み込み時と同様に、テーブルをオブジェクトTableとして取得し、列リストをオブジェクトColumnListとして取得しています。テーブルはセルのデータを参照するために、列リストは生成したワークシートにあるキー文字列を検索するために、それぞれ使用されます。
（5）からは、テーブルの各行に対する処理です。テーブルの各行は、DataBodyRangeプロパティで参照します。
（6）で、可視な行に対してのみ処理を行うことにしています。これは、テーブルのフィルタによって行が絞り込まれているときに、非表示の行を処理から外すためです。
（7）でテンプレートをCopyメソッドでコピーして、名前（Nameプロパティ）をテーブルの第1列の内容としています。
（8）からは、コピーしたワークシートから列名に一致するキー文字列があれば、それをアプリデータで置き換えます。日付だけは、現在日時を「年月日」に整形して置き換えています。
最後に（9）では、左端のワークシート（List）をアクティブにしています。これは、Copyメソッドの実行でアクティブなシートがコピーされたシートに切り替わるので、それを元に戻すためです。

これで、スクリプトの作成は終了です。ワークシートの形を整えて冒頭の図1のようになれば完成です。

まとめ

前回と今回は、Cybozuのkintone REST APIを使って、kintoneアプリの取り扱いについて紹介しました。今回は、APIの提供する最も基本的な部分のみを使った例を紹介しましたが、アプリを変更したり、他のAPIを使うことで活用の幅が拡がりそうだと感じていただけたのではないでしょうか。

WINGSプロジェクト 山内直著/山田祥寛監修
＜WINGSプロジェクトについて＞テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。一緒に執筆をできる有志を募集中