How to use Google Sheets as a database

Let's be honest, Google Sheets isn't the full package for your business's database management. However, it's great for small projects and POCs where simplicity and accessibility matter more than advanced querying and scalability provided by SQL databases.

In this blog post, we'll dive deeper into Google Sheets, examining its capabilities and limitations as a database through the lens of using Google API vs n8n, a source-available workflow automation tool.

Additionally, we'll demonstrate 7 workflow templates where Google Sheets, empowered by n8n, can serve as an unconventional yet effective database solution. You'll learn how to transform data, sync information in Google Sheets, use OpenAI's GPT for data manipulation and explore more features of Google Sheets automation with n8n.

Without further ado, let's get started!

Using Google Sheets as a database: Google Sheets API vs n8n

For the beginning, let's weigh the pros and cons of using Google Sheets as a database, either by interacting with it directly through Google's API or by utilizing n8n.

Google Sheets API: Precise integration vs complex setup

Google Sheets API is perfect for those who prefer a hands-on approach and direct interaction with data.

• Precise integration: With the API, you can precisely manage and manipulate your spreadsheet data. Most manual operations from the Google Sheets interface can be re-created and automated via direct API access.
• Seamless integration with your project: libraries for working with Google Sheets API are available on several programming languages, such as Python, JavaScript, Go and others. This ensures a tight integration of different project parts, created in the same programming framework.

• Complexity: direct API use demands a deeper dive into coding, which includes authenticating, error handling and data formatting.
• Hosting hurdles: when working with Google API programmatically, you need to upload the code, set up scheduling and maintain the server completely yourself.

n8n: Automated workflows vs additional dependency

For engineers looking to streamline and automate their data management n8n serves as a compelling alternative.

• Automated workflows: n8n excels in setting up automated workflows that can integrate nicely with hundreds of other apps and services, making repetitive tasks disappear.
• Simplified setup: Cloud version of n8n works right out of the box, and the self-hosted version is easily configurable, providing a convenient environment for your automation projects.
• Accessible to all: its visual UI allows both seasoned coders and those with less coding skills to easily create complex workflows.

• Additional dependency: introducing n8n adds another layer to your tech stack that could be affected by outages or changes to the service.
• Less granular control: while n8n simplifies things, it might hide away certain details compared to direct API calls.

How to use Google Sheets as a database with the Google API?

To get started, let’s first write a Python script that will create a Google Sheet document via Google API.

Step 1. Obtain Google Service Account Credentials

We described the process of getting the credentials for your Python script in our previous article on importing CSV data into Google Sheets.

Step 2. Use this Python script to create a new Google Sheet document

Let’s create a brand new Google Sheets document, fill it with mock data and apply document formatting:

##### 0 ##### 
# Import dependencies
import gspread
from gspread_formatting import *
from oauth2client.service_account import ServiceAccountCredentials
from random import choice, randint, randrange
from datetime import date
from datetime import timedelta

# Helper function: generate a random date within the last year
def get_random_date():
    start_date = date.today() - timedelta(days=365)
    end_date = date.today()
    random_number_of_days = randrange((end_date - start_date).days)
    random_date = start_date + timedelta(days=random_number_of_days)
    return random_date.isoformat() # Format the date as 'YYYY-MM-DD'


##### 1 ##### 
# Define the scope and credentials
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_name('gsheet-python-demo-43cd7ecdce5f.json', scope) # Update the file name with your Service Account Credentials! 

# Authorize the client
client = gspread.authorize(credentials)

# Create and share a new spreadsheet
spreadsheet = client.create('New Client Data Sheet')
spreadsheet.share('teds.tech.talks@gmail.com', perm_type='user', role='writer') # Put your email here!

##### 2 ##### 
# Example dictionaries for client names and email providers
client_names = ['John', 'Bob', 'Julia', 'Roger']
email_providers = ['example.com', 'mail.com', 'service.org', 'inbox.net']

# Generate the random data
data = [["Client Name", "Email", "Date of Purchase", "Amount"]]
for _ in range(100):
    name = choice(client_names)
    email = f"{name.lower()}.{randint(10, 99)}@{choice(email_providers)}"
    date_of_purchase = get_random_date()  
    amount = randint(10, 200)  # Random amount between 10 and 200
    data.append([name, email, date_of_purchase, amount])

# Select the first worksheet
worksheet = spreadsheet.sheet1

# Populate the sheet with random data
worksheet.update('A1', data)

##### 3 ##### 
# Define conditional formatting rules for amounts greater and less than 50
newrules = [
    ConditionalFormatRule(
        ranges=[GridRange.from_a1_range('D2:D101', worksheet)],
        booleanRule=BooleanRule(
            condition=BooleanCondition('NUMBER_GREATER', ['50']),
            format=cellFormat(backgroundColor=Color(0.698, 0.898, 0.698))
        )
    ),
    ConditionalFormatRule(
        ranges=[GridRange.from_a1_range('D2:D101', worksheet)],
        booleanRule=BooleanRule(
            condition=BooleanCondition('NUMBER_LESS_THAN_EQ', ['50']),
            format=cellFormat(backgroundColor=Color(0.949, 0.906, 0.898) )
        )
    )
]

# Add the conditional formatting rules to the worksheet
rules = get_conditional_format_rules(worksheet)
rules.append(newrules[0])
rules.append(newrules[1])
rules.save()

# Freeze the first row
set_frozen(worksheet, rows=1)

print("Sheet created and data populated successfully.")

This Python script has 4 main sections:

• Section 0 – load all the necessary libraries and define a helper function to assign a random date
• Section 1 – use the API credentials file to create a new Google Sheet document and share it with your main Google account
• Section 2 – populate a new spreadsheet document with random data. We simulate a customer order page
• Section 3 – apply formatting and freeze the first row via the API. This section makes some visual improvements to the spreadsheet.

The script is not that complicated for a simple case, but requires knowledge of Python (or any other common programming language with a ready-made library for a Google API). The complexity of the script, however, can grow rather quickly for real-world projects.

Step 3. Check the final output

If everything went well, you’ll see a new Google Sheets document under the Shared with me section of Google Drive:

You can further analyze the sales data, identify new clients, and create pivot tables or charts.

How to use Google Sheets as a database with n8n?

If you want to use n8n with Google Sheets, you need to create the same service account as described in the previous section.

Why use n8n instead of directly accessing the Google Sheets API?

First of all, n8n makes it easy to access Google Sheets. Take a look at this simple workflow:

With just 2 nodes, you can create a Webhook endpoint that serves Google Sheet content. The hosting effort is minimal and you don’t have to worry about credentials as they are securely stored in n8n.

Secondly, n8n offers a visual approach to integrating different services, which means you can see your data flow and logic in real-time. This is a stark contrast to the often complex and code-heavy process of working directly with the Google Sheets API.

Plus, n8n's built-in error handling can save you from the headaches that typically come with debugging API calls.

Here’s a very quick guide on how to make a database in Google Sheets using n8n:

Step one: Create n8n account

Start off by creating an account on n8n – it's available as a Cloud service, npm module, and Docker image. For this quickstart, n8n recommends using Cloud. A free trial is available for new users.

Step two: Open a workflow template

Once logged in, explore the template gallery to find a workflow that suits your needs. These templates can give you a head start by providing a pre-built structure that you can customize as per your requirements.

Step three: Run the example workflow

After selecting a template, click Copy workflow on the template page and paste it into n8n canvas.

To set up the Google Sheets node, you'll need to connect it to your Google account by creating credentials. Once your credentials are in place, you can configure the node and provide the URL to your Sheets document.

Now you can execute the workflow to see it in action. This step helps you understand how the data flows and processes within n8n.

Step four: Build your workflow from scratch

Once you are familiar with the example workflows, it's time to build your custom workflow and integrate Google Sheets. Drag and drop a Google Sheets node into your workflow. This node will enable you to perform various operations like reading, writing, updating, or deleting data in your sheets.

You can re-use the credentials that you’ve created in the previous step and save time.

And there you have it!

With these broad steps, you're well on your way to utilizing Google Sheets as a flexible database together with n8n's powerful automation capabilities.

Google Sheets database: 7 workflow templates

Let's look at practical examples and learn how to work with Google Sheets in n8n.

1. Identify new Google Sheets rows automatically

This workflow is designed to periodically check the Google Sheet and process any new rows.

This workflow, triggered manually or every 5 minutes, scans a Google Sheets document for unprocessed rows, updating them with the current timestamp in the "Processed" column. Ideal for lightweight database scenarios using Google Sheets, the workflow ensures efficient periodic checks for new records.

• Event registration: If Google Sheet is used to collect event registrations through Google Forms, this workflow can help send personalized welcome emails and materials to participants as soon as they register.
• Order processing: For small businesses using Google Sheets to track orders, this workflow can be set up to identify new orders and kick off the fulfillment process, such as generating invoices, updating inventory or notifying the shipping department.
• Lead tracking: In scenarios where leads are recorded in Google Sheets, this workflow can initiate a sequence to add leads to the CRM system, assign them to sales representatives or send initial contact emails.

2. Transform data in Google Sheets

This n8n workflow demonstrates how Google Sheets can be used as a flexible and convenient database to perform various operations such as appending new records, looking up existing data, updating records and reading data.

The workflow is triggered manually and performs a number of operations. First, it appends a new record to an existing spreadsheet with a random ID, predefined name, rent price and city. Then it searches for records in the same spreadsheet and updates the rent value for those records by incrementing it. Finally, the workflow reads the data from the spreadsheet, which can be used for additional processing or to view the updated data.

• Rental management: Track and update rent prices for properties in different cities and easily add new rental listings with unique IDs.
• Inventory tracking: Append new inventory items, search for specific items by location or category and update quantities or prices as needed.
• Task scheduling: Append new tasks, update the status of existing tasks and get the task list for a specific day or project.

3. Sync new data between two apps

This workflow facilitates the synchronization of new data between two applications. Specifically, it is triggered when the Postgres database is updated and then performs data transformation before appending or updating entries in the Google Sheet.

The workflow starts by listening for updates in the Postgres database table. It then uses a Filter node to exclude all data entries that contain the domain 'n8n.io' in the email addresses. This ensures that only qualified user data is processed. Once filtered, the workflow appends or updates these entries to a Google Sheet table, effectively using it as a simple database.

Users can replace the trigger and action nodes to meet their specific needs. Using sticky notes in the workflow gives users guidance on how to use the template and how to customize it to their needs.

• Syncing newly registered users from the web app to a Google Sheet for marketing campaigns.
• Updating CRM with contact information changes detected in the Postgres customer database.
• Syncing employee information between HR platforms and Google organization sheets.

4. Transform data to be sent to a service

This workflow illustrates a very common scenario when data from the source system requires pre-processing before being sent to the destination service.

Once activated, the workflow fetches customer data which simulates retrieving records from a database or other data source. A subsequent node transforms this data into the appropriate format expected by Google Sheets. In the last step, the transformed data is sent to the Google Sheet in the upsert mode. This mode ensures that new records are added and existing records are updated based on a unique identifier, maintaining the integrity of the data in the sheet.

• Maintaining a centralized customer database for a small business with the ability to easily update customer records.
• Managing inventory for a boutique or small online store where stock levels can be updated as sales are made or new items arrive.
• Aggregating feedback or survey responses in one convenient place for analysis and follow-up.

5. Sync Google Sheets data with MySQL

This n8n workflow automates data synchronization between Google Sheets and a MySQL database, effectively using Google Sheets as a form to collect data that is then reflected in a more robust SQL database.

At the core of the workflow is the "Compare Datasets" node, which identifies new and updated records by comparing the data from the Google Sheet to the records in the MySQL database. Depending on the results of the comparison, the workflow can insert or update records in MySQL, update the status in Google Sheets to reflect changes and ensure that the MySQL database sources are synchronized.

• Event management: Track event inquiries submitted via Google Form, sync with MySQL for team collaboration and reporting.
• Sales lead management: Capture sales lead information on Google Sheets and sync with MySQL CRM to assign a sales team and track engagement.
• HR recruitment: Manage job applications received via Google Form, syncing applicant data with MySQL database for review and processing by the HR team.

6. Create Salesforce accounts based on Google Sheets data

This n8n workflow automates the data integration process between Google Sheets and Salesforce, using Google Sheets as the source of account data for use in Salesforce.

The workflow starts by reading data from a Google Sheet and checking against Salesforce for existing accounts. It filters out duplicates, creates new accounts as necessary, and updates contact information. This automation keeps Salesforce accounts and contacts up-to-date with the Google Sheets data.

• Syncing new client information from a marketing campaign managed in Google Sheets directly into Salesforce in the form of new accounts and contacts.
• Regularly updating Salesforce records with the latest data from Google Sheets used to track customer interactions or sales activity.
• Automating the lead qualification process using Google Sheets to collect raw lead data and create the corresponding accounts and contacts in Salesforce once qualified.

7. Enrich company data with website content using OpenAI's GPT and Google Sheets

This advanced n8n workflow demonstrates a comprehensive process for automatically enriching company data with website content using OpenAI's GPT Large Language Model (LLM) and Google Sheets.

The workflow manually triggers to read domain names from Google Sheets and batch-processes them. It retrieves website content via HTTP requests, then an AI model analyzes this content to extract company details. Finally, the enriched data is formatted and updated back into the Google Sheets, with the process repeating for all batches until completion.

• Market research teams can use this workflow to quickly gather insights about potential clients or competitors, updating their databases with key company information.
• Sales and marketing professionals can use it to enrich leads with additional context before reaching out, enabling more personalized and effective outreach strategies.
• HR departments can automate the process of gathering information about potential employers or partners to help with decision-making or meeting preparation.
• Investment firms can use this workflow to conduct due diligence on potential investment opportunities, quickly gaining insight into a company's market positioning and audience.
• Web developers or digital agencies can use this to catalog and categorize their clients’ websites based on industry and services offered.
• Content creators and strategists can leverage this workflow to analyze trends in value propositions and messaging across industries.

Wrap Up

Today we touched on the broad topic of using Google Sheets as a small but simple and robust database:

• First, we showed how to start using Google Sheets as a database via the Google API.
• Second, we explored how to employ Google Sheets database capabilities with the help of n8n.
• Finally, we gave an overview of 7 n8n workflows that utilize Google Sheets in different ways. Each workflow overview contains a section with real-world use cases.

So what's best for your project: using Google Sheets as a database directly via the Google API or with the help of n8n?

The choice depends on your specific demands and your preference for control versus convenience. If you crave precise, fine-tuned updates and don't mind the extra coding, then the Google Sheets API is a powerful tool for you.

If you prefer to focus on efficiency and want to integrate multiple systems with minimal code, n8n's workflow automation might just be the solution you're looking for.

Choose the path that aligns with your project goals and technical expertise!

What’s next?

It's important to note that while Google Sheets may be suitable for certain cases, data engineers typically work with more powerful database management systems such as MySQL, PostgreSQL or cloud-based solutions such as Google BigQuery to store, process, and analyze large-scale data.

The use of Google Sheets in such cases is often limited to specific situations where their simplicity meets the requirements of the task at hand.

Here are some further readings:

• How to connect Google Sheets and MySQL database;
• An overview of pre-built nodes for Google Apps.

Finally, choose from our cloud plans to get started now or explore the powerful features of the Enterprise edition, trusted by many large corporations.