How to Migrate Google Sheets to Postgres in 6 Steps

Moving data from Google Sheets to Postgres - or the DBMS of your choice - is one of the fundamental steps toward achieving modern, effective data management.

See, huge numbers of teams rely on spreadsheets for use cases where they just aren’t really suitable. Obviously, spreadsheets are so dominant for a reason - there’s no disputing that they offer a huge degree of flexibility, power, and usability.

But - when it comes to securely scaling data operations while maintaining accuracy, reliability, and interoperability, they leave a lot to be desired.

Despite this, countless teams stick with spreadsheets anyway - because they think that moving to more sophisticated data management solutions would be beyond them - either in terms of cost, technical ability, or day-to-day user experience.

Today, we’re dispelling this myth.

Specifically, we’re checking out how we can use Budibase to move data from Google Sheets to Postgres. Along the way, we’ll also explore some of the ways we can use custom queries and automations to achieve more advanced solutions.

We’ll even check out how we can use our autogenerated CRUD screens to create admin screens for your data in seconds, so day-to-day spreadsheet users don’t need to miss out on intuitive interactions when we implement our formal database

But first, let’s explore a little bit of theory.

Why move from Google Sheets to Postgres - or any other database?

We’ve hinted at some of the shortcomings of spreadsheets already. But how do we know when it’s time to move our data and its associated processes to a formal database instead?

There are a bunch of different signs that could indicate that spreadsheets are no longer cutting it.

For example:

• Our data set is becoming difficult to manage in a spreadsheet.
• We’re encountering excessive errors, confusion, or other mistakes.
• We want to integrate our data with external tools.
• We have security/integrity concerns around relying on spreadsheets.
• We’re encountering bottlenecks because of large user volumes.
• We can’t achieve specific automations or other functionality with a spreadsheet-based approach.
• We want to build more advanced UIs for our data.
• We need to implement access control or authentication tools that Google Sheets can’t support.

These are just some of the most common, generic issues we might encounter.

There are also countless other - more contextual - indicators that are more likely to be unique to your business needs. For example, we might want to use a particular combination of authentication tools and data validation that Google Sheets can’t support.

It’s also worth noting that there are some inherent risks and limitations of spreadsheets that we can easily get around by using a proper database.

Take for example the time the UK government lost thousands of COVID test results because someone accidentally deleted them from a spreadsheet. This could easily have been prevented with an effectively managed database.

Spreadsheets do provide intuitive user experiences, but this is compromised the larger our data set becomes. So, they’re a great solution for working with a few dozen rows, but things get very messy when we start dealing with thousands of data entries.

You might also like our guide to web application development .

Migrating vs connecting Sheets to Postgres

It’s also worth exploring what our options are for moving data from Sheets to Postgres. Today, we mainly want to focus on migration, but this isn’t the only show in town.

In the most basic terms, migration means permanently transferring data from one location or storage solution to another. We’re not just copying our data across - we’re also trying to move all of our management processes over too.

In the real world, this will normally involve some element of data transformation too. That is, when we need to change the form, format, or content of our data as part of the process of migrating it.

We might need transformation for a whole bunch of reasons - including to fit the requirements of our DMBS or simply because we need the changes for some external reason and migration is a convenient opportunity to do it.

Ultimately, the goal is to retire our existing data storage after our migration is complete.

An alternative approach would be to use integration tools to connect Google Sheets to Postgres. That is, using cross-platform automations to keep data up to date across both solutions - with the ultimate view of keeping both live.

This is potentially a little bit less disruptive since we don’t have to move our entire data ops across as we would with wholesale migration. However, we’re also introducing a few new challenges.

One is that we’re increasing the number of attack vectors our data is exposed to, making it trickier to maintain security. When we store different versions of the same data in multiple locations, we’re also introducing the possibility of conflicts or inconsistencies.

Really, this approach would only be suitable for a much smaller set of use cases - such as if we wanted to copy our data from Google Sheets to Postgres in order to connect to an existing platform that supports the latter but not the former.

Methods for moving data from Google Sheets to Postgres

There are a few different ways that we could go about migrating Google Sheets to Postgres. As we’ll see in a minute, using Budibase offers the best combination of ease, flexibility, and customization.

Still, it’s worthwhile knowing what the alternatives are.

The most common solutions are:

1. Using a dedicated Google Sheets plug-in.
2. Using Google Apps Script or hard coding a solution.
3. Uploading a CSV in your Postgres admin panel.

As you might guess, each of these offers pros and cons.

To give a very quick overview, the issues we’re trying to balance are - on one side - usability and ease - and on the other - customization and flexibility.

So on the one hand, using a CSV or a plug-in might be very easy, but there’s a good chance that we’ll need to compromise on specific transformations or other custom elements of our migration.

On the other hand, hard-coding a solution in Apps Script or whatever other programming language we want to use requires a much higher level of technical ability. Additionally, if something goes wrong, it might be difficult for us to spot.

Take a look at our tutorial on workflow management database design .

How to migrate from Google Sheets to Postgres using Budibase

With all of that in mind, let’s check out how we can migrate data from Google Sheets to Postgres using Budibase.

Obviously, there are a couple of prerequisites to this. Specifically, we’ll need:

• A Budibase account.
• A Google Sheets account.
• A Postgres database.
• Optional - if we want to migrate a large data set, it’s best to use a self-hosted Budibase installation.

You might also like our tutorial on building MySQL admin tools .

1. Create a new app project

First, we need to make a new Budibase app. Hit Create new app and you’ll be presented with this screen, offering us a bunch of templates to get started with:

We’re not going to use any of these though, so select Start from scratch. Then, we’ll be prompted to give our app a name and a URL:

When we’ve done this, we’re ready to pick a data source. We can add more data sources later, so, for now, we just want to select Google Sheets:

2. Set up a Google Sheets data source

Then, we’ll be presented with this modal, asking us to sign in with Google’s SSO:

Once you’ve done this, all you need to do is paste in the URL of your spreadsheet, hit fetch tables and save your data source:

Now we can view and edit our Google Sheets data from our Budibase Data section:

3. Connect to Postgres

Next, we want to set up our Postgres connection. You can host your database however you want, but we’re using a free tool called ElephantSQL for the purpose of this demo.

We don’t need any pre-existing tables, because we’re going to create these with Budibase.

Hit Add source:

This time, when we’re prompted we’ll pick Postgres. Then, you can fill in your database credentials on the screen provided:

Now, we’re ready to start creating some queries.

4. Write our custom CREATE, DROP, and INSERT queries

For our basic migration, we’re going to need three basic Postgres queries:

1. To create a new table.
2. To insert our values from Google Sheets.
3. To delete the table so we can re-run our automation flow.

To start, head down to Queries and hit Add new:

We’ll do the CREATE query first. The first thing to do is give our query a name and set function to Update:

Then, we can add our query into the Fields box. If you’re not a SQL whizz, don’t worry. We’ve provided the code below so you can use this as inspiration. Basically what we want to do is tell Postgres to make a new table, give it a name, and tell it what columns to store.

These should match the columns in our Google Sheets sheet - in terms of name and format. The syntax is:

1CREATE table_name (
2
3column1_name DATATYPE CONSTRAINTS
4
5)

A constraint is a rule that we apply to the data we store. We’re just using one, to designate the id variable as our PRIMARY KEY. If you don’t know what this means, check out our guide to relational vs non-relational databases .

But it doesn’t actually matter much today.

Our id field will be of the type int because it’s a number. All of the others will be VARCHAR - since they’ll be strings.

So, our query is:

 1CREATE TABLE customers (
 2
 3​	id int PRIMARY KEY,
 4
 5​	first_name VARCHAR,
 6
 7​	last_name VARCHAR,
 8
 9​	gender VARCHAR,
10
11​	state VARCHAR,
12
13​	country VARCHAR,
14
15​	phone VARCHAR,
16
17​	email VARCHAR
18
19);

And the full thing should look like this:

Next, we’ll create a DROP query and call it DROP TABLE, setting the Function to DELETE.

The query is:

1DROP TABLE customers;

The configuration should look like this:

And finally, our INSERT query. This one is a little more complex, so pay close attention to what we’re doing. We need to use Bindings to create dynamic variables that we can assign values to later when we create our automation flow.

We need one for each column in our Google Sheets table - and we want to give them names that make it easy to recognize which field they relate to.

Here’s what we’ve created:

Note, we’ve given gs_id a default value of 1 because it’s the primary key for our table - so it can’t be blank or else the query will fail when we try to test it.

The syntax for an INSERT query is:

1INSERT INTO 
2
3table_name (field1, field2 …)
4
5VALUES
6
7value1, value2 …)

We use the following INSERT statement to populate our data:

1INSERT INTO 
2
3customers (id, first_name, last_name, gender, state, country, phone, email)
4
5VALUES
6
7({{ gs_id }}, {{ gs_first_name }}, {{ gs_last_name }}, {{ gs_gender }}, {{ gs_state }}, {{ gs_country }}, {{ gs_phone }}, {{ gs_email }})

And it looks like this:

And that’s all of our queries written. You can simply sub out the names of your own Google Sheets columns in order to apply the same principles to move your own data from Google Sheets to Postgres.

5. Create our migration flow

Next, we can start to pull all of this together in our automation flow. First, head over to the Automate tab and hit Add Automation. We’ll be prompted to give it a name and choose a trigger - that is, how we’ll initiate our automation.

We’re going with App Action:

This makes life a bit simpler when we test out our automation.

Next, we add a step to query our Google Sheets table:

There’s no filter rule specific here, so this block will retrieve the entire table.

Then we add blocks to initiate our DROP and CREATE queries from earlier. We’re deleting the table we created earlier when we built our first query and then creating it again.

This will also make life easier if we want to repeat our automation flow because of something going wrong.

And finally, we add a third external data connector block, this time using the add looping button to iterate over the rows we retrieved from Google Sheets:

The bindings we created earlier are available in this block when we select our INSERT query. All we need to do is bind them to the respective values from our loop:

Note! Budibase Cloud imposes a hard limit on the number of items you can loop over in an automation, but you can override this if your self-host your tenant. So, you’ll want to use self-hosting if you have more than 100 entries in your sheet.

We don’t need to create a UI to initiate our flow, since hitting Finish and test automation in the backend will fire our migration.

Lastly, we can head over to our Postgres admin tools to make sure everything has migrated correctly:

6. Optional: generate CRUD screens in Budibase

And we’ve got one more optional step once we’re satisfied that our data is successfully migrated over to Postgres.

We stressed earlier that a big part of the reason for Google Sheets’ ubiquity is how easy it is to use. When we migrate from Sheets to Postges, there’s a strong possibility that we’ll need to replace this user experience.

Budibase makes this a breeze.

We’re simply going to generate some CRUD screens to give anyone who doesn’t know how to write SQL queries an easy way to create, read, update, or delete information.

Head over to the Design tab and hit Add Screen. Then select List View:

And you’re prompted to pick a data source:

We can customize this however we want, but we already have a fully working UI for managing our data entries:

And that’s it! If you have a spreadsheet with multiple tables that you need to move to Postgres, you can simply repeat this process for each.

Check out our ultimate guide to data management software solutions .

Turn data into action with Budibase

Budibase is the fast, easy way to create all kinds of professional applications and internal tools. IT teams around the world choose our open-source, low-code platform to turn data into action.

Here’s what makes Budibase tick.

Our open-source, low-code platform

Our design philosophy is simplicity by default; extensibility when you need it. No other platform makes it so easy to connect your data, build professional interfaces, create automations, and launch your tools.

Check out our features overview to learn more.

External data support

Budibase is miles ahead of the competition for external data support. We offer dedicated connectors for SQL, Postgres, Airtable, S3, Oracle, Mongo, Couch, Arango, REST API, Google Sheets, Dynamo, and more.

We’ve even got our own built-in database that supports CSV uploads.

Self-hosting and cloud deployments

Security-first organizations love Budibase for the power to host their own solutions. We offer self-hosting through Kubernetes, Docker, Digital Ocean, Linode, Portainer, and many more.

We’ve also got our own proprietary cloud-based hosting. Check out our pricing page to learn more.

Flow-based automations

Budibase makes it easy to automate all kinds of internal processes. We have an intuitive, flow-based automation editor with a whole library of configurable, loopable, actions and triggers.

Leverage external app events in your automations with Zapier, REST, WebHooks, and more.

Custom plug-ins

We won’t be beaten for extensibility. Use our dedicated CLI tools to build and ship custom components, data sources, and automation actions. Or import community contributions at the click of a button.

Check out our plug-ins docs to learn more.

Flexible RBAC

Budibase offers flexible, configurable role-based access control. Assign users to pre-defined roles and grant access at the level of data sources, queries, automation rules, screens, or individual UI components.

We also offer free SSO.

50+ free application templates

Budibase is the ideal solution for creating all kinds of professional apps and utilities. To help get you started, we’ve created more than 50 free, fully customizable app templates .

To start building applications the smart, easy way, sign up to Budibase today for free.