Spotted a bug? Have a great idea? Help us improve developer profiles!

Chart and present data in Slides with Google Apps Script

In this codelab, you'll learn to use the Spreadsheet Service in Apps Script to chart a set of data and the Slides Service to export that chart to a new presentation in 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

What you'll need

  • Basic familiarity with the Apps Script editor
  • Basic familiarity with Sheets
  • Ability to read Sheets A1 Notation
  • Basic familiarity with JavaScript and its String class

The exercises in this codelab require a spreadsheet with some data. Follow the steps to create a new spreadsheet that you can use:

  1. Click this link to copy the data sheet, then click the Make a copy button. The new spreadsheet is named "Copy of Dates and USD Exchange Rates" and placed in Drive.
  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 the following screenshot 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, there's a bit of code to set up a custom menu in the spreadsheet. You may have seen the menu appear when your copy of the spreadsheet opened:

With the spreadsheet and project, you're ready to start the codelab! 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 Sheets! When a chart is created and placed in 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 from data in the spreadsheet itself. In most cases, updating the data in the spreadsheet will cause 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 that 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 that you added implements the function called by the Chart "Dates and USD Exchange Rates dataset" menu item. It creates a basic line chart. 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 you 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, you ensure 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 the range means that you are including the column headers. You decided to only chart the 100 most recent dates (rows).
  • hAxisOptions: a basic JavaScript object that includes some setting information, which the code uses to configure the appearance of the horizontal axis. Specifically, those settings set the horizontal axis text labels at a 60 degree slant and set 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 pattern. A full explanation of the design pattern is out of scope for this codelab, so for now you only need to 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, the following 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. That part of the code is simply a series of method calls that define the chart settings followed by a build() call to create the chart. 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 that 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): Determines 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 that 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 using 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 that you can set using this method, but you need to know the options and possible values for each chart type. That is all documented in the Chart Gallery. For example, the options that you can set for line charts are documented under Line Charts. 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, then 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 built an embedded line chart in Sheets 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 product to another. Most G Suite products have a dedicated Apps Script service, similar to the Spreadsheet Service. For example, Gmail has the Gmail Service, Docs has the Document Service, and Slides has the Slides Service. With all those 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 spreadsheet to a new 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

That code might be shorter than you expected!

Now, review the code by breaking it 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. Those lines use the Spreadsheet.getSheets() and Sheet.getCharts() methods 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. You want to avoid making a blank presentation, so if there are no charts, then the code creates a toast message using Spreadsheet.toast(message), 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, then the code creates the toast message and exits without doing anything else. If there are charts to export, then the code continues to create a presentation in the next few lines.

3. Create a presentation. The variable presentationTitle is created 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. You don't want that in your presentation, so the code removes it with Presentation.getSlides() and Slide.remove().

4. Export the charts. In the next section, the code defines the JavaScript object's 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.

To create a custom dialog, the code needs the HTML that defines its contents. That's 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 Class HtmlOutput object by the HtmlService.createHtmlOutput(html) method. The Class HtmlOutput object allows the code to set the size of the custom dialog with HtmlOutput.setHeight(height) and HtmlOutput.setWidth(width).

After the 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, then 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 reselect the menu item to create a new presentation with multiple slides.

Now, you know how to export charts created in Sheets to Slides, as well as how to write code to create a custom dialog!

You completed the final exercise of the codelab. Move to the next section to review what you learned.

Congrats! You completed the codelab. You can use the principles that you learned to extend your Sheets experience and continue to explore the capabilities of Apps Script.

Did you find this codelab helpful?

Yes No

What you 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

There's still more to learn about Apps Script! Check out the following resources:

Happy scripting!

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

Yes No