Visualize Your Data: Charts in Google Apps Script!

Wednesday, September 7, 2011 | 11:09 AM

Labels: ,

Charts are a great way to communicate significant amounts of data. We’ve joined forces with the Google Chart Tools team in order to bring a new Charts API to Apps Script. Every day, millions of charts are created, updated, put into presentations, emailed to managers, and published as web pages. Our goal is to automate chart creation in Google Apps and make the sometimes-tedious tasks of chart creation and updating a little more fun!

Charts Ahoy!

Our initial launch includes six chart types. These can be attached to sites, sent as email attachments, or displayed using an Apps Script UiApp.


4 Easy Steps to Create Charts

Step 1 - Open Apps Script Editor

You can access the Apps Script Editor from a Spreadsheet or a Google Sites Page. To access the Apps Script Editor from a Spreadsheet, Open a New Spreadsheet > Tools > Script Editor. To open the Script Editor from a Google Sites, More > Manage Site > Apps Scripts > Add New Script.

Step 2 - Create a Data Table

To build a chart, the first thing you need is a data table. The data table contains the data for the chart as well as labels for each category. In general, data tables contain one column of labels followed by one or more columns of data series, with some variations. Read the documentation for the type of chart you’re creating to learn the exact data table format it expects. Here’s an example of how you’d create a data table in Apps Script to use with a column chart:
function doGet() {    
  // Populate the DataTable.  We'll have the data labels in   
  // the first column, "Quarter", and then add two data columns,  
  // for "Income" and "Expenses"  
  var dataTable = Charts.newDataTable()      
        .addColumn(Charts.ColumnType.STRING, "Quarter")      
        .addColumn(Charts.ColumnType.NUMBER, "Income")      
        .addColumn(Charts.ColumnType.NUMBER, "Expenses")      
        .addRow(["Q1", 50, 60])      
        .addRow(["Q2", 60, 55])      
        .addRow(["Q3", 70, 60])      
        .addRow(["Q4", 100, 50])      
        .build();

In the example above, we’ve hard-coded the data. You can also populate the table in any of these ways:
  • Fetch the data from an existing spreadsheet using SpreadsheetApp
  • With data from a UiApp form
  • Using our JDBC API
  • Using UrlFetch
  • Or any other way in which you can get an array of data using Apps Script.

Step 3 - Build a Chart using Data Table

Once you have the data table ready, you can start building the chart. Our top-level Charts class has Builders for each of the chart types we support. Each builder is configured for the specific chart you’re building, exposing only methods which are available for the specific chart type. For example, in a Line Chart you can make the angles smooth, in Bar and Column Charts you can stack up the data, and in Pie Charts you can make the whole chart 3D!Here’s an example of using the above data table to build a Column Chart:

// Build the chart.  We'll make income green and expenses red  
// for good presentation.  
var chart = Charts.newColumnChart()      
    .setDataTable(dataTable)      
    .setColors(["green", "red"])      
    .setDimensions(600, 400)      
    .setXAxisTitle("Quarters")      
    .setYAxisTitle("$")      
    .setTitle("Income and Expenses per Quarter")      
    .build();

In the above chart, the only required methods are setDataTable() and build(), all of the others are optional. If you don’t set colors and dimensions, for instance, we’ll pick some default values for you. Use the different setter methods to customize your chart, however and whenever you feel like it.

Step 4 - Publish your chart in Documents, Email, Sites or UiApp

Once you’ve built your chart, there are different things you can do with it. For example, you can add it to an Apps Script UI. You can add a chart to any part of the UI that takes a widget, including the application itself. The following code snippet shows you how to publish a chart with UiApp.

// Add our chart to the UI and return it so that we can publish  
// this UI as a service and access it via a URL.  
var ui = UiApp.createApplication();  
ui.add(chart);  
return ui;}

Charts can also be used as Blobs. This allows Charts to be attached to Sites pages, saved to your Docs List, or attached to outgoing emails. The code below does all three of these things:

// Save the chart to our Document List  
var file = DocsList.createFile(chart);  
file.rename("Income Chart");  
file.addToFolder(DocsList.getFolder("Charts"));        

// Attach the chart to the active sites page.  
var page = SitesApp.getActivePage();  
page.addHostedAttachment(chart, "Income Chart");   

 // Attach the chart to an email.  
MailApp.sendEmail(      
    "recipient@example.com",       
    "Income Chart",  // Subject      
    "Here's the latest income chart", // Content      
    {attachments: chart });

And that’s it. We hope you enjoy the new API. If your favorite chart is not here yet, or if you have ideas on how we could improve the API, please let us know in our forum. Finally, enjoy the income chart we’ve been building.


Gustavo Moura

Gustavo has been a Software Engineer at Google since 2007. He has been part of the Google Docs team since 2009. Prior to that, he worked on AdWords.


Want to weigh in on this topic? Discuss on Buzz

23 comments:

TC said...

That's great!

Can I access charts already within a spreadsheet via GAS? I had to implement a workaround whilst this was being developed and now would like to be able to grab the chart from a sheet and mail it as an attachment.

gmoura said...

Hi TC,

You can't do that as part of our initial release, but this is a feature we're working hard on adding!

gmoura said...

Hi TC,

You can't do that as part of our initial release, but this is a feature we're working hard on adding!

Learner said...

Wonderful and so easy to use scripts for Charts. Thanks. Really, a nice piece of code, and still nicer way to present and explain it.

By the way, I searched here for Romain Vialard who wrote Embedding Google DocList in Google Site on Tutorial Page. But, as I couldn't find him, I am making my comment here.

Actually, there is some spelling mistakes in the word 'attachements' and that generates an error in Script Editor while testing the code. The code snippet is:

var attachments = page.getAttachements();
for(i in attachements){

attachements[i].deleteAttachment();
}.This took me a while to find the mistakes, and I hope others won't have to, once corrected.
Thank you once againg for nice charts in website, email attachment and Docs.

Anonymous said...

This really is great. But how do you send two charts (or more) via email?

Kevin said...

How do you attach two charts in one email? Thanks!

Learner said...

You can send more than one Charts by simply placing an arrays of charts after 'attachements:'. For example,

MailApp.sendEmail('youremail@gmail.com','Two Charts', 'Yes, TWO Charts can be sent!!!',{attachments: [chart1,chart2]});

Try using the following code, replacing 'youremail@gmail.com' with your real email address.
You are certainly going to receive two charts attached with the email.
The code is:

function sendMoreCharts(){

var dataTable1=Charts.newDataTable()
.addColumn(Charts.ColumnType.STRING, 'Students')
.addColumn(Charts.ColumnType.NUMBER, 'Math')
.addColumn(Charts.ColumnType.NUMBER, 'Computer')
.addRow(['John',75,80])
.addRow(['Rahul',65,85])
.addRow(['Suleman',90,70])
.build()
var chart1=Charts.newColumnChart()
.setDataTable(dataTable1)
.setColors(['red','green'])
.setXAxisTitle('Students')
.setYAxisTitle('Scores')
.setTitle('FIRST TEST')
.build();
var dataTable2=Charts.newDataTable()
.addColumn(Charts.ColumnType.STRING, 'Students')
.addColumn(Charts.ColumnType.NUMBER, 'Math')
.addColumn(Charts.ColumnType.NUMBER, 'Computer')
.addRow(['John',75,80])
.addRow(['Rahul',65,85])
.addRow(['Suleman',90,70])
.build();
var chart2=Charts.newColumnChart()
.setDataTable(dataTable2)
.setColors(['black','gray'])
.setBackgroundColor('#EEEEFF')
.setXAxisTitle('Students')
.setYAxisTitle('Scores')
.setTitle('SECOND TEST')
.build();
MailApp.sendEmail('rafik.talat@gmail.com',
'attach two files', 'See if Two files received',
{attachments:[chart1,chart2]});
}

Learner said...

To send more charts in atachments, you have to use this format in MailApp arg >>>

attachments:[chart1,chart2]

where chart1,chart2 are the charts you created with apps. Also, you can create five charts and try sending them all like this.

Faku said...

How do you send charts as pdf via email?
Thanks!!

Guga said...

Hi Faku,
Simply call "getAs" on the chart before mailing it out. Example:

var pdfChart =
chart.getAs("application/pdf")
.setName("Chart PDF.pdf");
MailApp.sendEmail('user@example.com',
'attach PDF', 'See attached',
{attachments:[pdfChart]});

Faku said...

Hi Guga,
Thanks a lot!!
Es exactly what we needed. =)

Anonymous said...

Great !!!

Are "Candlestick Charts" planned to be available in GAS ?

IF so, any idea of the schedule ?

Thanks

Faku said...

It's posible set the legend font and set column width in column chart??

Thanks!!

gmoura said...

Hi Anonymous,

Candlestick charts aren't planned right now, but we'll be adding more charts in the future, stay tuned.

Faku, we'll be allowing you to configure more options (including the legend font) soon.

I'm writing down all of these suggestions, so please keep them coming!

水野洋羽 said...

Hi, Guga:

Is it possible to insert a chart into google spreadsheet by GAS(not by the chart gadgets of spreadsheet)?

Guga said...

It's not currently possible to insert a chart into a Spreadsheet directly through GAS, but we're actively working on it!

arlejeun said...

Hi, How can I see the chart with my Android platform. I just get a suite of numbers but no chart.

Any idea ?

lawtonterri said...

Perfect post. Here’s a tool that lets youbuild all types of online reporting with graphs and charts for sales, marketing, finance, HR, support, etc. http://blog.caspio.com/web_apps/how-to-add-dynamic-data-charts-and-graphs-to-your-web-apps/

Smitha said...

I am using GWT and Gchart to render the images , Can I use the above api's to save those images to a doc file?

gmoura said...

Hi Smitha,

Yes, Charts will absolutely work in documents. Here's an example:

function myFunction() {
var table = Charts.newDataTable()
.addColumn(Charts.ColumnType.STRING, "color")
.addColumn(Charts.ColumnType.NUMBER, "blue")
.addColumn(Charts.ColumnType.NUMBER, "green")
.addColumn(Charts.ColumnType.NUMBER, "red")
.addColumn(Charts.ColumnType.NUMBER, "yellow")
.addRow(["votes", 5, 3, 2, 1])
var chart = Charts.newColumnChart()
.setDataTable(table)
.setColors(["blue", "green", "red", "yellow"])
.build();
var doc = DocumentApp.openById("");
doc.appendImage(chart);
}

Konish Dutta said...

This is fantastic work!

Any idea what the timeline for adding combo charts to this is?

David Pyle said...

Hi

I have a google site page and inserted this script so I can add a chart. However I keep getting this error:

"Another entity already exists with the same name."

on the line:
page.addHostedAttachment(chart, "Income Chart");

Any idea on how to fix this?

Cheers
David

Дмитрий Иванов said...

Gustavo, Hi!
Do you know what about issue 2351 on you tracker?

http://code.google.com/p/google-apps-script-issues/issues/detail?id=2351

Would you please give us any forecast?