Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Metrics Dashboard in Google Sheets with ChatGPT

Cody Schneider••9 min read

Using ChatGPT to build a formulas-on-demand Google Sheet is a powerful way to transform your raw data into a functional dashboard. You get the speed of AI combined with the flexibility and familiarity of a spreadsheet. This article provides a step-by-step guide to creating your own metrics dashboard in Google Sheets using ChatGPT to handle the heavy lifting of formula-writing and scripting.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

First Things First: Why Use Google Sheets for a Dashboard?

Before jumping into the “how,” let’s briefly touch on the “why.” Building a dashboard in Google Sheets is popular for a few simple reasons:

  • It’s free. You don't need expensive business intelligence software to get started.
  • It’s collaborative. Sharing insights with your team is as easy as sending a link.
  • It's familiar. Most of us have spent some time in a spreadsheet, so the environment doesn’t feel intimidating.

The primary downside has always been the manual effort. Crafting complex formulas with QUERY, VLOOKUP, or nested IF statements can be time-consuming and frustrating. This is where ChatGPT changes the game, acting as your co-pilot who can instantly generate these formulas for you.

Step 1: Get Your Data Ready

An amazing dashboard is useless without clean, reliable data. Before you even open ChatGPT, you need to gather and structure your source information.

Export Your Raw Data

Your performance data lives across different platforms. The first task is to consolidate it. This usually involves exporting CSV files from your key tools:

  • Web Analytics: Go to Google Analytics and export a report showing traffic by source/medium, sessions, users, and conversions over your desired time frame.
  • Ad Platforms: Export campaign performance data from Facebook Ads, Google Ads, or LinkedIn Ads. Include metrics like spend, impressions, clicks, and conversions.
  • Sales & CRM: If relevant, export lead or deal data from platforms like HubSpot or Salesforce.

Structure Your Data Inside Google Sheets

Once you have your data, create a new Google Sheet. Best practice is to have dedicated tabs to keep things organized.

  1. Create a tab and name it "Raw Data". This is where you’ll paste all your exported information.
  2. Create a second tab and name it "Dashboard". This will be where you build your charts and visualizations.

In your "Raw Data" tab, aim for a simple table format. A column for dates is essential for time-based analysis. For this tutorial, let’s imagine we have a simple content marketing dataset with the following columns:

  • Column A: Date
  • Column B: Article Title
  • Column C: Source (e.g., Google, Social, Direct)
  • Column D: Sessions
  • Column E: Conversions (e.g., newsletter sign-ups)

Having a well-organized data source makes it much easier to give ChatGPT clean instructions.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 2: Using ChatGPT to Generate Formulas

Now for the fun part. Think of ChatGPT as your personal formula expert. Your job is to describe the metric you need, and its job is to write the Google Sheets formula that calculates it.

The key to success is giving clear, specific prompts. Provide context about your sheet names and column layouts.

A bad prompt is vague: "Give me a formula for total sessions."

A good prompt provides critical context: "In my Google Sheet, I have a tab named 'Raw Data'. I need a formula to calculate the sum of all values in Column D, which is named 'Sessions'."

Responding to that prompt, ChatGPT will give you the perfect formula:

=SUM('Raw Data'!D:D)

You can then just copy and paste this directly into your “Dashboard” tab.

Step 3: Building Out Your Dashboard Components

Let's build a functional marketing dashboard piece by piece using this method.

Create a "Snapshot" KPI Section

Most dashboards start with a high-level overview of Key Performance Indicators (KPIs). Let's use ChatGPT to generate the formulas for a top-line metrics section.

Total Sessions

  • Your Prompt: "My sessions are in column D of my 'Raw Data' tab. Write a formula to sum them all."
  • ChatGPT's Formula: =SUM('Raw Data'!D:D)

Total Conversions

  • Your Prompt: "Similar to before, give me a formula to sum my total 'Conversions' which are in column E of the 'Raw Data' tab."
  • ChatGPT's Formula: =SUM('Raw Data'!E:E)

Overall Conversion Rate

  • Your Prompt: "I need a formula to calculate the overall conversion rate. It should be the sum of Column E ('Conversions') divided by the sum of Column D ('Sessions') in my 'Raw Data' tab. Please format the result as a percentage."
  • ChatGPT's Formula: =SUM('Raw Data'!E:E)/SUM('Raw Data'!D:D)

After pasting that last formula, click on the cell and select Format > Number > Percent in the Google Sheets menu to properly style it.

You can arrange these in your "Dashboard" tab to create a clean, glanceable KPI summary.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Visualize Your Key Metrics

Next, let's create a summary table that we can use to build charts. This is where ChatGPT starts to seriously save you time by generating more complex formulas.

Sessions by Source

We need a table that aggregates total sessions for each traffic source (Google, Social, etc.). Instead of doing this manually with a pivot table, let’s ask ChatGPT.

  • Your Prompt: "In my 'Raw Data' tab, Column C contains the traffic source and Column D contains the sessions. I want to build a summary table. Can you give me a formula that finds all unique sources in Column C and lists them vertically?"
  • ChatGPT's Formula: =UNIQUE('Raw Data'!C2:C)

Paste this formula into a cell (e.g., A10) on your dashboard sheet. Now, we need the session counts next to them.

  • Your Prompt: "Great. Now I need a formula for the cell next to the first source (in cell A10). It should sum the sessions from 'Raw Data'!D:D but only for the source listed in cell A10."
  • ChatGPT's Formula: =SUMIF('Raw Data'!C:C, A10, 'Raw Data'!D:D)

Paste this in cell B10, then drag the little blue square at the corner of the cell downwards to apply the formula for each traffic source.

You now have a clean summary table! To turn this into a pie chart:

  1. Select your summary table data (e.g., A10:B13).
  2. In the menu, click Insert > Chart.
  3. Google Sheets will default to a chart type, choose Pie chart from the Chart editor on the right if it isn’t already selected.

Sessions Over Time (Line Chart)

What about tracking trends? The QUERY function is perfect for this, but its syntax is tricky. Let's offload it to ChatGPT.

  • Your Prompt: "Write a Google Sheets QUERY formula. It needs to reference my 'Raw Data'!A:D range. I want to group the data by date (Column A) and sum the sessions (Column D) for each date. Please select column A and the sum of column D, and make sure to ignore any empty rows."
  • ChatGPT's Formula:

=QUERY('Raw Data'!A:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A LABEL A 'Date', SUM(D) 'Sessions'")

This single, powerful formula creates an updated summary table of sessions per day. All you have to do is copy it into your "Dashboard" tab, select the resulting data, and click Insert > Chart > Line chart.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 4: Automate Tasks with Google Apps Script

What if you want to add functionality to your spreadsheet, like a refresh button that sorts all your data? This requires Google Apps Script (basically JavaScript for Google Workspace). You probably don't know how to write it, but ChatGPT does.

  1. Open the script editor by going to Extensions > Apps Script.
  2. Erase any placeholder code in the Code.gs file.
  3. Go to ChatGPT and enter your prompt.

For example:

  • Your Prompt: "Write a Google Apps Script function named sortData. It should get the sheet named Raw Data, and sort the entire data range based on the first column (the date column), in descending order so the most recent dates are on top. Skip the first row, which are the headers."
  • ChatGPT's Generated Script:
function sortData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Raw Data"),
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()),
  range.sort({column: 1, ascending: false}),
}

Copy and paste this script into the Apps Script editor and click the floppy disk "Save project" icon. Now, you can run this automation anytime. You can even attach it to a button on your dashboard for a one-click refresh.

Important Tip: Always check ChatGPT's Work

ChatGPT is an incredibly powerful tool, but it's not infallible. It's an assistant, not a replacement for your judgment. Here are a few ways to keep your data accurate:

  • Spot-Check the Math: For any new formula ChatGPT gives you, find a small sample of your raw data and do the calculation manually. Does your number match what the formula produced?
  • Provide Context: The more detailed your prompts, the better the output. Mention your exact tab names and describe what each column represents. For complex requests, an example also really helps: "e.g., I want to put SUMIF right next to the corresponding Category Social."
  • Understand the Limitations: ChatGPT doesn't have live access to your Sheet. It's generating formulas and scripts based solely on your descriptions. If the output is wrong, it's often because the prompt was not specific enough.

Final Thoughts

Building a dashboard in Google Sheets with guidance from ChatGPT unlocks a powerful new way to handle your data. You can quickly generate the complex formulas and scripts needed to visualize your performance without spending hours buried in help articles or YouTube tutorials. This process empowers anyone on your team to turn overwhelming spreadsheets into clear, actionable insights.

While this method is a massive upgrade over building formulas by hand, it still requires manual steps like exporting CSV files and routinely importing fresh data. To take the final leap, we built Graphed to automate the entire process. Rather than exporting data, you connect your platforms (like Google Analytics, Shopify, Facebook ads) directly. And instead of copy-pasting AI-generated formulas, you simply describe the dashboard you want in plain English, and it’s built for you instantly with live, self-updating data.

Related Articles