Create and Format Charts in Google Sheets with Apps Script

Instead of manually creating charts every time your data updates, Apps Script can build or refresh them automatically — and even embed them in Google Docs or emails.

Create a basic bar chart

function createBarChart() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange('A1:B6'); // Headers + 5 data rows

  const chart = sheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(dataRange)
    .setPosition(2, 4, 0, 0) // row, column, offsetX, offsetY
    .setOption('title', 'Monthly Sales')
    .setOption('hAxis.title', 'Revenue ($)')
    .setOption('vAxis.title', 'Month')
    .setOption('width', 500)
    .setOption('height', 300)
    .build();

  sheet.insertChart(chart);
  Logger.log('Bar chart created.');
}

Create a line chart

function createLineChart() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(sheet.getRange('A1:C13'))
    .setPosition(5, 5, 0, 0)
    .setOption('title', 'Revenue vs Target')
    .setOption('curveType', 'function') // Smooth curves
    .setOption('legend', { position: 'bottom' })
    .setOption('width', 600)
    .setOption('height', 350)
    .build();

  sheet.insertChart(chart);
}

Create a pie chart

function createPieChart() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const chart = sheet.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(sheet.getRange('A1:B5'))
    .setPosition(2, 4, 0, 0)
    .setOption('title', 'Market Share')
    .setOption('is3D', true)
    .setOption('width', 400)
    .setOption('height', 400)
    .build();

  sheet.insertChart(chart);
}

Update an existing chart's data range

function updateChart() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const charts = sheet.getCharts();

  if (charts.length === 0) {
    Logger.log('No charts found.');
    return;
  }

  const chart = charts[0];
  const newRange = sheet.getRange('A1:B12'); // Extended range

  const updatedChart = chart.modify()
    .clearRanges()
    .addRange(newRange)
    .setOption('title', 'Updated Annual Sales')
    .build();

  sheet.updateChart(updatedChart);
  Logger.log('Chart updated.');
}

Delete all charts on a sheet

function deleteAllCharts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getCharts().forEach(chart => sheet.removeChart(chart));
  Logger.log('All charts removed.');
}

Embed a chart in a Google Doc

function embedChartInDoc() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const charts = sheet.getCharts();

  if (charts.length === 0) {
    Logger.log('No charts to embed.');
    return;
  }

  const doc = DocumentApp.create('Report with Chart');
  const body = doc.getBody();

  body.appendParagraph('Monthly Sales Report').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph('Below is the sales chart for this month:');

  // Embed the first chart as an image
  const chartBlob = charts[0].getAs('image/png');
  body.appendImage(chartBlob);

  doc.saveAndClose();
  Logger.log('Doc created with chart: ' + doc.getUrl());
}

Attach a chart as an image in an email

function emailChart() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const charts = sheet.getCharts();

  if (charts.length === 0) return;

  const chartBlob = charts[0].getAs('image/png').setName('sales-chart.png');

  GmailApp.sendEmail(
    '[email protected]',
    'Monthly Sales Chart',
    'Please find this month\'s sales chart attached.',
    { attachments: [chartBlob] }
  );

  Logger.log('Chart emailed.');
}

Tips

Chart types: BAR, COLUMN, LINE, AREA, PIE, SCATTER, COMBO, HISTOGRAM, TABLE.
setPosition(row, col, offsetX, offsetY) uses 1-based row/column numbers.
Chart options use the same keys as the Google Charts API — refer to the Google Charts documentation for the full list.
chart.getAs('image/png') exports the chart as a PNG blob — useful for embedding in docs and emails without needing a screenshot.