Build a Real-Time Dashboard in Google Sheets with Apps Script (Step-by-Step)

Why Build a Real-Time Dashboard in Google Sheets with Apps Script

If you're looking to build a real-time dashboard in Google Sheets with Apps Script, you have more power at your fingertips than most people realize. Every time someone asks me about dashboards, the first thing they reach for is Looker Studio (formerly Data Studio). And look, Looker Studio is fine for static reporting. But the moment you need something interactive, something that actually does things, it falls apart.

Here's why I keep coming back to this approach:

• Everyone already has Google Sheets. No new tool to learn. No licenses. No "can you give me access?" Slack messages.
• Apps Script lets you push buttons that do things. Approve a row, trigger an email, pull fresh data from an API. Looker Studio is read-only.
• Your team already lives in Sheets. The dashboard sits right next to the data they're already editing.
• Version history is built in. Something breaks? Roll back. Try doing that in Looker Studio.

The tradeoff is real: you'll write some code, and you'll hit Google's execution limits. But for most internal dashboards, sales pipelines, project trackers, support queues, Sheets + Apps Script is the fastest path from "I need a dashboard" to "here it is, it updates itself."

Let me walk you through exactly how I build these, step by step.

Planning Your Dashboard Layout

Before you write a single line of code, you need to answer two questions: what data matters, and how should it look?

Choosing KPIs and Data Sources

Start with the decisions your dashboard needs to support. Not "what data do we have?" but "what do we need to know?"

For a sales dashboard, that might be:

• Pipeline value by stage
• Close rate this month vs. last month
• Deals stuck longer than 14 days
• Rep activity (calls, emails, meetings)

For a project tracker:

• Tasks completed vs. remaining (by sprint or week)
• Blockers and their age
• Team velocity trend

Once you know your KPIs, map them to data sources. That data might live in:

• Another Google Sheet (use IMPORTRANGE)
• An external API (CRM, analytics, support tool)
• A Google Form that feeds a response sheet
• A database you can query via Apps Script's UrlFetchApp

Sketch Your Layout First

I use a dead-simple approach: open a blank sheet and block out cells with background colors.

• Top row: Big number scorecards (total revenue, open tickets, conversion rate)
• Middle section: 2-3 charts side by side
• Bottom section: A filtered table showing the raw detail

The key insight: use two sheets minimum. One sheet holds raw data (the "Data" sheet). The other sheet is the dashboard view (the "Dashboard" sheet). Never mix data entry with data display. This separation makes everything easier to maintain and keeps your Apps Script clean.

Setting Up the Data Layer

This is where most tutorials get it wrong. They jump straight to charts. But if your data layer is messy, your dashboard will break every time someone adds a row in the wrong place.

Pulling Data from APIs into Sheets

Apps Script's UrlFetchApp is your Swiss Army knife for API integrations. Here's a real pattern I use to pull data from a REST API:

function fetchSalesData() {
  var url = 'https://api.yourcrm.com/v1/deals';
  var options = {
    method: 'GET',
    headers: {
      'Authorization': 'Bearer ' + getApiKey(),
      'Content-Type': 'application/json'
    },
    muteHttpExceptions: true
  };

  var response = UrlFetchApp.fetch(url, options);

  // Always check the response code when using muteHttpExceptions
  var responseCode = response.getResponseCode();
  if (responseCode < 200 || responseCode >= 300) {
    console.error('API returned ' + responseCode + ': ' + response.getContentText());
    return;
  }

  var data = JSON.parse(response.getContentText());

  // Validate the parsed data before touching the sheet
  if (!data.deals || !Array.isArray(data.deals)) {
    console.error('Unexpected API response: missing deals array');
    return;
  }

  var rows = data.deals.map(function(deal) {
    return [deal.name, deal.stage, deal.value, deal.owner, deal.updatedAt];
  });

  // Clear AFTER validation, not before. If the API is broken,
  // you don't want to wipe your existing data for nothing.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  sheet.getRange('A2:E' + sheet.getLastRow()).clearContent();

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 5).setValues(rows);
  }
}

A few things to notice:

• `muteHttpExceptions: true` prevents your script from throwing on 4xx/5xx responses. But you must check getResponseCode() yourself, otherwise you'll try to parse an error body and either crash or write garbage to the sheet.
• Validate, then clear. Don't wipe the old data until you've confirmed the new data actually parsed correctly. If you clear first and the API returns junk, you've destroyed your existing data for nothing.
• Batch `setValues()` is critical. Never write cell by cell. One setValues() call with a 2D array is 10-100x faster than looping with setValue().

Structuring Raw Data vs. Display Sheets

Your Data sheet should look like a database table:

• Row 1: headers (bold, frozen)
• Row 2+: one record per row
• No merged cells, no blank rows, no formatting

Your Dashboard sheet references the Data sheet with formulas or with Apps Script that processes and summarizes the raw data. This separation means you can blow away and rebuild the Data sheet without touching your dashboard layout.

function summarizeByStage() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName('Data').getDataRange().getValues();
  var headers = data.shift();

  var stageIndex = headers.indexOf('Stage');
  var valueIndex = headers.indexOf('Value');

  var summary = {};
  data.forEach(function(row) {
    var stage = row[stageIndex];
    var value = row[valueIndex];
    summary[stage] = (summary[stage] || 0) + value;
  });

  var dashSheet = ss.getSheetByName('Dashboard');
  var output = Object.keys(summary).map(function(stage) {
    return [stage, summary[stage]];
  });

  dashSheet.getRange('A10:B' + (9 + output.length)).setValues(output);
}

Building the Dashboard UI

Now the fun part. You've got clean data flowing in. Time to make it visual.

Dynamic Charts with the Charts Service

Apps Script can create and update charts programmatically. This is useful when your data shape changes (new stages, new reps) and you want the chart to adapt automatically.

function buildPipelineChart() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');

  // Remove only our pipeline chart, not every chart on the sheet.
  // Filter by title so you don't nuke someone else's charts.
  var charts = sheet.getCharts();
  charts.forEach(function(chart) {
    if (chart.getOptions().get('title') === 'Pipeline by Stage') {
      sheet.removeChart(chart);
    }
  });

  var dataRange = sheet.getRange('A10:B15');

  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(dataRange)
    .setPosition(5, 4, 0, 0)
    .setOption('title', 'Pipeline by Stage')
    .setOption('legend', { position: 'none' })
    .setOption('colors', ['#4285F4'])
    .setOption('hAxis', { format: '$#,##0' })
    .build();

  sheet.insertChart(chart);
}

Conditional Formatting for Status Indicators

Forget traffic-light icons from the 2000s. Use conditional formatting rules applied via Apps Script for consistent, automated status coloring:

function applyStatusFormatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  var range = sheet.getRange('D2:D50');

  range.clearFormat();

  var greenRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('On Track')
    .setBackground('#E6F4EA')
    .setFontColor('#1E8E3E')
    .setRanges([range])
    .build();

  var redRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('At Risk')
    .setBackground('#FCE8E6')
    .setFontColor('#D93025')
    .setRanges([range])
    .build();

  var yellowRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Needs Review')
    .setBackground('#FEF7E0')
    .setFontColor('#F9AB00')
    .setRanges([range])
    .build();

  // Preserve any existing rules on other ranges, then append ours
  var existingRules = sheet.getConditionalFormatRules().filter(function(rule) {
    var ruleRanges = rule.getRanges();
    return !ruleRanges.some(function(r) { return r.getA1Notation() === 'D2:D50'; });
  });

  existingRules.push(greenRule, redRule, yellowRule);
  sheet.setConditionalFormatRules(existingRules);
}

Custom Menus and Buttons

This is where Apps Script dashboards leave every other solution in the dust. You can add buttons that do things:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Dashboard')
    .addItem('Refresh Data', 'fetchSalesData')
    .addItem('Rebuild Charts', 'buildPipelineChart')
    .addItem('Send Weekly Report', 'emailReport')
    .addSeparator()
    .addItem('Reset Filters', 'resetFilters')
    .addToUi();
}

Users get a custom menu right in the toolbar. No code knowledge required to use it. You can also add drawing-based buttons directly on the sheet and assign scripts to them. Great for that big green "Refresh" button your manager wants.

Auto-Refresh: Time-Driven Triggers

A dashboard that requires manual refresh isn't really a google sheets real time dashboard. Time-driven triggers solve this.

function createRefreshTrigger() {
  // Clear existing triggers to avoid duplicates
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function(trigger) {
    if (trigger.getHandlerFunction() === 'refreshDashboard') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('refreshDashboard')
    .timeBased()
    .everyMinutes(5)
    .create();
}

function refreshDashboard() {
  fetchSalesData();
  summarizeByStage();
  buildPipelineChart();
  applyStatusFormatting();

  // Log the refresh time on the dashboard
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  sheet.getRange('A1').setValue('Last updated: ' + new Date().toLocaleString());
}

Important limits to know:

• Minimum trigger interval: 1 minute (but 5 minutes is the practical minimum for most use cases)
• Maximum script runtime: 6 minutes per execution for consumer accounts, 30 minutes for Google Workspace
• Simultaneous executions: 30 per script (not per user, this matters for shared dashboards)
• Daily trigger quota: varies by account type. Check Google's official quotas page for current limits, as these numbers shift over time.

If your refresh takes 30 seconds and runs every 5 minutes, that's about 144 minutes per day. You'll blow past the consumer quota. Either move to Google Workspace or optimize your script to run faster.

Deploying as a Web App for Team Access

Want to share your dashboard without giving everyone edit access to the spreadsheet? Deploy it as a web app.

function doGet() {
  var ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID');
  var dashboard = ss.getSheetByName('Dashboard');
  var data = dashboard.getDataRange().getValues();

  var template = HtmlService.createTemplateFromFile('dashboard');
  template.data = data;
  template.lastUpdated = dashboard.getRange('A1').getValue();

  return template.evaluate()
    .setTitle('Sales Dashboard')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

Then create a dashboard.html file in your Apps Script project:

<!DOCTYPE html>
<html>
<head>
  <style>
    body { font-family: 'Google Sans', Arial, sans-serif; margin: 20px; }
    .kpi-card {
      display: inline-block;
      padding: 20px;
      margin: 10px;
      background: #f8f9fa;
      border-radius: 8px;
      text-align: center;
    }
    .kpi-value { font-size: 2em; font-weight: bold; color: #1a73e8; }
    .kpi-label { color: #5f6368; margin-top: 5px; }
  </style>
</head>
<body>
  <h1>Sales Dashboard</h1>
  <p>Last updated: <?= lastUpdated ?></p>
  <div class="kpi-card">
    <div class="kpi-value"><?= data[1][1] ?></div>
    <div class="kpi-label">Total Pipeline</div>
  </div>
  <!-- Add more KPI cards as needed -->

  <script>
    // Auto-refresh every 5 minutes
    setTimeout(function() { location.reload(); }, 300000);
  </script>
</body>
</html>

Deploy via: Extensions > Apps Script > Deploy > New deployment > Web app. Set access to "Anyone within your organization" for internal dashboards.

One thing that trips people up: every time you change your Apps Script code, you need to create a new deployment version. Editing the code doesn't automatically update the live web app. Go to Deploy > Manage deployments, click the pencil icon, select "New version" from the dropdown, and hit Deploy. Bookmark the deployment URL and share it with your team.

You can also embed the web app inside Google Sites or any internal wiki that supports iframes. The setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) line in the code above makes that possible. One security note though: ALLOWALL means any website can embed your dashboard in an iframe. If your dashboard shows sensitive data, use SAMEORIGIN instead, which restricts embedding to pages on the same domain. Only use ALLOWALL when you specifically need third-party sites to embed it and you understand the tradeoff.

Performance Tips for Large Datasets

Once you're past a few thousand rows, you'll start feeling the pain. Here's what actually works:

1. Batch all reads and writes. Every getValue() or setValue() call is a round trip. Read everything into an array with getDataRange().getValues(), process in memory, write back with one setValues().

2. Use the Cache Service. If your API data doesn't change every minute, cache it:

function getCachedData(key, fetchFunction, ttlSeconds) {
  var cache = CacheService.getScriptCache();
  var cached = cache.get(key);

  if (cached) {
    return JSON.parse(cached);
  }

  var freshData = fetchFunction();
  cache.put(key, JSON.stringify(freshData), ttlSeconds || 300);
  return freshData;
}

// Usage
var salesData = getCachedData('sales_data', fetchSalesData, 600);

3. Limit your data range. Don't call getDataRange() on a sheet with 50,000 rows if you only need the last 30 days. Use getRange() with specific coordinates.

4. Offload heavy processing. If you're crunching more than ~10,000 rows with complex logic, consider pushing the data to BigQuery via the BigQuery Apps Script service and pulling back aggregated results.

5. Avoid `SpreadsheetApp.flush()` in loops. This forces a write to the server. Call it once at the end, or not at all. Sheets will flush automatically when your function returns.

Complete Code and Template

Here's the full refreshDashboard function that ties everything together, data fetch, summary, chart build, formatting, and timestamp:

// Configuration — use a function instead of a file-scope object.
// SpreadsheetApp.getActiveSpreadsheet() returns null inside
// time-based triggers, so calling .getId() at file scope will throw.
function getConfig() {
  return {
    SPREADSHEET_ID: 'YOUR_SPREADSHEET_ID',  // hardcode or use PropertiesService
    DATA_SHEET: 'Data',
    DASHBOARD_SHEET: 'Dashboard',
    API_URL: 'https://api.yourcrm.com/v1/deals',
    REFRESH_MINUTES: 5,
    CACHE_TTL: 300
  };
}

function refreshDashboard() {
  var config = getConfig();
  var ss = SpreadsheetApp.openById(config.SPREADSHEET_ID);

  try {
    fetchSalesData();
    summarizeByStage();
    buildPipelineChart();
    applyStatusFormatting();

    var dash = ss.getSheetByName(config.DASHBOARD_SHEET);
    dash.getRange('A1').setValue('Last updated: ' + new Date().toLocaleString());

    console.log('Dashboard refreshed successfully');
  } catch (e) {
    console.error('Dashboard refresh failed: ' + e.message);

    // Optionally notify via email on failure
    MailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      'Dashboard Refresh Failed',
      'Error: ' + e.message + '\nTime: ' + new Date().toLocaleString()
    );
  }
}

function setupDashboard() {
  var config = getConfig();
  createRefreshTrigger();
  refreshDashboard();
  SpreadsheetApp.getUi().alert('Dashboard setup complete. Auto-refresh every '
    + config.REFRESH_MINUTES + ' minutes.');
}

To get started with this pattern:

1. Create a new Google Sheet with two tabs: "Data" and "Dashboard"
2. Open Extensions > Apps Script
3. Paste the code above and set YOUR_SPREADSHEET_ID in getConfig() to your sheet's ID (you can find it in the URL)
4. Run setupDashboard() once to initialize triggers and do the first refresh
5. Use the custom menu to manually refresh whenever you need it

The full working template is something I set up for clients regularly. The pattern scales well. I've used this same architecture for dashboards tracking everything from SaaS MRR to warehouse inventory to hiring pipelines.

Common Issues and How to Fix Them

A few problems come up in almost every Apps Script dashboard project. First, if your trigger stops firing silently, check the Executions log under Apps Script > Executions. Google will disable triggers that fail repeatedly without telling you. You need to fix the underlying error and recreate the trigger. Second, if your charts disappear after a refresh, make sure you are removing and reinserting them in the correct order. The Charts Service does not update chart data in place reliably, so the delete-and-rebuild pattern shown above is intentional. Third, watch out for the Exception: Service Spreadsheets failed error when multiple triggers fire at the same time. Add a LockService mutex to your refresh function to prevent concurrent writes to the same sheet. And if you are hitting the execution limit, profile each function individually using new Date().getTime() arithmetic before and after each call, then log the difference with console.log(). Look for unnecessary getValues() calls that pull in entire sheets when you only need a specific range.

The key takeaway: don't overthink it. A Google Sheets dashboard with Apps Script won't win any design awards, but it'll be running and useful in an afternoon. And that's usually exactly what the team needs.