Using Google Sheets as Database: A Complete Guide

Amirali Marvian Mashhad

Aug 24, 2021 . 7 min read

Is Google Sheets a widely used tool within your organization?

Do you and your colleagues use Google Sheets frequently to store data and collaborate?

Have you ever thought about using Google Sheets as a database for your business intelligence (BI) tool to generate engaging graphs and charts automatically?

If the answer to the above questions is yes, keep reading this article as I take you through a step-by-step guide on how to use Google Sheets as a database in Holistics as well as how to generate charts and reports automatically using your Google Sheets database.

What is Holistics?

Holistics is a self-service BI tool that enables business users to make sense of their data and answer business questions without bothering the technical teams or writing SQL. Holistics helps you to consolidate all your data from various sources and into one place for a comprehensive view. Given that your data operations might change according to the growth of your company, we believe in building a modern analytics stack, where you can bend the tools according to your needs.

For more information on how Holistics work, please visit this page or start a free 14-day trial with Holistics now to generate charts automatically in Google Sheet! No credit card is required.

Before we get into the detailed steps, let’s briefly look at what Google Sheets is and go through some of the pros and cons of using Google Sheets as a database.

Pros of Using Google Sheets as A Database

Connectivity: Google Sheets allows you to access it anytime anywhere as long as you're logged in to your Google account. There is a minimal risk of losing your data as it is stored safely in Google Cloud.
Permission or Access Control: Using Google Sheets you can control access to your data by adding or revoking user access with just a few clicks.
Pricing: Google Sheets is free of charge and this is perhaps one of its main pros compared to its competitor such as Microsoft Excel that requires subscription.

Cons of Using Google Sheets as A Database

Scalability: When your data increases, you’ll find it difficult to maintain the data integrity in Google Sheets. You’ll start seeing duplicate records or inaccurate data. Google Sheet isn’t scalable in the long-term.
Query Options: Compared to other Database Management System (DBMS) that enable users to handle large amounts of data easily with advanced queries, Google Sheets Query mechanism is limited.

How to use Google Sheet as Database

Integrating your Google Sheet with Holistics is done in 5 simple steps. The diagram below indicates the high-level steps and later on in the article, we’ll elaborate on each step in more details.

1. Connecting to Data Warehouse

Holistics requires you to use a SQL data warehouse. In a nutshell, a data warehouse is a type of database that is designed to enable BI and analytics initiatives. Data warehouses normally contain a huge amount of historical data. At the moment, Holistics supports 13 SQL data warehouses including major ones like:

PostgreSQL
MySQL
Microsoft SQL Server
Google BigQuery
Amazon Redshift
Snowflake
Presto
Clickhouse
Amazon Athena

To connect your data warehouse to Holistics, first login to your Holistics account and click on the “+” icon on top of the page and select “Connect Data Sources”. Then select “+ New Data source” and you can choose your data warehouse from the full list of data warehouses in the drop down:

Important Note: in Holistics, you have the option to connect to your noSQL databases. However, you still need a SQL database to load data from such databases into your SQL database using Holistics. This means you can use your google spreadsheet database and load it to the SQL database of your choice in Holistics.

2. Creating a Data Model

Once your data warehouse is successfully connected to Holistics, the next step is for you to create your data model. This is the step where you add in your Google spreadsheet database. To do this, click on “Data Modelling” in the header and then click on “+Create Data Model” on top right and select “Data Import”. Currently the following data import options available:

Google Sheet
MongoDB
Microsoft SQL Server
PostgreSQL
MySQL
PipeDrive
Google BigQuery
CSV
Facebook Ads

Select “Google Sheets” (Figure 2) and then paste the Google Source URL for validation. Once the URL validation is successful, click on “Create” and your data model is created. See Figure 3.

Figure 3: Google Sheet data model validation

3. Adding Relationships between Models

Adding relationships is crucial when you want to combine fields from various data models.By creating relationships you basically specify how different tables should be joined together. Defining relationships is required for Holistics to build the right SQL to create reports.

Currently, there are three types of relationships supported by Holistics:

Relationship	Definition
Many-to-one (n-1)	One or more records in a table can be associated with one record in another table.
One-to-many (1-n)	One record in a table can be associated with one or more records in another table.
One-to-one (1-1)	One record in a table is associated with one and only one record in another table.

To add relationships to your data model, click on “Data Modeling” and then click select “Relationships” in the vertical sidebar navigation on the left. Click on “Add Relationship” and you can start defining your relationships.

Figure 4: Adding Relationship to Data Model

You might’ve noticed that many-to-many relationship is missing from the options available. In Holistics, you can’t directly define a many-to-many relationship. The work around for this will be to have a junction model (a new data model) in order to turn relationships into 1-n-1.

4. Creating Dataset

A dataset in Holistics can contain many data models. Datasets enable users to explore several data models together using interactive charts, graphs and tables. Another use of datasets is enabling non-technical users to self-explore the data. Take note that in order to use the reporting features you must create a dataset using the data models that you want to analyse.

To create your dataset, click on “Reporting” and in the vertical sidebar navigation on the left, click on “Datasets”. After that, click on “+ Create” on the top right hand side and select “Add New Dataset”.

A window will pop up where you can select the data model (s) from the connected data sources. Enter a name for your dataset and click on “Save”. In this case your data model is the Google Sheet that you created in step 2.

Figure 6: Selecting Data Models for Dataset

5. Creating the First Report

Once your dataset is created and ready, you are all set to start your data exploration journey and create reports. Holistics allows you to explore and visualize your data using various types of charts, tables and heatmaps.

Figure 7: Data Visualization in Holistics

Figure 8: A Metric Sheet on Overview of Company's KPIs

Start a free 14-day with Holistics now to generate charts automatically in Google Sheet! No credit card is required.

How to Automatically Generate Charts and Reports via Google Sheets?

In Holistics, you can receive the latest reports and dashboards directly in your email, Slack or Google Spreadsheet using the scheduling feature. For this you need to have either “Admins'' or “Analysts” roles.

You might ask, what is the benefit of getting your reports and dashboards into Google Sheets and what can you do with them? Well, in Holistics, scheduling Google Sheets enables you to store raw data from your database into Google Sheets. This is especially useful if you want to perform additional calculations and analysis within Google Sheets.

Figure 9: Automatically Updating Google Sheets from Your Database

Frequently Asked Questions

Q: Does Holistics store my raw data?

A: No, Holistics does not store your raw data. It just stores metadata and cache to your database.

Q: Can I connect to a local SQL database for testing purposes?

A: Since Holistics is a cloud-based application, you will either need a public accessible IP or use a SSH reverse tunnel to connect your localhost to Holistics app.

Q: When I schedule Google Sheets in Holistics, will the entire Google Spreadsheet document (with multiple sheets) be overriden?

A: No. Each schedule will export data from a single chart widget into a single sheet in your spreadsheet document. The other sheets will remain intact.

Q: How does Holistics connect to my Google Sheets account securely?

A: Holistics links the Google credential token only to your Holistics account and will not share the token with anyone. It means if your colleagues also have a Holistics account, they will not reuse your token but instead, they will need to go through the process of generating their own token.