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.
- 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
- 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.
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
Postgresand you’ll get an option to use our
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
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.
- 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 internetso 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:
Once you’ve entered your connection parameters you can test the connection by hitting the
Test connectionbutton 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
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
Incorrect Password- If the username is correct then postgreSQL is throwing this error becuase the password is incorrect for the provided username
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)
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
Local Host- remember SyncWith cannot connect to your local host unless it’s accessible to the internet
PostgreSQL will throw the following error -
Timeout trying to connect, please ensure network security is not blocking the connection (ETIMEDOUT)
Incorrect PortBy 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
If your Postgres instance requires SSL and you haven’t set SSL Enabled to True doing so should fix the 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:
Security PermissionsThe most common reason is that the security settings on your host need to be updated to allow SyncWith to connect.
- 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
- As an example if your database is hosted in Digital Ocean you would add these two IPs to your trusted sources:
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
Userswe 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.
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.
Many tables have record created at timestamps, typically these are columns like
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'
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
limitoperator 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
select * from Users order by "createdAt" desc limit 5000
This get the users in descending order, hence the
order by "createdAt"
descwe’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"
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
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 fonfigwhen 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
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.
- Connect facebook analytics to google sheets
- Transferring facebook data to google sheets to create dashboards
- exception: service invoked too many times for one day: urlfetch
- More pagination api examples
- Connect google analytics to google sheets