Update existing google sheet with a pandas data frame and gspread

Question

I would like to update a particular tab in a Google Sheets document using a pandas data frame. I have reviewed the documentation on GitHub and readthedocs.io and am able to successfully update a certain range of cells using the following code:

cell_list = sheet4.range('A1:A7')
cell_values = [1,2,3,4,5,6,7]

for i, val in enumerate(cell_values):  
    cell_list[i].value = val    

sheet4.update_cells(cell_list)

As a beginner, I am having trouble connecting the dots and figuring out how to update the entire sheet using a Pandas data frame. The data frame will have 9 columns and up to 9,000 rows, with the number of rows varying depending on when the data frame is generated.

Any advice is appreciated.

Torquay

Answer 1

pygsheets have pandas support inbuild.

import pygsheets
gc = pygsheets.authorize(service_file='file.json')

#open the google spreadsheet 
sh = gc.open_by_url('url')

#select the first sheet 
wks = sh[0]

#update the first sheet with df, starting at cell B2. 
wks.set_dataframe(df, 'B2')

Gharry

Answer 2

I ended up converting the pandas dataframe to a list and using the pygsheets package to update the google sheet.

import pygsheets
gc = pygsheets.authorize(service_file='file.json')

df = df.tolist()

#open the google spreadsheet 
sh = gc.open_by_url('url')

#select the first sheet 
wks = sh[0]

#update the first sheet with df, starting at cell B2. 
wks.update_cells(crange='B2',values = df)

Torquay

pygsheets supports directly updating data from dataframe. see pygsheets.readthedocs.io/en/latest/… – Gharry

Answer 3

You can also use df2gspread library:

from df2gspread import df2gspread as d2g
import pandas as pd

# your DataFrame
df = pd.DataFrame(0, index=range(9000), columns=range(9))

# file ID or path to spreadsheet: '/folder/folder/spreadhsheet'
spreadsheet = '2VVfq...nhR3I' 

# uploading
d2g.upload(df, spreadsheet, wks_name="second worksheet")

Hypabyssal