How can I connect to Heroku Postgres from a Google Spreadsheet

Question

I'd like to use a Google spreadsheet to display my database analytics

I'd like to be able to do summary queries on my Heroku Postgres database using Google Apps Script and then display and chart them in a Google spreadsheet.

Heroku offers a number of ways to connect to Heroku Postgres: https://devcenter.heroku.com/articles/heroku-postgresql

Likewise Google Apps script offers access to a number of different external services https://developers.google.com/apps-script/defaultservices

I've never attempted this before and so am interested in what is simplest.

JDBC seems possible but are there any other options?

As far as I can see, the only overlap between the two is JDBC which I have no experience with but feels like a bit of a heavyweight third protocol to use to get between the systems.

IS JDBC the best way to get the data across or is there something simpler I'm missing?

Answer 1

1. Set up a dataclip from dataclips.heroku.com with your desired data described as a SQL query.
2. Append .csv to the resulting URL

3. Use that URL on the google spreadsheet's importData function, like so:

   =importData("https://dataclips.heroku.com/[your-dataclip].csv")