Inserting data via streamlit form to Google sheets

🎈 Using Streamlit
, ,
1

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.

2

Please check:

as for @st.cache used in the article: @st.cache_data VS @st.cache_resource - small issues - #12 by Kareem_Rasheed_babat

3

Is gsheetsdb still there?
on the pypi site, it says its deprecated?

4

It is still there, but the repo says, it is deprecated and the shillelagh library should be used instead:

1 Like
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"])
6

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?

image
image1342×728 70.3 KB

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? :slight_smile:

8

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
9

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?

1 Like
10

I am getting the same problem with a private sheet option. How would this fix work for a private google sheet?

11

Does gspread work with protected sheets?

12

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.

13

Can you post a sample code for reference? Oh and just to clarify, I want to read the data, not write to it.

14

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)
3 Likes
15

Hi and thanks! Is it also possible to add the ServiceAccountCredentials into a secrets.toml file?
And how to add this in the app secrets area?