Google Sheets is traditionally used as a spreadsheet program, but did you know that it can also be used as a database? When you use Google Sheets as a database, you can perform more advanced queries and calculations. You can also manipulate the data in another system.
Spreadsheets can do a lot of things. But there may come a time when you actually need a database – and it’s easier to create that database in the program where your data already resides.
In this article, we will explore how to create a relational database in Google Sheets. We will look at what works and what doesn’t, as well as the advantages and limitations of such a system.
What is the difference between a spreadsheet and a database?
A spreadsheet allows you to aggregate and organize data simply, stored in columns and rows. A database, on the other hand, is more fluid: although the data is also stored in tables, it can be extracted from that table in any configuration.
Another difference is that a spreadsheet is usually used on its own, while a database is often integrated with another software, such as a CMS (Content Management System).
Given that spreadsheets and databases store data in the same way, spreadsheets can also be used as databases. The major difference lies in how the data is accessible.
Can I create a database in Google Sheets?
It is entirely possible to create a database in Google Sheets. In fact, there are even some advantages to doing so:
- Familiarity: it uses a technology that you already know.
- Accessibility: Google Sheets can be used and accessed from anywhere.
- Cost: Google Sheets can be used for free.
- Features: Google Sheets has built-in data visualization functions.
But there are also some major issues. Google Sheets is not designed to be used as a database. The more information is stored in a Google Sheets database, the worse the database performance becomes. Additionally, Google Sheets does not have advanced features for searching or manipulating tables.
And if you want to use Google Sheets as a database, you will have to do considerable work.
How to use Google Sheets as a database
We will guide you through the steps of creating a free database on Google, starting with a simple database sheet.
This is simply a spreadsheet. It can contain data from anywhere. But there is an important aspect of this spreadsheet. It represents the data in the same way as a database would. A database would have field headers at the top of the columns and formatted data below. It would also have a unique identifier in the left column.
Once you have created this spreadsheet, you need to think about how you will use it as a database.
Importing your Google Sheets database into another database
The simplest way to use a Google Sheets database system is to directly import your main sheet into a database, such as MySQL. Your Google sheet remains your main copy of the data and it controls the data, but MySQL will be used to manipulate that data.
- Download the CSV (comma-separated values) file.
A CSV file is a universal file format that almost any database can use. - Import it directly into your database.
Your database should have an import function that allows you to automatically import a CSV file. - Use your database normally.
You will need to periodically refresh your data, but you can now use database functions with your Excel data. For example:
select email from users where name = "Mary";
The above SQL code would produce the following result:
[email protected]
This is not something you could easily do in Excel, although you can achieve it using the VLOOKUP, HLOOKUP, and INDEX functions.
Using Google Sheets as a database with the Database API
There is an API that you can use to manipulate Google Sheets. In other words, you can sign up for an Application Programming Interface (API) that allows you to read and write data, format data, build tables, validate cells, freeze rows, adjust column size, apply formulas, and even create charts.
That said, this requires some technical knowledge.
You will need to:
- Access the Google Cloud platform and go to the API and services section.
- Create a new project.
- Search for the Google Sheets API in the new project.
- Enable the Google Sheets API.
- Create a “Service account” identification of type “Service Account”.
- Select the “Project” role, then “Editor”.
- Create a new JSON private key.
Wow! What does all that do?
It makes it possible to control a spreadsheet via the Google Sheets Database API, essentially turning your Google sheet into a database.
In the JSON private key, there will be an email address. This email address is automatically generated and allows you to validate your identity so that you can modify your spreadsheet via the API.
Go back to your spreadsheet and share it with this email address as an editor.
Now, you won’t be able to simply send queries in a command line console like you would with MySQL. Instead, you will need to write a custom JSON script.
Adding a user would look like this:
let values = [ [ "6", "Lisa", "[email protected]", "123 New Home Circle" ] ];
If you’re a beginner, you will need to learn a little more about JSON, as well as how to run these scripts. But it allows you to do almost anything you would do in a database – all thanks to the Google API.
Using Google Sheets as a database for a CMS
Some Content Management Systems (CMS), such as Django, have a built-in feature to use a sheet or CSV file as a database. In Django, you can use the Google Sheets API to directly read data from your Google sheet.
But when it comes to WordPress, you may need to use a plugin. There are plugins like Spreadsheet Integration that can help you retrieve data from Google Sheets into an active database.
In this scenario, you are not using Google Sheets as a real-time database, but rather as a way to store data that is then synced and manipulated in a database.
How to use Google Sheets as its own database
It’s also important to note that you don’t always need database functions. Basic filtering, sorting, and searching functions can be performed directly in Google Sheets.
Creating a Pivot Table in Google Sheets
With the above pivot table, you can perform many search, match, and filter functions.
Using Filters in Google Sheets
Filters allow you to easily sort data based on what you want or don’t want to see.
Sorting in Google Sheets
Finally, you can also easily sort your data using Google Sheets.
So, what database functions do you actually need for your Google Sheets “database”? If you simply need to filter and store simple data, you may be able to do everything directly in Google Sheets – and that will still be the easiest method.
Using Google Sheets as a Relational Database
At this point, we need to note that you cannot use Google Sheets directly as a relational database – even if you use the Google Sheets Database API.
The only way to achieve something similar is to export your Google sheet as a CSV file and import it into a platform such as MySQL.
This is due to how Google Sheets stores its data. It stores its data as position – a specific cell.
Relational databases store their data interlinked. The data is stored like this:
1 <-> John <-> [email protected]
When you extract any of these values, they are intrinsically linked to the “1” record.
But Google Sheets simply stores its data as separate cells:
1 John [email protected]
“John” has nothing to do with “1” in the Google spreadsheet, except for a few minor exceptions (like linked rows when sorting).
If you need a relational database, you should consider a different type of technology.
What to do if you outgrow your Google Sheets database
At some point, you may find that you need additional features that Google Sheets simply cannot provide. But you’re in luck.
As mentioned earlier, it is not that difficult to import or export data from Google Sheets. You can export your data from Google Sheets to a CSV file and then import it directly into a more advanced database solution.
Some very popular database solutions include:
- MySQL
- Microsoft SQL
- PostgreSQL
All of these database solutions can be used with data exported from Google Sheets, with some minor modifications. You may need to assign data types to your columns (for example, an email address would be a variable-length “string”).
From there, you can decide whether you want to continue updating the Google sheet and importing the data, or if you want to start managing the data directly in the database system.
Using Google Sheets as a Database: Google Sheets Customers Database Template
As you can see, Google Sheets cannot provide a complete database solution. But it can come close.
When used alone, it can be the data source of the database or it can perform basic filtering, sorting, and searching functions. When used with the Google Database API, it can be very powerful, but you will need to learn a whole new system and programming language.
Want to learn more about using Google Sheets as a free Google database?
Get our Google Sheets Customers Database Template here.
To use it, simply copy the page to your own Google Drive, where you can freely edit it.
Please be aware that you will need to follow a few additional steps to use it as a real Google database, whether importing the data into another database system (Microsoft SQL, MySQL, PostgreSQL) or using the Google Sites Database backend.
