Search code examples
pythongoogle-sheetsgoogle-sheets-apiservice-accounts

Google sheets API: Service account authentication: Updating cell value fails with a 403 response, only reading is successful

from apiclient import discovery
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file(
  os.path.join(os.getcwd(), 'app3-c1824-91dfa420b4a8.json'), 
  scopes=['https://www.googleapis.com/auth/spreadsheets']
)
apiService = discovery.build('sheets', 'v4', credentials=credentials)

values = apiService.spreadsheets().values().get(
  spreadsheetId='1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok',
  range='A1'
).execute()

print(f"READ response: {values}") # READ is successful

res = apiService.spreadsheets().values().update(
  spreadsheetId='1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok',
  range='Sheet1!A2',
  valueInputOption='RAW',
  body={
    'values': [['test']]
  }
)

print(f"UPDATE response: {res}") # UPDATE fails with 403

Log output for the READ request:

2023-09-28 17:45:28 [googleapiclient.discovery_cache] INFO: file_cache is only supported with oauth2client<4.0.0
2023-09-28 17:45:28 [googleapiclient.discovery_cache] INFO: file_cache is only supported with oauth2client<4.0.0
2023-09-28 17:45:28 [googleapiclient.discovery] DEBUG: URL being requested: GET https://sheets.googleapis.com/v4/spreadsheets/1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok/values/A1?alt=json
2023-09-28 17:45:28 [google_auth_httplib2] DEBUG: Making request: POST https://oauth2.googleapis.com/token
READ response: {'range': 'Sheet1!A1', 'majorDimension': 'ROWS', 'values': [['prashan']]}

Log output for the UPDATE request:

2023-09-28 17:45:28 [googleapiclient.discovery] DEBUG: URL being requested: PUT https://sheets.googleapis.com/v4/spreadsheets/1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok/values/Sheet1%21A2?valueInputOption=RAW&alt=json
        UPDATE response: <googleapiclient.http.HttpRequest object at 0x106c1ef50>

When I open up the returned res object from the UPDATE request:

res
<googleapiclient.http.HttpRequest object at 0x106c1ef50>
special variables:
function variables:
body: '{"values": [["test"]]}'
body_size: 22
headers: {'accept': 'application/json', 'accept-encoding': 'gzip, deflate', 'user-agent': '(gzip)', 'x-goog-api-client': 'gdcl/2.96.0 gl-python/3.11.3', 'content-type': 'application/json'}
http: <google_auth_httplib2.AuthorizedHttp object at 0x108628590>
method: 'PUT'
methodId: 'sheets.spreadsheets.values.update'
response_callbacks: []
resumable: None
resumable_progress: 0
resumable_uri: None
uri: 'https://sheets.googleapis.com/v4/spreadsheets/1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok/values/Sheet1%21A2?valueInputOption=RAW&alt=json'
_in_error_state: False
_process_response: <bound method HttpRequest._process_response of <googleapiclient.http.HttpRequest object at 0x106c1ef50>>
_rand: <built-in method random of Random object at 0x14e0a9c20>
_sleep: <built-in function sleep>

When I visit the res.uri, I get the following JSON:

{
  "error": {
    "code": 403,
    "message": "The request is missing a valid API key.",
    "status": "PERMISSION_DENIED"
  }
}

I have properly enabled the Sheets API for the app3 project in which the service account resides:

enter image description here

The service account has been given "Editor" access to the sheet in question:

enter image description here

One thing that's interesting is that the UPDATE request doesn't explicitly fail, the log output from the UPDATE request is neutral at best and the A2 value of the Google sheet remains blank. I have to open up the res object to even get to the special json resource that shows me the 403 error. It's complaining about a missing API key, but I am using service account authentication. (Google sheets API does not support WRITE operations using API key authentication anyway.

Solution

  • In your showing script, how about the following modification?

    From:

    res = apiService.spreadsheets().values().update(
  spreadsheetId='1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok',
  range='Sheet1!A2',
  valueInputOption='RAW',
  body={
    'values': [['test']]
  }
)

    To:

    res = apiService.spreadsheets().values().update(
    spreadsheetId='1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok',
    range='Sheet1!A2',
    valueInputOption='RAW',
    body={
        'values': [['test']]
    }
).execute()
    • In this modification .execute() is added. If you have the write permission of this Spreadsheet, I think that the client of apiService can be used for this request.