Add multiple rows into google spreadsheet using API

Question

I need to add multiple (few hundreds) rows into google spreadsheet. Currently I'm doing it in a loop:

for row in rows _api_client.InsertRow(row, _spreadsheet_key, _worksheet_id)

which is extremely slow, because rows are added one by one.

Is there any way to speed this up?

The API has a "batch update", I have used it with Java, but not look at the python version. — eddyparkinson
– eddyparkinson, Commented Jul 31, 2014 at 0:16

damgad · Accepted Answer · 2014-07-31 16:01:13Z

Ok, I finally used batch request. The idea is to send multiple changes in a one API request.

Firstly, I created a list of dictionaries, which will be used like rows_map[R][C] to get value of cell at row R and column C.

rows_map = [ { 1: row['first_column'] 2: row['second'] 3: row['and_last'] } for row i rows ]

Then I get all the cells from the worksheet

query = gdata.spreadsheet.service.CellQuery() query.return_empty = 'true' cells = _api_client.GetCellsFeed(self._key, wksht_id=self._raw_events_worksheet_id, query=query)

And create batch request to modify multiple cells at a time.

batch_request = gdata.spreadsheet.SpreadsheetsCellsFeed()

Then I can modify (or in my case rewrite all the values) the spreadsheet.

for cell_entry in cells.entry: row = int(cell_entry.cell.row) - 2 col = int(cell_entry.cell.col) if 0 <= row < len(events_map): cell_entry.cell.inputValue = rows_map[row][col] else: cell_entry.cell.inputValue = '' batch_request.AddUpdate(cell_entry)

And send all the changes in only one request:

_api_client.ExecuteBatch(batch_request, cells.GetBatchLink().href)

NOTES:

Batch request are possible only with Cell Queries. There is no such mechanism to be used with List Queries.

query.return_empty = 'true' is mandatory. Otherwise API will return only cells which are not empty.

Collectives™ on Stack Overflow

Add multiple rows into google spreadsheet using API

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related