Googleスプレッドシートからデータを取得してみよう【Google Sheets API】後編

連載第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）。これが空なら何らかのエラーが発生していますが、これをそのまま関数の戻り値として返します。

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

次に、ボタンクリックでスプレッドシートを読み込むスクリプトを書いていきましょう。このスクリプトは、「データを取得する」ボタンをクリックした際に呼び出されるイベントハンドラとして記述します。コンボボックスにイベントハンドラを追加した要領で、イベントハンドラGetCommandButton_Clickを作成してください（リスト3以降）。以下のような流れで、スプレッドシートからデータを取得して結果をセルに書き込んでいます。

①スプレッドシートIDのリストを取得する
②書き込むセル位置を決定し、テーブルをクリアする
③APIを呼び出し、結果がJSONか判定する
④問題なければJSONをディクショナリに変換してデータを取得する
⑤エラーがない場合にはデータを取り出してセルに書き込む

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

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

' データ取得ボタンをクリックしたときの処理
Private Sub GetCommandButton_Click()
    ' (1)スプレッドシートIDのリストを取得する
    Dim IdWorksheet As Worksheet
    Set IdWorksheet = Worksheets("SheetIDs")
    Dim IdList As Variant
    IdList = IdWorksheet.Range("C4:C13")

①に相当するスクリプトです。
（1）では、GoogleスプレッドシートIDのリストをSheetIDsワークシートから取得しています。範囲は決め打ちとしているので、IDリストの表の配置に合わせて必要に応じて修正してください。結果は、配列IdListに格納します。

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

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

    ' (3)セルに書き込んだデータの数を保持
    Dim Count As Integer
    Count = 0

    ' (4)テーブルをあらかじめクリアする
    Range(Cells(Row, 1), _
          Cells(Row + MaxSheetCount * MaxDataCount - 1, 5)).Clear

②に相当するスクリプトです。
（2）では、変数Row、変数Colを宣言しています。これは、Googleスプレッドシートからの取得データを書き込むセル位置の起点です。もし、テーブルの位置を変えた場合には、それぞれ変更してください。
（3）では、セルに書き込んだデータの数を保持する変数Countを0に初期化しています。
（4）では、データ取得に先立ちテーブルのデータ部分をあらかじめクリアしています。シンプルに、考えられる最大範囲をクリアしてしまっています。

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

    ' (5)指定されているスプレッドシートについて繰り返す
    Dim i As Integer
    For i = 1 To MaxSheetCount

        ' (6)スプレッドシートIDが空なら終了
        If IdList(i, 1) = "" Then
            Exit For
        End If

        ' (7)パス文字列とパラメータ文字列（今回は空）を構築する
        Dim Path As String, Param As String
        Path = IdList(i, 1) & "/values/" & _
               WorksheetFunction.EncodeURL(SheetName)
        Param = ""

        ' (8)APIを呼び出す
        Dim Result As String
        Result = KickWebService(Path, Param)

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

③に相当するスクリプトです。
（5）では、スプレッドシートの最大数の繰り返しを開始しています。SheetIDsワークシートに格納されたIDの数が、必ずしも最大（MaxSheetCount＝10）であるとは限らないので、（6）においてIDの設定がなくなった時点で繰り返しを強制終了させています。
（7）では、KickWebService関数に渡すパスとクエリパラメータを生成しています。ここでは、Googleスプレッドシートから全データを取得するものとして、「スプレッドシートID/values/シート名」をパスとして生成しています。シート名をURLエンコードしていますが、これは日本語のシート名をそのままURLに含めることはできないためです。URLエンコードには、第9回と同様にWorksheetFunctionオブジェクトのEncodeURL関数を使用しています。クエリパラメータは必要ないので、空としています。
（8）では、生成したパスとクエリパラメータに基づき、APIを呼び出しています。結果がJSON文字列でない場合には、エラーとしてその旨をセルに書き込んで繰り返しを中止しています。

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

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

        ' errorキーがあれば処理を終了する
        If Json.Exists("error") Then
            Cells(Row, Col) = Json("error")
            Exit For
        End If

        ' (10)ワークシート上の値だけ取り出す
        Dim Values As Variant
        Set Values = Json("values")

        ' 取得データ格納用の配列を初期化する
        Dim List As Variant
        ReDim List(1 To MaxDataCount, 1 To 5)

④に相当するスクリプトです。
（9）では、取得したJSON文字列をディクショナリ形式に変換し、errorキーがあれば問題が発生しているとしてエラーメッセージをセルに書き込んで繰り返しを中止しています。
（10）からは、データの本体であるvaluesキーの値を取得し、セルに書き込む値を保持する配列を作成しています。これは、セルへの書き込みをできるだけまとめることで、パフォーマンスの悪化を防ぐためです。

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

        ' (11)取得した全データに対して繰り返す
        Dim j As Integer, DataCount As Integer
        DataCount = 0
        For j = 1 To MaxDataCount
            ' 入力欄が空なら終了
            If Values(2 + j).Count < 4 Then
                Exit For
            End If

            ' (12)JSONデータの内容を配列に格納する
            DataCount = DataCount + 1
            Count = Count + 1
            List(j, 1) = Count              ' No
            List(j, 2) = Values(1)(4)       ' 営業
            List(j, 3) = Values(2 + j)(3)   ' 取引先
            List(j, 4) = Values(2 + j)(2)   ' 品目
            List(j, 5) = Values(2 + j)(4)   ' 数量
        Next

        ' (13)配列をセルに書き込む
        Range(Cells(Row, Col), _
              Cells(Row + DataCount - 1, Col + 5 - 1)).Value = List
        Row = Row + DataCount
    Next
End Sub

⑤に相当するスクリプトです。 （11）からは、JSONデータから取り出したvaluesキーの値について繰り返しを開始しています。valuesキーの値は2次元の配列であるからです。配列の2次元目の要素数が4に満たない、すなわち取得元のセルが空である場合には、これ以上データがないものとして繰り返しを中止しています。 （12）からは、JSONデータの値を個別に配列に移しています。配列の有効なデータ数は変数DataCountが保持しているので、繰り返しの終了後に（13）において配列の内容をセルに書き込みます。

これで、スクリプトの作成は終了です。ワークシートの形を整えて冒頭の図1のようになれば完成です。スプレッドシートが固定だと何度実行しても結果は同じなので、各自で独自にスプレッドシートを用意して試してみることをおすすめします。

まとめ

前回と今回は、Google Sheets APIを使って、Googleスプレッドシートの取り扱いについて紹介しました。今回は、APIの提供する最も基本的な部分のみを使った例を紹介しましたが、処理対象のスプレッドシートのパターンを変更したり、他のAPIを使うことで活用の幅が拡がりそうだと感じていただけたのではないでしょうか。

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