Databases are the backbone of the internet and its applications. If you have ever used a native or a web application, you have already used a database. But what exactly is a database, you may ask? A database can be defined as any set of organized data. There are many different tools and systems that manage databases that require installation, configuration, and maintenance => they provide simple or sophisticated query mechanisms for this data.
Spreadsheet applications are very common in the business world, as they allow easy storage and manipulation of tabular data. People have become more familiar with spreadsheet applications such as Microsoft Excel and Google Sheets, to perform basic arithmetic and mathematical calculations, and to solve financial and statistical problems.
By default, spreadsheet applications were not created to work as databases, although small businesses and simple applications are already using them in this way. This is because such uses require simple solutions, and a standard database management system increases the complexity of the project. When the volume of the data is small, a company’s budget is limited, and the complexity must be kept to a minimum. Spreadsheets can be the go-to solution. A standard database management system requires a lot of time and resources to install, configure and maintain. It may also take a long time to become proficient in a single database tool. If you are wondering whether Google Sheets is the right choice for you, we have already answered the most frequent questions on the matter in our article on spreadsheets vs. database.
If you are still undecided and are considering using Google Sheets as your database, keep reading. We’ll show you why and how to use Google Sheets as your database. We’ll also present a couple of use cases to help you decide!
When we compare Google Sheets with a Database Management System (DBMS), we can see a lot of advantages and disadvantages. There is no one-size-fits-all solution when it comes to databases, so you will have to choose the best option for your needs.
Google Sheets, among other spreadsheet apps, has a number of great advantages as a database:
On the other hand, comparing Google Sheets to a DBMS has its disadvantages as well:
It’s clear that if you are managing a small business, if your data requirements are small, or you are in early stages of development of an app, then Google Sheets is the right option for you! The tool provides a lot of flexibility, it’s easy to set up, and can handle a decent amount of data. As your business grows, you might have to consider a full DBMS, but only when Google Sheets is no longer enough for your needs.
Let’s say you are managing a small business, and you keep a log of all your clients and their purchases. When a client buys something, you add a new record to the database that says which client bought which product, along with the total cost. This operation is called “Create”, and it means that you are importing new data to your database.
When you want to find a specific customer in your database, you retrieve the customer data. The operation of retrieving the information from the database is called “Read”.
When you’re modifying an existing record in your database, the database performs an “Update” operation.
Finally, when you want to remove a record from your database, you initiate a “Delete” operation.
The above, commonly known as CRUD operations, are the core actions that someone can perform in a database, and any tool must be able to handle these operations to be considered as one. To summarize the functions:
When using Google Sheets as a database, you can perform any of the above actions either manually or automatically.
Importing and exporting data manually means that you can simply add or retrieve your data by hand when necessary. Usually, this process is done in the early stages of application development for a small business, where the work is not as frequent and the data set is still relatively small.
Let’s say you are an accountant and you’re keeping a record of all your clients along with their contact information. As soon as a new client is acquired, you can insert the data in a new row as shown below:
On the other hand, if a client is no longer active and you want to remove them from your database, so you can keep track of only the active clients, you can simply remove the existing record:
In many cases you may have a set of data available that you need to import them to your Google Sheets database in a batch. Moreover, there can be cases where you want to export your database, and also keep a file on your hard drive for backup purposes. To do that in a batch without having to export them one by one, you can use a “comma-separated values” file also known as CSV. Here is how you can import CSV data manually in Google Sheets.
As your business or application grows, your data set will continue to grow as well, so it’s important to automate as many operations as possible. Google Sheets has an API which you can use to import, export or modify your data.
Let’s say you are a small business owner. If you have an application for your business, you can connect it to Google Sheets via the API and automatically import and export your data. Google provides built-in libraries for the following set of programming languages:
This way you can update your product information, add new products to your database, add new clients to your database, or remove out-of-stock products from it without having to open the database sheet and manually perform these operations.
If you have an existing database with some data, and you want to migrate this database to Google Sheets, there couldn’t be an easier way. Almost every database system offers the choice of exporting your data in a batch using CSV files. As soon as you have your CSV files with all your data ready, you can load them into Google Sheet. Depending on the size of your data, this process could take from minutes to a couple of hours, but as soon as your data is loaded, you are ready to connect the spreadsheet to your application and start using the database. The simplification of this process is one of the reasons why we created Coupler.io.
Coupler.io is a data automation and analytics platform designed to empower businesses to gain the most from their data. It provides an ETL tool to automate imports of data from other databases to Google Sheets withour much effort.
For example, if you’re using Airtable as a database, but you are considering migrating your data to Google Sheets, you don’t have to use the API or export your data into files. With a small number of clicks and configurations you can automatically export your data from Airtable to Google Sheets, and migrate your database in just a couple of minutes.
This can also be useful since you can use Google Sheets as an intermediate link to get data from Airtable to another data destination.
Similar to the above, if your current architecture sits under Google’s BigQuery, you can connect BigQuery to Google Sheets with just a few clicks and export queries.
As we mentioned, Google Sheets comes with an API to interact with its interface. Google Sheets API lets you programmatically:
In order to use the API and be able to read and write data to your database, you have to authenticate your application with Google and then use the API to perform your required operations. We’ll go through the authentication process below and show you a quick example of how to read and write data.
In order to be able to use Google Sheets API, you must first authenticate yourself with the service. All Google APIs use the OAuth 2.0 protocol for authentication and authorization, which simplifies the process. Moreover, you can also create a service account that can be used to access all of Google’s resources.
If the above feels intimidating, fear not! We’ll explain in detail all the required steps for obtaining the client_secret file, which is necessary to authenticate with Google, and how to use this file to access your data.
Before explaining the process, it’s important to say that every access to a Google API is handled by a Google Cloud Platform project. Through this project, you can manage the resources used, the user access, and define which APIs can be reached via your application. So, let’s jump to the process:
Now that your service account has been created, you will have to create a key, which will allow you to connect to the API automatically via this service account.
This file contains all the sensitive information that will allow your app to authenticate with Google and have access to the API. It’s critical for this file to be kept private so that only your application has access to it.
The file will look like this:
Find the “ client_email ” value and copy the email address. Each spreadsheet that you want to be manipulated by your app must provide access to this email.
Now your service account has Edit access to the sheet and your application can use Google Sheets API to access the spreadsheet.
Using one of the client libraries mentioned in the previous section, and after you get the client_secret.json file, it’s really easy to read values from Google Sheets. For example, when your application needs to show some data from the database on the screen, first it needs to connect to the database and query (read) the data. A quick example of reading a range of values from Google Sheets using Node.js is shown below:
let google = require('googleapis'); let secretKey = require("./client_secret.json"); let jwtClient = new google.auth.JWT( secretKey.client_email, null, secretKey.private_key, ['https://www.googleapis.com/auth/spreadsheets']); //authenticate request jwtClient.authorize(function (err, tokens) < if (err) < console.log(err); return; >else < console.log("Successfully connected!"); >>); //Google Sheets API let spreadsheetId = 'XXXXX'; let sheetRange = 'Homepage_Data!A1:B10' let sheets = google.sheets('v4'); sheets.spreadsheets.values.get(< auth: jwtClient, spreadsheetId: spreadsheetId, range: sheetRange >, function (err, response) < if (err) < console.log('The API returned an error: ' + err); >else < console.log('Movie list from Google Sheets:'); for (let row of response.values) < console.log('Title [%s]\t\tRating [%s]', row[0], row[1]); >> >);
In the above code block, we first authenticate with Google Sheets API using the secret file and then then we read the data providing the spreadsheet ID and a specific range using A1 notation. A1 notation is a string that tells the API which sheet and which range we want to query. For example, Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1. The cells are defined using column and row numbering, as shown below:
Similarly, there are many cases where we want to write results back to Google Sheets. The process is almost the same, so, using Node.js and the secret file, we can open the spreadsheet, select a range, and write the data in it. A sample code to do that is shown below:
let google = require('googleapis'); let secretKey = require("./client_secret.json"); let jwtClient = new google.auth.JWT( secretKey.client_email, null, secretKey.private_key, ['https://www.googleapis.com/auth/spreadsheets']); //authenticate request jwtClient.authorize(function (err, tokens) < if (err) < console.log(err); return; >else < console.log("Successfully connected!"); >>); //Google Sheets API let spreadsheetId = 'XXXXX'; let sheetRange = 'Homepage_Data!A4:E4' let sheets = google.sheets('v4'); let values = [ [ “00004”, “Jack”, “Smith”, “1115748594”, “jack.smith@gmail.com” ] ]; const sheetResource = < values, >; sheets.spreadsheets.values.update(< auth: jwtClient, spreadsheetId: spreadsheetId, range: sheetRange, resource: sheetResource >, function (err, response) < if (err) < console.log('The API returned an error: ' + err); >else < console.log('Movie list from Google Sheets:'); for (let row of response.values) < console.log('Title [%s]\t\tRating [%s]', row[0], row[1]); >> >);
When writing data to Google Sheets, you need to make sure that the selected range (rows and columns) matches the numbers of the data you want to write. For the above example, the range must describe 2 rows and 2 columns to write the four numbers defined in the first line of code. After running the above, a new customer row is added to the database:
One of the most common use cases when someone is thinking about using Google Sheets as a database is to integrate it with their website. Let’s say you have an amazing blog where you are writing information about your daily life and occasionally upload recipes you loved.
Wouldn’t it be great if you could keep a spreadsheet with all the recipes and the ingredients in it, and the website would load them automatically? Well, Google provides a Browser JavaScript client library, and this means that you can create a custom JavaScript snippet within your website to extract all the important information. Below you can see a simple way to structure your spreadsheet to have all the important information:
To access the above data and show them in your website, you can run an example like the one below:
function listTitles() < gapi.client.sheets.spreadsheets.values.get(< spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms', range: 'Recipes!A2:F', >).then(function(response) < var range = response.result; if (range.values.length >0) < appendPre('Name, Recipe:'); for (i = 0; i < range.values.length; i++) < var row = range.values[i]; // Print columns A and E, which correspond to indices 0 and 4. appendPre(row[0] + ', ' + row[4]); >> else < appendPre('No data found.'); >>, function(response) < appendPre('Error: ' + response.result.error.message); >); >
As you can see, you can have the title, blog post date, the body of the blog post, the ingredients, and a link to the blog post image. This information is sufficient to build an entire blog post. Just use the library to access Google Sheets API, connect to the spreadsheet, and extract the information you need.
As soon as you create this flow, it will be really easy to add or remove new posts as the only thing required is your time to input the new piece of information or delete an old one.
Google’s Apps Script provides all the resources you need to create HTML pages and dynamically interact with its content using only Google Sheets. Below we’ll describe the process of building a simple HTML page using Apps Script and Google Sheets as a database:
function doGet() < return HtmlService.createHtmlOutputFromFile('Homepage'); >var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Homepage_Data'); function readData() < var range = spreadsheet.getRange(1, 1,spreadsheet.getLastRow(), spreadsheet.getLastColumn()).getValues(); return range; >; function writeData(sendvalue) < spreadsheet.getRange(spreadsheet.getLastRow()+1, 1).activate(); spreadsheet.getRange(spreadsheet.getLastRow()+1, 1).setValue(sendvalue); >;
visit your app." width="762" height="601" />
Here is how it looks:
You can add and remove data, and you will see it in the spreadsheet.
Congratulations on your first HTML page with Google Sheets as a database
If you are involved in any part of web development, you probably already know Django. Django is a Python Web framework that makes it easy for developers to build, maintain, and deploy scalable web applications.
Let’s say you have built a Django application where you present a leaderboard for a board game tournament you are participating in. Building a whole database for this might be overkill as you just want a small solution that is easy to integrate and maintain. Moreover, you want to be able to change the leaderboard with as little effort as possible, so Google Sheets is your best option.
It’s really easy connecting to Google Sheets as there is already a package ready to simplify the process. Gspread is a Python package that is using the Google Sheets API v4 and adds spreadsheet functionality to your app, allowing you to sync data to and from Google Sheets. Using this package, you can:
Connecting your app to Google Sheets, you can keep the scores in a spreadsheet and, as you update the spreadsheet with new data, your website will be dynamically updated. You can interact with Google Sheets really easily, for example:
Read all data from a specific sheet:
import gspread from oauth2client.service_account import ServiceAccountCredentials # use creds to create a client to interact with the Google Drive API scope = ['https://www.googleapis.com/auth/spreadsheets'] creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope) client = gspread.authorize(creds) sheet = client.open("Example Spreadsheet").sheet1 sheet.get_all_values()
Or insert data to a sheet:
import gspread from oauth2client.service_account import ServiceAccountCredentials # use creds to create a client to interact with the Google Drive API scope = ['https://www.googleapis.com/auth/spreadsheets'] creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope) client = gspread.authorize(creds) sheet = client.open("Example Spreadsheet").sheet1 row = ["We","LOVE","COUPLER"] index = 1 sheet.insert_row(row, index)
APIs are critical components for two entities to communicate with one another. APIs essentially power the whole internet as they allow important operations such as reads and writes. As we have already discussed, to communicate with Google Sheets programmatically, we use its API to read, write or change data.
When building your own API to create an interface for clients to communicate with your application, you may want to create a simple logger to keep all the requests to the API or use the API itself to let the users read or write data to your database.
For example, if you’re building an API using Node.js, you can use a built-in client library for Node.js that allows you to communicate with Google Sheets, thus making it easy to use it as a database. Google has a detailed guide on how to quickstart using Node.js and Google Sheets.
If you have created an API or found a ready-made one and all you need to do is export data automatically to your Google Sheets database, Coupler.io can be your favorite friend. With the JSON to Google Sheets integration, you can connect your spreadsheet to JSON APIs to import records, meta data, and other valuable data to your Google Sheets database without code. Google Sheets to Google Sheets connection is also provided as an alternative to IMPORTRANGE function.
WordPress is the most popular CMS out there. If you have a website, there’s a very good chance that you’re using WordPress to build and host it. WordPress has a great community that has already built many plugins to make your life easier.
Almost every website, besides the basic content, usually has a “call to action” button to download some content, sign up to the service, or a form to express interest in learning more. While we’re not recommend using Google Sheets as a database for registrations, as you need a more secure and robust solution to store passwords, Google Sheets is a great option to store leads from form submissions.
There are many form plugins that you can use in WordPress, such as Contact Forms 7, NinjaForms, and GravityForms, and almost all of them provide a way to connect forms with Google Sheets. You don’t need to set up and maintain a whole database system just to store contact form submissions when you can set up Google Sheets and have a database up and running in just a couple of clicks and no code at all. After all, we are used to looking at spreadsheets and we can even analyze our leads on the spot!
An inventory database is where you store all of the information about your inventory. For example, in an inventory database, you can store part names, quantities, vendors, locations, etc. It should be accessible, accurate, up to date, and customizable so that you have complete control over your inventory and can run your business as efficiently as possible. The database can be on paper, on a database system, or on a spreadsheet.
Many small businesses use Microsoft Excel as an inventory database and are already familiar with keeping the information up to date within the spreadsheet files. With Google Sheets, you get all the nice features of a spreadsheet software along with online capabilities.
Using Google Sheets as an inventory database, you can keep your inventory up to date automatically. By building a nice interface and connecting it to your Google Sheets file, you can add, delete, update, or even enhance your inventory with new information.
This use case scenario is very popular and even Google provides a detailed guide on how to “Create an inventory management app from Google Sheets with AppSheet”. Google Cloud’s AppSheet lets you create apps without writing a single line of code and you can have your inventory database up and running in just a few steps.
We’ve talked a lot in previous sections about how to use Google Sheets as a database, but we haven’t said if Google Sheets could work as a relational database yet. Before going to that, it’s important to first understand what a relational database is.
A relational database is a type of database that uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into multiple tables.
In the above picture, you can see how a relational database is structured. The schema consists of three tables:
A relational database is implemented and maintained via a Relational Database Management System (RDBMS). An RDBMS is a tool that allows you to install, use, and maintain relational databases. They usually use a language called Structured Query Language (SQL) to query the data.
Usually, to query the data, you need to build a query that will join and filter the data you need in an efficient way. Unfortunately, while we can simulate such a functionality in Google Sheets, it’s not convenient enough to create all these functions to query the data in an efficient way. This is why we do not recommend using Google Sheets when your data are structured in this relational model.
The most important reason we collect and store data is to analyze them and start making more informed and smart decisions. Analyzing data is not always easy and one of the most useful methods we use is graphing. Graphing or creating graphs is a method of visualizing data in a more digestible manner so we can easily see trends, point out outliers, and compare time periods. For example, here is a graph with the daily revenue of a business mapped over time.
It’s clear how helpful these charts can be in order to understand the performance or usage of our service better and more quickly. Storing your data in a database, and especially in Google Sheets, is the most important part of the process as you have already collected and structured your data. To create a chart in Google Sheets and analyze your data, all you have to do is follow our comprehensive guide on “How to Make a Chart or How to Create a Graph in Google Sheets”.
In this post, we have explained in detail how you can set up and use Google Sheets as a database for many different use cases. But there is a chance that you want to move away from Google Sheets as a database and use another database management tool.
The easiest way to move away from Google Sheets is to export all your data in CSV format. This way, you can have each sheet as a separate file and any standard database system out there probably already has a tool for importing CSV files. The migration is easier than ever and you can move from one database to another in less than an hour.
“But why move away from Google Sheets?” you may well ask. That’s a very good question. As we have already mentioned, Google Sheets can be a perfect tool for early days, proof of concepts, and small business where the data needs are not too demanding yet. But as your business grows, there are some limitations that could be defined as “deal breakers”:
If you feel that your business has outgrown the use of spreadsheets as a database, you should migrate to a more advanced database as soon as possible. This will improve your performance and bring your business to the next level.
Partner and Tech lead at Amplifyd with around 8 years of experience in marketing technology and analytics. My studies and work experience in data analysis, as well as technological innovations around process automation, have helped the company and its customers grow efficiently. For my work in the analytics industry, I was included in the Forbes 30U30 2024 list in Greece. I'm also enthusiastic about sharing my expertise with the world through writing content about analytics, data management, and other areas related to my experience.
