Add Charts with Apps Script

Apps Script lets you create charts programmatically using sheet.newChart() and the EmbeddedChartBuilder API. You can build bar charts, pie charts, line charts, and more — all driven from your spreadsheet data.

Creating a Bar Chart of Deals by Region

This example builds a bar chart from a summary table on a Dashboard sheet showing the number of deals per region:

function createDealsByRegionChart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Build a summary table on the Dashboard sheet
  var dataSheet = ss.getSheetByName("Sales Tracker");
  var dashboard = ss.getSheetByName("Dashboard") || ss.insertSheet("Dashboard");

  var data = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, 7).getValues();
  // Columns: [Customer Name, Email, Product, Amount, Region, Sales Rep, Status]

  var regionCounts = {};
  data.forEach(function(row) {
    var region = row[4];
    if (!region) return;
    regionCounts[region] = (regionCounts[region] || 0) + 1;
  });

  // Write summary to Dashboard
  dashboard.getRange("A1").setValue("Region");
  dashboard.getRange("B1").setValue("Deals");
  var summaryRow = 2;
  Object.keys(regionCounts).forEach(function(region) {
    dashboard.getRange(summaryRow, 1).setValue(region);
    dashboard.getRange(summaryRow, 2).setValue(regionCounts[region]);
    summaryRow++;
  });

  // Build the chart
  var chartRange = dashboard.getRange(1, 1, summaryRow - 1, 2);

  var chart = dashboard.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(chartRange)
    .setPosition(2, 4, 0, 0)
    .setOption("title", "Deals by Region")
    .setOption("hAxis.title", "Number of Deals")
    .setOption("vAxis.title", "Region")
    .setOption("width", 500)
    .setOption("height", 300)
    .build();

  dashboard.insertChart(chart);
  Logger.log("Bar chart created.");
}

Creating a Pie Chart of Revenue by Product

function createRevenueByProductChart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Sales Tracker");
  var dashboard = ss.getSheetByName("Dashboard") || ss.insertSheet("Dashboard");

  var data = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, 8).getValues();

  var productRevenue = {};
  data.forEach(function(row) {
    var product = row[2];
    var amount = row[3];
    var status = row[6];
    if (status !== "Closed" || !product) return;
    productRevenue[product] = (productRevenue[product] || 0) + amount;
  });

  // Write summary starting at column D
  dashboard.getRange("D1").setValue("Product");
  dashboard.getRange("E1").setValue("Revenue");
  var row = 2;
  Object.keys(productRevenue).forEach(function(product) {
    dashboard.getRange(row, 4).setValue(product);
    dashboard.getRange(row, 5).setValue(productRevenue[product]);
    row++;
  });

  var chartRange = dashboard.getRange(1, 4, row - 1, 2);

  var chart = dashboard.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(chartRange)
    .setPosition(2, 8, 0, 0)
    .setOption("title", "Revenue by Product (Closed Deals)")
    .setOption("width", 450)
    .setOption("height", 300)
    .build();

  dashboard.insertChart(chart);
  Logger.log("Pie chart created.");
}

With the sample data, Starter Plan ($99), Pro Plan ($299), and Enterprise Plan ($0 — not closed) appear as slices.

Creating a Line Chart of Deals Over Time

function createDealsOverTimeChart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Sales Tracker");
  var dashboard = ss.getSheetByName("Dashboard") || ss.insertSheet("Dashboard");

  // Write date-based summary to columns G–H
  var data = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, 8).getValues();
  var dateCounts = {};
  data.forEach(function(row) {
    if (row[6] !== "Closed" || !row[7]) return;
    var dateStr = Utilities.formatDate(new Date(row[7]), Session.getScriptTimeZone(), "MMM dd");
    dateCounts[dateStr] = (dateCounts[dateStr] || 0) + 1;
  });

  dashboard.getRange("G1").setValue("Date");
  dashboard.getRange("H1").setValue("Closed Deals");
  var r = 2;
  Object.keys(dateCounts).forEach(function(d) {
    dashboard.getRange(r, 7).setValue(d);
    dashboard.getRange(r, 8).setValue(dateCounts[d]);
    r++;
  });

  var chartRange = dashboard.getRange(1, 7, r - 1, 2);

  var chart = dashboard.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(chartRange)
    .setPosition(14, 4, 0, 0)
    .setOption("title", "Closed Deals Over Time")
    .setOption("width", 500)
    .setOption("height", 300)
    .build();

  dashboard.insertChart(chart);
  Logger.log("Line chart created.");
}

Deleting All Charts from a Sheet

function clearAllCharts() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard");
  sheet.getCharts().forEach(function(chart) {
    sheet.removeChart(chart);
  });
  Logger.log("All charts removed.");
}