Google Spreadsheet API

Posted at 2024-07-04

背景

Google Spreadsheet APIへのアクセス方法は意外と複雑かつ Oauth Clientを指定するものやService AccountのJson Keyを使う例がネット上で多くみられ、ADCでの接続方法を毎回忘れてハマるのでメモっておく

結論

UserのADCでもAPIの使用可
Service Account ADCは試してない

やり方

Step1: 変数定義

SPREADSHEET_ID="xxxx"
RANGE_NAME="sheet!A1%3AB2"
PROJECT=<gcpproject>

Step2: gcloud auth login

gcloud auth application-default login \
            --scopes=openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets
gcloud auth application-default set-quota-project $PROJECT

scopesに https://www.googleapis.com/auth/spreadsheets を含める
quota projectを設定しないと怒られるので set-quota-projectで設定

Step3: Code

main.py

SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
]
SPREADSHEET_ID = ""
RANGE_NAME = "<sheet>!A1:B2"
creds, _ = default(scopes=SCOPES)

service = build("sheets", "v4", credentials=creds)
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range).execute()
values = result.get("values", [])

python main.py

色々試したもの

Appendix

oauth2l

Error: PERMISSION_DENIED

curl -H "$(oauth2l header --scope https://www.googleapis.com/auth/spreadsheets.readonly)" "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE_NAME"
{
  "error": {
    "code": 403,
    "message": "Your application is authenticating by using local Application Default Credentials. The sheets.googleapis.com API requires a quota project, which is not set by default. To learn how to set your quota project, see https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds .",
    "status": "PERMISSION_DENIED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "SERVICE_DISABLED",
        "domain": "googleapis.com",
        "metadata": {
          "consumer": "projects/xxx",
          "service": "sheets.googleapis.com"
        }
      }
    ]
  }
}

$(oauth2l header --scope https://www.googleapis.com/auth/spreadsheets.readonly)

curl "https://oauth2.googleapis.com/tokeninfo?access_token=$(oauth2l fetch --scope https://www.googleapis.com/auth/spreadsheets.readonly)"
{
  "azp": "xxxxxxx.apps.googleusercontent.com",
  "aud": "xxxxxxx.apps.googleusercontent.com",
  "sub": "xxxxx",
  "scope": "https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/spreadsheets.readonly https://www.googleapis.com/auth/userinfo.email openid",
  "exp": "1719662319",
  "expires_in": "2343",
  "email": "xxx@gmail.com",
  "email_verified": "true",
  "access_type": "offline"
}

UNAUTHENTICATED

curl -H "$(oauth2l header --scope https://www.googleapis.com/auth/spreadsheets.readonly)" "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE_NAME"
{
  "error": {
    "code": 401,
    "message": "Request had invalid authentication credentials. 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"
  }
}

curl "https://oauth2.googleapis.com/tokeninfo?access_token=$(oauth2l fetch --scope https://www.googleapis.com/auth/spreadsheets.readonly)"
{
  "error": "invalid_token",
  "error_description": "Invalid Value"
}

https://github.com/google/oauth2l

gcloud auth print-access-token

curl "https://oauth2.googleapis.com/tokeninfo?access_token=$(gcloud auth print-access-token)"
{
  "azp": "xxxxxxx.apps.googleusercontent.com",
  "aud": "xxxxxxx.apps.googleusercontent.com",
  "sub": "xxxxx",
  "scope": "https://www.googleapis.com/auth/accounts.reauth https://www.googleapis.com/auth/appengine.admin https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/compute https://www.googleapis.com/auth/sqlservice.login https://www.googleapis.com/auth/userinfo.email openid",
  "exp": "1719661328",
  "expires_in": "1271",
  "email": "xxx@gmail.com",
  "email_verified": "true",
  "access_type": "offline"
}

gcloud auth application-default print-access-token

curl -H "Autorization: Bearer $(gcloud auth application-default print-access-token)" "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE_NAME"
{
  "error": {
    "code": 403,
    "message": "Method doesn't allow unregistered callers (callers without established identity). Please use API Key or other form of API consumer identity to call this API.",
    "status": "PERMISSION_DENIED"
  }
}

curl "https://oauth2.googleapis.com/tokeninfo?access_token=$(gcloud auth application-default print-access-token)"
{
  "azp": "xxxxxxx.apps.googleusercontent.com",
  "aud": "xxxxxxx.apps.googleusercontent.com",
  "sub": "xxxxx",
  "scope": "https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/spreadsheets.readonly https://www.googleapis.com/auth/userinfo.email openid",
  "exp": "1719664539",
  "expires_in": "3599",
  "email": "xxx@gmail.com",
  "email_verified": "true",
  "access_type": "offline"
}

gcurl

alias gcurl='curl -H "Authorization: Bearer $(gcloud auth print-access-token)" -H "Content-Type: application/json"'

gcurl "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE_NAME"
{
  "error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "status": "PERMISSION_DENIED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT",
        "domain": "googleapis.com",
        "metadata": {
          "service": "sheets.googleapis.com",
          "method": "google.apps.sheets.v4.SpreadsheetsService.GetValues"
        }
      }
    ]
  }
}

curl -H "Authorization: Bearer $(gcloud auth application-default print-access-token)"

curl -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" -H "Content-Type: application/json" "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE_NAME"
{
  "error": {
    "code": 403,
    "message": "Your application is authenticating by using local Application Default Credentials. The sheets.googleapis.com API requires a quota project, which is not set by default. To learn how to set your quota project, see https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds .",
    "status": "PERMISSION_DENIED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "SERVICE_DISABLED",
        "domain": "googleapis.com",
        "metadata": {
          "consumer": "projects/xxx",
          "service": "sheets.googleapis.com"
        }
      }
    ]
  }
}

gcloud auth application-default set-quota-project $PROJECT

ADCでできると言ってるができない

https://stackoverflow.com/questions/72526314/google-sheet-api-access-with-application-default-credentials-using-scopes-giving/72986975#72986975

  File "xxxxxxx/lib/python3.10/site-packages/googleapiclient/http.py", line 938, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/xxxx/values/sheet%21A1%3AB2?alt=json returned "The caller does not have permission". Details: "The caller does not have permission">

Ref

https://cloud.google.com/docs/authentication/token-types#bearer

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up