Connect to postgresql database using google apps script from google spreadsheet

user_10386064 0
Viewed 5978

I've seen this question asked a few times before, most notably this StackOverflow question: Automate Google Spreadsheet data load from external database

and here on Google issue tracker: https://issuetracker.google.com/issues/36752790

but I'm looking for a more up-to-date answer.

I have a cloud-SQL instance of a PostgreSQL database running on GCP. I would like to automatically connect to it and populate a spreadsheet on a daily basis, which is possible for MySQL databases.

The docs on Google say to use a jdbc socket factory to connect to a PostgreSQL db using the format: 

"jdbc:postgresql://google/<DBNAME>?"
"socketFactory=com.google.cloud.sql.postgres.SocketFactory"
"&socketFactoryArg=<InstanceName>"

I've also tried the format (slightly different) from the Google GitHub page (linked under the example in the docs):

"jdbc:postgresql://google/<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<POSTGRESQL_USER_NAME>&password=<POSTGRESQL_USER_PASSWORD>"

After creating a url I run:

var conn = Jdbc.getConnection(dbUrl)

But get this error:

Connection URL uses an unsupported JDBC protocol.

I'm wondering if this is possible yet or if it is user error?

google-apps-script google-sheets postgresql
1 Answers

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

Disclaimer: I built this.

user_35831390
Related