More than 5 years have passed since last update.

サービスアカウント JWT を使って Google Spreadsheets APIにアクセスするための手順（CData ODBC Driver for Google Spreadsheets）

Last updated at 2018-08-03Posted at 2018-08-03

Google の API では主な認証方法として通常のOAuthを利用したフローとサービスアカウントを利用した JWT（Json Web Token）を方式の2種類が提供されています。（GmailですとBasicもありますが）

ベーシックな方法としては前者のOAuthかと思いますが、シチュエーションによっては後者のほうが望ましい場合も多々あります。ですが、後者を解説している記事があまり見当たらないので、まとめてみました。

JWT は Google の APIであれば、どれでもアクセスできるように構成できますが、今回はよく質問されることが多い Google Spreadsheets API へアクセスするという想定で書いています。

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

JWTにはサービスアカウントと呼ばれる、独自のアカウント情報が必要になるので、まずはこのサービスアカウントを準備します。

あらかじめGCPのアカウントが必要になるので、注意しましょう。

GCP コンソールに移動し対象とするプロジェクトを開きます。

対象のプロジェクトを構成していない場合は、「新しいプロジェクト」をクリックし

任意の内容でプロジェクトを作成してください。

対象のプロジェクトを作成後「APIとサービス」から「認証情報」を選択します。

「認証情報を作成」→「サービスアカウントキー」をクリック

新しいサービスアカウントで任意の「サービスアカウント名」と「サービスアカウントID」を入力し、キーのタイプから「P12」を選択して、作成をクリックします。

今回はGoogle Spreadsheets APIのため、役割は指定しませんが、BigQuery APIなどを利用したい場合は必要に応じて役割を設定します。

設定後、P12の証明書がダウンロードが開始されるので、任意のフォルダに保存してください。また、P12証明書をインストールする際のパスワードも表示されるので控えておきましょう。

P12証明書のインポート

次にダウンロードした証明書をインポートします。

ウィザードに従って設定するだけなので、特に難しいことはありません。会社マシンの場合推奨事項があるかもしれないので、そこは情シスなどへの確認が必要かと思います。

保存場所は任意の場所を選択してください。今回は「現在のユーザー」を選択しました。

パスワードは先程サービスアカウントを作成した際に表示されたパスワードになります。

証明書ストアはデフォルトで進めて問題ありません。

以上で、証明書のインポートが完了です。

API アクセス権限の付与

次に一度GCPの画面に戻り、作成したプロジェクトへAPI アクセス権を付与します。

「APIとサービス」のメニューから「ライブラリ」を選択し

一覧の中から必要なAPIのアクセス権を設定します。Google Spreadsheetで一つ注意したいのが、「SpreadsheetはGoogle Driveに保存されている」ということです。

そのため、Google Spreadsheet APIを利用する場合は「Google Drive API」と「Google Sheets API」の2つのAPI を有効化する必要があるので注意しましょう。

以下のようにそれぞれ、「有効にする」をクリックすればOKです。

Google Sheet をJWTサービスアカウントでアクセスさせる場合

それでは実際にAPIを試したいと思いますが、事前にアクセスしたいGoogle Sheetを作成しておきましょう。

今回は以下のようなGoogle Sheetを個人アカウント（サービスアカウントではない）で作成しました。

さて、一つ注意しなければいけないのは、「APIアクセス可能なGoogle SheetはそのアカウントのGoogle Driveに保存された、もしくは共有されたGoogle Sheetが対象になる」ということです。

つまりサービスアカウントがアクセスできるGoogle Sheetでなければいけません。

今回は私の個人アカウントで作成したので、このGoogle Sheetに先程作成したサービスアカウントに対して共有設定を行うことで、APIアクセスを可能にします。

共有の画面を開き、作成したサービスアカウントのアドレスを入力することで、設定は完了できます。

CData ODBC Driverでアクセス

それでは、Google Sheet APIを実際に利用してみましょう。

今回はGoogle Spreadsheetを手軽に扱うことができる「CData ODBC Driver for Google Spreadsheets」を使って、アクセスしてみたいと思います。

以下のサイトから評価版ビルドをダウンロードします。

CData ODBC Driver for Google Spreadsheets

ダウンロードされたインストーラ形式(.exe)のファイルを起動します。
(注)ODBCデータアドミニストレーターを閉じてから実行してください。
(注)途中オンラインでのアクティベーションが実行されるためインターネットアクセス出来る環境で行ってください。

下記のようなインストーラが起動するので、ライセンス契約書を確認した上で、デフォルト設定のままインストールを進めて完了させます。

インストールが完了するとデフォルトブラウザが起動して製品ヘルプファイルが起動します。

また、ODBCのDSN構成ダイアログが表示されるので、以下のように設定を行います。

（注）ODBCデータソースアドミニストレーターはマシンのビット数（32bit, 64bit）にあった方をご利用ください。

接続情報	設定項目	備考
OAuthJWTCertType	PFXFILE	デフォルト
OAuthJWTCertPassword	Password	.p12を登録する時に利用したパスワード
OAuthJWTCertSubject	*	デフォルト
OAuthJWTIssuer	ServiceAccountName@ProjectName.iam.gserviceaccount.com	作成したサービスアカウントアドレス
OAuthJWTCert	C:\Work\JWT\P12FileName.p12	P12証明書の保存場所

「接続テスト」ボタンをクリックします。下記のようなダイアログが表示されれば成功です。「ODBCのDSN構成」の「OK」ボタンをクリックして保存します。

それでは Power Shellを使って、アクセスしてみたいと思います。以下のようなプログラムでレコードを取得してみました。


Set-StrictMode -Version Latest

[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data")

# 作成したDSNを指定
$connectionsString = "DSN=Google Sheet JWT" 
$odbcCon = New-Object System.Data.Odbc.OdbcConnection($connectionsString)
$odbcCon.Open();

$odbcCmd = New-Object System.Data.Odbc.OdbcCommand
$odbcCmd.Connection = $odbcCon

# コマンド実行（SELECT）
$odbcCmd.CommandText = "SELECT * FROM Sample_Sheet1"
$odbcReader = $odbcCmd.ExecuteReader() 

while ($odbcReader.Read()) {
    $odbcReader["Id"].ToString() + " " + $odbcReader["Name"].ToString()
}

$odbcCmd.Dispose()

$odbcCon.Close()
$odbcCon.Dispose()