連載第14回の目的

連載第14回と第15回では、Excel VBAにおけるGoogle Sheets APIの活用について紹介します。Googleが提供するこのAPIは、Googleスプレッドシートの操作のための機能を無償で提供します。今回は、このGoogle Sheets APIを使用して、Google Drive上にある複数のスプレッドシートからデータを読み取り、手元のExcelワークシートにまとめて反映するサンプルを作成します（図1）。
今回は、Google Sheets APIの概要を紹介し、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 Workspace（以降、Workspace）は、Googleが提供するサービスの一つです。Workspaceは、Gmail（メール）、Calendar（スケジュール）、Drive（ストレージ）、Meets（オンライン会議）、Form（応答フォーム）といった、ビジネスとパーソナルを問わず利用できるアプリケーションを、多岐にわたって提供しています。非常になじみのあるサービスと言えます。 Workspaceにおいては、これらのアプリケーションに対応したAPIが提供されています。本稿作成時点で、主に以下のAPIが利用可能です。

  • Cloud Search API（サードパーティのリポジトリの検索）
  • Gmail API、Postmaster Tools API（Gmailの利用）
  • Calendar API、CalDAV API（Googleカレンダーの利用）
  • Drive API、Drive Activity API、Drive Labels API（Googleドライブの利用）
  • Forms API（Googleフォームの利用）
  • Tasks API（Googleタスクの利用）
  • Docs API（Googleドキュメントの利用）
  • Sheets API（Googleスプレッドシートの利用）
  • Slides API（Googleスライドの利用）

このうち、Docs API、Sheets API、Slides APIは、それぞれドキュメント、スプレッドシート、プレゼンテーションのアプリケーションに対応しています。本記事では、このうちSheets APIについて紹介していきます。なお、本稿作成時点のSheets APIの最新バージョンは4です。バージョン3は2021年6月を以て利用できなくなっており、バージョン4のみが唯一の選択肢となります。

Sheets APIについて

Sheets APIはRESTfulなAPIです。作成、参照、更新、削除などの基本的な機能を備えており、リクエストはURLパスとクエリパラメータ、それに必要に応じてリクエストボディによって行われます。リクエストボディ、レスポンスボディともにJSON形式となっており、データの取り扱いが容易になっています。
主要なプログラミング言語や環境（Google Apps Script、Node.js、JavaScript、Python、PHP、Ruby、Java）では、ライブラリやモジュールが整備されており、それらを利用することでシンプルにSheets APIの機能を利用できます。
ただし、VBAのサポートはないため、リクエストURLやリクエストボディをスクリプトで作成し、HTTP(S)で直接送信する必要があります。

利用制限（クォータ）

Sheets APIの機能は無償で利用できますが、クォータ（割り当て）と呼ばれる利用制限があります。クォータは、大まかに表1のようになっています。

表1：Sheets APIのクォータ

読み書きの別 種類 制限
読み込みリクエスト 日/プロジェクト なし（無制限）
分/プロジェクト 300リクエスト
分/ユーザ/プロジェクト 60リクエスト
書き込みリクエスト 日/プロジェクト なし（無制限）
分/プロジェクト 300リクエスト
分/ユーザ/プロジェクト 60リクエスト

基本的に、クォータの範囲内での利用になります。クォータを越えた分は有償になるということはなく、あくまでも利用の制限となります。クォータの上限アップを申請することができますが、これには審査が必要となっています。
また、Googleでは制限に引っかかった場合の対策として、指数バックオフと呼ばれるアルゴリズムによるリトライを推奨しています。このアルゴリズムは、エラーとなるレスポンスから最適なリクエストの頻度を見つけていくというものです。実用的なアプリケーションでは必要になってくると思われますが、本記事のサンプルのような利用であればほとんど制限に引っかからずに済むと思われるので、特に対策はしていません。

APIの使用を準備する

Sheets APIの利用には、Googleアカウントの取得やいくつかの登録手続きが必要です。順を追って取得、登録していきます。

  • Googleアカウントの取得
  • 支払い情報の登録
  • Sheets APIの利用開始

このうち、Googleアカウントの取得と支払い情報の登録については、第8回を参照してください。

Sheets APIの利用開始

Sheets APIは、以下のURLから手続きして利用できるようになります（図2）。

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

  • 図2：APIとサービス

  • 図2の画面で左ペインにある［ライブラリ］か、画面上部にある［＋APIとサービスの有効化］のいずれかをクリックします。
  • 検索窓に「google sheets」と入力します。候補が表示されますので、「google sheets api」をクリックします。
  • 「Google Sheets API」が一覧に表示されますのでクリックします。
  • 有効にするか尋ねられますので、［有効にする］をクリックします。

これで、Sheets APIが有効になります（図3）。

  • 図3：有効になったData API

APIキーの取得

Sheets APIの利用にはAPIキーが必要になります。APIキーを取得していない場合は、Cloud Consoleのページから取得してください。なお、第8回などでAPIキーを取得している場合には、それを使用することができます。 APIキーは、以下の手順で取得できます。

  • 上記のCloud Consoleのページで左ペインにある［認証情報］をクリックします。
  • ページ上部にある［＋認証情報を作成］をクリックします。
  • 表示されるドロップダウンで「APIキー」をクリックすると、作成中のメッセージのあと作成したキーの情報が表示されます。

ここでAPIキーをコピーしてテキストファイル等に保存しておき、スクリプトを書くときに参照できるようにしましょう。なお、作成したキーはいつでもこの画面から確認できます（図4）。

  • 図4：取得したAPIキー

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

APIの有効化とAPIキーの取得ができたので、APIの呼び出しを始めることができます。スクリプトを書く前に、APIが正しく呼び出せるかテストしておきましょう。返されるJSONデータの理解はスクリプトの読みこなしに必要なので、基本的な構成を理解しておいてください。

用意されたGoogleスプレッドシートについて

今回のサンプルは、複数のGoogleスプレッドシートからデータを取り出し、手元のExcelワークシートに反映するというものです。そのために必要なスプレッドシートは、あらかじめ著者が用意していますので、それを使用してAPIをテストできます（完成サンプルの実行もできます）。著者が用意したスプレッドシートは、図5のようなものです。

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

営業の受注リストを想定しています。独立の項目として日付と営業の氏名があります。また、最大10項目の受注リスト（No、品目、取引先、数量の4項目からなる）があります。
用意したスプレッドシートは3つありますが、とりあえずテストに使用するスプレッドシートのURLは以下の通りです。このURLをWebブラウザで開いて、このスプレッドシートが表示されるか確認しておいてください。

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

なお、スプレッドシートは閲覧のみ可能となっています。スプレッドシートに手を入れたい場合には、独自にスプレッドシートを作成するか、複製してください。

Sheets APIの利用方法について

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

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

エンドポイントは以下のようになります。

https://sheets.googleapis.com/v4/spreadsheets/…?key=＜APIキー＞

「…」の部分は、APIによって異なってきます。また、key以外のクエリパラメータを指定することもあります。以下の表に、APIの4つの分類ごとに、メソッドとGET/POSTの別、「…」のフォーマット、そして概要をまとめます。メソッドに「batch」が付いているものは複数範囲が対象となることを意味し、「ByDataFilter」が付いているものはデータフィルタ（Googleスプレッドシートの備えるフィルタ機能のこと）によって指定される範囲が対象となります。
なお、フォーマット中の{spreadsheetId}はスプレッドシートのIDを、{range}はワークシート上の範囲を表します。

表2：スプレッドシートについてのAPI

メソッド GET/POST フォーマット/概要
create POST ―
スプレッドシートを作成して返す
get GET {spreadsheetId}
指定されるスプレッドシートを返す
getByDataFilter POST {spreadsheetId}:getByDataFilter
フィルタで指定されるスプレッドシートを返す
batchUpdate POST {spreadsheetId}:batchUpdate
1個以上の変更を加える

表3：メタデータについてのAPI

メソッド GET/POST フォーマット/概要
get GET {spreadsheetId}/developerMetadata/{metadataId}
IDで指定されるメタデータを返す
search POST {spreadsheetId}/developerMetadata:search
指定されるメタデータを検索する

表4：シートについてのAPI

メソッド GET/POST フォーマット/概要
copyTo POST {spreadsheetId}/sheets/{sheetId}:copyTo
単一のワークシートを別のスプレッドシートにコピーする

表5：データについてのAPI

メソッド GET/POST フォーマット/概要
append POST {spreadsheetId}/values/{range}:append
データを追加する
clear POST {spreadsheetId}/values/{range}:clear
範囲を消去する
batchClear POST {spreadsheetId}/values:batchClear
複数の範囲を消去する
batchClearByDataFilter POST {spreadsheetId}/values:batchClearByDataFilter
フィルタで指定する複数の範囲を消去する
get GET {spreadsheetId}/values/{range}
範囲の値を取得する
batchGet GET {spreadsheetId}/values:batchGet
複数の範囲の値を取得する
batchGetByDataFilter POST {spreadsheetId}/values:batchGetByDataFilter
フィルタで指定する複数の範囲の値を取得する
update PUT {spreadsheetId}/values/{range}
範囲を更新する
batchUpdate POST {spreadsheetId}/values:batchUpdate
複数の範囲を更新する
batchUpdateByDataFilter POST {spreadsheetId}/values:batchUpdateByDataFilter
フィルタで指定する複数の範囲を更新する

{spreadsheetId}は、例えば上記の著者提供のスプレッドシートの場合は、以下URLの太字部分となります。

https://docs.google.com/spreadsheets/d/1JDu7nAGULWR3lPpk0D-wz0L7qAmhb-_KSCSZTzLNEdM/edit?usp=sharing

{range}は、さまざまな指定方法が可能です。表6に、代表的なものをまとめます。

表6：範囲の指定方法

表記法 例 概要
A1表記 Sheet1 シート全体を指定
Sheet1!A1:C3 シート名と範囲を指定
A:B 列全体を指定
1:3 行全体を指定
R1C1表記 Sheet1!R1C1:R3:C3 シート名と範囲を指定
R[2]C[3] カレントセルからの相対範囲を指定

テストを実行する

今回使用するSheets APIは、GETメソッドでアクセスするものだけなので、API呼び出しのテストはWebブラウザで実行できます。なお、WebブラウザがJSONデータを見やすく表示してくれるようになっていると便利です。Google Chromeに、第3回で紹介した拡張機能「JSON Viewer」を入れておいてください。
まずは、getメソッドにスプレッドシートIDのみを指定してAPIを呼び出してみます。この場合、スプレッドシートの情報を取得できます。各自で取得したAPIキーの指定を忘れないでください（以下では途中から省略しています）。

https://sheets.googleapis.com/v4/spreadsheets/1JDu7nAGULWR3lPpk0D-wz0L7qAmhb-_KSCSZTzLNEdM?key=AIza…

このURLをWebブラウザのアドレス欄に入力して呼び出すと、問題なければ以下のようなレスポンスが返ってきます。レスポンスで返ってきたJSONデータの構造を見てみましょう。その概略をリスト1に示します。

［リスト1］スプレッドシート情報取得結果のJSONデータの構造

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

ログイン／無料会員登録

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