Google Sheets API Python - Clear sheet

Question

I'd like to constantly update/re-write to a Google sheet. I cannot just update it, though, without clearing out the old sheet because sometime the update has less rows then the previous and the old rows are left in the sheet.

The protocol listed on the developer page is thus:

{
  "requests": [
    {
      "updateCells": {
        "range": {
          "sheetId": sheetId
        },
        "fields": "userEnteredValue"
      }
    }
  ]
}

Translated to python would look like this, I think:

requests = [{ 'updateCells': { 'range': { 'sheetId': spreadsheet_id }, 'fields': 'userEnteredValue' } }]

body = { 'requests': requests }
spreadsheet_id='[uniqueIDhere]'

result = service.spreadsheets( ).values( ).batchUpdate(spreadsheetId=spreadsheet_id, body=body ).execute( )

Which returns the error:

googleapiclient.errors.HttpError: https://sheets.googleapis.com/v4/spreadsheets/[uniqueIDhere]/values:batchUpdate?alt=json returned "Invalid JSON payload received. Unknown name "requests": Cannot find field.">

Seems weird that 'requests' is invalid as it's listed right there in the protocol. Anyways, anybody else get this to work? Thanks. - jason

Solution 1

Found a different method:

rangeAll = '{0}!A1:Z'.format( sheetName )
body = {}
resultClear = service.spreadsheets( ).values( ).clear( spreadsheetId=spreadsheet_id, range=rangeAll,
                                                       body=body ).execute( )

This works nicely. Still wondering why requests-updateCells protocol doesn't work.

Solution 2

result = service.spreadsheets( ).values( ).batchUpdate(...

Should be:

result = service.spreadsheets( ).batchUpdate(...

Took me too long to notice the same mistake in my code...

Comments

[fpilee]

Thanks, not setting the row number after the Z works fine to clear entirely a sheet at least under the Z