(For more resources related to this topic, see here.)

The first step is to set up a Google Docs spreadsheet for the project. Create a new sheet, give it a name (I named mine Power for this project, but you can name it as you wish), and set a title for the columns that we are going to use: Time, Interval, Power, and Energy (that will be calculated from the first two columns), as shown in the following screenshot:

We can also calculate the value of the energy using the other measurements. From theory, we know that over a given period of time, energy is power multiplied by time; that is, Energy = Power * Time.

However, in our case, power is calculated at regular intervals, and we want to estimate the energy consumption for each of these intervals. In mathematical terms, this means we need to calculate the integral of power as a function of time.

We don’t have the exact function between time and power as we sample this function at regular time intervals, but we can estimate this integral using a method called the trapezoidal rule. It means that we basically estimate the integral of the function, which is the area below the power curve, by a trapeze. The energy in the C2 cell in the spreadsheet is then given by the formula:

Energy= (PowerMeasurement + NextPowerMeasurement)*TimeInverval/2

Concretely, in Google Docs, you will need the formula, D2 = (B2 + B3)*C2/2.

The Arduino Yún board will give you the power measurement, and the time interval is given by the value we set in the sketch. However, the time between two measurements can vary from measurement to measurement. This is due to the delay introduced by the network. To solve this issue, we will transmit the exact value along with the power measurement to get a much better estimate of the energy consumption.

Then, it’s time to build the sketch that we will use for the project. The goal of this sketch is basically to wait for commands that come from the network, to switch the relay on or off, and to send data to the Google Docs spreadsheet at regular intervals to keep track of the energy consumption.

We will build the sketch on top of the sketch we built earlier so I will explain which components need to be added. First, you need to include your Temboo credentials using the following line of code:

#include "TembooAccount.h"

Since we can’t continuously measure the power consumption data (the data transmitted would be huge, and we will quickly exceed our monthly access limit for Temboo!), like in the test sketch, we need to measure it at given intervals only. However, at the same time, we need to continuously check whether a command is received from the outside to switch the state of the relay. This is done by setting the correct timings first, as shown in the following code:

int server_poll_time = 50;
int power_measurement_delay = 10000;
int power_measurement_cycles_max = power_measurement_delay/server_
poll_time;

The server poll time will be the interval at which we check the incoming connections. The power measurement delay, as you can guess, is the delay at which the power is measured.

However, we can’t use a simple delay function for this as it will put the entire sketch on hold. What we are going to do instead is to count the number of cycles of the main loop and then trigger a measurement when the right amount of cycles have been reached using a simple if statement. The right amount of cycles is given by the power measurement cycles_max variable.

You also need to insert your Google Docs credentials using the following lines of code:

const String GOOGLE_USERNAME = "yourGoogleUsername";
const String GOOGLE_PASSWORD = "yourGooglePass";
const String SPREADSHEET_TITLE = "Power";

In the setup() function, you need to start a date process that will keep a track of the measurement date. We want to keep a track of the measurement over several days, so we will transmit the date of the day as well as the time, as shown in the following code:

time = millis();
if (!date.running())  {
  date.begin("date");
  date.addParameter("+%D-%T");
  date.run();
}

In the loop() function of the sketch, we check whether it’s time to perform a measurement from the current sensor, as shown in the following line of code:

if (power_measurement_cycles > power_measurement_cycles_max);

If that’s the case, we measure the sensor value, as follows:

float sensor_value = getSensorValue();

We also get the exact measurement interval that we will transmit along with the measured power to get a correct estimate of the energy consumption, as follows:

measurements_interval = millis() - last_measurement;
last_measurement = millis();

We then calculate the effective power from the data we already have. The amplitude of the current is obtained from the sensor measurements. Then, we can get the effective value of the current by dividing this amplitude by the square root of 2. Finally, as we know the effective voltage and that power is current multiplied by voltage, we can calculate the effective power as well, as shown in the following code:

// Convert to current
amplitude_current=(float)(sensor_value-zero_
sensor)/1024*5/185*1000000;
effectivevalue=amplitude_current/1.414;

// Calculate power
   float effective_power = abs(effective_value * 
effective_voltage/1000);

After this, we send the data with the time interval to Google Docs and reset the counter for power measurements, as follows:

runAppendRow(measurements_interval,effective_power);
power_measurement_cycles = 0; 

Let’s quickly go into the details of this function. It starts by declaring the type of Temboo library we want to use, as follows:

TembooChoreo AppendRowChoreo;

Start with the following line of code:

AppendRowChoreo.begin();

We then need to set the data that concerns your Google account, for example, the username, as follows:

AppendRowChoreo.addInput("Username", GOOGLE_USERNAME);

The actual formatting of the data is done with the following line of code:

data = data + timeString + "," + String(interval) + "," + 
String(effectiveValue);

Here, interval is the time interval between two measurements, and effectiveValue is the value of the measured power that we want to log on to Google Docs. The Choreo is then executed with the following line of code:

AppendRowChoreo.run();

Finally, we do this after every 50 milliseconds and get an increment to the power measurement counter each time, as follows:

delay(server_poll_time);
power_measurement_cycles++;

The complete code is available at https://github.com/openhomeautomation/geeky-projects-yun/tree/master/chapter2/energy_log.

The code for this part is complete. You can now upload the sketch and after that, open the Google Docs spreadsheet and then just wait until the first measurement arrives. The following screenshot shows the first measurement I got:

After a few moments, I got several measurements logged on my Google Docs spreadsheet. I also played a bit with the lamp control by switching it on and off so that we can actually see changes in the measured data. The following screenshot shows the first few measurements:

It’s good to have some data logged in the spreadsheet, but it is even better to display this data in a graph. I used the built-in plotting capabilities of Google Docs to plot the power consumption over time on a graph, as shown in the following screenshot:

Using the same kind of graph, you can also plot the calculated energy consumption data over time, as shown in the following screenshot:

From the data you get in this Google Docs spreadsheet, it is also quite easy to get other interesting data. You can, for example, estimate the total energy consumption over time and the price that it will cost you. The first step is to calculate the sum of the energy consumption column using the integrated sum functionality of Google Docs.

Then, you have the energy consumption in Joules, but that’s not what the electricity company usually charges you for. Instead, they use kWh, which is basically the Joule value divided by 3,600,000. The last thing we need is the price of a single kWh. Of course, this will depend on the country you’re living in, but at the time of writing this article, the price in the USA was approximately $0.16 per kWh. To get the total price, you then just need to multiply the total energy consumption in kWh with the price per kWh.

This is the result with the data I recorded. Of course, as I only took a short sample of data, it cost me nearly nothing in the end, as shown in the following screenshot:

You can also estimate the on/off time of the device you are measuring. For this purpose, I simply added an additional column next to Energy named On/Off. I simply used the formula =IF(C2<2;0;1).

It means that if the power is less than 2W, we count it as an off state; otherwise, we count it as an on state. I didn’t set the condition to 0W to count it as an off state because of the small fluctuations over time from the current sensor. Then, when you have this data about the different on/off states, it’s quite simple to count the number of occurrences of each state, for example, on states, using =COUNTIF(E:E,”1″).

I applied these formulas in my Google Docs spreadsheet, and the following screenshot is the result with the sample data I recorded:

It is also very convenient to represent this data in a graph. For this, I used a pie chart, which I believe is the most adaptable graph for this kind of data. The following screenshot is what I got with my measurements:

With the preceding kind of chart, you can compare the usage of a given lamp from day to day, for example, to know whether you have left the lights on when you are not there.

Summary

In this article, we learned to send data to Google docs, measure the energy consumption, and store this data to the Web.

Resources for Article:

---

Further resources on this subject:

• Home Security by BeagleBone [Article]
• Playing with Max 6 Framework [Article]
• Our First Project – A Basic Thermometer [Article]

---