{"id":1077,"date":"2024-11-12T17:59:53","date_gmt":"2024-11-12T17:59:53","guid":{"rendered":"https:\/\/tapegraph.io\/blog\/?p=1077"},"modified":"2025-07-13T19:47:37","modified_gmt":"2025-07-13T19:47:37","slug":"how-to-run-sql-queries-on-google-sheets-data-and-create-dynamic-dashboards","status":"publish","type":"post","link":"https:\/\/tapegraph.io\/blog\/how-to-run-sql-queries-on-google-sheets-data-and-create-dynamic-dashboards\/","title":{"rendered":"How to Run SQL Queries on Google Sheets Data and Create Dynamic Dashboards"},"content":{"rendered":"\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"2692\" height=\"1762\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/google-sheet-1.jpg\" alt=\"\" class=\"wp-image-1085\"\/><\/figure>\n\n\n\n<!--more-->\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Why Run SQL Queries on Google Sheets Data?<\/h3>\n\n\n\n<p>Whether you&#8217;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.<\/p>\n\n\n\n<p>Let\u2019s explore how to set up SQL querying on Google Sheets and create real-time dashboards to visualize your data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step-by-Step Guide: Setting Up SQL Queries on Google Sheets<\/h3>\n\n\n\n<p>To run SQL queries on Google Sheets, you\u2019ll first need to connect your sheet to a Tapegraph. Below is a general process that can help you achieve this:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">1. Create a Google Cloud Service Account<\/h4>\n\n\n\n<p>Start by creating a service account in Google Cloud to manage permissions. Here\u2019s how:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the <a href=\"https:\/\/console.cloud.google.com\/\" target=\"_blank\" rel=\"noopener\" title=\"\">Google Cloud Console<\/a>.<\/li>\n\n\n\n<li>Navigate to <strong>&#8220;IAM &amp; Admin&#8221;<\/strong> &gt; <strong>&#8220;Service Accounts&#8221;<\/strong>.<\/li>\n\n\n\n<li>Click <strong>&#8220;Create Service Account&#8221;<\/strong> and follow the prompts.<\/li>\n\n\n\n<li>After the service account is set up, create a JSON key. This key will give you access to your Google Sheets data.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1600\" height=\"1207\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/Adding-a-Service-Account-on-Google-Cloud-1.jpg\" alt=\"Adding a Service Account on Google Cloud\" class=\"wp-image-1079\" style=\"width:690px;height:auto\"\/><figcaption class=\"wp-element-caption\">Adding a Service Account on Google Cloud<\/figcaption><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">2. Share Your Google Sheet with the Service Account<\/h4>\n\n\n\n<p>Google Sheets requires permission to be shared with the service account email address. Share the sheet just like you would with any user.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">3. Connect Google Sheets as a Data Source<\/h4>\n\n\n\n<p>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).&nbsp;<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1600\" height=\"1207\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/Adding-a-New-Table-on-the-Tapegraph-1.jpg\" alt=\"Adding a New Table on the Tapegraph\" class=\"wp-image-1081\"\/><figcaption class=\"wp-element-caption\">Adding a new Table on Tapegraph<\/figcaption><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">4. Writing SQL Queries<\/h4>\n\n\n\n<p>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.&nbsp;<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">5. Exporting Query Results<\/h4>\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Real-World Example: Running SQL on Google Sheets for a Restaurant<\/h3>\n\n\n\n<p>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:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"2692\" height=\"1762\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/google-sheet.jpg\" alt=\"Data collected in Google Sheet\" class=\"wp-image-1083\"\/><figcaption class=\"wp-element-caption\">Data collected in Google Sheet<\/figcaption><\/figure>\n\n\n\n<p>This Google Sheet is now connected to Tapegraph as it was described previously. In Tapegraph\u2019s interface, it looks like the following table:<br><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"2954\" height=\"1770\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/tapegraph-table.jpg\" alt=\"Table on Tapegraph\" class=\"wp-image-1087\"\/><figcaption class=\"wp-element-caption\">Table on Tapegraph<\/figcaption><\/figure>\n\n\n\n<p>This data can now be seamlessly queried.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Querying Data with SQL<\/h3>\n\n\n\n<p>Let&#8217;s write a simple query that returns a number of guests who came to the restaurant in August:<\/p>\n\n\n\n<div style=\"height: 250px; position:relative; margin-bottom: 50px;\" class=\"wp-block-simple-code-block-ace\"><div style=\"position:absolute;top:-20px;right:0px;cursor:pointer\" class=\"copy-simple-code-block\"><span class=\"dashicon dashicons dashicons-admin-page\"><\/span><\/div><pre class=\"wp-block-simple-code-block-ace\" style=\"position:absolute;top:0;right:0;bottom:0;left:0\" data-mode=\"sql\" data-theme=\"sqlserver\" data-fontsize=\"14\" data-lines=\"Infinity\" data-showlines=\"true\" data-copy=\"true\">SELECT SUM(Breakfast) + SUM(Dinner) + SUM(Lunch) AS Guests_total\nFROM {Guest_Flow_Data}\nWHERE `Year` = 2024 AND `Month` = 'August';<\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<p>This is the data that the query returns:&nbsp;<br><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1974\" height=\"1460\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/tapegraph-SQL.jpg\" alt=\"Data Routine. SQL Queries on Google Sheets\" class=\"wp-image-1088\"\/><figcaption class=\"wp-element-caption\">Data routine<\/figcaption><\/figure>\n\n\n\n<p>The following query will return the number of guests from different sources:<br><\/p>\n\n\n\n<div style=\"height: 250px; position:relative; margin-bottom: 50px;\" class=\"wp-block-simple-code-block-ace\"><div style=\"position:absolute;top:-20px;right:0px;cursor:pointer\" class=\"copy-simple-code-block\"><span class=\"dashicon dashicons dashicons-admin-page\"><\/span><\/div><pre class=\"wp-block-simple-code-block-ace\" style=\"position:absolute;top:0;right:0;bottom:0;left:0\" data-mode=\"sql\" data-theme=\"sqlserver\" data-fontsize=\"14\" data-lines=\"Infinity\" data-showlines=\"true\" data-copy=\"true\">WITH t1 AS (\n   SELECT COALESCE(SUM(`Passed by`), 0) +\n      COALESCE(SUM(`Instagram`), 0) +\n      COALESCE(SUM(`Word of mouth`), 0) +\n      COALESCE(SUM(`Google Maps`), 0) +\n      COALESCE(SUM(`Google search`), 0) +\n      COALESCE(SUM(`Return visits`), 0) +\n      COALESCE(SUM(`Tik Tok`), 0) AS Tables_total\n   FROM\n       {Guest_Flow_Data}\n   WHERE\n       `Year` = 2024 AND `Month` = 'August'\n),\nt2 AS (\n   SELECT 'Passed by' AS Source, SUM(`Passed by`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n   UNION ALL\n   SELECT 'Instagram' AS Source, SUM(`Instagram`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n   UNION ALL\n   SELECT 'Word of mouth' AS Source, SUM(`Word of mouth`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n   UNION ALL\n   SELECT 'Google Maps' AS Source, SUM(`Google Maps`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n   UNION ALL\n   SELECT 'Google search' AS Source, SUM(`Google search`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n   UNION ALL\n   SELECT 'Return visits' AS Source, SUM(`Return visits`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n   UNION ALL\n   SELECT 'Tik Tok' AS Source, SUM(`Tik Tok`) AS Tables\n   FROM {Guest_Flow_Data}\n   WHERE `Year` = 2024 AND `Month` = 'August'\n),\nt3 AS (\n   SELECT SUM(Tables) AS Tables_sum\n   FROM t2\n),\nt4 AS\n(\nSELECT Source, Tables, t1.Tables_total, ROUND((Tables \/ t1.Tables_total) * 100, 2) AS Percentage\nFROM t2, t1, t3\n)\nSELECT * FROM t4 ORDER BY Tables;<\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1974\" height=\"1460\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/tapegraph-SQL-Guests-by-Sources.jpg\" alt=\"Guests by Sources. SQL Queries on Google Sheets\" class=\"wp-image-1090\"\/><figcaption class=\"wp-element-caption\">Guests by Sources<\/figcaption><\/figure>\n\n\n\n<p>We can visualize the data.<br><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1570\" height=\"1216\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/tapegraph-Viz-Guests-by-Sources.jpg\" alt=\"Tapegraph Bar Chart\" class=\"wp-image-1092\"\/><figcaption class=\"wp-element-caption\">Guests by Sources Bar Chart<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Building Dashboards from SQL Queries on Google Sheets<\/h3>\n\n\n\n<p>We&#8217;ve created a new dashboard, allowing us to organize all the visualizations exactly how we want.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"2026\" height=\"1452\" src=\"https:\/\/tapegraph.io\/blog\/wp-content\/uploads\/2024\/11\/tapegraph-Dashboard-Guest-Information.jpg\" alt=\"Dashboard on Tapegraph\" class=\"wp-image-1094\" style=\"width:691px;height:auto\"\/><figcaption class=\"wp-element-caption\">Dashboard<\/figcaption><\/figure>\n\n\n\n<p>For this particular case, <a href=\"https:\/\/tapegraph.io\/share\/dashboard\/b801358935e5\/zjszBFTUOIoOwxnpVwaNkowfvJKIBRphB8u4bpALd1lA6Q6c\" target=\"_blank\" rel=\"noopener\" title=\"\">the dashboard created looks like this<\/a>.&nbsp;<\/p>\n\n\n\n<p>The dashboard updates automatically when the data in the Google Sheet changes.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Benefits and Applications<\/h3>\n\n\n\n<p>The integration of Google Sheets with Tapegraph opens up numerous benefits and versatile applications across industries.<\/p>\n\n\n\n<p><strong>Benefits:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Centralized Data Management:<\/strong> Consolidate data from multiple sheets into one source, making it easier to manage and analyze.<\/li>\n\n\n\n<li><strong>Enhanced Analytics:<\/strong> With SQL querying, even complex datasets can be manipulated, filtered, and joined, allowing for more in-depth analysis.<\/li>\n\n\n\n<li><strong>Real-Time Insights:<\/strong> Dashboards update automatically as data in Google Sheets is modified, ensuring that decision-makers always have the latest information.<\/li>\n<\/ul>\n\n\n\n<p><strong>Applications:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Business Analytics:<\/strong> From tracking sales performance to customer behavior, companies can create detailed, up-to-date dashboards.<\/li>\n\n\n\n<li><strong>Operations Monitoring:<\/strong> Real-time dashboards can help businesses monitor operations, such as inventory levels or service efficiency.<\/li>\n\n\n\n<li><strong>Financial Reporting:<\/strong> Financial data across different departments can be unified and visualized, providing clear insights for budgeting and forecasting.<\/li>\n\n\n\n<li><strong>Marketing Analysis:<\/strong> Track campaign performance, customer demographics, and other key marketing metrics all in one place.<\/li>\n<\/ul>\n\n\n\n<p>The integration transforms Google Sheets from a simple data entry tool into a powerful analytics platform, enabling businesses to make smarter, data-driven decisions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p>Running SQL queries on Google Sheets data can transform simple spreadsheets into powerful data analysis tools. Whether you\u2019re 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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Set up SQL on Google Sheets today and start building real-time, dynamic dashboards for your business needs.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[14,28,27,16],"tags":[43,40,6,42,41,7,36,3],"class_list":["post-1077","post","type-post","status-publish","format-standard","hentry","category-visualization","category-how-to-guides","category-integrations","category-tech","tag-beginner","tag-dashboards","tag-googlesheets","tag-how-to","tag-real-time-data","tag-sql","tag-tapegraph","tag-visualization"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts\/1077","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/comments?post=1077"}],"version-history":[{"count":15,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts\/1077\/revisions"}],"predecessor-version":[{"id":1107,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts\/1077\/revisions\/1107"}],"wp:attachment":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/media?parent=1077"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/categories?post=1077"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/tags?post=1077"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}