
Connecting to Google Sheets with Python - benbreen
http://tinaja.computer/2017/10/27/gspread.html
======
j_s
Google Spreadsheets and Python |
[https://news.ycombinator.com/item?id=13668916](https://news.ycombinator.com/item?id=13668916)
(2017Feb:140comments)

・SQL into Sheets -
[http://pastebin.com/ACwqc5um](http://pastebin.com/ACwqc5um)

・LibreOffice Calc server - [https://github.com/robsco-
git/spreadsheet_server](https://github.com/robsco-git/spreadsheet_server)

・Sheets API (JS) -
[http://chriszarate.github.io/sheetrock/](http://chriszarate.github.io/sheetrock/)
| [https://github.com/theoephraim/node-google-
spreadsheet](https://github.com/theoephraim/node-google-spreadsheet)

・Grunt / Jekyll - [https://github.com/stpe/grunt-gss-to-
json](https://github.com/stpe/grunt-gss-to-json)

・simple read-only (JS) - [https://github.com/franciscop/drive-
db](https://github.com/franciscop/drive-db) |
[https://github.com/digplan/gsheet-web](https://github.com/digplan/gsheet-web)

・auth proxy (Golang) - [https://github.com/VLO-GDA/server-
app](https://github.com/VLO-GDA/server-app)

~~~
potatote
Thank you for sharing the links. Is there a pure JavaScript library that helps
with reading and writing data from/to Google private spreadsheet? From the
links above, I think node-google-speadsheet repo comes closest to what I'd
like to achieve, but it uses node, which I'm not very familiar with.

------
foxylad
We used to use the excellent Xero for our accounts, but it couldn't handle
international business (exchange rates, different tax regimes etc)well. So we
rolled our own system of python scripts and Google sheets.

The python scripts use gspread to import and process bank data from eight
accounts around the world. Summary sheets pull that data together into real-
time profit and loss, position statements, and tax summaries.

It's easily shared with our accountant, who can add a sheet to do some custom
analysis at the drop of a hat. Our annual report takes about an hour to
prepare, and sheets mimicking tax forms make filing tax returns simple.

~~~
Kunlun
Sounds awesome! We are in the process of revamping our sheets internally and
would love to do something similar. Anything you could share on the subject?

~~~
foxylad
Not sure what you need - contact me by doing a whois for vig.co.nz.

------
subhobroto
In addition to the popular gspread project, there is a port that uses api v4
here:
[https://github.com/nithinmurali/pygsheets](https://github.com/nithinmurali/pygsheets)

Since v4 was substantially different from v3, it's (almost) a complete rewrite
and unfortunately still not as stable as gspread.

Anyone here writing some interesting code using the GoogleDocs APIs?

While this ([https://digitalinspiration.com/google-
addons](https://digitalinspiration.com/google-addons)) is interesting, I would
like to see some client code (preferably Python!)

Also of interest:

[https://sheetsu.com](https://sheetsu.com)

------
bkovacev
Am I the only one that finds the Google Sheets API not fully baked or has had
issues with it?

Search functionality is not there, retrieve row, find and replace is not
adequate, row index not sent when retrieve values etc. Updating particular row
gets hard etc. We ended up adding row counter as a column as well.

We used Sheets in between two major iterations of our internal tools and we
found it inadequate for any advanced usage.

Also sheets api would tend to 503/504 (not sure which one) if there was more
than 10k rows with 4-5 columns.

------
beering
There's butterdb (formerly known as fuckitdb) which is based on gspread. It
made its rounds on HN and other sites a while ago as an abstraction on top of
gspread.

------
adam-p
I used Google Sheets as the database for a neighbourhood association, written
in Python, running on Google App Engine. [https://github.com/adam-p/danforth-
east](https://github.com/adam-p/danforth-east)

It has worked pretty well for a few years now, and it allows ordinary humans
to filter and review in a familiar format. (With the risk that they start
messing with stuff enough to break its DB-ness.)

~~~
phyrex
Why hello there, neighbour!

------
SwellJoe
A couple weeks ago I helped my nephew with a project he was working on using
gspread. I haven't programmed in Python in years but was able to make it all
go in an hour or so of poking at the gspread docs. It's pretty neat. I've
often thought spreadsheets should be more commonly used as the UI for
software. Lots of business folks have a good grasp of spreadsheets, but not of
programmer-y or database concepts. I dunno how I'd integrate spreadsheets into
anything I'm working on, but if I were doing enterprisey stuff, I'd be trying
to make it fit.

~~~
stephen-mw
That’s funny you say that. One of my pet projects was using google sheets to
control SSH access with non-techies. It worked surprisingly well.

[https://github.com/stephen-
mw/gdoc_ssh_manager/blob/test/REA...](https://github.com/stephen-
mw/gdoc_ssh_manager/blob/test/README.md)

------
franciscop
I made the same thing for Node.js but without credentials. The main gotcha is
that your spreadsheet is public (but unlisted). It also has some basic
commands to use it as a Mongo database, or just use the array of data:

[https://github.com/franciscop/drive-db/](https://github.com/franciscop/drive-
db/)

I also made a small tutorial of how to use it:

[https://serverjs.io/tutorials/spreadsheet/](https://serverjs.io/tutorials/spreadsheet/)

------
elliottcarlson
I've used gspread a few times to automate generating reports. I wrote a
library ([https://github.com/elliottcarlson/pptx-
canvas](https://github.com/elliottcarlson/pptx-canvas)) that allows you to
draw in a powerpoint file using canvas style syntax, and combining that with a
spreadsheet another team was maintaining/updating, we could quickly generate
new reports for them at a time. gspread was definitely easy to use in this
scenario.

------
mooreds
This seems useful. Google spreadsheets are pretty universal, easy for non-
technical folks to update and have zero infrastructure and cost.

However, don't try to create a full fledged (or even read only) rest API
against a Google spreadsheet. I did, and I wasn't pretty:

[http://www.mooreds.com/wordpress/archives/1359](http://www.mooreds.com/wordpress/archives/1359)

------
IanCal
I've been using this for working with people on managing/reviewing data. I can
get them to enter some data on google drive, then lookup relevant info from
various services and fill in

It's a really nice setup for bringing data from APIs and other services into a
more usable, query-able setup for people more used to spreadsheets.

------
anjakefala
I found this to be a very clear and well-structured post. I was not surprised
to read that the author is a historian. I especially liked the detail in their
process where they automatically update the communal sheet to indicate that
the data has already been processed.

------
linux2647
Combine this with Multicorn and Postgres and you can query Google Spreadsheets
from your database

~~~
smoll
Excuse my ignorance on FDWs but how does this work with respect to DB
constraints? For example, if a postgres column is supposed to be a date, but
it's not a properly formatted date, does it throw an error at query time? Or
is everything treated as a varchar/text?

------
orliesaurus
Did you try stuff like airtable or maybe even use spreadsheets with zapier?
They "seem" pretty powerful alternatives

