Connecting to Google Sheets with Python

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.

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.

"4 lines of code connecting to an http(s) endpoint"

...

"with python"

!!!!!!!!!

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.

If you have something to say, could you please say it?

https://news.ycombinator.com/newsguidelines.html