Appending pandas Data Frame to Google spreadsheet

Question

Case: My script returns a data frame that needs has to be appended to an existing google spreadsheet as new rows of data.As of now, I'm appending a data frame as multiple single rows through gspread.

My Code:

import gspread
import pandas as pd
df = pd.DataFrame()

# After some processing a non-empty data frame has been created.

output_conn = gc.open("SheetName").worksheet("xyz")

# Here 'SheetName' is google spreadsheet and 'xyz' is sheet in the workbook

for i, row in df.iterrows():
    output_conn.append_row(row)

Is there a way to append entire data-frame rather than multiple single rows?

Answer 1

I can recommend gspread-dataframe:

import gspread_dataframe as gd

# Connecting with `gspread` here

ws = gc.open("SheetName").worksheet("xyz")
existing = gd.get_as_dataframe(ws)
updated = existing.append(your_new_data)
gd.set_with_dataframe(ws, updated)

Answer 2

Here is the code to write, append(without loading the existing sheet into memory), and read to google sheets.

import gspread_dataframe as gd
import gspread as gs
gc = gs.service_account(filename="your/cred/file.json")

def export_to_sheets(worksheet_name,df,mode='r'):
    ws = gc.open("SHEET_NAME").worksheet("worksheet_name")
    if(mode=='w'):
        ws.clear()
        gd.set_with_dataframe(worksheet=ws,dataframe=df,include_index=False,include_column_header=True,resize=True)
        return True
    elif(mode=='a'):
        ws.add_rows(df.shape[0])
        gd.set_with_dataframe(worksheet=ws,dataframe=df,include_index=False,include_column_header=False,row=ws.row_count+1,resize=False)
        return True
    else:
        return gd.get_as_dataframe(worksheet=ws)
    
df = pd.DataFrame.from_records([{'a': i, 'b': i * 2} for i in range(100)])
export_to_sheets("SHEET_NAME",df,'a')

---

1. Write Mode: First clear existing worksheet => ws.clear() .Second using set_with_dataframe() uploading the dataframe, here note that resize=True, which strictily set the row and col in worksheet to df.shape. This will help later in append method.
2. Append Mode: First, add rows according to the dataframe. Second setting the parameter resize=False as we are adding rows and row=ws.row_count+1 anchoring its row value for append.
3. Read Mode(Default): returns a dataframe

Answer 3

I was facing the same problem, here's what I did converted the dataframe into list and used gspread's append_rows()

    gc = gspread.service_account(filename="credentials.json")
    sh = gc.open_by_key('<your_key>')
    ws = sh.sheet1
    
    ##data is the original data frame
    data_list = data.values.tolist()
    
    ws.append_rows(data_list)