In this post, I demonstrate How to Embed Charts on Google Sheets using Google Apps Script.
How to Video:
Video Notes:
- Embedding Chart Documentation
- Set Options Documentation
Code in Video:
function onOpen(e)
{
addMenu();
}
function addMenu()
{
var menu = SpreadsheetApp.getUi().createMenu('Charts');
menu.addItem('Bar Chart', 'BarChart');
menu.addItem('Line Chart', 'LineChart');
menu.addItem('Pie Chart', 'PieChart');
menu.addToUi();
}
function BarChart()
{
AddChart('Bar');
}
function LineChart()
{
AddChart('Line');
}
function PieChart()
{
AddChart('Pie');
}
function AddChart(type)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('SALES');
var lastRow = sheet.getLastRow()+1;
var charts = sheet.getCharts();
for (var i in charts) {
var chart = charts[i];
sheet.removeChart(chart);
}
if(type == 'Line')
{
var chart = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.setOption('title', 'Sales Chart')
.setOption('hAxis.title', 'Sales Person')
.setOption('vAxis.title', 'US Dollars')
.addRange(sheet.getRange("A2:B"+lastRow))
.setPosition(2, 4, 0, 0)
.build();
}
if(type == 'Bar')
{
var chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.setOption('title', 'Sales Chart')
.setOption('vAxis.title', 'Sales Person')
.setOption('hAxis.title', 'US Dollars')
.addRange(sheet.getRange("A2:B"+lastRow))
.setPosition(2, 4, 0, 0)
.build();
}
if(type == 'Pie')
{
var chart = sheet.newChart()
.setChartType(Charts.ChartType.PIE)
.addRange(sheet.getRange("A2:B"+lastRow))
.setOption('title', 'Sales Chart')
.setOption('is3D', true)
.setPosition(2, 4, 0, 0)
.build();
}
sheet.insertChart(chart);
}
Related Posts
![Back Up Google Drive Files](https://codewithcurt.com/wp-content/uploads/2023/04/Back-Google-Drive-Files-WS-150x150.png)
![Search Google Drive Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Search-Google-Drive-GS-WS-150x150.png)
![Create Custom Menu Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/10/Create-Custom-Menu-GS-WS-150x150.png)
![Get and Set Value Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/07/Get-and-Set-Value-GS-WS-150x150.png)
![Create Data Validation Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Create-Data-Validation-GS-WS-150x150.png)
![Stock Trend Analysis Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Stock-Trend-Analysis-GS-WS-150x150.png)
![Scrape Amazon Website Googe Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Scrape-Amazon-Website-GS-WS-150x150.png)
![Alert Popups on Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Alert-Popups-on-GS-WS-150x150.png)
![Combine Multiple Spreadsheets Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Combine-Multiple-Spreadsheets-GS-WS-150x150.png)
![Last Row and Column Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Last-Row-and-Column-GS-WS-150x150.png)
![Time and Distance Map Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Time-and-Distance-GS-WS-150x150.png)
![Create Dependent Dropdown Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Create-Dependent-Dropdown-GS-WS-150x150.png)
![Call Rest API Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Call-Rest-API-GS-WS-150x150.png)
![Google Doc From Apps Script](https://codewithcurt.com/wp-content/uploads/2021/12/Google-Doc-From-Apps-Script-WS-150x150.png)
![How to Clear, Delete, Insert, and Replace Row Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/12/How-to-Clear-Delete-Insert-and-Replace-Row-GS-WS-150x150.png)
![Email Last Update Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/12/Email-Last-Update-GS-WS-150x150.png)
![JSON Web Service Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/11/Web-Service-GS-WS-150x150.png)
![Scrape Website Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Scrape-Website-GS-WS-150x150.png)
![Query Function Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/02/Query-Function-WS-150x150.png)
![Copy Row Google Sheet to Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Copy-Row-Sheet-to-Sheet-WS-150x150.png)
![Updated Date Column Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/01/Updated-Date-Column-GS-WS-150x150.png)
![](https://codewithcurt.com/wp-content/uploads/2021/01/Create-Google-Sheets-Button-Thumbnail-3-150x150.png)