gspread sheet_to_df code example

Example 1: gspread writing dataframe to sheet

#!/usr/bin/env python
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials

def iter_pd(df):
    for val in df.columns:
        yield val
    for row in df.to_numpy():
        for val in row:
            if pd.isna(val):
                yield ""
            else:
                yield val

def pandas_to_sheets(pandas_df, sheet, clear = True):
    # Updates all values in a workbook to match a pandas dataframe
    if clear:
        sheet.clear()
    (row, col) = pandas_df.shape
    cells = sheet.range("A1:{}".format(gspread.utils.rowcol_to_a1(row + 1, col)))
    for cell, val in zip(cells, iter_pd(pandas_df)):
        cell.value = val
    sheet.update_cells(cells)

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name('service.json', scope)

gc = gspread.authorize(credentials)

workbook = gc.open_by_key("<workbook id>")
sheet = workbook.worksheet("worksheet_name")

df = pd.read_csv("input_data.tsv")
pandas_to_sheets(df, workbook.worksheet("worksheet"))

Example 2: gspread send dataframe to sheet

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)