How to Run SQL Queries on Google Sheets Data and Create Dynamic Dashboards

Google Sheets is a popular tool for businesses worldwide due to its accessibility and ease of use. But what if you could run SQL queries on your Google Sheets data, making it a powerful tool for deeper analysis? This guide will walk you through how to run SQL queries on Google Sheets data, helping you create dynamic dashboards for better data visualization and analysis.

Why Run SQL Queries on Google Sheets Data?

Whether you’re tracking customer information, analyzing sales figures, or monitoring operational metrics, being able to run SQL queries directly on Google Sheets can significantly enhance your data management capabilities. You can filter, manipulate, and join data from multiple sheets, gaining deeper insights and enabling more informed decision-making.

Let’s explore how to set up SQL querying on Google Sheets and create real-time dashboards to visualize your data.

Step-by-Step Guide: Setting Up SQL Queries on Google Sheets

To run SQL queries on Google Sheets, you’ll first need to connect your sheet to a Tapegraph. Below is a general process that can help you achieve this:

1. Create a Google Cloud Service Account

Start by creating a service account in Google Cloud to manage permissions. Here’s how:

• Go to the Google Cloud Console.
• Navigate to “IAM & Admin” > “Service Accounts”.
• Click “Create Service Account” and follow the prompts.
• After the service account is set up, create a JSON key. This key will give you access to your Google Sheets data.

2. Share Your Google Sheet with the Service Account

Google Sheets requires permission to be shared with the service account email address. Share the sheet just like you would with any user.

3. Connect Google Sheets as a Data Source

Once your Google Sheet is shared, you can connect it as a data source. On Tapegraph click the New Data Source button, select connector Google Sheets and your Google Service Account Key (the JSON file). 

After the new data source is created you may add a new table by putting the link to your Google Sheet and selecting the value range.

4. Writing SQL Queries

Now you can use your Google Sheet as a data source. Tapegraph allows you to write SQL queries directly against your Google Sheets data. You can manipulate, filter, and join data as with any SQL database. 

5. Exporting Query Results

After executing your SQL queries, you might want to use the results in other applications. Tapegraph allows you to export the query results in TSV format. Exporting as TSV provides flexibility, enabling you to open the data in spreadsheet programs, import it into databases, or process it with scripts.

Real-World Example: Running SQL on Google Sheets for a Restaurant

Imagine a restaurant where hosts gather guest information, including how they found out about the restaurant, their arrival times, and demographic details. The hosts then input this data into a Google Sheet that resembles the following:

This Google Sheet is now connected to Tapegraph as it was described previously. In Tapegraph’s interface, it looks like the following table:

This data can now be seamlessly queried.

Querying Data with SQL

Let’s write a simple query that returns a number of guests who came to the restaurant in August:

SELECT SUM(Breakfast) + SUM(Dinner) + SUM(Lunch) AS Guests_total
FROM {Guest_Flow_Data}
WHERE `Year` = 2024 AND `Month` = 'August';

This is the data that the query returns: 

The following query will return the number of guests from different sources:

WITH t1 AS (
   SELECT COALESCE(SUM(`Passed by`), 0) +
      COALESCE(SUM(`Instagram`), 0) +
      COALESCE(SUM(`Word of mouth`), 0) +
      COALESCE(SUM(`Google Maps`), 0) +
      COALESCE(SUM(`Google search`), 0) +
      COALESCE(SUM(`Return visits`), 0) +
      COALESCE(SUM(`Tik Tok`), 0) AS Tables_total
   FROM
       {Guest_Flow_Data}
   WHERE
       `Year` = 2024 AND `Month` = 'August'
),
t2 AS (
   SELECT 'Passed by' AS Source, SUM(`Passed by`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
   UNION ALL
   SELECT 'Instagram' AS Source, SUM(`Instagram`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
   UNION ALL
   SELECT 'Word of mouth' AS Source, SUM(`Word of mouth`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
   UNION ALL
   SELECT 'Google Maps' AS Source, SUM(`Google Maps`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
   UNION ALL
   SELECT 'Google search' AS Source, SUM(`Google search`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
   UNION ALL
   SELECT 'Return visits' AS Source, SUM(`Return visits`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
   UNION ALL
   SELECT 'Tik Tok' AS Source, SUM(`Tik Tok`) AS Tables
   FROM {Guest_Flow_Data}
   WHERE `Year` = 2024 AND `Month` = 'August'
),
t3 AS (
   SELECT SUM(Tables) AS Tables_sum
   FROM t2
),
t4 AS
(
SELECT Source, Tables, t1.Tables_total, ROUND((Tables / t1.Tables_total) * 100, 2) AS Percentage
FROM t2, t1, t3
)
SELECT * FROM t4 ORDER BY Tables;

We can visualize the data.

Building Dashboards from SQL Queries on Google Sheets

We’ve created a new dashboard, allowing us to organize all the visualizations exactly how we want.

For this particular case, the dashboard created looks like this. 

The dashboard updates automatically when the data in the Google Sheet changes.

Benefits and Applications

The integration of Google Sheets with Tapegraph opens up numerous benefits and versatile applications across industries.

Benefits:

• Centralized Data Management: Consolidate data from multiple sheets into one source, making it easier to manage and analyze.
• Enhanced Analytics: With SQL querying, even complex datasets can be manipulated, filtered, and joined, allowing for more in-depth analysis.
• Real-Time Insights: Dashboards update automatically as data in Google Sheets is modified, ensuring that decision-makers always have the latest information.

Applications:

• Business Analytics: From tracking sales performance to customer behavior, companies can create detailed, up-to-date dashboards.
• Operations Monitoring: Real-time dashboards can help businesses monitor operations, such as inventory levels or service efficiency.
• Financial Reporting: Financial data across different departments can be unified and visualized, providing clear insights for budgeting and forecasting.
• Marketing Analysis: Track campaign performance, customer demographics, and other key marketing metrics all in one place.

The integration transforms Google Sheets from a simple data entry tool into a powerful analytics platform, enabling businesses to make smarter, data-driven decisions.

Conclusion

Running SQL queries on Google Sheets data can transform simple spreadsheets into powerful data analysis tools. Whether you’re managing guest information in a restaurant, analyzing sales trends, or creating financial reports, the ability to query Google Sheets with SQL provides flexibility, efficiency, and deeper insights. Set up SQL on Google Sheets today and start building real-time, dynamic dashboards for your business needs.