Google gadgets can make it easy to feed live totals straight from your internal working documents to your website.
Like most newsrooms, we track vote totals precinct by precinct over the course of an election night, with some precincts reporting almost as soon as polls close and others taking two or three hours. Our web platform includes a nice-looking election ticker function, which we’ve used in the past, but having to go back and forth from our spreadsheet to the content management system, entering data manually the whole time, was a time-consuming process and we inevitably fell behind.
This year, we used Google Docs for the spreadsheet, allowing us to share it between multiple users in the newsroom, and fed the data directly from the spreadsheet into a widget on our homepage. All you need is a Google account, a working knowledge of spreadsheet formulas, and the ability to embed wild HTML in your site.
1. Set up your tracking spreadsheet. We had one countywide proposal that we were tracking and a number of local races that only had a few precincts, so this made our spreadsheet relatively simple. For the countywide question, we had four columns for each precinct: Yes votes, no votes, total (calculated by formula), and a column where we would simply enter “1″ for each precinct as it reported. This last column allowed us to automatically display how many precincts had reported and also calculate the percentage reporting.
2. Set up the data you want to display. The area where you enter all your data will probably be far too complicated to display conveniently, so you’ll want to have a section where all the data is displayed exactly the way you want to feed it into the site. You can make this pull data directly from the area where you’re making all your calculations, so you don’t have to enter anything directly into this area if you don’t want to.
Create a gadget to display your data.
3. Create a gadget to display your data. Go to the “Insert” menu, then select “Gadget.” In the menu you get next, select “Tables” on the left, then select the gadget titled “Table.” Google will ask you to select the cells you want to display. Highlight those cells, click “OK,” then click “Apply and close.”
4. Adjust the layout of your spreadsheet if needed. We had to try several different configurations to get the gadget to look good — in particular, if we had a header above the first race, the gadget kept trying to grab that header as part of the header for the entire chart, so it may take a few tries to make Step 3 yield a gadget that looks the way you want it to. (It’s also possible to use a separate gadget for each race you want to track, or to add gadgets that display selected data as pie graphs, etc., but we found that sticking with a single gadget was more attractive and took much less time to load.)
As an interesting aside, we found that if the column we used for percentages had nothing but divide-by-zero errors at first, the “#DIV/0!” message showed up in the gadget, but if that column had even a single legitimate value in any of its cells — such as the 0 resulting from dividing by the total number of precincts — the error message wouldn’t show up in the gadget.
Choose "Publish Gadget" both to grab the embed code and to update your data.
5. Get the embed code for your gadget. Once you’re comfortable with the way the gadget looks, click on it and go to the “Gadget” pulldown menu right above the display. Select “Publish.” Google will give you a block of code that starts with “<script src=…” and ends with “</script>”. Copy this code into a blank text document. Toward the end of the block of code, you’ll see “&height=(number)&width=(number).” Replace the automatically generated height and width numbers with whatever is appropriate for your data and the region on your site where you’ll be displaying it.
6. Embed the code in whatever way is appropriate for your platform. This will vary depending on your CMS.
A Google gadget in action on lenconnect.com.
7. Enter your data and update your gadget. The data in the gadget will update whenever you change the data in the spreadsheet that it’s pulling from. Important: In order to push the updated gadget out to your site, you need to go the menu at the top of the gadget, select “Publish Gadget,” and click “Done.” The display on your site will not automatically refresh — your readers will still need to reload the page — but the updated data will instantly be there, without you needing to touch anything in your CMS.
Modified from a description originally written for GHNewsroom.com.