How to use Google Sheets as a database

If you’re building a SaaS app or small project, the first thing you need to consider is which database to use. If you have a small project or you’re building a prototype, Google Sheets is a good starting point. It’s simple to use, cost-effective and it’s easy to migrate your data over when you eventually scale your SaaS app and need a database management system (DBMS).

However, Google Sheets does not scale like a production database and you may face challenges with scalability (Google Sheets can only store 5 million cells), functionality (you can’t run queries or joins) and Google’s API has limits. 

In this article, I’m going to walk you through why Google Sheets is a solid starting point, its pros and cons, how to set up a database with Google Sheets and how to maximize your Google sheets database with no code data syncing.

Let’s dive in.

Why consider Google Sheets as your database?

If you’re building a prototype or an early stage startup, traditional database management systems (DBMS) can feel like overkill. They often require a lot of setup, maintenance, and technical expertise that may not be necessary.

On the other hand, Google Sheets provides a lightweight, intuitive interface that you’re probably already used to. Google Sheets is also a good tool for collaboration, you can easily manage users and permissions. If you want to give access to a team member, you just update the sharing settings, whereas with a traditional DBMS, you have to write a query to create a login for a user. 

Additionally, Google Sheets integrates with a wide range of Google tools and third-party applications, making it an even more powerful and flexible option for managing data.

Here are a few examples of how Google Sheets can connect with both Google tools and external apps:

All Google Workspace apps: You can easily link, share, and sync data across these apps, whether it's adding form responses to Sheets, inserting charts into Docs or Slides, or scheduling events in Calendar based on your sheet’s data.

Third-party apps: Google Sheets can be used with numerous third-party applications with data syncing tools like Whalesync. These syncs allow you to automate workflows, such as syncing data with CRMs like HubSpot, or sending marketing data to Salesforce. You connect Google Sheets to the tools you already use, enhancing productivity and reducing manual work.

Additionally, Google Sheets is an ideal database for internal tools and dashboards. It provides an easy way to store, manage, and share data without the overhead of a full-scale database. You can quickly set up and manage tasks like tracking projects, leads, or inventory, all within a simple, spreadsheet environment.

Here are a few examples of small-scale projects where Google Sheets is a good option as a database:

When is Google Sheets a good fit as a database?

Managing tasks in a small team: Use Google Sheets to track tasks, assign deadlines, and monitor progress. You can easily sort and filter tasks and integrate with Google Calendar. Additionally, you can connect Google Sheets with Notion to enhance collaboration across your team by syncing Google Sheets task with a more robust organizational tool.

Lead tracking: Manage and update customer or prospect information, track lead status, and monitor next steps in a shared sheet. Team members can collaboratively update the sheet to keep everyone informed.

Vendor contact list: Organize vendor details such as names, phone numbers, email addresses, locations, and business categories. You can also use filters and sorting options to easily find vendor information.

Inventory management: Track stock levels, product details, pricing, and order history. Google Sheets can help with simple inventory calculations, which is perfect for small businesses or startups. You can also sync Google Sheets to Shopify to automatically update inventory data, ensuring your stock levels are accurate across platforms and saving time on manual updates.

Event planning: Manage attendees, sponsors, presenters for upcoming events and conferences. Due to the seasonal nature of events, a traditional database may not be necessary, but you need a robust solution to keep track of everything in one place. 

Pros of Google Sheets as a database

While it may not have the advanced features needed for a fully operational, scalable SaaS app, Google Sheets serves as a great way to get started quickly and cheaply. Here are some of the pros.

Free to use— forever

Google Sheets is free to use, which makes it an option for small businesses or early-stage developers who need to keep costs low. There’s no need to worry about expensive database licenses or infrastructure costs in the beginning.

Familiar interface

There’s little to no learning curve when it comes to using Google Sheets. You can quickly get up and running without needing specialized knowledge. And billions of people already have accounts.

Easy to set up

Since Google Sheets is cloud-based, you get the benefit of minimal setup. You don’t need to worry about server hosting or maintaining infrastructure as you do with traditional DBMS.

Pre-built templates

Google Sheets comes with built-in charting and graphing features, which can be useful for visualizing data quickly without needing an additional tool like a business intelligence (BI) tool.

Cons of Google Sheets as a database

Although Google Sheets is a good starting point, but it does have the following drawbacks:

Data volume limitations

Google Sheets is limited to 5 million cells per sheet. This can be an issue as your dataset grows, and you start dealing with large amounts of data. If you're managing a SaaS app that stores a lot of information, this limitation can quickly become a bottleneck.

Limited query capabilities

Google Sheets doesn’t allow you to perform complex queries or joins. For simple searches and filtering, it can be effective, but when you need to scale to more complex database operations, you’ll quickly hit a wall. If you want to write more queries, you have to write and insert a Google Apps Script, which requires foundational JavaScript knowledge.

Concurrent users can cause conflict

While Google Sheets supports real-time collaboration, it’s not optimized for multiple concurrent users making changes to the data simultaneously. You may experience data overwriting, which can be confusing for teams working in real-time.

Structural issues

Google Sheets lacks an enforced structure. In Sheets, any cell can contain any type of data, which can lead to inconsistencies and errors over time. To avoid this, you have validate your data (explained in detail below), which can be tasking. However, you can use Airtable as a database, which has a more structured approach, similar to a traditional database. 

How to use Google Sheets as a database

Before we dive into the tutorial, it’s important to note that we’re going to use Google Sheets as  a relational database. A relational database stores and organizes data points that are related to one another. 

Relational databases hold data in table formats similar to spreadsheets, with rows and columns, and that’s why we can create a relational database with Google Sheets.

We’re going to create a relational database for a small SDR team who need to track their leads.

Create a new workbook

Open Google Sheets and create a new workbook. Create three sheets called ‘Leads’ and ‘Status’ and ‘Sales Reps’. Each worksheet in the workbook will act as a “table” in your relational database.

Create Sheets for your tables

In the Leads Sheet, add these columns:

• Lead ID
• Name
• Email
• Phone
• Status

In the Status sheet, add these columns:

• Status ID
• Status Name (e.g., New, In Progress, Closed)

In the Sales Reps Sheet, add these columns:

• Rep ID
• Name
• Email

Define primary keys

A primary key is a column (in this context) with unique values for each entry in the table, for example, phone number or email ID. You need a primary key in each table to uniquely determine or reference an entry in the table.

Assign unique primary keys to each table. For example:

Lead ID in the Leads Sheet (e.g., 1, 2, 3)

Status ID in the Status Sheet (e.g., 1 for New, 2 for In Progress)

Rep ID in the Sales Reps Sheet (e.g., 1 for Daniel, 2 for Edward)

Create relationships

Add a Status ID column to the Leads Sheet to track the lead status and 

add a Rep ID column to the Leads Sheet to assign sales reps.

Set up named ranges for dropdowns

Go to the Status Sheet and select the status name column. Click data, named ranges and label it status_names and then click done.

Repeat this step for the Sales Rep Sheet in the name column, and create another named range, e.g., rep_names.

Add data validation for dropdowns

In the Leads Sheet, for the Status ID column, select the cells under Status ID, right-click and then go to data validation (you may have to click view more cell actions).

Then click add rule.

Choose a dropdown from a range and enter status_names.

Repeat this step for the Rep ID column using rep_names as the named range.

Automate value entries using VLOOKUP

Suppose you want to auto-fill the status name in the Leads Sheet when a Status ID is entered, here’s how you do that:

In the Leads Sheet, add a Status Name column, you should insert this next to the Status ID column 

Enter a Status ID for each of the leads. In cell F2, enter: =IFNA(VLOOKUP(E2, Status!$A$2:$B, 2, 0), “ “)

• Drag the formula down for the column.
• Repeat this process for other lookups, such as fetching the rep name based on Rep ID.

This is what you should end up with:

Apply data validations

For the Email column in the Leads Sheet, select the cells under the Email column. Click on data, then click data validation and add rule. Click text is a  valid email to ensure only valid emails are entered.

For the Phone column, Click on data, then click data validation and add rule. Click number is greater than 0 to allow valid phone numbers only.

Now you have a relational database in Google Sheets! 

Sync your Google Sheets database with other tools

Google Sheets is a great starting point for small-scale projects and prototypes, it’s crucial to recognize its limitations in scalability and functionality as your project grows. The key to maximizing its potential lies in leveraging its simplicity and integrating it with other tools. 

Maximize your Google Sheets database with data syncing. Data syncing enables you to centralize your data from multiple sources into one— automatically. With real-time data syncing, you eliminate the need to manually update your data, so you can focus on what you do best, building.

Data syncing saves you valuable time by automating the process of updating and transferring data between platforms, which also reduces the risk of errors and miscommunication.  Additionally, syncing allows you to build more efficient internal solutions, making it easier to scale and adapt as your project grows. 

With Whalesync, you can connect your Google Sheets database to Airtable, Notion and other tools. Maintain data consistency across platforms to ensure that your workflows remain smooth.