自动从外部数据库加载Google电子表格数据加载 [英] Automate Google Spreadsheet data load from external database

问题描述

我有一个共享的Google电子表格。工作表的某些列必须填充DB服务器（PostgreSQL）中存在的数据（SQL查询）。

I have a shared Google spreadsheet. Some columns of a worksheet must be filled with data (SQL query) that exist in a DB server (PostgreSQL).

此外，数据加载必须安排为自动完成（例如每天的1.00点）。

Also the data load must be scheduled to be done automatically (e.g. at 1.00 o'clock every day) .

最后，我需要在电子表格中进行一些调整（例如，在将日期时间放入工作表名称中或发送确认电子邮件等）

Finally I need some tweaks in the spreadsheet (e.g after the load to put the datetime in the name of the worksheet or send a confirmation email etc)

实现此目的的最佳和最简单的方法是什么？ （我开始看Google应用程序脚本和Google Sheets API，但不知道什么更合适）

What is the best and easiest approach to achieve this? (I start looking Google app scripts and Google Sheets API but not sure what is more appropriate)

谢谢

Thanks

推荐答案

Apps脚本可以使用 Apps脚本的JDBC服务。不过，正如此处（阅读最后的评论/文章），让应用脚本从PostgreSQL数据库读取，你需要自己实现一个API层。

Apps Script is able to connect to external database using the JDBC Service of Apps Script. However, as mentioned here(read last comment/post), to have App Script read from a PostgreSQL DB you'll need to implement an API layer yourself.

关于计划数据加载：可以使用时间驱动触发器和

Regarding Scheduled Data Loads: This can be achieved by using Time-Driven Triggers and Class ClockTriggerBuilder might also be of some help.

关于您正在寻找的调整： Class ClockTriggerBuilder 也可能有帮助。 https://developers.google.com/apps-script/articles/sending_emails =nofollow noreferrer>这里是一个教程，将向您展示如何使用Apps脚本发送电子邮件。根据您的使用案例以及您正在实施的内容，上述可安装触发器（使用与时间驱动触发器相同的链接）也可用于发送电子邮件确认以完成数据加载过程。和这个应该作为使用日期时间重命名工作表的一个很好的参考。

Regarding "Tweaks" that you are looking to implement: Here is a tutorial that will show you how to send emails using Apps Script. Depending on your use case and what you are looking into implementing, the above mentioned Installable Triggers(use same link as Time-Driven Triggers) can also be used to send email confirmation for completion of your Data Load Process. And this should serve as a good reference for renaming your Sheet using date-time.

因此，有可能实现您正在使用的计划Apps脚本，但在开始之前，我建议您彻底浏览应用程序脚本文档。

Hence, it is possible to achieve what you are planning on doing using Apps Script but before you start, I would suggest you to thoroughly go through the App Script Documentation.

这篇关于自动从外部数据库加载Google电子表格数据加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！