Automate Google Spreadsheet data load from external database

Question

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).

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)

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

Answer 1

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.

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.

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.

Answer 2

I found JDBC in Google Sheets to be quite buggy, so I built SeekWell which lets you automatically send data from Postgres to Sheets and can also sync changes from Sheets back to a database. You can schedule refreshes daily, hourly or every five minutes.

Disclaimer: I built this.