Google Apps Developer Blog: Programmatically Managing Charts in Spreadsheets

Programmatically Managing Charts in Spreadsheets

June 19, 2012

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 ChartLet’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 SpreadsheetsWhat 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 SpreadsheetsThe 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 YouJust 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!

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.

14 comments :

Blackfireshocker said...: Been waiting for a feature like this to match Excel's Dynamic Charts for a while now. Thanks Google!; June 20, 2012 at 9:01 AM
Michael said...: This is awesome, and the first example sounds like you've been reading my own personal spreadsheets. :-)

Wouldn't it be easier if we could define ranges like A2:B in charts like we can in the rest of the spreadsheet? Any chance that's next on your 20% list, Kevin?; June 20, 2012 at 10:44 AM
Magnus Kwan said...: Couldn't find the reference for function getSheetValues() in rangeShouldExpand_();

Should that be the second getRange() in https://developers.google.com/apps-script/class_sheet#getRange ?; June 20, 2012 at 5:44 PM
It Combine said...: This is awesome... thankx google.

Converage Integrated Media Solutions; June 21, 2012 at 4:32 AM
Kevin said...: @Michael - as a warmup exercise, I actually made it possible to pass a Range object (from a Sheet) to the Chart Builder setDataTable method (this isn't very well documented). However, this simply copies the current data from the range to use as a DataTable - it won't update as the sheet's data changes. If you want that, you can either use an EmbeddedChart or set a datasourceUrl instead of a DataTable/Range: https://developers.google.com/chart/interactive/docs/spreadsheets

@Magnus Kwan, you're right, that should be getRange(...).getValues() instead of getSheetValues(...) - good catch.; June 21, 2012 at 6:21 AM
rosek said...: Thanks guys! I've been waiting over a year for this feature!; June 24, 2012 at 4:43 AM
Nathan said...: This is the perfect solution for a project that just came up. This will eliminate the need for any other external software or resources.

I do have a question though. It appears that you can't access a chart that has been moved into it's own sheet. I've tried accessing them by name, etc, but it appears they don't show up in the sheet list.

I've got 2 "normal" sheets and 4 charts in their own sheets for a total of 6 sheets. However, if I do:

SpreadsheetApp.getActiveSpreadsheet().getNumSheets(); returns 2, not 6.

So, it appears that charts in their own sheet aren't accessible, or am I missing something obvious?

Thanks for the fantastic updates!; July 1, 2012 at 2:12 PM
Kevin said...: Hi Nathan,
I'm sorry that doesn't work yet - those special sheets aren't exposed. It'll take some work to get them exposed properly (so that you can create, delete, etc them instead of just editing the chart) - I'll see what we can do.; July 2, 2012 at 7:58 AM
Nathan said...: Thanks Kevin, at least I know I'm not totally botching something.

I'll play around with it a bit, I'm sure I can figure out some sort of a workaround. I'm thinking I'll look at the configuration of each chart by copying it and then trying to duplicate it with this new API.

If I can do that, then I'll just create an actual sheet and embed the chart in it, instead of moving it to it's own sheet.

Thanks again!; July 2, 2012 at 2:51 PM
Guillermo Ermel said...: Hi Kevin! Is there a way to set DataTable Rolse roles (as in https://developers.google.com/chart/interactive/docs/roles#assigningarole). The goal is to have decent error bars coming from a cell value (min/max)...

Best,
Guillermo; September 25, 2012 at 9:43 AM
Valentina said...: Hi Kevin, this has been very usefull in my proyect but I'm having a problem, I'm traying to graph more than 30 arrows but it only shows 20 in the graphic. It has something to do with the code??

Thanks; March 25, 2013 at 8:23 PM
Valentina said...: Hi Kevin, this has been very usefull in my proyect, but I'm having a problem, I'm traying to graph more than 30 rows but in the graphic it shows 20 only, Is it have something to do with the code?

Thanks; March 25, 2013 at 8:26 PM
Qiang said...: Hi Kevin,
Does any way for use Spreadsheet charts from gae Spreadsheet api?

Thanks; September 3, 2013 at 3:04 AM
Alessandro Iacopetti said...: how do you build a chart with 2 series, one on the left vertical axis and one on the right vertical axis?
i just can't find any option for that, even though you can easily build it with the gui.; September 22, 2013 at 2:41 AM

Apps Developer Blog

Programmatically Managing Charts in Spreadsheets

14 comments :

Labels

Archive

Feed

Company-wide

Products

Developers