Inserting a list of list in python to google sheets using gspread

Question

I have created a nested list in python which looks like

my_list = [[a,b],[c,d],[e,f],[g,h].....]

What I want to do is insert this list as a batch so that each element gets inserted in a new row in the google sheet. This list is generated from an user input and therefore the number of elements in my_list may vary. The final output should look as follows:

I do not want to do this row by row as the list can be lengthy and there can be many similar lists which will the make the entire process inefficient.

Any help would be greatly appreciated!!

Answer 1

In gspread version 3.0.0 (the latest at the time of writing), the most efficient way to insert a nested list like you described is the following:

my_list = [['a', 'b'], ['c', 'd'], ['e', 'f'], ['g', 'h']]

sh.values_update(
    'Sheet1!A1', 
    params={'valueInputOption': 'RAW'}, 
    body={'values': my_list}
)

Where sh is an instance of Spreadsheet class and Sheet1 is the name of a sheet you're updating.

This will update the values of cells in one go with a single request to the Sheets API.

Note: in future releases of gspread, there could be an alternative way to do this. Please keep an eye on the repo.