17

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.

Jayadevan
  • 1,306
  • 2
  • 12
  • 33
  • If your question is actually "How can I link a Google spreadsheet to PostgreSQL?", then ask that. "Has anyone tried...?" will get closed. – Jeffrey Bosboom Feb 24 '15 at 15:41
  • I'm trying to do the same thing. If I find out, I'll let you. In the meantime, did you figure it out? – netskink May 01 '15 at 22:33
  • No. Instead of linking it, I am managing with a perl script which will generate a tab-separated file and then importing that file. Not the optimal solution, but works... – Jayadevan May 04 '15 at 05:06
  • If you don't want to roll your own solution, [SeekWell](https://seekwell.io/) lets you automatically send data from Postgres to Sheets and can also sync changes from Sheets back to a database. Apps Script can do pieces of this, but I found it buggy and limited for our use case, so I built something else. There are both free and paid plans. Disclaimer: I built this. – howMuchCheeseIsTooMuchCheese Jan 06 '20 at 22:22

4 Answers4

5

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

Diego
  • 2,196
  • 1
  • 21
  • 26
4

We have been pulling Google Sheets into QGIS via PostgreSQL Foreign Data Wrappers. We then build a materialized view that connects records to geometry (via School Numbers, for example) and use the materialized view as a standard spatial table.

From there we have also published this 'google sheet materialized view' to a web application via node.js and add a button to refresh the map if the google sheet data has been changed. It works really well.

Method 1: Multicorn FDW and GSheets Extension:

(Note: The gsspreadsheet extension to the multicorn FDW is questionably maintained, and there is the risk that v4 of the GSheets API may break it... we don't know yet, and are planning for the possibility that we might have to implement Method 2 (below) if it does indeed break. )

To connect to a Google Sheet via a PostgreSQL FDW, use the Multicorn FDW:

https://github.com/Kozea/Multicorn

Then install the gspreadsheet_fdw extension for Multicorn:

https://github.com/lincolnturner/gspreadsheet_fdw

In PostgreSQL, create the multicorn FDW:

CREATE SERVER multicorn_gspreadsheet
    FOREIGN DATA WRAPPER multicorn
    OPTIONS (wrapper 'gspreadsheet_fdw.GspreadsheetFdw');

Then create the FDW table connecting to the Google Sheet using the gspreadsheet extension of multicorn:

CREATE FOREIGN TABLE speced_fdw.centerprogram_current_gsheet (
    column1 integer NULL,
    column2 varchar NULL,
    column3 varchar NULL 
)
SERVER multicorn_gspreadsheet
OPTIONS (keyfile '/usr/pgsql-9.5/share/credential.json', gskey 'example_d33lk2kdislids');

You now have a foreign table pointing directly to your Google Sheet, which you can built a materialized view from.

Method 2: Using FILE_FDW and CSV GSheet Export:

The other option is to use the FILE_FDW right out of PostgreSQL and connect to the CSV export of the GSheet using WGET.

First, create the server:

CREATE SERVER fdw_files
    FOREIGN DATA WRAPPER file_fdw
    OPTIONS ()

Then create the FDW table:

CREATE FOREIGN TABLE public.test_file_fdw (
    "name" varchar NOT NULL,
    "date" varchar NULL,
    "address" varchar NULL
)
SERVER fdw_files
OPTIONS (program 'wget -q -O - "https://docs.google.com/spreadsheets/d/2343randomurlcharacters_r0/export?gid=969976&format=csv"', format 'csv', header 'true');

The above options for wget are listed here: https://www.gnu.org/software/wget/manual/html_node/HTTP-Options.html

DPSSpatial
  • 767
  • 3
  • 11
  • 31
  • Really nice solution but since `gspreadsheet_fdw` looks abandoned and google is [deprecating the sheet api v3](https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api) in exactly 3 months, do you have a plan B? – bbigras Jun 20 '20 at 05:55
  • 1
    @brunoqc so we haven't upgraded after all, but are taking a 'wait and see' approach to whether a) v4 will break the gspreadsheet_fdw and/or b) anyone makes any upgrades to the extension so it will work. Our Plan B is to use the FILE_FDW to connect to the CSV export of a google sheet... I updated my answer above. – DPSSpatial Jun 22 '20 at 18:04
2

You can do this using Google app script and database connector like psycopg2 if you're using python or node-postgres if you're using node. Run your db connector on some server, front it with a REST API and fetch the data using app script from google sheet.

One thing you'd need to figure out is permissions (Google calls them scopes).

You can build an addon and publish it internally. Here is a good tutorial on how to do this.

If you don't want to build your own addon, you can use database connectors like Castodia, Kpibees and Seekwell from Google marketplace. Most of them support Postgres.

Ayazhan
  • 73
  • 5
1

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.

netskink
  • 4,033
  • 2
  • 34
  • 46