The Tweet-A-Watt project was Make Magazine’s winning entry into the 2009 Greener Gadgets Design Competition sponsored by Core77 and the Greener Gadgets conference. The meter can be built very inexpensively, including the cost of the Kill-A-Watt meter itself and the kit needed to turn it into a wireless mesh transmitter. The project was published as “open-source hardware”, and has been built by numerous energy conscious makers around the world.

The standard Python scripts which were written for the project are able to upload the data to the Google Application Engine (GAE) and some web-applications were written to view the data as a series of scrolling time-series graphs. But getting all the requisite database and python applications working together can be daunting, even for someone comfortable with programming and building web pages.

EnergyLogger is designed to take the place of the Python scripts developed for the original Tweet-A-Watt project. In addition to “Tweeting” usage statistics, EnergyLogger has the ability to log average demand data to a user’s private Google Doc spreadsheet and from there, additional analysis and adding a time series graph to their iGoogle homepage is fairly straightforward. Taking advantage of this feature requires the user to create a free Google account, but by doing so will allow them to access their data from a web browser anywhere in the world.

This is meant to be a basic guide to getting your Tweet-A-Watt data onto your iGoogle homepage as a nice scrolling time-series gadget like the one shown in the upper right section of the screenshot above. The method described here is based around EnergyLogger, our shareware application, but it could be modified to work with the existing python scripts, as long as the data was available in a Google Doc spreadsheet.

Step 1: Set up the Google Doc spreadsheet

You must first create a spreadsheet in your Google Doc account, or have editing privileges on someone else’s spreadsheet document. Start by creating the spreadsheet. An easy way to accomplish this is by uploading an existing CSV (Comma Separated Value) data file created by EnergyLogger. This has the advantage that the format and any existing data will already be in place, although you can easily start with a new blank spreadsheet as well.

A single spreadsheet can contain multiple worksheets, logging data from various locations by multiple copies of EnergyLogger, but only one worksheet is required. In the screenshot above you can see that I have created a spreadsheet call “energylog” by uploading an EnergyLogger CSV data file of the same name.

Open the document and optionally rename the worksheet from “Sheet1” to something more descriptive using the drop down menu (down arrow) beside the worksheet’s name at the bottom of the window. Here you can see that I have two worksheets, one for “Home” and one for the Tweet-A-Watt meter in my office (ignore “Raw Data”, this was a leftover from testing).

If you are starting with a blank spreadsheet you will need to create a header row with entries similar to those shown above. If you have imported an existing EnergyLogger CSV file, these headers will already exist. The first column should be labeled “Timestamp” and each subsequent column header should exactly match the name of the corresponding Kill-A-Watt meter. Since I started by uploading an existing EnergyLogger CSV data file, my spreadsheet already contains a fair amount of demand data averaged over 15 minute intervals.

Step 2: Configure EnergyLogger to store data to a Google Doc spreadsheet

From the “Google” tab of the EnergyLogger Options dialog box, fill in your Google username and password. Make sure the “Log data...” checkbox is checked and select an interval for logging averaged demand data for each Kill-A-Watt meter. Fill in the Spreadsheet and Worksheet Name fields with the names that you have created above.

EnergyLogger will average the demand for each Kill-A-Watt meter and post the data to your Google document at the user specified interval. When data is successfully posted, the status bar will display a message indicating the update time. If any errors occur, the status bar will indicate this and double clicking on the message will open a dialog containing information about all recent activity.

Relax and let EnergyLogger do it’s thing. You can check on your spreadsheet intermittently to assure yourself that things are being uploaded correctly. When you get a reasonable number of rows in the spreadsheet, move on to the next step. 

Step 3: Create an Interactive Time Series Chart

Google Doc spreadsheets function very much like a desktop spreadsheet application like Excel or OpenOffice. There are a number of useful objects that can be added to your Google Doc spreadsheet. For our purposes we are going to add a graphing “gadget” which will be configured to display power consumption vs. time.

From the “Insert” menu in your Google Doc spreadsheet, chose “Gadget”

In the resulting dialog box, scroll down to select the “Interactive Time Series Chart” and click on the “Add to spreadsheet” button.

Modify the gadget settings to include the full range of data in your worksheet and fill in the Title and Values suffix (Units) fields appropriately.

Click “Apply and close” and you should end up with something like the picture on the right. This type of gadget displays columns of numerical values (in our case power in watts) vs time. The user can use the scrolling area on the bottom of the chart to zoom in and scroll through the entire data set while a detailed view is shown in the top section. You can experiment with the gadget settings, specifying how the scaling should occur. Units for the y-axis should be in Watts (W).

One thing you will notice however is that the data in the chart does not update in real-time, and any attempt to set the range to a row number greater than the end of the spreadsheet results in an error. Don’t worry about this for now, we’ll get around that in the next step...

Step 4: Add the chart to iGoogle

Note: This may work with the Time Series Chart Gadget implemented in the Google Application Engine scripts used in the original Python code. If someone can confirm this please let me know...

One thing that you will notice while fooling around with the chart is the “Add Gadget to iGoogle...” item in the Gadet’s dropdown menu.

If you have a personalized Google Homepage (i.e. iGoogle), clicking this will add a smaller version of the “Interactive Time Series Chart” to your iGoogle homepage like the one shown below. 

Once a copy of the spreadsheet gadget appears in your iGoogle homepage it is no longer needed you can delete it from the spreadsheet, or as I did, move it onto its own sheet (which I called “Raw Data”). At this point we are nearly finished, but we still need to trick the iGoogle gadget into updating continuously.

Step 5: Modify the iGoogle gadget settings to update in real-time

In the drop-down menu of the iGoogle gadget, chose "Edit Settings...". Scroll over in the "Data Source url" field until you see the "range" section of the URL string like shown below...

Your range is likely set from A1 to the bottom rightmost cell in your spreadsheet ABxxxx. If you change the row value to some really large number (you can see here that I've changed it to 65535) the graph will always show the latest data points. This only works after adding the gadget to iGoogle (If you try to set the range to a huge row number in the spreadsheet gadget itself, it will give you an error complaining that the spreadsheet doesn't have that many rows).

I'm not exactly sure what the range limit is, I've chosen 65535 because it's the largest unsigned short integer value, but any large value should work. Either way, 65,000 rows is nearly 2 years of 15 minute data, so it will be a while before I’ll have to mess with it again.

Step 6: Watch your energy usage in iGoogle

Even though the data presentation available in the “Interactive Time Series Chart” is fairly simplistic, it will at least give you some of the functionality promised by Google PowerMeter while we wait for the gadget to be made publicly available.

Here you can see the Google PowerMeter software in action in a private rollout of the service for Google’s own employees. Although no details have been made available about exactly how the service will receive it’s underlying data, they have said that the service will be made available via a standard Google Gadget (like the “Interactive Time Series Chart” that we used here) and that the data will come via Google’s Data API, which is how we are storing the load data now.

There are other gadgets and functionality which could prove useful as well (the “Gauge” looks particularly interesting), and as I come up with ways to make use of some of these I’ll mention them here as well. If you’re able to get this working, or run into problems, post a message in our support forums and I’ll try to answer as many as I can.

Cheers.

Bill K