Creating Google Charts using a Google Docs spreadsheet

October 14, 2010 at 7:59 pm | Posted in Programming | 2 Comments

This blog describes my experiments to use the Google Chart API from within a Google Docs spreadsheet. The standard graphs that come with Google spreadsheet look passable, but the charts from Google Chart are a lot more customizable while looking better at the same time.

Google Charts works by passing a URL that contains all the formatting and data for a graph. This is processed server-side and an image is returned that can be pasted for example in a website. Google Docs allows to insert an image from an URL, so this is our starting point. Since we want to be able to change the graph dynamically, we have to write a little script that reads relevant data from our spreadsheet, formats the url and finally inserts the image into our document. This is shown in the JavaScript code below:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet= ss.getSheets()[0];

  var range = sheet.getRange("C4");
  var text = range.getValue();

  var chartApi = "http://chart.apis.google.com/";
  var url = chartApi + "chart?chst=d_bubble_icon_text_big&chld=flag_nl|bb|" + text + "|FFFFFF|000000";

  sheet.insertImage(url, 3, 12);
}

Line 2 and 3 retrieve the first (index 0) sheet of our active spreadsheet. Line 5 and 6 are used to read the current value of cell C4. Next this value is used to build an URL. And finally we insert the image into our current sheet (line 11). I have added this script to a drawing of a button. The result looks like this:

No real surprises here: a nice text bubble containing the Dutch flag and the text from cell C4 is generated. However there are a couple of problems:

  • Suppose I want to change this image? It is generated server-side, but the insertImage call doesn’t return anything. So I don’t get a handle to the generated image. Besides, the Google Apps API doesn’t have a call for deleting images.
  • The image is positioned at cell C12, but it actually isn’t part of the spreadsheet. So clearing that cell doesn’t help to delete the image.
  • More problems start when you insert another sheet and the revert back to the first one. The image somehow disappears, leaving only an outline:
  • I tried to add the image to a new sheet. The idea was to delete that sheet and recreate it every time I press the button. The result was very unpredictable, sometimes deleting the sheet, and sometimes ending up with two or more sheets with the same name.

My conclusion so far: both Google Docs (in particular the spread sheet using scripts) and Google Charts are great tools. However they don’t play well together. If you really need that cool graph, you might still be able to use Google Docs, but you will probably have to insert the image into a dynamically generated website. This is something I want to try next to generate a dashboard report on a webpage. In the meanwhile I will file a couple of change requests and bugs  to get these issues fixed in the Google Docs API.

2 Comments »

RSS feed for comments on this post. TrackBack URI

  1. […] This post was mentioned on Twitter by X4s_feeds, Maurits Rijk. Maurits Rijk said: Posted blog about some Google Chart in combination with Google Docs experiences: http://bit.ly/bT4EKF […]

  2. […] Creating google charts using a google docs spreadsheet […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: