How to use Google Sheets as a database for your Business?

by Dhanashree 11 min read

Published: Apr 10, 2023 ● Updated: Mar 26, 2024

Automate your workflow with Nanonets

From business analysts to Management consultants, Excel and Google sheets have been used the most. This makes Google’s sheets user interface universally familiar and easy to store and manipulate data. Google spreadsheets are a good alternative for DBMS ( Data Base Management System) if you are looking for a simple, quick, cost-efficient solution for a small dataset.

Often, small businesses and projects face a shortage of resources, and skilled labor to set up a complex database management system. In these cases, Google sheets come to the rescue, providing easy connectivity, data visualization features, and access and control sharing options. Remember that it’s still not scalable to handle large datasets and very complex queries, unlike standard DBMS.

In this blog, I’ll discuss how to use google sheets as a database and the various methods available!

How to import and export data from Google spreadsheets?

The first step of using a database is to know how to import data from the raw files, or other formats. Then, we need to know the tools/options to add, remove or update the database. In this section, I’ll discuss four methods by which you can import/migrate and export data from Google sheets.

Method #1: Import data manually:

The most straightforward way, where any individual creates a Google spreadsheet and manually enter data into different column fields. This is feasible when the dataset is small in size, and the frequency of changes/updates needed is low. For example, imagine you are an artist who sells paintings and record the transactions by entering them into a Google spreadsheet. You can see a sample of this data in the below figure, which I’ll be using as an example throughout this blog.

Fig) Transactions.csv file

You can use the tools like filters, and sort by to look at selective sections further. It’s easy to export data in your format of preference from the “File->Download” option.

But what happens when the artist expands and the dataset size grows bigger? You need to look for more efficient options like using the API or Nanonets. Keep reading to find out how!

Method#2 Use the Google Sheets API:

A popular way to automate the import and export of data is through the API of Google sheets. You need to create a service account and complete Authentication to use the API.

Here’s a stepwise guide to go about it:

Enable the Google Sheets API in the Google Cloud Console. For more detailed instructions, you can check their official guide.
Create a new project and set up the OAuth consent screen.
Generate credentials for your project by creating a new service account and downloading the JSON key. Ensure to save the JSON key file safely.
Share the Google Sheet with the service account email address.
Run the ’pip install google-api-python-client’ to install the package

Then, you can write a simple code in Python as shown below to import the above transactions.csv file into a Google Spreadsheet.

import os
import csv
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

# Set the path to your JSON key file
key_path = 'key.json'

# Set the ID of your Google Sheet
sheet_id = 'paintings_sales'

# Authenticate with the Google Sheets API using your JSON key file
creds = None
if os.path.exists(key_path):
creds = service_account.Credentials.from_service_account_file(
key_path, scopes=['https://www.googleapis.com/auth/spreadsheets'])

# Create a new Google Sheets API client
service = build('sheets', 'v4', credentials=creds)

# Open the CSV file and read the data
with open('transactions.csv', 'r') as csv_file:
csv_data = csv.reader(csv_file)
values = [row for row in csv_data]

# Set the range where you want to insert the data in your Google Sheet
range_name = 'Sheet1!A1:E'
# Build the request to insert the data into the Google Sheerequest_body = {
'range': range_name,
'majorDimension': 'ROWS',
'values': values
}
request = service.spreadsheets().values().update(
spreadsheetId=sheet_id, range=range_name, valueInputOption='USER_ENTERED', body=request_body)
# Execute the request to insert the data into the Google Sheet
response = request.execute()

Method #3 Migrate Data from other databases:

Google Spreadsheets also support easy migration of data from other databases. As per the requirement, you can select and migrate a batch of data from other databases like Airtable and BigQuery as a CSV file and upload it to Google Sheets.

Method #4. Use Nanonets :

What if the artist emails digital invoices of his sales and wants to import transaction data from there? Nanonets is the go-to solution here!

Nanonets is an AI-powered platform that uses machine learning algorithms to automatically extract the relevant data and convert it into a spreadsheet format that can be easily imported into Google Sheets. Nanonets can save you time by eliminating manual data entry and streamlining your data entry process. The OCR has high accuracy and can handle many document formats and file sizes. Multiple OCRs exist for specific business use cases, including Invoice OCR and Receipt OCR. Check out an example!

To get started, sign up for a Nanonets account and upload your document or PDF file. Setup

Database for your website with Google Sheets

Google Sheets can be a simple and cost-effective database for small websites. If you want to set up a database for your website, you can use Google sheets to web scrape and obtain the data.

To get started, first, open a new Google Sheet and select "Tools" and then "Script Editor".Here, you can write simple code scripts to execute tasks like data import and manipulation.

The built-in IMPORTXML function allows you to extract data by inputting the Xpath and URL of the website.

You can use the ImportHTML function to scrape data from HTML tables. It also provides additional options to choose a particular row or column. What if you want to extract data that matches a particular pattern, like the price mentioned of paintings otr invoice number?

You can use the REGEXTRACT function for the same. In the below snip, I have included the syntax and examples of these common functions. Check it out!

#Syntax: IMPORTXML(url, xpath_query)
IMPORTXML("https://www.flagster.com", "decor_flags")
# Syntax: IMPORTHTML(url, query, index)
IMPORTHTML(‘hhtps://www.abstractart.com’,’table’,3)
#IMPORTDATA(url)
#Syntax: REGEXEXTRACT(text, regular_expression)

If you are looking for more steps on how to this, you can check out this guide

Use Google Sheets as a database for an HTML page

You can create an HTML page with Google Sheets as a database, set it up and dynamically interact and manage the content. Navigate to Tools => Script Editor to access the Google Apps Script environment. Here, you can write a simple code as shown below for an HTML web page, where the user enters data is stored in the input field and written to Google sheets.

<!DOCTYPE html>
<html>
<body>
<table width="100" border="3" id="paintings"></table><br><br>
<input id="enterdata" type="text"/><button onclick="writeData()">Write Data</button>
<script>
function input_data(values) {
values.forEach(function(item, index) {
var tbl = document.getElementById("table");
tbl.innerHTML += '<tr><td>' + item[0] + '</td></tr>';
});
}
google.script.run.withSuccessHandler(input_data).readData();
function extract_data() {
var temp = document.getElementById("enterdata").value;
var tbl = document.getElementById("table")
tbl.innerHTML += '<tr><td>' + temp + '</td></tr>';
google.script.run.extract_data(temp);
document.getElementById("enterdata").value = null;
}
</script>
</body>
</html>

The above code has a table element with an id of "paintings" to display the data. There is an input field with an id of "enterdata", and a button element that executes the JavaScript function "writeData()" when clicked.

The "input_data()" will get executed when the web page is loaded, retrieving data and populating Googe sheets. Whereas, when "extract_data()" is executed, the Google Apps script is deployed to write data into tables. It uses Google Apps Script to retrieve the data from the Google Sheet and populates the table with the retrieved values. Use "google.script.run" method to execute the Google Apps Script functions "readData()" and "extract_data()" from the HTML page.

An advantage of this method is the easy collaboration, access and integration with other Google ecosystem facilities available.

Use Google Sheets as a database for a Django App

Django is a web framework that allows you to create web applications quickly. It comes with a built-in database called SQLite but also supports other databases like MySQL, PostgreSQL, and Oracle. While SQLite is great for development, it's not well suited for production use. That's where Google Sheets comes in.

Let's dive into how to connect Django to Google Sheets and use it as a database! To connect Django to Google Sheets, use the API. If you dont recall, you can check how we set up the API and service account for a new Google sheet. Now, lets write the code in the Django App directory.

import os
from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = os.path.join(BASE_DIR, 'path/to/credentials.json')
SPREADSHEET_ID = 'spreadsheet_120'

creds = None
creds = service_account.Credentials.from_service_account_file(
SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()

def read_data(sheet_name):
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=sheet_name).execute()
data = result.get('values', [])
return data

def write_data(sheet_name, data):
body = {
'values': data
}
result = sheet.values().append(spreadsheetId=SPREADSHEET_ID, range=sheet_name, valueInputOption='USER_ENTERED', insertDataOption='INSERT_ROWS', body=body).execute()
return result

You can provide the spreadsheet id as a parameter. Next, I’ll demonstrate how to create a simple Django app to store and retrieve our paintings dataset's transactions.

python manage.py startapp paintings_database
from django.db import models
class painting(models.Model):
name = models.CharField(max_length=255)
email = models.EmailField()
phone = models.CharField(max_length=20)
from django import forms
from .models import painting
class paintingForm(forms.ModelForm):
class Meta:
model = painting
fields = ['name', 'email', 'phone']
from django.shortcuts import render
from django.http import HttpResponseRedirect
from .forms import paintingForm
from .google_sheets import read_data, write_data
def painting_list(request):
data = read_data('Sheet1') # Replace 'Sheet1' with the name of your sheet
people = []
for row in data[1:]:
painting = {
'name': row[0],
'email': row[1],
'sale_price': row[2]
}
people.append(painting)
context = {'people': people}
return render(request, 'gs_database/painting_list.html', context)
def painting_create(request):
if request.method == 'POST':
form = paintingForm(request.POST)
if form.is_valid():
data = [[form.cleaned_data['name'], form.cleaned_data['email'], form.cleaned_data['sale_price']]]
write_data('Sheet1', data)
return HttpResponseRedirect('/painting-list/')
else:
form = paintingForm()
context = {'form': form}
return render(request, 'paintings_database/painting_create.html', context)

I defined a class ‘painting’ with different data attributes and links to a form. I then access, write and manipulate data of the form. Overall, This is a great option if you're just starting out, as it's easy to set up and use

Use Google Sheets as a database for a WordPress website

In today’s digital world, people write and share stories regularly on Medium, WordPress, etc. Lucky for us, we can use the Sheets Database add-on and easily connect your WordPress website to a Sheet. You can then store and retrieve data easily. There are diverse plug-ins like SheetDB that can be to link to WordPress.Once linked and activate, the data can be displayed in many formats, like tables, and graphs along with the option to filter and query.

When should you go for this method?

If you want to create a dynamic website that pulls data from a Sheet, or if you want to use a Sheet as a backend for a WordPress plugin. One of the drawbacks of WordPress, however, is that it can be quite resource-intensive. If you are not careful, your WordPress website can end up costing you a lot of money in hosting fees.

Use Google Sheets as an inventory database

Inventory management is crucial for any artist or small businessman who sells physical products online. The data on the number of raw materials, orders in production, supplier orders, and price surges, need to update on a timely basis. A well-organized inventory database can save you time and money by helping you avoid stock-outs, overstock, and inventory shrinkage. The practice of maintaining a database curtailing to inventory & supply chain data is called ‘inventory database management.’

Google Sheets provides a one-stop solution to set up an inventory management app with the AppSheet product. It comes with a lot of in-built features for the specific use case. Hence, for startups and small businesses with limited data sizes should hands down try Google Sheets for their inventory!

Should you use Google Sheets as a relational database?

In short, NO.

If you are unfamiliar with the RDBMS system, let me give a short explanation. A relational database is a database that stores data in tables that are related to each other. In relational databases, multiple tables in a dataset are connected by common fields or keys. Check out the below example image to understand better. Such databases are often used to store large amounts of data that needs to be accessed by multiple users.

Google Sheets weren’t made to serve as a relational database management system and may fail in cases of complex data structures and querying requirements.

Visualize data with Google Sheets.

While we have been focusing on data collection and storage, let’s not forget the next step of analyzing data and capturing patterns. Google Sheets provides multiple tools for visualizing the data in various forms, including bar charts, histograms, pie charts, and much more.

You can detect outliers, compare trends over various time periods and present the insights in a creative way! Below, I quickly show how to create and change chart types in Google sheets for selected data.

Using Google Sheets as a database from Google Forms

Google Forms have become increasingly popular due to their easy interface and quick response time. Did you know you can link your Google form to a spreadsheet database?

Once you link them, the data collected in your form will get automatically added to the sheet.

You can use the below code in the Script editor.

function onFormSubmit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getLastRow();
var data = e.namedValues;
sheet.getRange(row, 1).setValue(data['Name'][0]);
sheet.getRange(row, 2).setValue(data['Email Address'][0]);
sheet.getRange(row, 3).setValue(data['Sale price’’][0]);}

Next, navigate to your Google form's "Responses" tab and select "Create Spreadsheet". You should be able to see an option to "Link Form". That’s it! The data will be organized in rows and can be sorted, filtered, and analyzed using Google Sheets' powerful tools. This is a quick and efficient method to set up a streamlined database for personal projects, collecting survey data, etc.

Automate Data Entry into any Database with Nanonets

Nanonets’powerful OCR and workflow automation platform can quickly automate data entry from any data source with a no-code interface. Nanonets has easy integrations with top CRM, ERP, and Databases, ensuring high data accuracy while extracting and syncing data.

Worry less about updating databases regularly and easily convert unstructured document data into a structured format with Nanonets. Check out Nanonets’ integrations.

Looking to automate document conversion processes, check out our free OCR tools:

Conclusion

We have seen the diverse use-cases of Google sheets a database till now! Remember that the power of Google sheets are best realized when its integrated with other tools of Google ecosystem like Google forms, API, Appstore and much more. But, they still lack many functionalities of traditional datbase and businesses should upgrade as they expand. There may be security concerns for storing sensitive third party information on Google sheets too. Adapt and improvise as per the need of time! Hope you enjoyed the read!

When should you choose Google sheets for building a database?

a major disadvantage to Google Sheets is its limited functionality at an advanced level, which means it’s not very scalable and it’s not the best tool for building more complex databases. because of its similarities to Excel’s user interface (UI), it has a pretty shallow learning curve.