Google Sheets is traditionally used as a spreadsheet program, but using Google Sheets as a database is also a possibility. 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 altogether.
Spreadsheets can do a lot. But there may come a time when you actually need a database — and it’s easiest to create that database in the program in which your data already resides.
In this article, we’re going to take a look at creating a relational database in Google Sheets. We’re going to look at what works and doesn’t work — and the benefits and limitations of such a system.
This Article Covers:
What is the Difference Between a Spreadsheet and a Database?
A spreadsheet provides simple aggregation and organization of data, held in columns and rows. A database is more fluid: while the data is also held within a table, it can be pulled out of that table in any configuration.
Another difference is that a spreadsheet is generally used alone, whereas a database is often integrated with another software problem, such as a CMS (content management system).
Because both spreadsheets and databases store data in the same way, spreadsheets can also be used as databases. The major difference is how the data is accessed.
Can I Create a Database in Google Sheets?
It’s more than possible to create a database in Google Sheets. In fact, there are even some advantages to doing so:
- Familiarity. It utilizes a technology that you are already likely familiar with.
- Accessibility. Google Sheets can be accessed and used from anywhere.
- Cost. Google Sheets can be used completely for free.
- Features. Google Sheets has built-in data visualization.
But there are also some major issues, too. Google Sheets isn’t designed to be used as a database. The more information held within a Google Sheets database, the worse the database will perform. Google Sheets also doesn’t have advanced features for searching through or manipulating tables.
And if you want to use Google Sheets as a database, you are going to have to do a significant amount of work.
How to Use Google Sheets as a Database
We’re going to go through the steps of creating a free Google Database, starting with a simple database sheet.
This is just a spreadsheet. It could have data from anywhere. But there is one important aspect of this spreadsheet. It represents data the way that a database would. A database would have the titles of the fields at the top of the columns and the data formatted below. It would also have a unique ID in the left column.
Once you’ve created this customer spreadsheet, you need to figure out how you’re going to use it as a database.
Importing Your Google Sheets Database Into Another One
The easiest way to use a Google database system is actually to import your master sheet directly into a database, like MySQL. Your Google Sheet is still your master copy of data and it controls the data, but MySQL will be used to manipulate that data.
1. Download the CSV (comma separated) file.
A “CSV” file is a sort of universal file that nearly every database can use.
2. Import it directly into your database.
Your database should have an import function that lets you automatically bring in a CSV file.
3. Use your database as normal.
You’ll need to periodically “refresh” your data, but you’ll now be able to use database functions with your Excel data. For example:
select email from users where name = “Mary”;
The above code in SQL (Structured Query Language) would produce the following result:
mary@gmail.com
This isn’t something that you could easily do in Excel, although you can achieve it through LOOKUP, HLOOKUP, and VLOOKUP functions.
Use 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 Layer Interface that lets you program in the ability to read and write data, format data, build tables, validate cells, freeze rows, adjust column sizes, apply formulas, and even create charts.
That being said, it requires a little technical knowledge.
You will need to:
- Load up the Google Cloud Platform and head to the APIs and Services area.
- Create a new project.
- Within the new project, search for the Google Sheets API.
- Enable the Google Sheets API.
- Create a “Credentials Account” of the type “Service Account.”
- Select the role “Project” and then “Editor.”
- Create a new JSON Private Key.
Whew! What did that all do?
It made it possible to control a spreadsheet through the Google Sheets Database API, which in turn makes your Google Sheet essentially a database.
In the JSON Private Key, there will be an email address. This email address is an automatically generated email address that lets you validate your identity so you can modify your spreadsheet through the API.
Go back to your spreadsheet and share your sheet with that email address as an editor.
Now, you’re not going to be able to just send queries into a console line the way you could with MySQL. Instead, you’ll have to write custom JSON script.
Adding a user would look like this:
let values = [
[
“6”,
“Lisa”,
“lisa@gmail.com”,
“123 New Home Circle”
]
];
If you’re a beginner, you’ll need to learn a little more about JSON, as well as how to run these scripts. But this does let you do almost anything you would do in a database — all through the Google API.
Using Google Sheets as a Database for a CMS
Some content management systems, such as Django, have built-in functionality for using a sheet or a CSV as a database. In Django, you can use the Google Sheets API to read data directly from your Google Sheet.
But when it comes to WordPress, you might need to rely upon a plug-in. There are plug-ins like Spreadsheet Integration that can help you pull Google Sheets data into an active database.
In this scenario, you aren’t using Google Sheets as a real-time database, but you are using it to store the data that is then synced and manipulated within 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 finding functions can take place in Google Sheets itself.
Creating a Pivot Table in Google Sheets
With the above pivot table, you can perform many finding, matching, and filtering functions.
Using Filters in Google Sheets
Filters let you sort data easily based on what you do and don’t want to see.
Sorting in Google Sheets
Finally, you can also easily sort your data using Google Sheets.
So, what functions do you actually need for your Google Sheets “database”? If you just need simple data filtering and storing, then you may actually be able to do everything in Google sheets itself — and that will always be the easiest method.
Using Google Sheets as a Relational Database
At this point, we should note that you cannot use Google Sheets directly as a relational database — even if you use the Google Database API.
The only way that you can do something similar to this is if you export your Google Sheet as a CSV and bring it into a platform such as MySQL.
This is because of the way that Google Sheets stores its data. It stores its data as a location — a specific cell.
Relational databases will store their data interconnectedly. The data is stored like this:
1 <-> John <-> john@gmail.com
When you pull any of these values, they are inherently related to the record “1.”
But Google Sheets just stores its data as discrete cells:
1 John john@gmail.com
“John” has nothing to do with “1” within the Google Sheet, except for a few small exceptions (such as rows being tied together during sorting).
If you need a relational database, you need to consider a different type of technology.
What to Do If You Outgrow Your Google Sheets Database
Eventually, you may find that you need additional features that Google Sheets simply can’t provide. But you’re in luck.
As noted, it’s not that hard to import or export Google Sheets data. You can export your Google Sheets data to a CSV and then import it directly into a more advanced database solution.
Some highly popular database solutions include:
- MySQL
- Microsoft SQL
- PostgreSQL
Any of these database solutions can be used with data that’s been exported from Google Sheets, with a few minor changes. You may need to assign data types to your columns (for instance, an email address would be a “string” of variable length).
From there, you can make the decision as to whether you want to continue updating the Google Sheet and importing the data or whether you want to start maintaining the data within the database system.
Using Google Sheets as a Database: Google Sheets Customer Database Template
As you can see, Google Sheets can’t provide a complete database solution. But it can get quite close.
When used alone, it can be the source of database data — or it can perform basic filtering, sorting, and search functions. When used with the Google Database API, it can be pretty powerful, but you will need to learn an entirely new system and programming language.
Want to learn more about using Google Sheets as a free Google Database?
Get our Google Sheets database template here.
To use, copy the page to your own Google Drive, where you can modify it freely.
But be aware that you’re going to need to take some additional steps to use this as a true Google Database, whether you’re importing the data into another database system (Microsoft SQL, MySQL, PostgreSQL) or you’re using the Google Sites database backend.
