How can I link a Google spreadsheet to PostgreSQL?

Question

How can I link a Google spreadsheet to PostgreSQL? I googled and got some sample code for MySQL, and a couple of products which do it. As per this link ,support exists for a few databases.

Answer 1

My approach is using R and its googlesheets and DBI libraries. Googlesheets connects the spreadsheet with R and DBI connects the R with PostgreSQL. You can update the table whenever you want by simply running the script. With this approach you can also add transformations to the data before storing them in PostgreSQL. Another way is using Python and pandas and gspread libraries.

More info: Googlesheets: https://cran.r-project.org/web/packages/googlesheets/vignettes/basic-usage.html

DBI: https://cran.r-project.org/web/packages/DBI/vignettes/DBI-1.html

Answer 2

Wow, I forgot about this question. I got this working by making an additional MySQL database for two databases. The Mysql database had a single table which was used to talk to the google sheets api. In turn this mysql table was a foreign table in the Postgres database.

Answer 3

You can also use our self service reporting tool - Kloudio to connect your Google Sheets to PostgreSQL database and integrate it for both downloads and uploads. Moreover, you can schedule a refresh as needed so your Google Sheet will remain updated. No need to run queries manually each time.

Answer 4

Seekwell works great for this. The Google Suite add-on lets you run queries in Sheets and schedule reports.

Answer 5

You should look at QueryClips. You're describing its primary use case.