Fundamentals of Apps Script with Google Sheets #5: Chart and Present Data in Slides

1. Introduction

Welcome to the fifth part of the Fundamentals of Apps Script with Google Sheets codelab playlist. This codelab teaches you how to use the Spreadsheet service in Apps Script to chart a dataset. You'll also learn how to use the Slides service to export the 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 with Google Sheets 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

2. Set up

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 Make a copy. The new spreadsheet is placed in your Google Drive 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:

45a3e8814ecb07fc.png

  1. To open the script editor, click Extensions> Apps Script.

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

9b9caf6c1e9de34b.png

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

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:

  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)
    .setOption("useFirstColumnAsDomain", true)
    .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 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:

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:

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:

  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:
/**
 * 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!"
  );
}
  1. 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

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:

  1. If you haven't already, save your script project in the Apps Script editor.
  2. 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.
  3. Click the Present dataset > Export charts to Slides menu item. You might be asked to reauthorize the script.
  4. You should see your script process the request and display the custom dialog.
  5. To open the new Slides presentation, click the Dates and USD Exchange Rates Presentation link:

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.

5. Conclusion

Congrats, you've now completed this codelab and the entire Fundamentals of Apps Script with Google Sheets codelab playlist. You can use the principles taught in this playlist to extend your Sheets experience and 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 present toast messages and custom dialogs to a user in Sheets.
  • How to export a chart to a new Slides presentation.

What's next

You've successfully completed this playlist. However, there's still more to learn about Apps Script.

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