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.
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.
EmbeddedChart
Sheet
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; }
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.
EmbeddedChartBuilder
The EmbeddedChartBuilder allows you to modify the chart in a couple ways:
addRange
removeRange
setOption
setChartType
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]); }
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 "Here's the latest income charts", // Content {attachments: charts });
We hope you found this blog post useful. Enjoy editing embedded charts using Google Apps Script!
