Sync data from Google Sheets to PostgreSQL database in real-time

Anton Svetlov -

Most of the internal tools start with Google Sheets. Google sheets help you quickly create your internal database and share & collaborate with your teammates. But when your company and the team are scalable, supporting all your data in Google sheets becomes complicated therefore, a lot of companies migrate from Google Sheets to PostgreSQL database.

This article will compare Google Sheets and PostgreSQL and how you can quickly sync or migrate from Google Sheets to PostgreSQL without code in 10 mins.

Google Sheets vs. Postgresql database

Let's consider the pros and cons of Google sheets vs. PostgreSQL:

1. Open-Source

– Google Sheets. App development platform from Google. Managed by Google Services.

– PostgreSQL. Open-Source SQL Database. Managed hosting available on Google, AWS, Azure, etc.

1. Data Types & Data Structure

– Google Sheets. There is no specific data structure and data types (text, number, image). Google sheets users can add text and numbers in the same column.

– PostgreSQL. You can specify your Table Data Structure Data Types for each Column.

2. Relations between Tables

– Google Sheets. No relations between sheets.

– PostgreSQL. PostgreSQL is a relational database that allows you to specify relations between Tables.

3. Missing data and nulls values

– Google Sheets. Cells could be null or empty.

– PostgreSQL. Allow specifying which Column can't be null or empty.

4. Unique and Duplicate records

– Google Sheets. Cells could contain duplicate data.

– PostgreSQL. Postgresql database allows you to specify unique fields or primary key to avoid duplicate records.

5. Custom queries & reports

– Google Sheets. Pivot Table allows you to calculate metrics in Google Sheets.

– PostgreSQL. SQL queries allow you to create any reports.

6. Scalability

– Google Sheets. Not scalable. The more data you store, the more latency you have with different operations: loading data, searching, and filtering.

– PostgreSQL. Scales vertically. Postgresql database designed with scalability in-core.

7. Build Apps

– Google Sheets. Google sheets have limited API capability and Google Sheets API rate limits. It's not suitable to build apps.

– PostgreSQL. No limitations to building any APIs. Using Jet API, you can generate API on top of the Postgresql database.

How to Sync Google Sheets to PostgreSQL database

This chapter will use Jet Tables to store Google Sheets data. Jet Tables is a free PostgreSQL database hosted in the cloud.

Firstly, create a new project and connect Google Sheets database. Allow Jet to Sync Google Sheets data to Jet Tables by choosing operation mode.

Next, you can set up a visualization for the Collection: change the field type and name, update the data, etc. Click to More icon right after the Google Sheets icon to change the Sync configuration.

Query and Blend your Google Sheets data using SQL

Create your first SQL query by clicking Create with SQL query on the Data page. On the right side panel, you can find prompts – the list of Google Spreadsheet. Press Send Request to run your SQL query.

Jet allows you to join data from different data sources. For example, you can join data from Google Sheets and Hubspot. There are two options:

  1. Relations and Roll-up/Look-ups fields without code
  2. SQL Queries

Connect one more data source you want to join with Google Sheets data (for example, Stripe, Hubspot, Intercom).

Using Relations and Roll-up/Look-ups fields to Blend Data

Go to your Collection that has the Stripe Collection ID and specify Relation. For example, my Google Sheets collection has Stripe ID field -> Customer ID Stripe: Click to Column and change Type to Link to Record. Specify Data Source and Collection (Stripe -> Customers).

Add a new Custom Column and choose Lookups/Rolloup based on what operations you would like to do. Learn more about Rolloup/Lookups.

SQL Queries to Blend Data

Click Create with SQL query in Data page. On the right side panel you can see the list of Data Sources that you can blend, join.

Extend Google Sheets API using Jet API

The flexible Jet API enables you to use Jet as a backend, extend your apps, and benefit from inter-operability. Jet generates List, Retrieve, Create, Update, and Delete endpoints for each synced Google Sheets.

Build an App on top of your data

Jet allows you to build Internal tools, Customer portals, Dashboards, Automations&Workflows on top of your Data.

Learn how to build an app on top of Google Sheets database

Learn how to build Automations & Workflows on top of Google Sheets data

Use Airtable? See our guide for syncing Airtable to a PostgreSQL database.