3. Create a chart in Sheets with Apps Script
Suppose you want to design a specific chart to visualize a dataset. You can actually use Apps Script to build, edit, and insert charts into Google Sheets. When a chart is contained within a spreadsheet, it's called an
embedded chart
.
Charts are used to visualize one or more data series. For embedded charts, the data they present usually comes from within the spreadsheet. Usually, updating the data in the spreadsheet causes Sheets to automatically update the chart as well.
You can use Apps Script to create customized, embedded charts from scratch, or to update existing charts. This section introduces the basics of building embedded charts in Sheets with Apps Script and the
Spreadsheet
service
.
Implementation
In your copy of the data spreadsheet, the "Dates and Exchange Rates" dataset shows the exchange rates (for 1 US dollar) of different currencies at different dates. You'll be implementing an Apps Script function that makes a chart to visualize part of this data.
Take the following steps:
- In the Apps Script editor, add the following function to the end of your script project's
Code.gs
script, after the
onOpen()
function:
/**
* Creates and inserts an embedded
* line chart into the active sheet.
*/
function createEmbeddedLineChart() {
var sheet = SpreadsheetApp.getActiveSheet();
var chartDataRange = sheet.getRange(
'Dates and USD Exchange Rates dataset!A2:F102');
var hAxisOptions = {
slantedText: true,
slantedTextAngle: 60,
gridlines: {
count: 12
}
};
var lineChartBuilder = sheet.newChart().asLineChart();
var chart = lineChartBuilder
.addRange(chartDataRange)
.setPosition(5, 8, 0, 0)
.setTitle('USD Exchange rates')
.setNumHeaders(1)
.setLegendPosition(Charts.Position.RIGHT)
.setOption('hAxis', hAxisOptions)
.setOption("useFirstColumnAsDomain", true)
.build();
sheet.insertChart(chart);
}
- Save your script project.
Code review
The code you've added implements the function called by the
Chart "Dates and USD Exchange Rates dataset"
menu item to create a basic line chart. Let's review the code.
The first lines set up the following three variables:
sheet
: a reference to the current active sheet.
chartDataRange
: the data range we want to visualize. The code uses A1 notation to specify the range covers cells A2 through F102 in the sheet named
Dates and USD Exchange Rates dataset
. By naming the sheet specifically, we make sure the menu item works even if a different sheet is active as the range always covers the data position. Beginning at row 2 means we're including the column headers and we'll only chart the 100 most recent dates (rows).
hAxisOptions
: a basic JavaScript object that includes some setting information the code uses to configure the appearance of the horizontal axis. Specifically, they set the horizontal axis text labels at a 60-degree slant, and it sets the number of vertical gridlines to 12.
The next line creates a
line chart builder
object. Embedded charts in Apps Script are constructed using a
Builder design pattern
. A full explanation of this design pattern is out-of-scope for this codelab, so for now just understand the
Spreadsheet
service
provides several
EmbeddedChartBuilder
classes. To create a chart, your code first creates an embedded chart builder object, uses its methods to define the chart settings, and then calls a
build()
method to create the final
EmbeddedChart
object. Your code never modifies the
EmbeddedChart
object directly as all chart configuration is managed through the builder classes.
The Spreadsheet service provides a parent
EmbeddedChartBuilder
class and multiple child builder classes (such as
EmbeddedLineChartBuilder
) that inherit from it. The child classes allow Apps Script to provide the builders chart configuration methods that are only applicable to certain chart types. For example, the
EmbeddedPieChartBuilder
class provides a
set3D()
method that's only applicable to pie charts.
In your code, this line creates the builder object variable
lineChartBuilder
:
var lineChartBuilder = sheet.newChart().asLineChart();
The code calls the
Sheet.newChart()
method to create an
EmbeddedChartBuilder
object, and then uses
EmbeddedChartBuilder.asLineChart()
to set the builder type to
EmbeddedLineChartBuilder
.
The code then builds the chart using lineChartBuilder. This part of the code is just a series of method calls to define the chart settings, followed by a
build()
call to create the chart. As you've seen in previous codelabs, the code uses
method chaining
to keep the code human-readable. Here's what the method calls are doing:
addRange(range)
: Defines the data range the chart displays.
setPosition(anchorRowPos, anchorColPos, offsetX, offsetY)
: Determines where the chart is placed in the sheet. Here, the code is inserting the chart's upper-left corner in the cell H5.
setTitle(title)
: Sets the chart title.
setNumHeaders(headers)
: Determine how many rows or columns in the data range should be treated as headers. Here, the code uses the first row in the data range as headers, which means the text in that row is used as labels for the individual data series in the chart.
setLegendPosition(position)
: Moves the chart legend to the right side of the chart. This method uses the
Charts.Position
enum as a parameter.
setOption(option, value)
: Sets complex chart options. Here, the code sets the
hAxis
option to the
hAxisOptions
object. There are several options you can set using this method. The options and possible values for each chart type is documented in the
Charts API Chart Gallery
. For example, the options you can set for line charts are documented under
Line chart configuration options
. The
setOption(option, value)
method is an advanced topic so you might want to avoid using it until you're more comfortable with chart creation in Apps Script.
build()
: Creates and returns an
EmbeddedChart
object using the above settings.
Finally, the code calls
Sheet.insertChart(chart)
to place the built chart into the active sheet.
Results
You can see your formatting function in action by doing the following:
- If you haven't already, save your script project in the Apps Script editor.
- Click the
Present dataset > Chart "Dates and USD Exchange Rates dataset"
menu item.
Your script now places a new chart to the right of your data:
![bbf856699b6d2b45.gif](/static/codelabs/apps-script-fundamentals-5/img/bbf856699b6d2b45.gif)
Congrats, you've built an embedded line chart with Apps Script. The next section teaches you how to export your chart to Google Slides.
4. Export your charts to Slides
One of the great strengths of Apps Script is it allows you to easily move data from one Google Workspace application to another. Most of these applications have a dedicated Apps Script service, similar to the
Spreadsheet service
. For example, Gmail has the
Gmail service
, Google Docs has the
Document service
, and Google Slides has the
Slides service
. With all these built-in services, you can extract data from one application, process it, and write the result to another.
In this section, you'll learn how to export every embedded chart in a Google spreadsheet into a new Google Slides presentation. You'll also see two ways of showing your user custom messages in Sheets.
Implementation
Here you'll implement the function called by the
Present dataset > Export charts to Slides
menu item. Take the following steps:
- In the Apps Script editor, add the following function to the end of your script project's
Code.gs
script, after the
createEmbeddedLineChart()
function:
/**
* Create a Slides presentation and export
* all the embedded charts in this spreadsheet
* to it, one chart per slide.
*/
function exportChartsToSlides() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Fetch a list of all embedded charts in this
// spreadsheet.
var charts = [];
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
charts = charts.concat(sheets[i].getCharts());
}
// If there aren't any charts, display a toast
// message and return without doing anything
// else.
if (charts.length == 0) {
ss.toast('No charts to export!');
return;
}
// Create a Slides presentation, removing the default
// title slide.
var presentationTitle =
ss.getName() + " Presentation";
var slides = SlidesApp.create(presentationTitle);
slides.getSlides()[0].remove();
// Add charts to the presentation, one chart per slide.
var position = {left: 40, top: 30};
var size = {height: 340, width: 430};
for (var i = 0; i < charts.length; i++) {
var newSlide = slides.appendSlide();
newSlide.insertSheetsChart(
charts[i],
position.left,
position.top,
size.width,
size.height);
}
// Create and display a dialog telling the user where to
// find the new presentation.
var slidesUrl = slides.getUrl();
var html = "<p>Find it in your home Drive folder:</p>"
+ "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
+ presentationTitle + "</a></p>";
SpreadsheetApp.getUi().showModalDialog(
HtmlService.createHtmlOutput(html)
.setHeight(120)
.setWidth(350),
"Created a presentation!"
);
}
- Save your script project.
Code review
This code might be shorter than you expected. Let's review what it's doing by breaking the code into five sections:
1: Get the charts
The first few lines search the active spreadsheet to find all the embedded charts, collecting them into the array
charts
. These lines use the
Spreadsheet.getSheets()
method and the
Sheet.getCharts()
method to get lists of sheets and charts. The JavaScript
Array.concat()
method is used to append the list of charts from each sheet into
charts
.
2: Check there are charts to export
The code verifies if there are any charts to export. We want to avoid making a blank presentation, so if there are no charts the code instead creates a
toast message
using
Spreadsheet.toast(message)
. This is a small ‘peek' dialog that pops up in the lower-right corner of Sheets, stays for a few seconds, and then disappears:
![db7e87dcb8010bef.gif](/static/codelabs/apps-script-fundamentals-5/img/db7e87dcb8010bef.gif)
If there are no charts to export, the code creates the toast message and exits without doing anything else. If there are charts to export, the code continues to create a presentation in the next few lines.
3: Create a presentation
The variable
presentationTitle
is created to hold the new presentation's file name. It's set as the spreadsheet's name, with "
Presentation
" concatenated on the end. The code then calls the Slides service method
SlidesApp.create(name)
to create a presentation.
New presentations are created with a single, blank slide. We don't want that in our presentation, so the code removes it with
Presentation.getSlides()
and
Slide.remove()
.
4: Export the charts
In the next section, the code defines the
position
and
size
of the JavaScript objects to set where the imported charts are placed in the slide and how large the chart will be (in pixels).
The code loops over every chart in the charts list. For each chart, a
newSlide
is created with
Presentation.appendSlide()
, adding the slide to the end of the presentation. The
Slide.insertSheetsChart(sourceChart, left, top, width, height)
method is used to import the chart into the slide with the specified
position
and
size
.
5: Share the presentation location
Finally, the code needs to tell the user where the new presentation is located, preferably with a link they can click to open it. To do this, the code uses Apps Script's
HTML
service
to create a
custom modal dialog
. Modal dialogs (also known as
custom dialogs
in Apps Script) are windows that appear over the Sheets interface. When displayed, custom dialogs prevent the user from interacting with Sheets.
To create a custom dialog, the code needs the HTML that defines its contents. This is provided in the
html
variable. The contents include a short paragraph and a hyperlink. The hyperlink is the
presentationTitle
variable, linked to the presentation URL provided by
Presentation.getUrl()
. The hyperlink also uses the
target="_blank"
attribute so the presentation is opened in a new browser tab, rather than within the dialog.
The HTML is parsed into an
HtmlOutput
object by the
HtmlService.createHtmlOutput(html)
method. The
HtmlOutput
object allows the code to set the size of the custom dialog with
HtmlOutput.setHeight(height)
and
HtmlOutput.setWidth(width)
.
Once
htmlOutput
is created, the code uses the
Ui.showModalDialog(htmlOutput, title)
method to display the dialog with the given title.
Results
Now that you've implemented the second menu item, you can see it in action. To test the
exportChartsToSlides()
function:
- If you haven't already, save your script project in the Apps Script editor.
- Open your spreadsheet and click the
Present dataset > Chart "Dates and USD Exchange Rates dataset"
menu item to create a chart to export. It will appear anchored to cell H5 in the active sheet.
- Click the
Present dataset > Export charts to Slides
menu item. You might be asked to reauthorize the script.
- You should see your script process the request and display the custom dialog.
- To open the new Slides presentation, click the
Dates and USD Exchange Rates Presentation
link:
![51326ceaeb3e49b2.gif](/static/codelabs/apps-script-fundamentals-5/img/51326ceaeb3e49b2.gif)
If you want, you can also add more charts to your spreadsheet and re-select the menu item to create a presentation with multiple slides.
Now you can export charts created in Sheets into a Slides presentation. You can also write code to create a custom dialog.
You've completed the final exercise of this codelab. Move to the next section to review what you've learned.