Connecting to Google Sheets with Python | Hacker News Reader

Connecting to Google Sheets with Python

tinaja.computer

162 pointsbenbreen23 comments

Open articleView on HN

Google Spreadsheets and Python | https://news.ycombinator.com/item?id=13668916 (2017Feb:140comments)

・SQL into Sheets - http://pastebin.com/ACwqc5um

・LibreOffice Calc server - https://github.com/robsco-git/spreadsheet_server

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

・Grunt / Jekyll - https://github.com/stpe/grunt-gss-to-json

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

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

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.

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.

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?

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

In addition to the popular gspread project, there is a port that uses api v4 here: 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) is interesting, I would like to see some client code (preferably Python!)

Also of interest:

https://sheetsu.com

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.

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.

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

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.)

Why hello there, neighbour!

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.

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...

> I've often thought spreadsheets should be more commonly used as the UI for software.

I've had this same thought! I've been working on [VisiData] (Python+spreadsheet+terminal) for the past year, and I think the interface is more than sufficient for all kinds of applications. Besides the main VisiData app, I've made a git interface (vgit), a game (vdgalcon, a remake of Galactic Conquest), a chat client, and I have designs for a unix shell and a python debugger, among other things.

If you want to try to fit something you're working on into a terminal spreadsheet interface, we could put something together really quickly. With vdtui (the core interface layer), you just have to collect your row objects, specify the columns, write the commands, et voila! We should be able to get a workable prototype going in an hour or two at most.

Funny, I do a lot of enterprise stuff, and our biggest pain is getting organizations/individuals/clients to not use spreadsheets. Like, we'll have clients wanting to export/import data via sheets, but it'll be complex multidimensional data that cannot be represented in a 2 dimensional data format like a spreadsheet. As soon as you start needing to do lookups, stuff is gonna get hairy. Or organizations that don't understand that data needs to be aligned properly, etc... Sure, you're a human, you can look at this, and understand it, but when you change where you put your labels constantly, it's a nightmare.

Oh, just let some customer send their data over, and we can copy and paste it in.... HAHA right... good luck with that if you don't teach every single user who touches that data how to sanitize it. That's too much for a bunch of humans to deal with, generally, for anything remotely complex. It's too easy to make too many mistakes, whether it be typos, copy/pasting the wrong thing, referring to something by a slang or localized terms, that fails a lookup, so they create a new record, etc... Sure, I'm sure some nutter has implemented a fuzzy search algorithm in Excel, but I doubt it's truly production quality... Most people would just use Access or a real database at that point.

So my solution is to build software to solve these problems. Spreadsheets are great, especially collaborative ones, for certain tasks. I've seen plenty of large companies running their whole business off a gigantic shared spreadsheet (on google drive) complete with colored spreadsheet dashboards up on TVs, and everything. (and yes, we built new pipeline software for them, using those formula, it was invaluable)

Spreadsheets just don't scale though (depending how you use them), and for anything remotely complex, humans will make mistakes. At some point you might as well be doing some kind of light-weight bespoke software development, as you'll gain more flexibility and ease of use in the long run, and you'll probably save time/money/hassle with proper data warehousing, etc...

I'm not saying I never use sheets, it's ideal, but you have to build in lots of validation logic, etc... I use gspread myself, personally, when doing very specific data tasks for clients, but I hate giving clients direct access to a sheet that interfaces with code, or I do give them access to a sheet, but not access to run the code against it. Too much room for pain and confusion. They will break it, and most customers are not the trial-and-error type, and not all tasks are even safe to attempt with brute force.

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/

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

https://serverjs.io/tutorials/spreadsheet/

I've used gspread a few times to automate generating reports. I wrote a library (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.

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

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.

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.

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

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?

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

Reply on news.ycombinator.com