I am trying to populate an already created google sheet from my sql table using python and gspread.
I can update the sheet one row at a time using a for loop, but i have a lot of data to add to the sheet and want to do a column at a time or more if possible.
Any suggestions here's what i've been using and i get an error: Object of type 'Row' is not JSON serializable
#!/usr/bin/python3
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import dbconnect
#credentials for google
gc = gspread.authorize(credentials)
worksheet = gc.open('NAMEOFWS').sheet1
cell_list = worksheet.range('A2:A86')
#connect to database using dbconnect and grab cursor
query = "select loc from table"
cursor.execute(query)
results = cursor.fetchall()
cell_values = (results)
for i, val in enumerate(cell_values):
cell_list[i].value = val
worksheet.update_cells(cell_list)
I am not sure how to do this with gspread, but you can modify you code very easily and use pygsheets and it allows to update a column all at once. Also, I am not sure what your data looks like so the below may need to be altered or you may need to alter your data set a little. Hope this helps.
import pygsheets
gc = pygsheets.authorize(service_file = 'client_secret2.json')
# Open spreadsheet and select worksheet
sh = gc.open('Api_Test')
wks = sh.sheet1
#update Column
notes = [1,2,3,4] #this is the dataset to getupdated in the column
wks.update_col(4, notes, 1)# 4 is the number of column, notes is the dataset to update, 1 skips the first row (I used a header)