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

问题描述

我有一个共享的 Google 电子表格.工作表的某些列必须填充数据库服务器 (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 点钟).

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)

谢谢

推荐答案

Apps Script 能够使用 JDBC 服务 of Apps Script.但是，正如在此处(阅读最后的评论/帖子)，要从 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.

关于预定数据加载:这可以通过使用来实现时间驱动触发器和Class ClockTriggerBuilder 也可能有一些帮助.

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

关于您希望实施的调整":这里一个教程，将向您展示如何使用 Apps 脚本发送电子邮件.根据您的用例和您正在考虑实施的内容，上述可安装触发器(使用与时间驱动触发器相同的链接)也可用于发送电子邮件确认以完成数据加载过程.和 this 应该作为使用日期时间重命名工作表的良好参考.

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 Script 实现您的计划，但在开始之前，我建议您彻底阅读 应用脚本文档.

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屋！