mysheet.py

import streamlit as st
#from google.oauth2 import service_account
import pandas as pd
import gspread
import json

def get_gspread_connection():
    # Create a connection object.
    # credentials = service_account.Credentials.from_service_account_info(
    #     st.secrets["gcp_service_account"],
    #     scopes=[
    #         "https://www.googleapis.com/auth/spreadsheets",
    #     ],
    # )
    #client = gspread.authorize(credentials)

    st_credentials = st.secrets["gcp_service_account"]
    if type(st_credentials) is str:
        print("INFO: transforming str to dict")
        credentials_dict = json.loads(st_credentials, strict=False)
        client = gspread.service_account_from_dict(credentials_dict)
    else:
        print("INFO: using credentials in dict")
        client = gspread.service_account_from_dict(st_credentials)


    st_sheet_url = st.secrets["private_gsheets_url"]
    spreadsheet = client.open_by_url(st_sheet_url)
    worksheet = spreadsheet.get_worksheet(0)
    return worksheet

#@st.cache_data
def read_gspread():
    worksheet = get_gspread_connection()
    df = pd.DataFrame(worksheet.get_all_records())
    return df

def write_gspread(df):
    #df.loc[len(df)] = ['Mia','worst']
    worksheet = get_gspread_connection()
    worksheet.update([df.columns.values.tolist()] + df.values.tolist())