Google Spreadsheet API

## 背景

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

## 結論

1. UserのADCでもAPIの使用可
1. 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
```

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


### Step3: Code

```py: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
```

## 色々試したもの
<details>

## 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

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


</details>