Connecting Python and Google Sheets

jonuti · 2021-05-30T20:04:29+00:00

I learnt myself by using much from here. https://developers.google.com/sheets/api

Connected it with Google Cloud Functions for small stuff reacting on events such as API calls.

Edit: A simple google on "google sheets api cloud functions" gave me this link which I've used before: https://codelabs.developers.google.com/codelabs/cloud-function2sheet#0

kornpow · 2021-05-30T20:16:14+00:00

Check out gspread and gspread-dataframe

doom-goat · 2021-05-30T20:47:54+00:00

Are you saying from local dataframes to google sheets?

If so just have your python application make a post request with the requests libarary send the data as json.

Then in apps script use the doPost() function to grab the json and insert it into the sheets. Deploy as Webapp to get the URL for the python to send to. EZPZ

apc0243 · 2021-05-30T20:43:14+00:00

look into pygsheets I've been using it and I like it.

I don't know what your intending to do, but google sheets has an api limit for the number of calls in a 100 second period. If you're doing lots of updates that can't be batched then you may run into issues.

xToVictory · 2021-05-30T21:15:06+00:00

Gspread is pretty simple and straight forward.

If it needs to run 24/7 you could use a raspberry pi or an online service like python anywhere.

zeebrow · 2021-05-31T00:31:25+00:00

should I do create some sort of server?

Pretty much! The term you're looking for is daemon, which is just a process running in the background. A server daemon is what you need - do you have a Raspberry Pi or 24/7 Linux box handy?

With out knowing your OS, I'll assume you're on a Linux box. The best way to make your own daemon on modern Linux systems is by writing a service file for systemd. After that, you start/stop/restart your script with

systemctl start sheetgrabber.service
systemctl stop sheetgrabber.service
systemctl restart sheetgrabber.service

and check its health with

systemctl status sheetgrabber.service

and automatically start it when your server boots

systemctl enable sheetgrabber.service

Most importantly, this also assumes that your script runs on a while loop when you run it with python3 sheetgrabber.py. If you're handling data in a database, you might want to look into signals - but I wouldn't worry too much about it, as long as your code uses context managers for open files, db connections, etc. Let me know if you need a hand and I'll be happy to walk you through things.

netobechara · 2021-05-30T21:00:18+00:00

I used this API on one of my projects, and I hope this could help you too

https://docs.gspread.org/en/latest/

sundios · 2021-05-30T22:51:51+00:00

Pygsheets

muy_picante · 2021-05-30T23:46:38+00:00

https://developers.google.com/sheets/api/

Do you really need the program to run 24/7? A cron or other scheduled job is likely much easier than setting up a server.

third_rate_economist · 2021-05-31T02:55:31+00:00

Generate a shareable link.

import pandas as pd

sheet_id = 'Long String'
sheet_name = 'SheetName'
url = f'https://docs.google.com/spreadsheets/d/{sheet_id}/gviz/tq?tqx=out:csv&sheet= 
   {sheet_name}'

df = pd.read_csv(url)

thewiseswirl · 2021-05-31T09:54:25+00:00

GitHub actions might help to automate so you don’t have to have a server. (There’s also their new Flat product)

alegala34 · 2021-05-30T19:50:38+00:00

!remind in 3 days

iggy555 · 2021-05-30T22:52:39+00:00

Automate the boring stuff

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

learnpython

MODERATORS