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"))