5

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?

Peter Nixey
  • 16,187
  • 14
  • 79
  • 133

3 Answers3

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

hgmnz
  • 13,208
  • 4
  • 37
  • 41
  • I haven't because I don't use the JVM, but heroku postgres database are just vanilla postgres databases, so using JDBC should not be a problem (but you must enable SSL) – hgmnz Jan 04 '13 at 18:56
  • Is this read only, or will updating the spreadsheet also update the database at Heroku? – iamtoc Jan 29 '16 at 09:00
0

I prefer to use Skyvia for connecting Google Sheets and Heroku Postgres without coding. Here is how I do it: https://skyvia.com/data-integration/integrate-google-sheets-heroku-postgres. All I need is to specify the connections to Google Sheets and Heroku Postgres and select data to replicate. Skyvia will copy the specified Google Sheets data to Heroku Postgres and maintain this copy up-to-date automatically with incremental updates.

Mofizul I
  • 55
  • 2
  • 10
  • You are a little too impressed by the tool you recommend ( https://stackoverflow.com/help/promotion ) and you provide too little information for making an acceptable answer. Describing **what** the advertised tool does is not enough, you need to demonstrate **how** it solves the specific problem described in the question at the top of this page. https://stackoverflow.com/questions/52376138/how-to-load-google-sheets-data-to-google-cloud-mysql/72612996#72612996 – Yunnosch Sep 13 '22 at 18:09
-1

QueryClips is exactly what you need. This is its primary use case.

dave paola
  • 1,815
  • 3
  • 16
  • 27