Google Spreadsheets

This page describes how to use or build visualizations for Google Spreadsheets.

Audience

This documentation is designed for people who have programmed with the Google Visualization API and know how to use Google Spreadsheets.

Introduction

Visualizations built on the Google Visualization API can use any accessible Google Spreadsheet as a data source. Using a spreadsheet is as simple as finding out the proper URL to use for a sheet or a range of cells in a spreadsheet to which it has access, and using that URL as a data source URL. The visualization can be an embedded gadget in the spreadsheet itself, a gadget outside the spreadsheet, or a non-gadgetized visualization.

Google Spreadsheets support the Google Visualization API query language for sorting and filtering data.

This document describes how to use Google Spreadsheets as a data source for your visualization, as well as how to develop your own visualization that uses a spreadsheet as a data source.

Using a Google Spreadsheet as a Data Source

Any selection of cells within a Google Spreadsheet is a valid data source for a visualization, as long as the visualization has access to the spreadsheet. A visualization has access to a spreadsheet either if it is embedded in the spreadsheet itself (as a gadget visualization), or if it has view privileges on the spreadsheet. Note that a non-embedded visualization runs with the privileges of the person viewing the visualization, so the spreadsheet must either assign view access to everyone, or to the specific person running the visualization.

To use Google Spreadsheets as a data source, you simply need to get the URL of the sheet and cells that hold the data that you want. Specify that URL as the data source for your visualization in your visualization's user interface.

To get the data source url of your spreadsheet cells:

1. Create or open a spreadsheet. This spreadsheet must have the format expected by your visualization, and it should have viewing privileges set properly. (View privileges of everyone is simplest, but you can restrict view privileges to people who will use that spreadsheet as a data source. The easiest way to guarantee access by everyone is to publish your spreadsheet and use the published URL.)
2. Select the cells to visualize.
3. From the spreadsheet toolbar, click 'Insert' and choose 'Gadget'. Choose an existing gadget or select 'Custom' as the gadget type, enter the URL of your gadget XML specification and click 'Add'. You can choose a visualization gadget, if you want your visualization to live in the spreadsheet; otherwise, you can choose any gadget if you just need to get a URL to pass to an external visualization (either a gadget or non-gadget).
4. Open the gadget's menu by clicking the arrow in the title bar.
5. Select the menu option "Get query data source url...." This will display the data source URL, which will look something like this: http://spreadsheets.google.com?...&key=123ABC&range=B10:B22. You'll have the choice of several data URLs, including selected cells only, or the whole sheet.
6. Use the URL in your visualization query. The query supports the following optional parameters:
   • headers=N - Specifies how many rows are header rows, where N is an integer zero or greater. These will be excluded from the data and assigned as column labels in the data table. If you don't specify this parameter, the spreadsheet will guess how many rows are header rows. Note that if all your columns are string data, the spreadsheet might have difficulty determining which rows are header rows without this parameter.
   • gid=N - Specifies which sheet in a multi-sheet document to link to, if you are not linking to the first sheet. N is the sheet's ID number, an integer zero or greater. It is one less than the number in the sheet name when it is created: for example, gid=0 for Sheet1. This parameter should be supplied for you in step 5. You can also learn the ID number by navigating to the published version of that sheet and looking for the gid=N parameter in the URL. You can use the sheet parameter instead of this parameter. Example: gid=5.
   • sheet=sheet_name - Specifies which sheet in a multi-sheet document you are linking to, if you are not linking to the first sheet. sheet_name is the display name of the sheet. You can use the gid parameter instead of this parameter. Example: sheet=Sheet5.

Building a Visualization That Accesses a Spreadsheet

To have your visualization access a Google spreadsheet is simple: just get the URL of the data as described above, and use it in a standard data query. Google spreadsheets support the Visualization query language to manipulate or filter the data. To learn more about data queries, refer to the Data Queries section of this guide.

Note that you cannot write to the spreadsheet using the Visualization API; to write to the spreadsheet programmatically, you should add the Google Spreadsheets GData API to your visualization.

Here are some additional details, depending on whether you're building a non-gadget or gadget visualization:

Non-Gadget Visualizations

By default, a visualization does not monitor a spreadsheet for changes. To have your visualization check for changes in the spreadsheet data, you should call Query.setRefreshInterval() with an appropriate refresh interval.

Gadget Visualizations

Embedded visualization gadgets are built on the Gadgets-in-Docs platform. To build an embedded visualization gadget, you should read that documentation, as well as the documentation on building visualization gadgets, and the documentation on developing a Spreadsheets gadget. This section describes a few topic specific to developing visualization gadgets for Spreadsheets.

Specifying Real-Time Data Updates

A visualization does not monitor spreadsheets for changes unless specifically configured. Configuration for this feature is handled differently, depending on whether the gadget is embedded in Google Spreadsheets, or is used elsewhere. Because you don't know where a user might install your gadget, you should code for both possibilities, as described here:

Important: Gadgets with a content type of "url" (<Content type="url">) cannot make real-time data updates. To enable real-time spreadsheet updates, the content type must be "html" (<Content type="html">).

1. Add the idi and locked-domain feature requirements to your gadget configuration, as shown here:

   <ModulePrefs title="Simple Table Gadget">
     <Require feature="idi"/>
     <Require feature="locked-domain"/>
   </ModulePrefs>
   

   This is for gadgets embedded in a spreadsheet. Setting this value will cause the spreadsheet to push updates to the visualization when data changes. Gadgets outside a Spreadsheet container will ignore this code.
2. Call Query.setRefreshInterval() with a value indicating how often it should requery the spreadsheet. This is for all non-spreadsheet-embedded visualizations. If a gadget is outside of the spreadsheet, it cannot get notifications when data changes, but instead must periodically query the spreadsheet for data. (Note that calling setRefreshInterval() on a gadget inside a Spreadsheet causes unnecessary refresh requests— the gadget is pushed updates automatically—but the extra overhead is low, if you are not calling it once per second.)

Disabling Gadget Caching

By default, gadget XML specs are cached by the gadgets server. When developing gadgets and making frequent changes to the XML spec, it is useful to disable gadget caching, so that changes are immediately reflected every time the page is refreshed.

To disable caching, append the ?nocache suffix to the URL of your gadget XML specification when entering the gadget URL. For example http://www.mydomain.com/mygadget.xml?nocache.