連載第15回の目的

連載第15回では、前回の続きとしてExcel VBAにおけるGoogle Sheets APIの活用について紹介します。前回で、APIの概要を紹介し、APIキーの取得やAPI呼び出しをテストしました。今回はワークシートを準備してスクリプトを作成し、サンプルを完成させます（図1）。

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

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

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

ワークシートの準備

APIが使えることを確認できたら、ワークシートを用意し、基本的なデザインを行っていきましょう。今回は、ワークシートを2個使います。1個目のシートは、ボタンや取得結果を格納するためのものです。2個目のシートは、データ取得対象のGoogleスプレッドシートIDのリストを格納するためのものです。スプレッドシートIDのリストは変更されることが少ないとして、別シートに配置することにしました。
配置および書き込むものは、それぞれ以下の通りです。

・シート1：集計シート（Account）
　　アプリケーションのタイトル（セルのテキスト）
　　「データを取得する」ボタン（GetCommandButton）
　　データ取得対象日（セルのテキスト）
　　取得結果を収納する表（テーブル）

  • 図2：集計シート（Account）が準備された状態

・シート2：GoogleスプレッドシートIDリストのシート（SheetIDs）
　　アプリケーションのタイトルなど（セルのテキスト）
　　GoogleスプレッドシートのIDを記載した表（セルのテキスト）

  • 図3：IDリストのシート（SheetIDs）が準備された状態

上記のテキストおよびコントロールを、図2と図3を参考に書き込み、配置してください。具体的な手順は、第6回などを参考にしてください。

シート2：GoogleスプレッドシートIDリストのシート（SheetIDs）

2個目のシート（IDリストのシート）から作業します。IDリストのシートには、「SheetIDs」という名前を付けておきます。この名前はスクリプトから参照されますので、名前の付け忘れに注意してください。

・GoogleスプレッドシートのIDを記載した表
表は、以下の構成とします。最大10個のGoogleスプレッドシートIDを登録できることにします。普通にセルのテキストとして配置します。

・「No」（番号、プログラム上は意味はありません）
・「営業」（営業の名前。人間のための識別用です）
・「スプレッドシートID」（営業に対応したGoogleスプレッドシートのID。このIDをスクリプトから取得します）

スクリプトでは、最大10個のGoogleスプレッドシートを扱うことを想定しているので、数を増減する場合にはスクリプトの変更も必要になることに注意してください。

シート1：集計シート（Account）

1個目のシート（集計シート）には、「Account」という名前を付けておきます。既定のワークシートとなるので名前の変更は特に必要ないですが、シート2とのバランス上、変更しています。

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

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

・取得結果を収納する表
表は、取得対象のGoogleスプレッドシートの構成に倣って、以下の構成とします。取得後に、並び換えなどの操作が可能になるように、テーブルとしておきます。テーブルとするには、以下の見出しを入力したセルを選択し、［ホーム］―［テーブルとして書式設定］を選択します。デザインはお好みのものを選んでください。先頭行を見出し行にする指定も忘れないでください。

  • 「No」（番号、プログラム上は意味はありません）
  • 「営業」（営業の名前）
  • 「取引先」（受注した取引先の名称）
  • 「品目」（受注した品目の名称）
  • 「数量」（受注した数量）

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

ライブラリ等を準備する

スクリプトを書き始める前に、必要なライブラリ等の準備をしておきます。

VBA-JSON

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

スクリプトを書いていく

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

  • エンドポイントURLやAPIキーの定数を定義するスクリプト
  • APIにアクセスするスクリプト
  • ボタンクリックでスプレッドシートを読み込むスクリプト

エンドポイントURLやAPIキーの定数を定義するスクリプト

エンドポイントURLやAPIキーといった、アプリケーションの実行中に変化しない文字列は、定数として定義しておきます。なお、定数の定義とは直接の関係はないですが、安全のために冒頭でOption Explicit文によって未定義の変数を使えないようにしています（リスト1）。ApiKeyには、おのおの取得したAPIキーを設定してください。

［リスト1］エンドポイントURLやAPIキーの定数を定義

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

' エンドポイントURLを定数で共有
Const EndPoint = "https://sheets.googleapis.com/v4/spreadsheets/"
' APIキーを定数で共有
Const ApiKey = "AIza……"
' 取得対象のシート名
Const SheetName = "シート1"
' 処理できるスプレッドシートの最大数
Const MaxSheetCount = 10
' 処理できるスプレッドシートあたりのデータ数
Const MaxDataCount = 10

取得対象のシート名は、Googleスプレッドシートにおける既定です。著者は特に変更していないので、既定値をそのままスクリプト中で使っています。もしワークシート側で変更しているなら、ここも変更してください。 MaxSheetCountとMaxDataCountは、それぞれ処理できるスプレッドシート数、ワークシートあたりのデータ数です。サンプルなので少なめの数値（10）にしています。既述の通り、スプレッドシート数（MaxSheetCount）はワークシート（SheetIDs）と合わせておく必要があります。スプレッドシートあたりのデータ数（MaxDataCount）も同様です。

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

続けて、APIにアクセスするスクリプトを書いていきます。拡張性を考慮して、APIへのアクセスは関数として独立させます。APIを呼び出すスクリプトは、KickWebServiceという関数にします。KickWebServiceは、引数にパス（APIの種類を表すエンドポイントの一部）とクエリパラメータを受け取って、結果としてのJSON文字列を返す関数です（リスト2）。

［リスト2］KickWebService関数

' APIを呼び出す（引数はAPIの種類とパラメータ、戻り値はJSON文字列）
Private Function KickWebService(ByVal Path As String, _
                                ByVal Param As String) As String
    ' (1)APIキーが唯一のパラメータとなるか判定して区切り文字を決める
    If Param = "" Then
        Param = "?"
    Else
        Param = "?" & Param & "&"
    End If

    ' (2)URLを作成する
    Dim Url As String
    Url = EndPoint & Path & Param & "key=" & ApiKey
    ' (3)MSXML2.XMLHTTPモジュールのオブジェクトを作成
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        ' (4)リクエストを送信する
        .Open "GET", Url, False
        .send
        ' (5)リクエスト結果を取得する
        KickWebService = .responseText
    End With
End Function

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

ボタンクリックでスプレッドシートを読み込むスクリプト

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

ログイン／無料会員登録

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