連載第16回の目的

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

  • 完成サンプルイメージ

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

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

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

Google WorkspaceとGoogle Sheets API

Google Sheets API（以降Sheets API）と、それを含むGoogle Workspaceについては第14回で紹介しました。もし、概要の説明が必要な場合には第14回を参照してください。Google Workspaceでは、それぞれのAPIごとに各種のプログラミング言語から利用可能なライブラリが整備されていますが、残念ながらVBAのサポートはないため、リクエストURLやリクエストボディをスクリプトで作成し、HTTP(S)で直接送信して利用することになります。本記事のサンプルもそれに倣っています。

APIの使用を準備する

Sheets APIの利用には、第14回で紹介したように、Googleアカウントの取得やいくつかの登録手続きが必要です。Googleアカウントの取得と支払い情報の登録については第8回を、Sheets APIの利用開始については第14回を、それぞれ参照してください。ここでは、今回必要になるサービスアカウントの作成やアクセストークンの生成について取り上げます。

サービスアカウントの作成

第14回で紹介したようにSheets APIはAPIキーで利用できますが、これは参照系のAPIのみに限定されます。更新系のAPIでは、OAuthかサービスアカウントによる認証が必要になります。今回は、このうち比較的容易に利用できるサービスアカウントによる認証を利用します。 サービスアカウントは、Cloud Consoleのページから以下の手順で作成してください。

▼APIとサービス
https://code.google.com/apis/console?hl=ja
  • APIとサービス

    図2：APIとサービス

・Cloud Consoleの「APIとサービス」ページで左ペインにある［認証情報］をクリックします。
・ページ上部にある［＋認証情報を作成］をクリックします。
・表示されるドロップダウンで「サービスアカウント」をクリックします。
・「①サービス アカウントの詳細」の「サービスアカウント名」「サービスアカウントID」「サービスアカウントの説明」をそれぞれ入力します（IDは6文字以上になるようにする）。ここでは、「test-sheets」「test-sheets」「Sheets API検証用」としました。
・②と③は省略して［完了］をクリックします。

これで、サービスアカウントが作成されます（図3）。メールアドレスは、後ほど複製したGoogleスプレッドシートの共有設定に必要となるので、控えておいてください。

  • 作成されたサービスアカウント

    図3：作成されたサービスアカウント

図3の「認証情報」ページの「サービスアカウント」欄に、作成したサービスアカウントが作成されていることを確認して、続けてキーファイルの作成を行います。

・メールアドレスのリンクか右端の鉛筆型の編集アイコンをクリックします。
・「サービスアカウントの詳細」に切り替わるので、上部のタブから［キー］をクリックします。
・［鍵を追加］をクリックして表示されるドロップダウンリストから［新しい鍵の作成］をクリックします。
・「「test-sheets」の秘密鍵の作成」画面がポップアップするので（「test-sheets」の部分は実際に作成したサービスアカウント名に準ずる）、「キーのタイプ」が「JSON」になっているのを確認して［作成］をクリックします。
・「秘密鍵がパソコンに保存されました」と表示されれば、キーファイルがダウンロードフォルダに保存されているはずです（ファイル名は、プロジェクトIDとランダムな文字列から構成される）。

このキーファイルから生成されるアクセストークンは、Googleスプレッドシートの更新に使うものなので、ファイルの漏えいには気を付けましょう。

gcloud CLIのインストール

サービスアカウントから作成したキーファイルで認証するには、キーファイルとスコープ（アクセストークンが通用するAPIの種類）からアクセストークンを生成する必要があります。このアクセストークンをAPI呼び出し時のリクエストヘッダに含めることで、認証を行うことができます。
生成には、Google Cloud SDKのCLIツールgcloud CLIが必要なので、ここでインストールしておきます。以下にアクセスして、「インストール手順」の［Windows］タブの手順でダウンロード、インストールを行ってください。このページではPowerShellでの手順となっていますが、表示されているリンクからインストーラをダウンロードし、それを実行しても同様です。

▼gcloud CLI をインストールする  |  Google Cloud
https://cloud.google.com/sdk/docs/install?hl=ja

インストール中のオプションは、全て既定で問題ありません。なお、既定ですとバンドル版のPythonインタプリタもインストールされます。これはCloud SDKがPython 3.5～3.9を必要とするためです。すでにシステムでPythonの当該バージョンが利用可能である場合には、インストール時のオプションを外してもらっても構いません。
インストール後、以下のようにgcloudコマンド実行して、それぞれにコンポーネントのバージョンが出力されれば成功です。

> gcloud --version
Google Cloud SDK 439.0.0
bq 2.0.94
core 2023.07.14
gcloud-crc32c 1.0.0
gsutil 5.25

APIの呼び出しをテストする

サービスアカウントとアクセストークンを作成できたので、APIの呼び出しを始めることができます。スクリプトを書く前に、APIが正しく呼び出せてGoogleスプレッドシートが更新できるかどうかテストしておきましょう。

Googleスプレッドシートの準備

今回のサンプルで書き換えるGoogleスプレッドシート（以降、単にスプレッドシートとします）は、あらかじめ著者によって用意されていますので、それを複製してAPIをテストできます。著者が用意したスプレッドシートは、図4のようなものです。

  • 著者が用意したスプレッドシート

    図4：著者が用意したスプレッドシート

このスプレッドシートは、営業受注の集計表を想定しています。受注リストを収納する表（No、営業、取引先、品目、数量の5項目からなる）から構成されます。複数のシートがあり、「20130718」シートは2023年7月18日の受注リストを想定したシート（サンプルの実行で書き込まれる対象のシート）、「テンプレート」シートは新たな日付のシートを作成するためのひな型です。スプレッドシートのURLは以下の通りです。

▼著者が用意したスプレッドシートのURL
https://docs.google.com/spreadsheets/d/1GMc-NRWZS1ksoBQhzcOibltoa4x4YGNTU1USeMkXtk0/edit?usp=drive_link

このURLをWebブラウザで開いて、スプレッドシートが表示されるか確認してください。そして、Googleアカウントでログインした状態で、［ファイル］メニューから［コピーを作成］をクリックして、各自のGoogle Driveにスプレッドシートのコピーを保存してください。なお、著者提供のスプレッドシートは閲覧専用ですので、これをそのまま使っての作業はできません。
保存したスプレッドシートには、以下の作業を行ってください。

・スプレッドシートIDをURLから抜き出して控えておく
・スプレッドシートの共有設定で、サービスアカウントのメールアドレスを編集者権限で追加する

スプレッドシートIDは、たとえば著者のスプレッドシートだと以下の太字の部分になります。

https://docs.google.com/spreadsheets/d/1GMc-NRWZS1ksoBQhzcOibltoa4x4YGNTU1USeMkXtk0/edit#gid=0

Sheets APIの利用方法

Sheets APIについては、下記にリファレンスがありますので、全容を知りたい場合には参照してください（第14回）。

▼Google Sheets API  |  Google Developers
https://developers.google.com/sheets/api/reference/rest

エンドポイントは以下のようになります。今回はAPIキーを使用しないので、クエリパラメータにkeyは指定しません。

https://sheets.googleapis.com/v4/spreadsheets/…

「…」の部分は、APIによって異なっています。概略は第14回に示しましたので、必要に応じて参照してください。今回も第14回、第15回と同様に値操作系のAPI（続くパスが…/values/となる）を使用していきます。

アクセストークンの生成

ここで、API利用のためのアクセストークンをキーファイルから生成する手順を紹介します。PowerShellで作業します。

［NOTE］PowerShell 7.xが必要

ここからの操作は、PowerShell 7以上が必要です。Windows 10/11に標準でインストールされているのはPowerShell 5.1ですので、下記からPowerShell 7.2（本記事執筆時点の最新バージョン）を入手してインストールしてください。PowerShell 7.2はPowerShell 5.1とは別のアプリケーションとしてインストールされますので、両方を使い分けることが可能です。

▼Windows への PowerShell のインストール
https://docs.microsoft.com/ja-jp/powershell/scripting/install/installing-powershell-on-windows?view=powershell-7.2

PowerShell 7.2は、［スタート］－［PowerShell］－［PowerShell 7(x64)］を選択して実行できます。

まず、環境変数GOOGLE_APPLICATION_CREDENTIALSにキーファイルのパスを設定します。パスには、各自がダウンロードしたものを設定してください。著者の場合はファイルをC:\User\usersフォルダに配置して、そこをカレントフォルダにしたとして説明しています。

> $env:GOOGLE_APPLICATION_CREDENTIALS = ".\vaulted-valor-xxxxx-yyyyyyyyyyy.json"

そして、以下のコマンドを実行します。これは、環境変数GOOGLE_APPLICATION_CREDENTIALSがあれば、その内容でアクセストークンを生成するコマンドです。--scopesオプションはスコープの指定で、この場合はhttps://www.googleapis.com/auth/spreadsheets内で有効なアクセストークンを生成することになります。スコープの指定がないとエラーになるので注意してください。

> gcloud auth application-default print-access-token --scopes "https://www.googleapis.com/auth/spreadsheets"
ya29.c.b0Aaekm1Jm_aqgnqmbLdQhrv3JPx_Jrt5qfqSO76U2vhZS46S_wRjgcLdQroFz05FVSZaxX6mXiBJc3_MNrtcbMVIHdPIEkgLoYyaug-yentbrwOt0skiAy6ocDSbsT-dE-oILn-cBfyTP06WnP7qdgg_VctwdL-GPnW7oo-WJ0S60eCtizk51HNdxJo5gP6xb4j4NxOR1tySjY4AbZAA0…

コマンドの実行で、ずらっと長い文字列が出力されるので、コピーしてファイルなどに控えておいてください。後ほどのテストやスクリプトの実行時に必要になります。

［NOTE］アクセストークンの有効期限

この手順で生成されるアクセストークンの有効期限は非常に短く、ドキュメントに明記されてはいませんが約1時間ほどです。gcloudコマンドでは有効期限の指定はできません。有効期限を超過したアクセストークンの利用は認証エラーとなるので、テストやExcelワークシートからのスクリプト実行の直前にアクセストークンを生成し、利用する必要があります。

テストを実行する

今回使用するSheets APIは更新系がメインとなるため、POSTメソッドとPUTメソッドでアクセスします。また、リクエストヘッダに認証情報（アクセストークン）を埋め込みます。よってWebブラウザではテストできないので、同じくPowerShellを用いてAPI呼び出しをテストします。
上記で保存したしたスプレッドシートに、ダミーの値を書き込んでみます。まずは、書き込むデータをリスト1のJSONファイルで用意します。エンコーディングはUTF-8としておいてください。

［リスト1］テスト用JSONデータupdaqte.json

{
  "range": "20230718!A4:E4",
  "majorDimension": "ROWS",
  "values": [
    ["=ROW()-3", "谷川岳史", "ミナミBC", "入門Rails", 10]
  ],
}

ファイルが用意できたら、第6回で紹介したようにPowerShellのInvoke-WebRequestコマンドを使ってリクエストを発行していきます。以下、詳細は省くので必要に応じて第6回の説明を参照してください。なお、Invoke-WebRequestコマンドに与えるURLのスプレッドシートID部分は、各自が複製したスプレッドシートのものを指定してください。また、$headersに設定するAuthorizationキーの値に「Bearer」を含めるのを漏らさないようにしてください。

> $headers = @{
  "Authorization" = "Bearer …各自生成したアクセストークンを入れる…"
  "Content-Type" = "application/json"
}
> $param = ConvertFrom-Json -InputObject (Get-Content update.json -Raw)
> $poststr = ConvertTo-Json $param -Depth 10
> $postbytes = [System.Text.Encoding]::UTF8.GetBytes($poststr)
> $result = Invoke-WebRequest https://sheets.googleapis.com/v4/spreadsheets/1GMc-NRWZS1ksoBQhzcOibltoa4x4YGNTU1USeMkXtk0/values/20230718!A4:E4?valueInputOption=USER_ENTERED -Headers $headers -Method PUT -Body $postbytes
> $result
StatusCode        : 200
StatusDescription : OK
Content           : {
                      "spreadsheetId": "1Xrff…",
                      "updatedRange": "'20230718'!A4:E4",
                      "updatedRows": 1,
                      "updatedColumns": 5,
                      "updatedCells": 5
                    }
…略…

上記のように、Invoke-WebRequestコマンドの実行でエラーメッセージなどが何も表示されず、$statusの表示で「StatusCode : 200」となっていれば成功です。このあと、スプレッドシートを見ると、update.jsonに指定したデータが書き込まれていることが確認できるはずです。
ここで、送信したJSONデータの構造を見てみましょう。改めて内容をリスト2に示します。

［リスト2］テスト用JSONデータの構造

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

ログイン／無料会員登録

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