Gadgets: Getting Started with Spreadsheets Gadgets

Gadgets: Getting Started with Spreadsheets Gadgets

Print

• Your first spreadsheet gadget
• Tools for coding
• Testing and Using your gadget
• Sharing your gadget
• Submitting your gadget

A spreadsheet Gadget is a small program that interacts with the content of a spreadsheet. It is visually instantiated either within the spreadsheet itself or on another web page that supports Google Gadgets, such as iGoogle.

Spreadsheets gadgets can:

• Add specific features to a spreadsheet
• Combine spreadsheets data with web content
• Visualize spreadsheet data in unique ways
• Present information from a spreadsheet on other web pages

To see how existing spreadsheet gadgets are used, visit this page of samples.

To see where spreadsheet gadgets are accessed within the Google Docs product, log in to Google Docs and do the following:

• Open a new spreadsheet
• Click the Chart Icon or the "Insert" menu
• Select "Gadget..." to see the list of available public gadgets.

Your first spreadsheet gadget:

To write a new spreadsheet gadget, start with the code presented below and experiment with our logic. The example fetches and displays, as one column, the contents of a range of cells. Any cells holding "world" are colored red. The example consists of three main parts: defining the Google Gadgets header, using Google Visualizations to retrieve our spreadsheet data, and executing our logic to generate html. This requires two other APIs: the Google Gadgets API and the Google Visualizations API. As your technical needs become more complex, you should refer to the detailed information related to those APIs.

Start with the Google Gadgets header:

<?xml version="1.0" encoding="UTF-8"?>
<Module>
<ModulePrefs
title="Simple Table Gadget"
description="Fun sample spreadsheet Gadget."
 author="Google Engineering"
author_affiliation="Google Inc."
author_email="visualization.api+spreadsheet@gmail.com"
screenshot="/ig/modules/spreadsheet.png"
 thumbnail="/ig/modules/spreadsheet-thm.png" > 
<Require feature="idi"/>
<Require feature="locked-domain" />
</ModulePrefs>
<UserPref name="_table_query_url" display_name="Data source url" required="true"/>
 <UserPref name="_table_query_refresh_interval" display_name="Data refresh interval (minutes)" 
default_value="0" datatype="enum" required="false">
<EnumValue value="0" display_value="Do not refresh"/>
 <EnumValue value="60" display_value="1"/>
<EnumValue value="300" display_value="5"/>
<EnumValue value="1800" display_value="30"/>
</UserPref>
 <Content type="html"><![CDATA[ 

Use the Visualizations API to fetch spreadsheet data. First load the Google common loader, which gives us the Visualization API for reading spreadsheet contents:

<script src="http://www.google.com/jsapi" type="text/javascript"></script>

Define some HTML area (a DIV tag in this case) to put our results:

<div id="tablediv" style="overflow: auto;"><img src="http://www.google.com/ig/images/spinner.gif" /></div>
 

Load all the APIs in an orderly way and give the name of a function ("sendQuery") to run once the API is loaded:

<script>

var gadgetHelper = null;
_IG_RegisterOnloadHandler(loadVisualizationAPI);
function loadVisualizationAPI() { 
  google.load("visualization", "1");
   google.setOnLoadCallback(sendQuery);
}

Create a query (shaped by the Gadget's user preferences), then send it to the spreadsheet data source. Also give the name of a function ("handleQueryResponse") to run once the spreadsheet data is retrieved:

function sendQuery() {
  var prefs = new _IG_Prefs(); // User preferences 
  gadgetHelper = new google.visualization.GadgetHelper(); 
  var query = gadgetHelper.createQueryFromPrefs(prefs);
   query.send(handleQueryResponse);
} 

The core logic. Process the spreadsheet data however you want. In this case, we create HTML to be presented back to the user. We'll use inline comments to provide a step-by-step description of what we're doing:

function handleQueryResponse(response) {

  /**
   * Use the visualization GadgetHelper class to handle errors 
   */
  if (!gadgetHelper.validateResponse(response)) {
    return;     // Default error handling was done, just leave. 
   }

  /**
   * GET THE DATA FROM THE SPREADSHEET - sorry to scream in caps, but this is a key step
   */
  var data = response.getDataTable();

  var html = [];   // start the HTML output string
  html.push('Looking for the secret word\n');
 
  /**
   * Process all Rows in the specified range
   */
  for (var row = 0; row < data.getNumberOfRows(); row++) {

    /**
     * Process the Columns in each Row
     */
    for (var col = 0; col < data.getNumberOfColumns(); col++) {
 
      /**
       * GET A DATA VALUE FROM THE RANGE - sorry again for screaming - but this is the next key step 
       */
      var formattedValue = data.getFormattedValue(row, col);
      formattedValue = escapeHtml(formattedValue);
 
      /**
       * Look for the 'world'... add the word to the html either way, but format it differently
       */
      if (formattedValue == 'world') {
        html.push('<span style="color:red; font-size:24pt; font-weight:bold;">');
         html.push(formattedValue);
        html.push('</span>');
      } else {
        html.push(formattedValue);
      }
      html.push('\n');
    }
  } 

  /**
   * Set the generated html into the container div.
    */
  var tableDiv = _gel('tablediv');
  tableDiv.innerHTML = html.join('');
  tableDiv.style.width = document.body.clientWidth + 'px';
  tableDiv.style.height = document.body.clientHeight + 'px';
 }

Define any supporting code you need (like this handy function to escape special characters for html output):

function escapeHtml(text) {
  if (text == null) {
    return '';
  }
  return _hesc(text);
} 
</script>

End all that Gadget Stuff with end XML tags:

]]>
</Content>
</Module>

And there you have it. Here is that code embedded in the Google Gadget Editor. Feel free to play around with our logic. (Note: this may render as a blank area on safari)
To fully develop and deploy a spreadsheet Gadget, simply:

1. Code it.
2. Put the code on a server.
3. Access the code from within the spreadsheet editor of Google Docs.

Here are a few things you might find useful during each step in this process:

1. Coding it:

Tools to write, edit, and save your Gadget code online:

• The Google Gadget Editor (GGE) lets you to edit, preview, save, load, and publish Gadget code. It is used in this very page to let you interact with our example code. The GGE is itself a Gadget (meta!).
• Google Home Project Hosting is another option for getting your code online.
• If you prefer a different editor or hosting provider, feel free to use it.

Using the Google Gadgets Editor

• To set up the Google Gadget Editor (GGE) for Gadgets development:
1. Add it to your iGoogle page.
2. Place the GGE in its own tab.
3. Edit the tab's settings by clicking the small arrow next to the tab's name and selecting "Edit this tab." If you switch the layout to one or two columns, the GGE will be wider.
• The GGE lets you save and load code from a Google server. To get the URL of your code, click the hyperlink on the top-right of the GGE.

2. Putting the code on a server:

If you choose to use the Google Gadgets Editor, your code already resides on a google server. You can get the URL of any specific gadget by opening it in the Google Gadgets Editor, right clicking (or ctrl-clicking on a mac) the name/link of the gadget on the upper right side and selecting "Copy Link." That prepares you for the next step of "Accessing the gadget code." If you choose another method of hosting your gadget, you just need to get the full URL of the Gadget.

3. Accessing the Gadget code from within the spreadsheets editor:

Testing and using your custom Gadget

1. Now that your Gadget is hosted on some server, copy the URL of your Gadget. It should end in ".xml"
2. In a spreadsheet, click "Insert" > "Gadget..." and choose "Custom..." from the left side of the Gadget picker.
3. Paste your URL into the field. IMPORTANT NOTE: If you are testing your Gadget, add the "?nocache" option to the end of the URL to make sure that the Gadget is updated when you change your code.
4. Click "Add." Your Gadget will now appear as a floating window on top of the spreadsheet. You can now set user preferences and interact with the working Gadget. If you'd like, you can also publish your Gadget to iGoogle by selecting the Gadget, clicking the triangle on the right of the title bar, and picking "Add Gadget to iGoogle,Ķ"
5. If your gadget doesn't perform as expected, check out these debugging tips.

Sharing your Gadget with other people

• If you want to share your Gadget with other people, you can either share a spreadsheet with them that has the gadget in it, or, if you want to share only the gadget and not the spreadsheet, you'll need to send them the URL and ask them to add your Gadget to their spreadsheets as a custom Gadget. We're working on making this easier for you.

Submitting your Gadget for anyone to use

• When your code is ready for prime time, you can submit it to the Google Visualization API gallery. The best Gadgets will be made available to all Google Docs spreadsheets users!