CODEWITHSUNDEEP
pythongdatagoogle-sheets-api
damgad
damgad

Reputation: 1446

Add multiple rows into google spreadsheet using API

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?

Upvotes: 5

Views: 3042

Answers (1)

damgad
damgad

Reputation: 1446

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.

Upvotes: 6

Related Questions