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

Question

I have a Google Spreadsheet that I populate with values ​​using a python script and gdata library. If I run the script more than once, it adds new lines to the worksheet, I would like the script to first clear all the data from the lines before filling them, so I have a fresh dataset every time I run the script. I tried using:

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

but two are not enough for such cycles, is there a cleaner way? Also this loop looks awfully 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 found out or not, but to speed up the cleaning of your current data, try using a batch request . For example, to clear every single cell in a worksheet, you can 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 column headers (assuming they are on the first row), you can use 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 can use this to update your cells (perhaps more according to what you want). A link to the documentation provides a basic way to do this, which (copied from documents if the link 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)