Populating google spreadsheet by row, not by cell

Question

I have a spreadsheet whose values I want to populate with values from dictionaries within a list. I wrote a for loop that updates cell by cell, but it is too slow and I get the gspread.httpsession.HTTPError often. I am trying to write a loop to update row by row. Thats what I have:

lstdic=[
{'Amount': 583.33, 'Notes': '', 'Name': 'Jone', 'isTrue': False,},
{'Amount': 58.4, 'Notes': '', 'Name': 'Kit', 'isTrue': False,},
{'Amount': 1083.27, 'Notes': 'Nothing', 'Name': 'Jordan', 'isTrue': True,}
]

Here is my cell by cell loop:

headers = wks.row_values(1)

    for k in range(len(lstdic)):
        for key in headers:
            cell = wks.find(key)
            cell_value = lstdic[k][key]
            wks.update_cell(cell.row + 1 + k, cell.col, cell_value)

What it does is it finds a header that corresponds to the key in the list of dictionaries and updates the cell under it. The next iteration the row is increased by one, so it updates cells in the same columns, but next row. This is too slow and I want to update by row. My attempt:

headers = wks.row_values(1)

row=2
for k in range(len(lsdic)):
    cell_list=wks.range('B%s:AA%s' % (row,row))
    for key in headers: 
        for cell in cell_list:
            cell.value = lsdic[k][key]
    row+=1
    wks.update_cells(cell_list)

This one updates each row quickly, but with the same value. So, the third nested for loop assigns the the same value for each cell. I am breaking my head trying to figure out how to assign right values to the cells. Help appreciated.

P.S. by the way I am using headers because I want a certain order in which values in the google spreadsheet should appear.

score 4 · Accepted Answer · answered Jun 01 '16 at 12:27

The following code is similar to Koba's answer but writes the full sheet at once instead of per row. This is even faster:

# sheet_data  is a list of lists representing a matrix of data, headers being the first row.
#first make sure the worksheet is the right size
worksheet.resize(len(sheet_data), len(sheet_data[0]))
cell_matrix = []
rownumber = 1

for row in sheet_data:
    # max 24 table width, otherwise a two character selection should be used, I didn't need this.
    cellrange = 'A{row}:{letter}{row}'.format(row=rownumber, letter=chr(len(row) + ord('a') - 1))
    # get the row from the worksheet
    cell_list = worksheet.range(cellrange)
    columnnumber = 0
    for cell in row:
        cell_list[columnnumber].value = row[columnnumber]
        columnnumber += 1
    # add the cell_list, which represents all cells in a row to the full matrix
    cell_matrix = cell_matrix + cell_list
    rownumber += 1
# output the full matrix all at once to the worksheet.
worksheet.update_cells(cell_matrix)

This worked super well for me and sped up my script by orders of magnitude. Thank you. — Andi, Aug 04 '16 at 22:33
Good to hear, I noticed the round trip time is quite long and I'm not sure if this is the Gspread library not being very efficient or the way google made it, but doing as much as possible in one go seems fastest. I personally use this to create a sheet with the current date in the name and fully replace it with the latest data. — Steven Stip, Aug 08 '16 at 12:49

score 2 · Answer 2 · answered Aug 01 '14 at 17:55

I ended up writing the following loop that fills a spreadsheet by row amazingly fast.

headers = wks.row_values(1)        
row = 2 # start from the second row because the first row are headers
for k in range(len(lstdic)):
        values=[]
        cell_list=wks.range('B%s:AB%s' % (row,row)) # make sure your row range equals the length of the values list
        for key in headers:
            values.append(lstdic[k][key])   
        for i in range(len(cell_list)):
            cell_list[i].value = values[i]
        wks.update_cells(cell_list)
        print "Updating row " + str(k+2) + '/' + str(len(lstdic) + 1)
        row += 1

for each row you are fetching a range (apparently that's all you can do though) but the updating of the cells is row by row as well, this can be done in one go at the end. — Steven Stip, Jun 01 '16 at 12:18

Populating google spreadsheet by row, not by cell

2 Answers2