Hey All,
I have created a form and after submitting the form I want the data to be inserted into a google sheet.
I have connected the google sheet to my streamlit app and its running fine.
Which python library has anyone used to connect python streamlit and gsheets. If there is any tutorial that will help please send it.
TomJohn
February 22, 2023, 7:14pm
2
Is gsheetsdb still there?
on the pypi site, it says its deprecated?
Franky1
February 22, 2023, 8:16pm
4
It is still there, but the repo says, it is deprecated and the shillelagh
library should be used instead:
TomJohn
February 22, 2023, 8:33pm
5
Yes, and it was discussed in the abovementioned post. @jcarroll suggested using pandas:
import pandas as pd
@st.cache_data(ttl=600)
def load_data(sheets_url):
csv_url = sheets_url.replace('/edit#gid=', '/export?format=csv&gid=')
return pd.read_csv(csv_url)
# ok let's load the data
questions_df = load_data(st.secrets["public_gsheets_url"])
So I tried with the private google sheets from the streamlit documentation.
and it gives following error for gsheetsdb:
Has anyone tried with shillelagh to insert data?
TomJohn
February 23, 2023, 8:04pm
7
H! Not tried shillelagh but I am sucesfully using gbsheets: streamlit-po/streamlit-po.py at main ยท TomJohnH/streamlit-po ยท GitHub
Have you set requirements.txt file properly?
This could be an issue with python 3.10 ImportError: cannot import name 'Iterable' from 'collections' in Python - Stack Overflow
I would recommend trying shillelagh, since gsheetsdb is no longer being maintained. You will probably need to install a few extra packages to get it to work with shillelagh Error occured in connecting streamlit with public google sheets - #9 by blackary
2 Likes
Yes. I tried with Shillelagh and it worked for reading the data but faced difficulties with writing the data to the google sheets.
In the end after struggling a lot, I went ahead gspread , and it worked.
I would suggest Streamlit should update the documentation. gspread is easy to understand and implement as well.
Currently, I need to figure out how gspread works with streamlit cloud
Has anyone done that?
I am getting the same problem with a private sheet option. How would this fix work for a private google sheet?
Does gspread work with protected sheets?
Hey @sudo_reboot
It worked for me with spread.
For private sheet you need to add the email of the GCP account in the share option.
It will work.
Can you post a sample code for reference? Oh and just to clarify, I want to read the data, not write to it.
This is the code for inserting new data to the sheet
import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('testingdb-b6d4d-d0a2646c069a.json', scope)
client = gspread.authorize(creds)
sh = client.open('TestSheet').worksheet('names')
row = [name,adr,age,symptoms,gender,email]
sh.append_row(row)
2 Likes