連載第17回の目的

連載第17回では、第14回と第15回と第16回と同様にExcel VBAにおけるGoogle Sheets APIの活用について紹介します。これらの回では、Google Drive上にある複数のスプレッドシートのデータを、手元のExcelワークシートにまとめて反映するサンプルを紹介しました。今回は、この逆の処理を行うものとして、手元のExcelワークシートにあるデータを、Google Drive上にあるスプレッドシートに反映するサンプルを紹介します（図1）。今回は、ワークシートやライブラリ等を準備してスクリプトを書いていきます。

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

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

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

ワークシートの準備

APIが使えることを確認できたら、ワークシートを用意し、基本的なデザインを行っていきましょう。第16回と同様に、今回もワークシートを2個使います。1個目のシートは、ボタンや送信するデータを格納するためのものです。2個目のシートは、データ反映対象のスプレッドシートIDとアクセストークンを格納するためのものです。送信するデータそのものと、内部的に使うスプレッドシートIDやアクセストークンは分けた方が良いとして、別シートに配置することにしました。
配置および書き込むものは、それぞれ以下の通りです。

・シート1：データシート（Data）
　　アプリケーションのタイトル（セルのテキスト）
　　「データを反映する」ボタン（PostCommandButton）
　　対象日（セルのテキスト）
　　営業名（セルのテキスト）
　　送信するデータを収納した表（テーブル）
・シート2：アクセストークンとスプレッドシートIDなどの情報シート（Info）
　　アプリケーションのタイトルなど（セルのテキスト）
　　アクセストークン（セルのテキスト）
　　スプレッドシートID（セルのテキスト）

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

  • 図2：データシート（Data）が準備された状態

  • 図3：情報シート（Info）が準備された状態

シート2：アクセストークンとスプレッドシートIDなどの情報シート（Info）

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

シート1：データシート（Data）

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

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

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

コントロール プロパティ 値
「データを反映する」ボタン Name PostCommandButton
Caption データを反映する

・「営業」（営業の名前）

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

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

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

ライブラリ等を準備する

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

VBA-JSONを準備する

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

Microsoft ActiveX Data Objects Libraryを準備する

JSONデータをPOST/PUTメソッドでAPIに送信する際には、JSONデータをUTF-8形式にエンコードする必要があります。そのための関数の作成に、ADODB.Streamモジュールを使用します。このモジュールの使用にはMicrosoft ActiveX Data Objects Libraryが必要になりますので、これへの参照設定を第7回の記事を参照して有効にしておいてください。

スクリプトを書いていく

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

・エンドポイントURLなどの定数とグローバル変数を定義するスクリプト
・APIにアクセスするスクリプト
・ボタンクリックでスプレッドシートへ反映するスクリプト

エンドポイントURLなどの定数とグローバル変数を定義するスクリプト

エンドポイントURLといった、アプリケーションの実行中に変化しない文字列は、定数として定義しておきます。また、アクセストークンやスプレッドシートIDなど複数のプロシージャ間で共有する変数は、グローバル変数として定義しておきます。なお、定数や変数の定義とは直接の関係はないですが、安全のために冒頭でOption Explicit文によって、未定義の定数と変数を使えないようにしています（リスト1）。

［リスト1］エンドポイントURLなどの定数とグローバル変数を定義するスクリプト

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

' エンドポイントURLを定数で共有
Const EndPoint = "https://sheets.googleapis.com/v4/spreadsheets/"
' 処理できるワークシートあたりのデータ数
Const MaxDataCount = 10
' アクセストークン保持用
Dim AccessToken As String
' 反映対象のスプレッドシートID保持用
Dim SheetId As String

EndPointは、Sheets APIを呼び出すためのエンドポイントの共通部分です。MaxDataCountは、ワークシートあたりの最大データ数です。AccessTokenは、ワークシート（Info）から読み出したアクセストークンを保持するための変数です。SheetIdも、ワークシート（Info）から読み出したスプレッドシートIDを保持するための変数です。

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

続けて、APIにアクセスするスクリプトを書いていきます。拡張性を考慮して、APIへのアクセスは関数として独立させます。APIを呼び出すスクリプトは、KickWebServiceという関数にします。KickWebServiceは、引数にHTTPメソッド（GETやPOST、PUTといった文字列）、パス（APIの種類を表すエンドポイントの一部）、クエリパラメータ、リクエストボディ（HTTPメソッドがPOSTおよびPUTの場合に送信されるデータ）を受け取って、結果としてのJSON文字列を返す関数です（リスト2）。

［リスト2］KickWebService関数

' APIを呼び出す
' 引数はHTTPメソッド、APIの種類、クエリパラメータ、リクエストボディ
' 戻り値は取得したJSON文字列
Private Function KickWebService(ByVal Method As String, _
    ByVal Path As String, ByVal Param As String, _
    ByVal Body As String) As String
    ' (1)パラメータが指定されていれば区切り文字を付加する
    If Param <> "" Then
        Param = "?" & Param
    End If

    ' (2)URLを作成する
    Dim Url As String
    Url = EndPoint & Path & Param
    ' (3)MSXML2.XMLHTTPモジュールのオブジェクトを作成
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        ' (4)リクエストを送信する
        .Open Method, Url, False
        .setRequestHeader "Authorization", "Bearer " & AccessToken
        .setRequestHeader "Content-Type", "application/json"
        ' リクエストボディが指定されていればエンコードして送信する
        If Body <> "" Then
            .send EncodeUTF8(Body)
        Else
            .send
        End If
        ' (5)リクエスト結果を取得する
        KickWebService = .responseText
    End With
End Function

（1）では、クエリパラメータが空文字列でない場合に、URLパスとの区切りとして「?」をparamに付加しています。
（2）では、変数Urlに、エンドポイント、パス（path）、クエリパラメータ（param）を結合したものをセットしています。これでURLが完成します。
（3）では、HTTPアクセスに必要なMSXML2.XMLHTTPモジュールのオブジェクトを作成しています。続けて（4）において、HTTPメソッドとURLを指定してOpenメソッドを呼び出し、必要なリクエストヘッダを設定した後、sendメソッドで実際にリクエストを送信します。リクエストヘッダは、Authentication（アクセストークンを送信）とContent-Type（リクエストボディの形式であるJSON）の2つを送信しています。Authenticationヘッダの「Bearer」は「所持者」「使者」といった意味です。アクセストークンを持った使者というわけです。
リクエストボディが空でない、すなわちPOSTおよびPUTによるリクエストでは、EncodeUTF8プロシージャによってエンコーディングされたリクエストボディを、sendメソッドの引数に指定して送信しています。なお、文字列をUTF-8エンコードするEncodeUTF8関数については、この連載では関係が薄いため掲載を割愛しますので、実際のコードについてはサンプルファイルを参照してください。
（5）では、呼び出し結果をResponseTextプロパティから取得しています。これが空なら何らかのエラーが発生していますが、これをそのまま関数の戻り値として返してエラー時の処理は呼び出し側に委ねています。

ボタンクリックでスプレッドシートへ反映するスクリプト

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

①アクセストークンとスプレッドシートIDを取得する
②リクエストボディのJSON文字列を生成する
③パス文字列とパラメータ文字列を構築する
④APIを呼び出し結果のJSONデータをディクショナリ形式に変換して結果を確認する

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

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

' データ反映ボタンをクリックしたときの処理
Private Sub PostCommandButton_Click()
    ' (1)アクセストークンとスプレッドシートIDを取得する
    Dim InfoWorksheet As Worksheet
    Set InfoWorksheet = Worksheets("Info")
    AccessToken = InfoWorksheet.Range("A3:A3")
    SheetId = InfoWorksheet.Range("A5:A5")

①に相当するスクリプトです。
（1）では、Infoワークシートからアクセストークン文字列とスプレッドシートID文字列を取得しています。セル範囲は決め打ちとしているので、Infoワークシート内の配置に合わせて必要に応じて修正してください。結果は、グローバル変数AccessTokenとSheetIdに格納されます。

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

    ' (2)リクエストボディのJSON文字列を生成する
    Dim DataWorkSheet As Worksheet
    Set DataWorkSheet = Worksheets("Data")
    Dim DataList As Variant
    DataList = DataWorkSheet.Range("A8:D17")
    Dim TargetDate As String
    TargetDate = DataWorkSheet.Range("B5")
    Dim Sales As String
    Sales = DataWorkSheet.Range("D5")
    ' (3)セルのデータを取得して各行のコレクションと値のコレクションを生成する
    Dim Requests As New Dictionary
    Dim Values As New Collection
    Dim i As Integer
    For i = 1 To MaxDataCount
        ' データ欄の品目が空になるまで繰り返す
        If DataList(i, 2) = "" Then
            Exit For
        End If
        ' 各行のコレクションを生成する
        Dim RowData As Collection
        Set RowData = New Collection
        With RowData
            .Add Item:="=ROW()-3"            ' No
            .Add Item:=Sales                 ' 営業
            .Add Item:=DataList(i, 2)        ' 取引先
            .Add Item:=DataList(i, 3)        ' 品目
            .Add Item:=DataList(i, 4)        ' 数量
        End With
        ' 値のコレクションに加える
        Values.Add Item:=RowData
        i = i + 1
    Next
    Requests.Add "majorDimension", "ROWS"
    Requests.Add "values", Values
    Dim Body As String
    Body = ConvertToJson(Requests)

②に相当するスクリプトです。リクエストボディとするJSON文字列を生成しています。
（2）では、受注データと対象日、営業名を取得しています。対象日は、スプレッドシートのシートIDの指定、営業名はスプレッドシートの「営業」欄に設定します。セル位置は決め打ちしているので、配置を変えた場合には、それぞれ変更してください。 （3）では、セルのデータを取得して各行のコレクションと値のコレクションを生成しています。データ数は最大MaxDataCountまで、途中で品目データが空になったら処理を終了しています。
（4）では、リクエストボディのディクショナリを作成してJSON文字列に変換しています。
このスクリプトの実行で、リスト5のようなJSONデータが作成されています。

［リスト5］生成されるJSONデータ

{
  "majorDimension": "ROWS",
  "values": [
    ["=ROW()-3", "谷川岳史", "梅田書店", "Rails辞典", 3],
    ["=ROW()-3", "谷川岳史", "梅田書店", "Android入門", 5],
    ["=ROW()-3", "谷川岳史", "梅田書店", "応用JavaScript", 15],
    …略…
    ["=ROW()-3", "谷川岳史", "ミナミBC", "Rails辞典", 3],
  ],
}

JSONデータのフォーマットは、第16回でテストに使用したものとほぼ同様です。rangeキーは省略されており、この場合はURLパスにおいて指定されているものが採用されます。

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

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

ログイン／無料会員登録

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