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 gadgets, such as iGoogle.
Spreadsheets gadgets can:
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:
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 gadgets header, using Google Visualizations to retrieve our spreadsheet data, and executing our logic to generate html. This requires two other APIs: the 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 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"><
|
<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>
To fully develop and deploy a spreadsheet gadget, simply:
Here are a few things you might find useful during each step in this process:
1. Coding it:
Using the Google Gadgets Editor
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. Testing and using your custom gadget
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.
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.
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!