Using Python gdata to clear the rows in worksheet before adding data

Question

I have a Google Spreadsheet which I'm populating with values using a python script and the gdata library. If i run the script more than once, it appends new rows to the worksheet, I'd like the script to first clear all the data from the rows before populating it, that way I have a fresh set of data every time I run the script. I've tried using:

UpdateCell(row, col, value, spreadsheet_key, worksheet_id)

but short of running a two for loops like this, is there a cleaner way? Also this loop seems to be horrendously slow:

for x in range(2, 45):
      for i in range(1, 5):
        self.GetGDataClient().UpdateCell(x, i, '', 
                                         self.spreadsheet_key,
                                         self.worksheet_id)

Answer 1

Not sure if you got this sorted out or not, but regarding speeding up the clearing out of current data, try using a batch request. For instance, to clear out every single cell in the sheet, you could do:

cells = client.GetCellsFeed(key, wks_id)
batch_request = gdata.spreadsheet.SpreadsheetsCellsFeed()

# Iterate through every cell in the CellsFeed, replacing each one with ''
# Note that this does not make any calls yet - it all happens locally
for i, entry in enumerate(cells.entry):
  entry.cell.inputValue = ''
  batch_request.AddUpdate(cells.entry[i])

# Now send the entire batchRequest as a single HTTP request
updated = client.ExecuteBatch(batch_request, cells.GetBatchLink().href)

If you want to do things like save the column headers (assuming they are in the first row), you can use a CellQuery:

# Set up a query that starts at row 2
query = gdata.spreadsheet.service.CellQuery()
query.min_row = '2'

# Pull just those cells
no_headers = client.GetCellsFeed(key, wks_id, query=query)

batch_request = gdata.spreadsheet.SpreadsheetsCellsFeed()

# Iterate through every cell in the CellsFeed, replacing each one with ''
# Note that this does not make any calls yet - it all happens locally
for i, entry in enumerate(no_headers.entry):
  entry.cell.inputValue = ''
  batch_request.AddUpdate(no_headers.entry[i])

# Now send the entire batchRequest as a single HTTP request
updated = client.ExecuteBatch(batch_request, no_headers.GetBatchLink().href)

Alternatively, you could use this to update your cells as well (perhaps more in line with that you want). The link to the documentation provides a basic way to do that, which is (copied from the docs in case the link ever changes):

import gdata.spreadsheet
import gdata.spreadsheet.service

client = gdata.spreadsheet.service.SpreadsheetsService()
# Authenticate ...

cells = client.GetCellsFeed('your_spreadsheet_key', wksht_id='your_worksheet_id')

batchRequest = gdata.spreadsheet.SpreadsheetsCellsFeed()

cells.entry[0].cell.inputValue = 'x'
batchRequest.AddUpdate(cells.entry[0])
cells.entry[1].cell.inputValue = 'y'
batchRequest.AddUpdate(cells.entry[1])
cells.entry[2].cell.inputValue = 'z'
batchRequest.AddUpdate(cells.entry[2])
cells.entry[3].cell.inputValue = '=sum(3,5)'
batchRequest.AddUpdate(cells.entry[3])

updated = client.ExecuteBatch(batchRequest, cells.GetBatchLink().href)