如何将 Google 电子表格链接到 PostgreSQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28699903/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I link a Google spreadsheet to PostgreSQL?
提问by Jayadevan
回答by howMuchCheeseIsTooMuchCheese
If you don't want to roll your own solution, SeekWelllets 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.
如果您不想推出自己的解决方案，SeekWell可让您自动将数据从 Postgres 发送到 Sheets，还可以将更改从 Sheets 同步回数据库。Apps Script 可以做到这一点，但我发现它有问题并且对我们的用例有限制，所以我构建了其他东西。有免费和付费计划。
Disclaimer: I built this.
免责声明：我构建了这个。
回答by Diego
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.
我的方法是使用 R 及其 googlesheets 和 DBI 库。Googlesheets 将电子表格与 R 连接起来，DBI 将 R 与 PostgreSQL 连接起来。您可以通过简单地运行脚本随时更新表。通过这种方法，您还可以在将数据存储到 PostgreSQL 之前向数据添加转换。另一种方法是使用 Python、pandas 和 gspread 库。
More info: Googlesheets: https://cran.r-project.org/web/packages/googlesheets/vignettes/basic-usage.html
更多信息：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
DBI：https: //cran.r-project.org/web/packages/DBI/vignettes/DBI-1.html
回答by netskink
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.
哇，我忘了这个问题。我通过为两个数据库创建一个额外的 MySQL 数据库来实现这一点。Mysql 数据库有一个表，用于与 google sheet api 对话。反过来，这个 mysql 表是 Postgres 数据库中的一个外部表。
回答by DPSSpatial
I posted this answer on GIS Stackexchange:
我在 GIS Stackexchange 上发布了这个答案：
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) and use the materialized view in QGIS.
我们一直在通过 PostgreSQL 外部数据包装器将 Google Sheets 拉入 QGIS。然后我们构建一个实体化视图，将记录连接到几何（通过学号）并在 QGIS 中使用实体化视图。
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.
从那里我们还通过 node.js 将此“谷歌表实体化视图”发布到网络应用程序，并添加一个按钮以在谷歌表数据已更改时刷新地图。它真的很好用。
To connect to a Google Sheet via a PostgreSQL FDW, we use the Multicorn
要通过 PostgreSQL FDW 连接到 Google Sheet，我们使用 Multicorn
https://github.com/Kozea/Multicorn
https://github.com/Kozea/Multicorn
Then install the gspreadsheet_fdw extension for Multicorn:
然后为 Multicorn 安装 gspreadsheet_fdw 扩展：
https://github.com/lincolnturner/gspreadsheet_fdw
https://github.com/lincolnturner/gspreadsheet_fdw
The instructions in the gspreadsheet_fdw show how to create the FDW table from your Google Sheet. From there, you can build views/materialized views to connect spatial data, and from there consume it anywhere you want - either QGIS, or connect through Node.Js, or even publish to Geoserver.
gspreadsheet_fdw 中的说明显示了如何从您的 Google 表格创建 FDW 表。从那里，您可以构建视图/物化视图以连接空间数据，并从那里在您想要的任何地方使用它 - QGIS，或通过 Node.Js 连接，甚至发布到 Geoserver。
