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

Answer 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.

Answer 2

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

Should be:

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

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

Answer 3

I had a similar issue while using Gspread and the following worked for me:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('example.json', scope)
client = gspread.authorize(creds)

actvsheet = client.open('examplesheet')
sheet = actvsheet.get_worksheet(1)

sheet.clear()