5 Knock ‘Em Dead Google Gadgets

Google Docs Spreadsheets include a special feature not available for other Google Docs apps — Gadgets. These are elements that you can add to your worksheets to do anything from create an organization chart, create a QR code or make an animated chart — all of which are tools that your small business can use. In this column, I’ll show you how to use some of the best gadgets available for Google Docs spreadsheets.

QR Code Generator

The QR Code Gadget will convert text from a worksheet into a QR code that you can use on your website, for example. Start by typing the content for the QR code in one or more adjacent cells. If, for example, you want the text to be formatted, place it in a series of cells down a column — the QR code generator adds a new line and a comma at the end of each cell’s contents.

Figure 1: Create a QR code from data in a worksheet using the QR Code Gadget.

Choose Insert > Gadget > Web Gadgets > QR Code Gadget > Add to Spreadsheet. When prompted, select the cells containing the data and click Apply & Close. The QR code will be created automatically. Click the menu in the top left corner of the gadget window

for options including editing the code and publishing it. If you publish it, it will give you the code to use to embed in your Web page or blog.

Organization Chart Gadget

You can use the Organization Chart Gadget to create an organizational or hierarchical chart in a spreadsheet. To do this, Google expects you to provide data in two columns. The first column contains the subordinate or second-level entry, and the second column contains the manager’s name or the higher-level entry. Because a person — or an item — may be higher than some items in the hierarchy and lower than others, they — or it — may appear in both columns.

Figure 2: Use the Organization Chart to create all sorts of hierarchical charts.

When you’re done, choose Insert > Gadget > Diagrams > Organization Chart and click Add to Spreadsheet. Select the two columns of data as the range and click OK. You’ll need to give the chart a title and then click Apply & Close.

As with all gadgets, you can use the menu in the top-left to manage, edit and update the gadget when the data changes.

Animated a Chart

You can use the Motion Chart gadget to create an animated chart. The chart data needs to be in a series of columns. The first column contains the data you’re charting, in our case the various offices for the business. The second column contains the period of time that needs to conform to ISO8601 standards, so you need to use numbers like 2011 for the year, 2011W08 for a numbered week in a year, and 2011Q2 for a particular quarter.

Figure 3: The Motion Chart gadget lets you create a chart that can be played to show change over time.

The next column contains the data to plot which, in our example, is the Profit. You can add other columns, if desired, and I’ve added one for Units Sold.

To use the gadget: select Insert > Gadgets > Charts > Motion Chart. Then select the range, which is all your data including the headings. Leave the Default State setting empty for now, type a title for the chart, and click Apply & Close.

If you want to customize the chart, use the fly-out menu of Y-axis options (on the left), and select the third data column. From the X-axis dropdown list, select the Time series so that you’re plotting values against time.

From the Color dropdown list, select Unique colors and from the Size dropdown list, select an option. I chose Units Sold, which is the data in the fourth column, so the size of the bubble on the chart reflects this value. Click Play to view the changes in the two values (columns three and four) over time.

To make the chart play whenever the worksheet is viewed, first drag the play line back to the beginning. Click Settings (the icon in the bottom right) > Advanced > Advanced, double-click the State String to select it, right-click and choose Copy. Click the chart’s title, to display the chart menu, click Edit Gadget, and paste the string into the Default State textbox. Save the worksheet, and now the chart will appear correctly configured and ready to be viewed whenever anyone opens the worksheet.

You can click on the link to see our animated chart.

Google Maps

The Google Maps Gadget is a great way to create maps that show plotted data. You can use this to show information such as the location of your office or anything else that’s relevant to your website viewers.

All you need to do is to provide a list of addresses to plot on the map and, in the adjacent column, any text that you want to appear as a tool tip when the user hovers the mouse over that position on the map. The Google Map Gadget does the work of creating the map and plotting the data on it.

You can learn how to use Google Map Gadget in our recent article.

Smarter Tables

The Table Gadget gives you tools to filter and group data according to categories that you are interested in. You’ll start with a table of data — I’m using the same data I used for the motion chart.

Figure 4: The Table gadget turns simple data into an interactive table allowing you to group and filter your data.

Choose Insert > Gadget > Tables > Table > Add to Spreadsheet. Click in the Range field, and select the table including headings. Type a title, select Show Filters and Enable Grouping, and click Apply & Close. The table appears in a gadget box with filter arrows at the top of each column. Click and select a value from the filter to show only matching data.

Click Define Groupings and Calculations to select a field to group on, and to include calculations for the numerical data for each group. This way you can, for example, group your data by office and then show the totals or averages for each office.

You can publish the resulting table if you click the Gadget menu and choose Publish Gadget. Copy the code provided, and then add it to a Web page to display the interactive table on your site.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com