TIPS COM: Programmatically Managing Charts in Spreadsheets

Monday, March 9, 2015

Programmatically Managing Charts in Spreadsheets

Editor’s Note: Kevin Winter is a guest author from the AdWords API Developer Relations team. He implemented these features in his 20% time. - Jan Kleinert

Google Spreadsheets provides powerful charting functionality to let you analyze your data many different ways. We recently added the ability to programmatically create, modify and delete Spreadsheet charts using Google Apps Script.

What are Embedded Charts?

Charts that a user creates in a Google Spreadsheet are called Embedded Charts. Apps Script can be used to manage these types of Spreadsheet Charts. Each EmbeddedChart belongs to a Sheet. Embedded charts are named this way because they are embedded onto a Sheet within the spreadsheet. Embedded charts align to the upper left hand corner of the specified column and row in the spreadsheet.

An Example of an Embedded Spreadsheet Chart

Let’s say I want to track my gas mileage using Google Spreadsheets. First, I create a Spreadsheet, and set up columns and formulas. Next, I add a form interface to make it easy to add new entries and a chart to visualize my mileage. Now I can start recording data - but each time I add enough entries to go past the ranges the chart uses, I need to manually increase them. How about we use some Apps Script magic to solve this?

The script below iterates through all charts on this sheet and determines if any of the ranges need to be expanded (i.e. if there are more rows with data to display). It then updates the title, builds the new EmbeddedChart object and saves it to the sheet. It could also add a menu interface or a trigger to execute this periodically or when the spreadsheet is edited.


function expandCharts() {
  var sheet = SpreadsheetApp.getActiveSheet()
  // Get a list of all charts on this Sheet.
  var charts = sheet.getCharts();
  for (var i in charts) {
    var chart = charts[i];
    var ranges = chart.getRanges();
    // Returns an EmbeddedChartBuilder with this chart’s settings.
    var builder = chart.modify();
    for (var j in ranges) {
      var range = ranges[j];
      // rangeShouldExpand_ is defined later.
      if (rangeShouldExpand_(range)) {
        // Removes the old range and substitutes the new one.
        builder.removeRange(range);
        var newRange = expandRange_(range);
        builder.addRange(newRange);
      }
    }
    // Update title.
    builder.setOption(title, Last updated  + new Date().toString());
    // Must be called to save changes.
    sheet.updateChart(builder.build());
  }
}

function rangeShouldExpand_(range) {
  var s = range.getSheet();
  var numColumns = range.getNumColumns()
  var values = s.getSheetValues(range.getLastRow(), 
                                range.getColumn(), 
                                2, 
                                numColumns);
  for (var i = 0; i < numColumns; i++) {
    // If the next row has the same pattern of values,
    // it’s probably the same type of data and should be expanded.
    if (!values[0][i] && !values[1][i] 
        || !!values[0][i] && !!values[1][i]) {
      continue;
    } else {
      return false;
    }
  }
  return true;
}

function expandRange_(range) {
  var s = range.getSheet()
  var startRow = range.getRow();
  var startCol = range.getColumn();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  while (rangeShouldExpand_(range)) {
    numRows++;
    range = s.getRange(startRow, startCol, numRows, numCols);
  }
  return range;
}

Creating New Charts in Spreadsheets

What if you wanted to create a new chart from scratch? You can do that too!


var sheet = SpreadsheetApp.getActiveSheet();
var chart = sheet.newChart()
    .setPosition(5, 6, 5, 5)
    .setChartType(Charts.ChartType.AREA)
    .addRange(sheet.getActiveRange())
    .build();
sheet.insertChart(chart);

In the above code example, we’ve gotten a reference to an EmbeddedChartBuilder, set its position within the sheet, change the chartType, add the currently selected range and insert the new chart.

Modifying Charts in Spreadsheets

The EmbeddedChartBuilder allows you to modify the chart in a couple ways:

Add/Remove the ranges this chart represents via addRange and removeRange.
Set options that modify how the chart will be rendered as well as change the chart type with setOption and setChartType.
Change where the chart will be displayed (the cell and cell offset of the chart container) via setPosition.

Embedded charts use more options than regular Apps Script charts, so options are handled slightly differently. Developers can pass a dotted field path to change options. For example, to change the animation duration, you can do this:


builder.setOption("animation.duration", 1000);

Now that we’ve created a bunch of charts, your spreadsheet is probably pretty cluttered. Want to clear it and start afresh with charts? Just remove them all:


var sheet = SpreadsheetApp.getActiveSheet();
var charts = sheet.getCharts();
for (var i in charts) {
  sheet.removeChart(charts[i]);
}

Take Your Charts with You

Just like standalone charts, you can use embedded charts elsewhere. You can add them to a UIApp or a sites page as well as sending them as an email attachment:


// Attach all charts from the current sheet to an email.  
var charts = SpreadsheetApp.getActiveSheet().getCharts();
MailApp.sendEmail(      
    "recipient@example.com",       
    "Income Charts",  // Subject      
    "Heres the latest income charts", // Content      
    {attachments: charts });

We hope you found this blog post useful. Enjoy editing embedded charts using Google Apps Script!

Kevin Winter profile

Kevin is a Developer Programs Engineer in the AdWords API Team. He maintains the latest version of the Python and Java client libraries for the AdWords API. You can find him in the New York office.

TIPS COM