Hacker News Reader

Comment by teej | Hacker News Reader

Can we talk about getting data into Google Sheets? Is there a standard way to build a pipe from, say, a reporting database to dump aggregates into Google Sheets?

I built a private Add-on for my company that surfaces specific aggregates as Sheets functions (i.e. getSalesByDay(...)) and I have found so many bugs with that whole ecosystem. Deploys are completely manual and require copy-paste, you can't reliably tell what version is being invoked in a sheet, invisible cell-level caching that caches error state, concurrency limits that are too low and impossible to work around, and more. It all kinda sorta works but Google doesn't make it easy.

View on HN

Yes, so much this. I've encountered this same issue.

For our non-technical employees I write an endpoint that gives them a CSV with the most current info (often with a super simple front-end that they can use to query for specific date ranges or with filters). They download this CSV and upload it into their sheet manually to update the underlying data when they need new info.

It would be so great if you could just say "here's a URL; keep my data fresh from this source" and it would automatically do it.

Does the IMPORTDATA function work for that?

It does. I use heroku dataclips to get data out of a database and import it into google sheets using importdata. It's really simple and for my use case incredibly useful.

I never had a problem with the Sheets V4 API that came out last May, how big of a database are you talking here?

The add on doesn't talk to the reporting database directly, Google's Apps Script JDBC service doesn't support Postgres/Redshift. The add on gets data from a REST API. I then have an etl job that precalculates the aggregates and stashes them in Redis that the API then reads from.

See https://www.blockspring.com. The add-on has prebuilt connectors for both postgres and mysql. Can also build your own connectors https://open.blockspring.com/blocks/new.

Ive used gspread for Python without any issues, except for its lack of support for font/cell formatting (highlights, strikethrought...etc)

what kind of DB was the reporting database if you don't mind me asking? Seems like translating the data into Google Sheets is unnecessary if you're just trying to graph/chart it, although as I say that I'm realizing there may not be a tool that could just graph it directly that's easy enough to use/cheap enough.

The reporting DB in my case is Redshift. The business use case isn't exactly charting. My goal is to enable a business user to reference certain types of important aggregates in Google Sheets. By enabling this, business users can then compose tabular views of data that are rich, dense, and always correct and up to date. It also allows business users to very quickly prototype dashboards and make detailed specs for reports before an analyst or engineer has to get involved.

This is one tool in our tool belt for communicating with data. We also have an in house SQL-in-chart-out platform that everyone has access to and we have Looker for deeper exploratory work.

I use Zapier. It works. Sounds as if its not even close to the depth of feature set you need, but for basic sync its beautiful.

Reply on news.ycombinator.com