How to Use Google Sheets as a Database | Coupler.io Blog (2024)

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.

Using a spreadsheet as a database

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!

Why use Google Sheets as a database

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.

Pros as a database

Google Sheets, among other spreadsheet apps, has a number of great advantages as a database:

• Connectivity: Google Sheets is a web application, which means that is available online. There is no way to lose your files, thus your database is safely stored in Google’s cloud.
• Access Control: If you use Google Sheets, you have access to Google’s access control system. This means that with only a few clicks you can provide view and edit permissions to your data, or revoke existing permissions.
• Pricing: Google Sheets is one of many Google products that is free for unlimited usage. Other proprietary software such as Microsoft Excel requires a license or a subscription, which adds another layer to the overall application cost.
• Learning Curve: If you are managing a business or working at one, you probably have already used a spreadsheet application. Even if you haven’t, the learning curve is very smooth. You can quickly understand how the software works and leverage its capabilities to the fullest.
• Visualization: Google Sheets has a great number of built-in features that allows you to quickly manipulate the appearance and structure of the data, then quickly visualize and analyze it.

Cons as a database

On the other hand, comparing Google Sheets to a DBMS has its disadvantages as well:

• Scalability: When it comes to scalability, Google Sheets is not the best option. There are data limitations on the platform, and as your dataset grows, the response time becomes slower.
• Query Options: While Google Sheets provides query mechanisms, it is limited compared to a DBMS. A DBMS provides advanced query mechanisms that can handle a large amount of data with ease.
• Shareability: DBMS are built in a way that allows multiple users to access and modify data simultaneously. Unfortunately, that’s not the case with Google Sheets, where there can be inconsistencies when two users are changing the same set of data in parallel.

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.

How you can move data to/from Google Sheets as a database

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:

1. Create: Import/Add new data to the database.
2. Read: Query the database and retrieve its data.
3. Update: Modify the data in the database.
4. Delete: Delete data from the database.

When using Google Sheets as a database, you can perform any of the above actions either manually or automatically.

Export/import data manually

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.

Export/import data automatically using the Google Sheets API

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:

• Browser JavaScript
• C#
• Go
• Java
• Node.js
• PHP
• Python
• Ruby

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.

Import data to Google Sheets from other databases

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.

How to set up Google Sheets as a database with the API

As we mentioned, Google Sheets comes with an API to interact with its interface. Google Sheets API lets you programmatically:

• Read and write data
• Format text and numbers
• Build pivot tables
• Enforce cell validation
• Set frozen rows
• Adjust column sizes
• Apply formulas
• Create charts

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.

Authenticating with Google Sheets API

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:

Enable the Google Sheets API

• Go to the Google APIs Console.
• Click Select a project in the top-right corner.

• Click the New Project option.

• Give a unique name to your project and click Create.

• Go to the APIs dashboard.
• Search for Google Sheets API and click on it.

• Click Enable and wait for the API to be enabled.

Create a Service Account

• When the API is enabled, move to the Credentials page.
• Click the Create credentials option and select Service Account.

• Give a name to the service account and click Create.

• Click Select a role => Project => Editor.

• Click Done.

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.

Create a Service Key

• On the Credentials page, click on your service account name.

• Go to Keys.

• Select Add Key => Create new key.

• Leave the option as JSON and click Create.

• This action will download a JSON file – rename that file client_secret.json

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.

• Click Share in the top-right corner of your spreadsheet.

• Paste the client email you just copied in the field and give Editor rights. Click Send.

Now your service account has Edit access to the sheet and your application can use Google Sheets API to access the spreadsheet.

Read from Google Sheets

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 requestjwtClient.authorize(function (err, tokens) { if (err) { console.log(err); return; } else { console.log("Successfully connected!"); }});//Google Sheets APIlet 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:

Write to Google Sheets

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 requestjwtClient.authorize(function (err, tokens) { if (err) { console.log(err); return; } else { console.log("Successfully connected!"); }});//Google Sheets APIlet 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:

Use Google Sheets as a database for a website

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.

Use Google Sheets as a database for for an HTML page

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:

• Let’s start by creating a brand new Google Sheets spreadsheet.
• Rename the current sheet (or create a new one and name it) “Homepage_Data”.
• Go to Tools => Script Editor.

• You will be introduced to the standard Apps Script environment.

• Click on Untitled Project and rename it Sample HTML Page.
• Press the + icon next to Files on the left panel and select HTML.
• Name the new HTML file Homepage.
• Replace all the code from the new HTML page with the code below:

<!DOCTYPE html><html> <head> <base target="_top"> </head> <body> <table width="175" border="1" id="table"></table><br><br> <input id="enterdata" type="text"/><button onclick="writeData()">Write Data</button> <script> function getData(values) { values.forEach(function(item, index) { var tbl = document.getElementById("table"); tbl.innerHTML += '<tr><td>' + item[0] + '</td></tr>'; }); } google.script.run.withSuccessHandler(getData).readData(); function writeData() { var sendvalue = document.getElementById("enterdata").value; var tbl = document.getElementById("table") tbl.innerHTML += '<tr><td>' + sendvalue + '</td></tr>'; google.script.run.writeData(sendvalue); document.getElementById("enterdata").value = null; } </script> </body></html>

• Then open the “Code.gs” file and replace the included code with the code below:

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);};

• Then click the blue Deploy button on the right and select New deployment.

• Click on the little gear icon next to Select type and select Web App.

• Give a description to your app, leave the other fields as they are, and click Deploy.

• Wait for the app to be deployed. The first time, you will need to authorize access to the app – click Authorize access => and choose your Google account. If the next screen says “Google hasn’t verified this app”, that’s natural as you are not an official developer – just click Advanced => Go to Sample HTML Page (unsafe) => Allow

• Your app is now deployed – click your web app URL to visit your app.

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

Use Google Sheets as a database for a Django App

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:

• Use Google Sheets API v4
• Open a spreadsheet by title, key or URL
• Read, write, and format cell ranges
• Control sharing and access
• Perform batching updates

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 gspreadfrom oauth2client.service_account import ServiceAccountCredentials# use creds to create a client to interact with the Google Drive APIscope = ['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").sheet1sheet.get_all_values()

Or insert data to a sheet:

import gspreadfrom oauth2client.service_account import ServiceAccountCredentials# use creds to create a client to interact with the Google Drive APIscope = ['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").sheet1row = ["We","LOVE","COUPLER"]index = 1sheet.insert_row(row, index)

Awesome, right?

Use Google Sheets as a database for an API

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.

Use Google Sheets as a database for a WordPress website

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 asContact 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!

Use Google Sheets as an inventory database

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.

Use Google Sheets as a relational database

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:

1. Teachers: Contains the IDs, first names and surnames of the teachers
2. Classes: Contains the IDs, the teachers, the students and the names of the class.
3. Students: Contains the IDs, first names and surnames of the students

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.

Using Google Sheets as a database for creating graphs

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”.

How to move from Google Sheets as a database

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.

Why to move away from Google Sheets as a database

“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”:

• Data Limitations: Google Sheets has a limit of 5 million cells. This means that if your data grows a lot, there may be cases when you reach this limit and have to implement some workarounds.
• Consistency: Google Sheets API is great for simple usage and common requests, but it’s not always consistent. There may be cases when a request fails or times out. Common database systems have “fault tolerance” features that solve this kind of problems while, in Google Sheets, you have to create them yourself.
• Security: Google Sheets is a great tool for storing simple data, but when it comes to security, other options may be a better fit. For example, if you want a database to store sensitive user information and passwords that needs to be encrypted, you may have to use a database system that provides more tools for this kind of data.

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.

Dimitris Vogiatzis

Technology, Data & Analytics Lead at Amplifyd