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!!

Katerine

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.

Chile

Answer 2

If you're using google spreadsheet api, it can be achieved easily. (This is not a gspread solution, but check whether this endpoint can be accessed with gspread, will remove much burden using API calls) From the array length take the number of rows. Example

my_list = [[1,2],[3,4],[5,6],[7,8]]

So here we have 4 elements with 2 columns (maximum)

range = "A1:B"+len(my_list)

now simply send a post request to the spreadsheets.values.append endpoint

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append

with the post body as below

{
  "range": range,
  "majorDimension": "ROWS",
  "values": my_list,
}

You can try this API endpoint online here

Mood

Answer 3

I'd recommend using a data forms library like pandas to convert the list into a xlsx obj then upload it using the google drive api.

Celina

Answer 4

I was able to successfully accomplish inserting a large array (dataframe) into a google sheet using the following:

source link: https://pythonhosted.org/gspread-dataframe/

import pandas_datareader.tsp as tsp
tspreader = tsp.TSPReader(start='2019-12-1', end='2019-12-5')
tspout=tspreader.read()

import gspread
#documentation https://gspread.readthedocs.io/en/latest/api.html
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open("nameOFyourWORKBOOK").worksheet("nameOFyourSHEET")

from gspread_dataframe import get_as_dataframe, set_with_dataframe

set_with_dataframe(sheet, tspout, row=1, col=1, include_index=True, include_column_header=True, resize=False, allow_formulas=True)

I hope this helps.

Edile