たぬこんにちは、グロースハッカーの たぬ （ @tanuhack ）です。
プログラムから Google スプレッドシートとデータのやり取りを行いたい場合、
- 共有設定は『リンクを知っている全員』か『許可したアカウントのみ』か
- スプレッドシートのデータを『読み込むだけでいい』のか『書き込みもしたい』のか
- 『フリーのGoogleアカウント』か『Google Workspaceのアカウント』か
といった、それぞれの状態・目的に適した認証方法を行う必要があります。
文字で説明すると難しくなるので、状態・目的に応じたフローチャート図を作成しました。①とか②とか③はこの記事の章の番号になっています。よければ参考にしてください。
目次
①無料のGoogleアカウントの場合
手順
- GCP アカウントを開設する
- Sheets API を有効にする
- サービスアカウントのクレデンシャルファイルを発行する
- サービスアカウントのアドレスを共有設定で追加する
- google-api-python-client モジュールでデータを読み込む
サービスアカウントのアドレスを共有設定で追加する
共有設定でサービスアカウントのアドレスを 閲覧権限 または 編集権限 で追加します。データを取得するだけなら 閲覧権限 でも大丈夫です。
サービスアカウントのアドレスは、クレデンシャルファイルのclient_emailプロパティの中に格納されています。
{
"type": "service_account",
"project_id": "************",
"private_key_id": "************",
"private_key": "************",
"client_email": "************@************.iam.gserviceaccount.com",
"client_id": "************",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "************"
}google-api-python-client モジュールでデータを読み込む
モジュールのインストール
pip install google-auth
pip install google-api-python-clientgoogle-auth：Google APIs の認証周りgoogle-api-python-client：Google API Python クライアントライブラリ
プログラム
import google.auth
from googleapiclient.discovery import build
import pandas as pd
# (1) Sheets APIの認証周り
scopes = ['https://www.googleapis.com/auth/spreadsheets']
creds, _ = google.auth.load_credentials_from_file('{ クレデンシャルファイルのパス }', scopes=scopes)
service = build('sheets', 'v4', credentials=creds)
# (2) スプレッドシートからデータを取得
request = service.spreadsheets().values().get(
spreadsheetId='{ ワークブックID }',
range='{ ワークシート名[!範囲セル] }'
)
response = request.execute()
data = response['values']
# (3) レスポンスの内容をデータフレームに変換
df = pd.DataFrame(data=data[1:], columns=data[0])②Google Workspaceアカウントの場合
手順
- GCP アカウントを開設する
- Sheets API を有効にする
- サービスアカウントのクレデンシャルファイルを発行する
- OAuth 2.0 クライアント ID を発行する
- 管理コンソールでクライアント ID を追加する
- google-api-python-client モジュールでデータを読み込む
OAuth 2.0 クライアント ID を発行する
STEP
IAM と管理 → サービスアカウント
OAuth 2.0 クライアント ID を発行するサービスアカウントを選択
STEP
Google Workspace ドメイン全体の委任を有効にする
STEP
クライアントID をコピーする
管理コンソールでクライアント ID を追加する
STEP
セキュリティ → APIの制御 → ドメイン全体の委任
新しく API クライアントを追加します。
STEP
クライアント ID と OAuth 認証するスコープを設定する
google-api-python-client モジュールでデータを読み込む
モジュールのインストール
pip install google-auth
pip install google-api-python-clientgoogle-auth：Google APIs の認証周りgoogle-api-python-client：Google API Python クライアントライブラリ
プログラム
from google.oauth2 import service_account
from googleapiclient.discovery import build
import pandas as pd
# (1) Sheets APIの認証周り
scopes = ['https://www.googleapis.com/auth/spreadsheets']
creds = service_account.Credentials.from_service_account_file(
'{ クレデンシャルファイルのパス }',
scopes=scopes,
subject='{ Google Workspaceのアカウント }'
)
service = build('sheets', 'v4', credentials=creds)
# (2) スプレッドシートからデータを取得
request = service.spreadsheets().values().get(
spreadsheetId='{ ワークブックID }',
range='{ ワークシート名[!範囲セル] }'
)
response = request.execute()
data = response['values']
# (3) レスポンスの内容をデータフレームに変換
df = pd.DataFrame(data=data[1:], columns=data[0])from_service_account_fileメソッドのsubjectパラメータには、スプレッドシートの認証が通っているGoogle Workspaceのアカウント（**@example.com）を入力します。
③『リンクを知っている全員』の場合
手順
- GCP アカウントを開設する
- Sheets API を有効にする
- API キーを発行する
- requests モジュールでデータを読み込む
リンクの共有がオンになっている場合、面倒なクレデンシャルファイルの読み込み等は行わず、GCP で API キーを発行するだけで、スプレッドシートのデータにアクセスすることできます。
共有設定を『リンクを知っている全員』にして誰かとワークブックを共有することは滅多にないと思いますが、紹介だけしておきます。
API キーを発行する
STEP
『API とサービス』から認証情報を作成する
STEP
APIキーを取得する
STEP
APIキーを制限する
STEP
APIキーをコピーする
requests モジュールでデータを読み込む
モジュールのインストール
pip install requestsrequests：HTTP 通信ライブラリ
プログラム
import requests
import pandas as pd
url = 'https://sheets.googleapis.com/v4/spreadsheets/{ ワークブックID }/values/{ ワークシート名[!範囲セル] }'
payload = {
'key': '{ APIキー }',
}
r = requests.get(url, params=payload)
data = r.json()['values']
df = pd.DataFrame(data=data[1:], columns=data[0])参考公式リファレンス
注意点
API キーで認証を通した Google Sheets API v4 は、GET メソッドしかサポートしていないので、clearやupdateのような POST メソッドは使用できません。
共有設定を『リンクを知っている全員が編集可能』に変更しても同様です。
{
"error": {
"code": 401,
"message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
"status": "UNAUTHENTICATED"
}
}
コメント