SyncWith

Get started

PostgreSQL to Google Sheets

Your users may prefer to analyze data using pivot tables, filter views, charts and the other useful features of Google Sheets. Postgres to Google Sheets connections makes a lot of sense if that’s where users are most comfortable analyzing data.
This way you can avoid writing and modifying complex sql statements, users can use the power of Google Sheets to modify and analyze data and easily share and collaborate with team mates.

What we’ll cover in this article

  • How to connect to a postgres database from Google Sheets
  • Common errors when connecting to postgres to Google Sheets
  • Basic SQL queries for beginners
  • Syncing your google sheets postgres integration

Don’t have SyncWith?

  • SyncWith is a Google Sheets Add On
  • SyncWith allows you to move data from any API into Google Sheets including databases like Postgres
  • ⭐️⭐️⭐️⭐️⭐️ Reviewed by hundreds of Google businesses
  • Check out our Google Workspace Listing and see why we’re the highest rated API add on with over 100,000 installs. Install today and get your data into Sheets in minutes.

How Does SyncWith Work?

notion image
 

Setting up your PostreSQL Google Sheets Integration

To connect Postgres to Google Sheets we’ll be using the SyncWith plugin. SyncWith makes it super easy to connect 1000s of APIs and databases to Google Sheets. It’s trusted by over 100,000 businesses and it’s easy to use.
Once you have the SyncWith addon installed you can search for Postgres and you’ll get an option to use our PostgreSQL Connector
In the gif to the left you can see a search for database - which brings up many options:
  • BigQuery to Google Sheets
  • MySQL to Google Sheets
  • Notion database to Google Sheets
  • Etc.
If we just search for Postgres then we quickly get the connector we want, click and we’re ready to create our PostgreSQL connection
Connecting to a PostgreSQL database is super easy but if you’ve never connected to a database before and your dev team is opening up the resource to you to use in a Google Sheets it’s worth an overview of what the connection requires.
 
notion image

Configuring you Google Sheets Postgres Connector

  • Give the PostgreSQL connection a name - for instance if you’re connecting to your users table to do some analysis of your users in Sheets you might call your connection Customer Records - because SyncWith supports multiple connections this helps you distinguish between each connector. For instance you might have a users table as well as leads table in your PostgreSQL db and you could pull both of them into a Google Sheet for analysis. You could even pull from multiple different databases into a single sheet.
  • Input your username and password - You’ll probably need to talk to your IT / Devops / Database Administrator to get a username and password with read permissions to the database and table you want to access
  • Host is the location of the database - if your host is on your local machine SyncWith cannot connect to it currently it needs to be accessible to the internet so you would need to punch through a port or your router and map it to your machine (which in a corporate setting isn’t likely feasible)
  • Configure your Port which for Postgres is typically 5432
  • Finally you’ll need to provide the database you’re connecting to, it’s possible you’ll want to connect to multiple databases in your Google Sheet so this allows each connection your create to work with a specific database.
  • Also typically you’ll set SSL Enabled to True
 
PRO TIP - You can use the connection string to enter in all the above information in one shot. Often you can simply ask your database administrator or dev team for a connection string and simply enter it here. It leaves less room for error when connecting your postgreSQL database to Google Sheets.
Connection strings will look like:
postgres://username:password@host.site.com:25060/defaultdb?ssl-mode=REQUIRED

Testing your PostgreSQL and Google Sheet Connection

Once you’ve entered your connection parameters you can test the connection by hitting the Test connection button and SyncWith will make sure we can connect to your database and that the credentials provided are correct.
PRO TIP - Your database likely can only be connected to by a list of trusted IPs, if SyncWith’s servers are not on that list we won’t be able to talk to your database. The static IP addresses used by SyncWith are listed in the addon - make sure you add them or you’ll get a ETIMEDOUT error message.
notion image
 
 
notion image

Common Errors when testing your Postgres and Sheets Connection

PostgreSQL returned an error: password authentication failed for user (28P01)

  1. Incorrect Username - Postgres will list the username that the password failed for. It’s possible that the password is the correct one, just the user name is incorrect. It’s important to double check the username to verify it’s correct
  1. Incorrect Password - If the username is correct then postgreSQL is throwing this error becuase the password is incorrect for the provided username

ECONNREFUSED

Postgres will throw the following error - Connection refused, please make sure Postgres is running on the specified host/port, and that it is publicly accessible (ECONNREFUSED)
  1. Incorrect Host - If you’ve entered the hostname in incorrectly or PostgreSQL is not running on the host you’ll get this message. Check with IT on the correct hostmane
  1. Local Host - remember SyncWith cannot connect to your local host unless it’s accessible to the internet

ETIMEDOUT

PostgreSQL will throw the following error - Timeout trying to connect, please ensure network security is not blocking the connection (ETIMEDOUT)
  1. Incorrect Port By default most postgres instillations will use port 5432 but that could be different for your server or you may have accidentally deleted or miss-entered the port

PostgreSQL returned an error: SSL/TLS required (28000)

If your Postgres instance requires SSL and you haven’t set SSL Enabled to True doing so should fix the error.

ER_ACCESS_DENIED_ERROR

You may get the message Timeout trying to connect, please ensure network security is not blocking the connection (ETIMEDOUT) - this indicates that while trying to connect we timeout - meaning we kept trying and could not connect to the host. This could be happening for a few reasons:
  1. Security Permissions The most common reason is that the security settings on your host need to be updated to allow SyncWith to connect.
    1. Typically databases will specify trusted sources - a list of ip addresses or ranges that are allowed to access the database. In the addon you’ll see the SyncWith Static IP Addresses you’ll need to register
        2. notion image
    2. As an example if your database is hosted in Digital Ocean you would add these two IPs to your trusted sources:
        3. notion image
 

Basic SQL Queries for Beginners

Now that you’ve successfully connected to your database it’s time to write a simple SQL query to get PostgreSQL data into Google Sheets. If you’re going to be using Sheets to filter your database data then we just really need to get all the data from a table. If we have a table called Users we would do that as follows:
Select * from "Users"
If your Users table is relatively small relative to the limits of Sheets - eg maybe Users is 5000 rows big with 20 Columns - 100k cells in total - this is completely fine for Google Sheets - dump the data in as is and use Pivot Tables, Filter Views and Slicers to play with the data.

Basic Filtering

But some data sets are quite large and it isn’t reasonable to get all the records from all time into a Google Sheet from Postgres. In this instance you likely want to filter.

Filtering by Timestamp

Many tables have record created at timestamps, typically these are columns like createdAt, created_at, CreatedAt. For big datasets where you only want to say look at Users created in the last year we can change the query. As an example lets find users created after Sept 1, 2022.
select * from Users where "createdAt" >= '2022-09-01  00:00:00'

Filtering by # of Records with Ordering

Maybe you just want to get the last 1000 or 10,000 records and you’re more concerned with getting as much in as possible for Sheets to analyze, you know you can strip out old records. To limit the number of records coming back we can use the limit operator in SQL.
select * from Users limit 5000
Now we’ll only get 5000 users, BUT WAIT - which 5000 users will we get, we want to set an ordering, because maybe we want the 5,000 most recent users, for that we’ll use the order by operator.
select * from Users order by "createdAt" desc limit 5000
This get the users in descending order, hence the order by "createdAt" desc we’re sorting the list of users in descending order based on their createdAt timestamp, aka newest users first. If you want the oldest users first you’d change it to ascending order, order by "createdAt" asc.
By filtering, ordering and limiting your query you can dump in the data that is important to you from a PostgreSQL database table into Google Sheets and then use Sheets to do more advanced filtering and analysis - perfect for folks not skilled with SQL

Common SQL Errors

relation does not exist

A common reason you’ll get this error is you’ve specified the table name incorrectly. For example
  • If the table is Users and you specify user, you’ll get this error as capitalized table names must be wrapped in double quotes eg select * from "Users"
  • You may simply have specified a table name that does not exist eg select * from fonfig when you should have typed select * from config
  • On table names with spacing you also need to use double quotes eg select * from "user config" and not select * from user config

Syncing your Google Sheets PostgreSQL Integration

Now that we have the data we want coming in we need to set a sync schedule - eg maybe you want today’s orders or new users sync’d every hour. SyncWith allows you to set your sync schedule.
notion image
 

Other Database Connector Guides

More Google Sheets API Guides