Google Spreadsheet をサービスアカウントに共有せずにGoogle Spreadsheet APIから読みだす方法

はじめに

Google Workspace で原則として社外からのアクセスを不可としていると、時折こんな質問が飛んできます。

Google Spreadsheet API でスプレッドシートを読み出したいけどサービスアカウントを共有に追加できません。
共有を許可してくれませんか？

いやそれ、 サービスアカウントに共有しなくても出来ます から…
というわけで本記事を書いてみました。尚、以下の条件で書いています。

• 言語に依らず cURL のみでのプロセスを記載
• 個人のアカウントのGoogleアカウント(Google Workspaceではないアカウント)でも使える
• 読み出したいスプレッドシートには既に必要な権限が用意されている
  • サービスアカウントではなく読むべき人のアカウントが正しく設定されている前提

概要

ざっくり以下の事を行います。

1. 事前準備
   1. GCP でプロジェクトを作る
   2. プロジェクトでGoogle Spreadsheet APIを利用できるようにする
   3. OAuth2.0クライアントを作る
2. Refresh TokenからAccess Tokenを得てGoogle Spreadsheet APIへアクセスできるようにする
   1. OAuth2.0でGoogle Spreadsheetへのアクセス権限を承認(委譲)してもらう
   2. Authorizationコードを取得する
   3. AuthorizationコードからAccess TokenとRefresh Tokenを得る
   4. Refresh TokenからAccess Tokenを得る

ここまで出来れば、以降は Refresh Token から毎回 Access Token を得て、Google Spreadsheet API でスプレッドシートに対して読み書きができます。

事前準備

Google Spreadsheet API を利用可能にする

まずは Google Cloud Platform (以下 GCP) を利用可能な状態にしましょう。
Googleアカウントを持っていれば、まずは https://console.cloud.google.com へアクセスすればOKです。
そして、Google Spreadsheet API を利用できるようにしましょう。具体的には、以下の順番です。

1. GCP でプロジェクトを作る
2. プロジェクトでGoogle Spreadsheet APIを利用できるようにする
3. OAuth2.0クライアントを作る
   1. OAuth同意画面を登録する
   2. OAuth2.0クライアントを

GCPでプロジェクトを作る

GCPダッシュボード でプロジェクトを選択します。

プロジェクトをまだ1つも作っていない場合、「プロジェクトの選択」をクリックするとプロジェクト作成画面になります。

Google Spreadsheet API を有効にする

左側のメニューより APIとサービス → 有効なAPIとサービス を選んでAPI一覧を表示します。

APIとサービスの有効化 をクリックします。

Google Sheets API を検索して有効にします。

OAuth2.0クライアントを作る

OAuth 同意画面を登録する

左側のメニューより APIとサービス → OAuth同意画面 を選びます。
この際、

• Google Workspace の組織内のみで利用する場合は 内部
• Google Workspace の組織外のユーザーも一時的に利用できるようにするには 外部
  を選択してください。
  外部 の場合はテストユーザーを後で追加する必要があります。
  このサンプルでは 外部 で進めます。
  

OAuth同意画面

以下の必須項目に値を入力、または選択肢から値を選択します。

• アプリ名（OAuth2.0で権限確認時に表示されるアプリ名となります)
• ユーザーサポートメール (選択肢から選んでください。殆どの場合、現在そのページを開いているアカウントのメールアドレスのみが選べる状態となっています)
• デベロッパーの連絡先情報 (Google から連絡があった場合の連絡先となります。貴方の本来の連絡先メールアドレスを記載ください。前述のユーザーサポートメールと違っていても大丈夫です。
  他は空白でOKです。
  

スコープの設定

スコープの追加または削除 をクリックして、 Google Sheets API を検索し、チェックを入れます。
スプレッドシートの読み込みだけであれば一番下の Google スプレッドシートのすべてのスプレッドシートの参照 だけで大丈夫です。

テストユーザーの追加

テストユーザーの画面で + ADD USERS をクリックしてテストユーザーを追加します。
シートへアクセスを許可するアカウントのみ追加しましょう。また、アクセスさせるシートには、事前に権限を付与しておきましょう。

認証情報を作成する

左側のメニューより APIとサービス → 認証情報 を選んで + 認証情報を作成 をクリックします。
認証情報は OAuth クライアントID を選択してください。

OAuth クライアント ID の作成画面で以下を設定します

• アプリケーションの種類 ウェブアプリケーション
• 名前 適当に付けてください
• 承認済のリダイレクトURI http://localhost:8080 を登録しておいてください
  • ご自身でウェブサイトを用意してそちらを使うこともできますが、まずは今回は使いませんので暫定のホストで問題ありません
  • 今回は ウェブサイトを一切使わない サンプルなので、実際には上記で設定したURIへのアクセスは一切ありません
  • node.JSなどでローカルホストを用意する必要も特にありません
    作成をクリックするとOAuth2.0クライアントが作成され、OAuth2.0ではお馴染みの
• クライアントID
• クライアントシークレット
  が作成されます。
  

以上で事前準備は終了です。おつかれさまでした。

実はここからが本番です

OAuth2.0クライアントが作成できましたので、いよいよ Google Sheets API にてスプレッドシートへアクセスする 方法に入ります。
以下の流れを改めておさらいがてら記載しておきます。

1. OAuth2.0でGoogle Spreadsheetへのアクセスを承認(委譲)してもらう
2. Authorizationコードを取得する
3. AuthorizationコードからAccess TokenとRefresh Tokenを得る
4. Refresh TokenからAccess Tokenを得る ← 今回のキモはココです

OAuth2.0で Google Spreadsheetへのアクセスを承認(委譲)してもらう

以下のURLを シートへアクセスできるアカウントで ブラウザにて 開きます

https://accounts.google.com/o/oauth2/v2/auth
?scope=https://www.googleapis.com/auth/spreadsheets.readonly
&access_type=offline
&redirect_uri=http://localhost:8080
&client_id=[クライアントID]
&response_type=code

[クライアントID]には 先ほど設定したOAuthクライアント の クライアントID を指定します。
ブラウザで上記URIを開くと、Googleアカウントへのサインイン、または既にサインインしている場合はアカウントの選択が促されます。

テストアプリであることの確認

OAuthクライアントが「外部」であれば、アプリがまだテスト状態であることを確認する画面が出ます。
テストアカウントとして登録しているアカウントでサインインしていればそのままテストとして使用できますので、 続行 をクリックして続けます。

OAuth2.0による権限委譲の承認

OAuthによるアクセスを許可する画面が出ますので 続行 をクリックして承認します。

Authorization Code の取得

リダイレクトURIを http://localhost:8080 と設定しているので、どこへも飛ばずエラーとなりますが、意図した動作です。

この状態でブラウザのアドレスバーに入力されている以下のような形式のURIをメモ帳などへコピーしておきましょう

http://localhost:8080/
?code=[Authorization Code]
&scope=https://www.googleapis.com/auth/spreadsheets.readonly

ここで取得できた Authorization Code が大変重要です。次にそのまま使います。

Authorization Code を使用して Access Token と Refresh Token を取得する

以下の形式で https://www.googleapis.com/oauth2/v4/token を POST で呼び出します。
オプション

キー	値
code	上記で取得した authorization code
client_id	OAuthクライアントのクライアントID
client_secret	OAuthクライアントのクライアントシークレット
grant_type	authorization_code
redirect_uri	http://localhost:8080

cURLで呼び出す例

curl -X POST -d 'code=[Authorization Code]' -d 'client_id=[Client ID]' -d 'client_secret=[Client Secret]' -d 'grant_type=authorization_code' -d 'redirect_uri=http://localhost:8080' https://www.googleapis.com/oauth2/v4/token

上記を呼び出すと、以下のようなJSONが戻ってきます。

{
    "access_token": "～～～～～", // ←これが Access Token
    "expires_in": 3599,
    "refresh_token": "～～～～～", // ←これが RefreshToken
    "scope": "https://www.googleapis.com/auth/spreadsheets.readonly",
    "token_type": "Bearer"
}

ここまでくれば終わりが見えてきました。

Refresh Token を控えておきましょう。これを永続的に使います。

Access Token で Google Sheets API へアクセスする

上記で取得した Access Token を使用すれば Google Sheets API 経由でスプレッドシートへアクセスできます。
expires_in は Access Token が有効な時間(秒)、token_type は HTTP の Authorization ヘッダで指定する方法を意味します。
以下の方法でスプレッドシートへアクセスできます。

curl -X GET -H 'Authorization:Bearer [Access Token]' https://sheets.googleapis.com/v4/spreadsheets/[スプレッドシートのドキュメントID]

但しこのままでは Access Token が1時間(3600秒)で期限切れとなってしまいますので、定期的に Access Token を取得する必要があります。
そこで Refresh Token の出番です。

Refresh Token で Access Token を取得する

以下の形式で https://www.googleapis.com/oauth2/v4/token を POST で呼び出します。
オプション

キー	値
client_id	OAuthクライアントのクライアントID
client_secret	OAuthクライアントのクライアントシークレット
refresh_token	上記で取得した Refresh Token
redirect_uri	http://localhost:8080
grant_type	refresh_token

cURLで呼び出す例

curl -X POST -d 'refresh_token=[Refresh Token]' -d 'client_id=[Client ID]' -d `client_secret=[Client Secret] -d `grant_type=refresh_token` -d 'redirect_uri=http://localhost:8080' https://www.googleapis.com/oauth2/v4/token

上記を呼び出すと、以下のようなJSONが戻ってきます。

{
    "access_token": "～～～～～", // ←これが Access Token
    "expires_in": 3599,
    "scope": "https://www.googleapis.com/auth/spreadsheets.readonly",
    "token_type": "Bearer"
}

これで、 Refresh Token を使用して Access Token を取得 し、改めて取得した Access Tokenを使って Google Sheets API へアクセスする ことが出来る様になりました。
目的達成です🙌🙌🙌
Refresh Token は大事な情報ですので 忘れない様にどこかへ 控えておきましょう。

さいごに

今回ご紹介した方法は、Refresh Token (リフレッシュトークン) の本来の利用目的から若干外れた使い方をしていますので、以下ご留意ください。

Refresh Token を使い続けて大丈夫なの？

OAuth2.0での Refresh Token(リフレッシュトークン) は、本来、「アクセスを承認された人がアクセストークンを更新する為に使うトークン」です。ですので、 これを使い続けるということ は「あなたがアクセスを許可した証をずっと使い続ける」＝「あなたの代わりにアクセスし続ける」ということになります。
リフレッシュトークンが本来想定されている使い方とは若干異なる使い方ですので、ご理解の上、生成したリフレッシュトークンは厳密に管理してご利用ください。

Refresh Token はいつまで使えるの？

GCPのRefresh Tokenは、6ヶ月使用されない場合、そのほか以下の場合は期限切れとなりますのでご注意ください。
更新トークンの有効期限

- ユーザーがアプリのアクセス権を取り消しました。
- 更新トークンが 6 か月間使用されていない。
- ユーザーがパスワードを変更し、更新トークンに Gmail のスコープが含まれている。
- ユーザー アカウントが、付与できる（ライブ）更新トークンの最大数を超えました。
- 管理者がアプリのスコープでリクエストされたサービスを制限付きに設定している場合（エラーは admin_policy_enforced）。
- Google Cloud Platform API - 管理者が設定したセッション継続時間を超過している可能性があります。