![](https://boul.tech/wp-content/uploads/2021/06/tanu.jpg)
こんにちは、グロースハッカーの たぬ ( @tanuhack )です。
プログラムから Google スプレッドシートとデータのやり取りを行いたい場合、
- 共有設定は『リンクを知っている全員』か『許可したアカウントのみ』か
- スプレッドシートのデータを『読み込むだけでいい』のか『書き込みもしたい』のか
- 『フリーのGoogleアカウント』か『Google Workspaceのアカウント』か
といった、それぞれの状態・目的に適した認証方法を行う必要があります。
文字で説明すると難しくなるので、状態・目的に応じたフローチャート図を作成しました。①とか②とか③はこの記事の章の番号になっています。よければ参考にしてください。
![](https://boul.tech/wp-content/uploads/2021/07/Untitled-1024x651.jpg)
![](https://boul.tech/wp-content/uploads/2021/07/Untitled-1024x651.jpg)
①無料のGoogleアカウントの場合
- GCP アカウントを開設する
- Sheets API を有効にする
- サービスアカウントのクレデンシャルファイルを発行する
- サービスアカウントのアドレスを共有設定で追加する
- google-api-python-client モジュールでデータを読み込む
サービスアカウントのアドレスを共有設定で追加する
共有設定でサービスアカウントのアドレスを 閲覧権限 または 編集権限 で追加します。データを取得するだけなら 閲覧権限 でも大丈夫です。
![](https://boul.tech/wp-content/uploads/2021/07/81f3419f1a1038e36d987ab90080cb8c.png)
![](https://boul.tech/wp-content/uploads/2021/07/81f3419f1a1038e36d987ab90080cb8c.png)
サービスアカウントのアドレスは、クレデンシャルファイルの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-client
google-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 を発行する
![](https://boul.tech/wp-content/uploads/2021/07/5398da52c52ac9bd218227cc512c888d-1024x707.png)
![](https://boul.tech/wp-content/uploads/2021/07/5398da52c52ac9bd218227cc512c888d-1024x707.png)
OAuth 2.0 クライアント ID を発行するサービスアカウントを選択
![](https://boul.tech/wp-content/uploads/2021/07/b6169d63263ba473181e6b9a03728478-1024x709.png)
![](https://boul.tech/wp-content/uploads/2021/07/b6169d63263ba473181e6b9a03728478-1024x709.png)
![](https://boul.tech/wp-content/uploads/2021/07/b1a2ce5967a21c4222055fde21bec38e-1024x709.png)
![](https://boul.tech/wp-content/uploads/2021/07/b1a2ce5967a21c4222055fde21bec38e-1024x709.png)
管理コンソールでクライアント ID を追加する
![](https://boul.tech/wp-content/uploads/2021/07/24395d111ad8e4fb4f901449b6fb0b30-1024x708.png)
![](https://boul.tech/wp-content/uploads/2021/07/24395d111ad8e4fb4f901449b6fb0b30-1024x708.png)
新しく API クライアントを追加します。
![](https://boul.tech/wp-content/uploads/2021/07/38af528c7af67693834cdec13e117f46.png)
![](https://boul.tech/wp-content/uploads/2021/07/38af528c7af67693834cdec13e117f46.png)
google-api-python-client モジュールでデータを読み込む
モジュールのインストール
pip install google-auth
pip install google-api-python-client
google-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 キーを発行する
![](https://boul.tech/wp-content/uploads/2021/07/19ba1ec9a53d4f48d0b3246cf3f4cd02-1024x804.png)
![](https://boul.tech/wp-content/uploads/2021/07/19ba1ec9a53d4f48d0b3246cf3f4cd02-1024x804.png)
![](https://boul.tech/wp-content/uploads/2021/07/48be45b0bad15fdb41021382fc34fe48.png)
![](https://boul.tech/wp-content/uploads/2021/07/48be45b0bad15fdb41021382fc34fe48.png)
![](https://boul.tech/wp-content/uploads/2021/07/e3d1254ccb81485287eb06165c8fa9f7.png)
![](https://boul.tech/wp-content/uploads/2021/07/e3d1254ccb81485287eb06165c8fa9f7.png)
![](https://boul.tech/wp-content/uploads/2021/07/0d9243711cbbdd9b24bfd14159714b81-1024x803.png)
![](https://boul.tech/wp-content/uploads/2021/07/0d9243711cbbdd9b24bfd14159714b81-1024x803.png)
![](https://boul.tech/wp-content/uploads/2021/07/3692e7883e198827adfcfeec78cb3a40-1024x804.png)
![](https://boul.tech/wp-content/uploads/2021/07/3692e7883e198827adfcfeec78cb3a40-1024x804.png)
requests モジュールでデータを読み込む
モジュールのインストール
pip install requests
requests
: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"
}
}
コメント