Spotted a bug? Have a great idea? Help us make google.dev great!

Welcome to the fifth part of the Fundamentals of Apps Script with Sheets playlist. This codelab teaches you how to use the Spreadsheet service in Apps Script to chart a set of data. You'll then learn how to use the Slides service to export that chart to a new presentation in Google Slides.

What you'll learn

  • How to build a line chart with Apps Script.
  • How to export charts to a new Slides presentation.
  • How to present dialogs to the user in Sheets.

Before you begin

This is the fifth codelab in the Fundamentals of Apps Script playlist. Before starting this codelab, be sure to complete the previous codelabs:

  1. Macros and Custom Functions
  2. Spreadsheets, Sheets, and Ranges
  3. Working with data
  4. Data formatting

What you'll need

  • An understanding of the basic Apps Script topics explored in the previous codelabs of this playlist.
  • Basic familiarity with the Apps Script editor
  • Basic familiarity with Google Sheets
  • Ability to read Sheets A1 Notation
  • Basic familiarity with JavaScript and its String class

Before you continue, you need a spreadsheet with some data. As before, we've provided a data sheet you can copy for these exercises. Take the following steps:

  1. Click this link to copy the data sheet and then click the Make a copy button. The new spreadsheet is placed in your Google Drive home folder and named "Copy of Dates and USD Exchange Rates".
  2. Click the spreadsheet title and change it from "Copy of Dates and USD Exchange Rates" to "Dates and USD Exchange Rates". Your sheet should look like this, with some basic information about different US dollar exchange rates at different dates:

  1. Select Tools > Script Editor to open the script editor:

To save you some time, we've included a bit of code to set up a custom menu in this spreadsheet. You may have seen the menu appear when your copy of the spreadsheet opened:

With this spreadsheet and project, you're ready to start the lab! Move to the next section to start learning about graphs and time-driven triggers.

Suppose you wanted to design a specific chart to visualize a dataset. You can actually use Apps Script to build, edit, and place charts in Google Sheets! When a chart is created and placed in Google Sheets, it's called an embedded chart.

Embedded charts are charts contained within a spreadsheet. Charts are used to visualize one or more data series; for embedded charts, the data they present usually comes form data within the spreadsheet itself. In most cases updating the data in the spreadsheet will cause Google Sheets to automatically update the chart as well.

You can use Apps Script to create customized embedded charts from scratch, or 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 depicts the exchange rates (for 1 US dollar) of different currencies at different dates. You will be implementing an Apps Script function that makes a new chart to visualize part of this data.

Take the following steps:

  1. 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)
    .build();
 
  sheet.insertChart(chart);  
}
  1. 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. It creates 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: a ranging the data that we want to visualize. The code uses A1 notation to specify that 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 that the menu item will work even if a different sheet is currently active—the range always covers where the data is. Including row 2 in this range means we are including the column headers. We've decided to 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, these settings set the horizontal axis text labels at a 60 degree slant, and 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 that Spreadsheet service provides a number of 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; all configuration of the chart is handled through the builder classes.

The Spreadsheet service provides a parent EmbeddedChartBuilder class and a number of 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 only makes sense for pie charts.

In your code, this line is creating a builder object variable lineChartBuilder:

var lineChartBuilder = sheet.newChart().asLineChart();

The code calls the Sheet.newChart() method to create a new 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 that define the chart settings, followed by a build() call to create the chart. As you've seen in previous codelabs, the code makes use of method chaining to keep the code human-readable. Here's what the method calls are doing:

  • addRange(range): Defines a data range the chart will display.
  • setPosition(anchorRowPos, anchorColPos, offsetX, offsetY): Determines where the chart is placed in the sheet. Here, the code is placing 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 says to use the first row in the data range as headers, which means the text in that row will be 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 lots of options you can set using this method, but you need to know the options and possible values for each chart type. This is all documented in the Charts API Charts Gallery. For example, the options you can set for line charts are documented under Line chart configuration options. Using setOption(option, value) methods is an advanced use of Apps Script, so you may want to avoid using them 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:

  1. If you haven't already, save your script project in the Apps Script editor.
  2. 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!

Congrats! You've built an embedded line chart with Apps Script. The next section teaches you how to export your chart to Slides!

One of the great strengths of Apps Script is that it allows you to easily move data from one G Suite application to another. Most G Suite 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 quickly extract data from one application, process it, and write the result to another.

In this section you'll see how to export all the embedded charts 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 will implement the function called by the Present dataset > Export charts to Slides menu item. Take the following steps:

  1. In the Apps Script editor, add the following function to the end of your script project's Code.gs script, after the createEmbeddedLineChart() function:
/**
 * Creates a new Slides presentation and exports
 * 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 new 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 that tells 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!"
  );
}
  1. Save your script project.

Code review

This code might be shorter than you were expecting! Let's review what it's doing by breaking the code into 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 that there are charts to export

The code then checks to see if there are actually 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:

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 as the new presentation's file name. It is 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 new presentation.

New presentations are created with a single blank slide. We don't want that in our presentation, so the cod removes it with Presentation.getSlides() and Slide.remove().

4: Export the charts

In the next section, the code defines the JavaScript objects position and size describe where the imported charts will be 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 that slide to the end of the presentation. The Slide.insertSheetsChart(sourceChart, left, top, width, height) method is then used to import the chart into the slide with the specified size and position.

5: Tell the user where the presentation is

Finally, the code needs to let the user know where the new presentation is, preferably with a link they can click to open it. To do this, the code makes use of Apps Script's HtmlService to create a customized modal dialog. Modal dialogs (also known as custom dialogs in Apps Script) are windows that appear over the Sheets interface; while they are present the user is prevented 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 consist of a short paragraph text and a hyperlink. The hyperlink is the presentationTitle, linked to the presentation URL provided by Presentation.getUrl(). The hyperlink also uses the target="_blank" attribute so that the presentation will be opened in a new browser tab, rather than in the dialog itself.

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 have implemented the second menu item, you can see it in action! Do the following to test the exportChartsToSlides() function:

  1. If you haven't already, open your spreadsheet and select 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.
  2. Select the menu item Present dataset > Export charts to Slides. You may be asked to reauthorize the script.
  3. You should see your script process the request and then display the custom dialog. Click the Dates and USD Exchange Rates Presentation link to open the new presentation:

If you want, you can also add more charts to your spreadsheet and re-select the menu item to create a new presentation with multiple slides.

Now you can export charts created in Sheets into a Google 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.

Congrats! You've completed this codelab and the Fundamentals of Apps Script with Sheets playlist! You can use the principles taught in this playlist to extend your Sheets experience and continue to explore the capabilities of Apps Script.

Did you find this codelab helpful?

Yes No

What you've learned

  • How to build an embedded line chart with Apps Script.
  • How to export a chart to a new file in Slides.
  • How to present toast messages and custom dialogs to the user in Sheets.

What's next

You've completed the playlist! There's still more to learn about Apps Script, however!

Check out these resources:

Happy Scripting!

Did you find this codelab playlist helpful?

Yes No

Would you like to see more Apps Script codelabs in the future?

Yes No