Sending Data to Google Docs

Exclusive offer: get 50% off this eBook here
Internet of Things with the Arduino Yún

Internet of Things with the Arduino Yún — Save 50%

Projects to help you build a world of smarter things with this book and ebook

£8.99    £4.50
by Marco Schwartz | May 2014 | Networking & Telephony Open Source

In this article by Marco Schwartz, author of Internet of Things with the Arduino Yún, we will discuss about sending the data to a Google Docs spreadsheet so that it can be accessed remotely from any web browser or from the Google Docs mobile app, and calculate the energy consumption and some other useful data such as the total energy cost of the device connected to your project.

(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:


Internet of Things with the Arduino Yún Projects to help you build a world of smarter things with this book and ebook
Published: May 2014
eBook Price: £8.99
Book Price: £14.99
See more
Select your format and quantity:

About the Author :


Marco Schwartz

Marco Schwartz is an electrical engineer, entrepreneur, and blogger. He has a Master's degree in Electrical Engineering and Computer Science from Supélec, France, and a Master's degree in Micro Engineering from EPFL, Switzerland.

He has more than 5 years of experience working in the domain of electrical engineering. His interests gravitate around electronics, home automation, the Arduino and Raspberry Pi platforms, open source hardware projects, and 3D printing.

He runs several websites based on Arduino, including the Open Home Automation website that is dedicated to building home automation systems using open source hardware.

He has written another book on home automation and Arduino called Home Automation with Arduino, CreateSpace. He has also published a book on how to build Internet of Things projects with Arduino called Internet of Things with the Arduino Yun, Packt Publishing.

Books From Packt


C Programming for Arduino
C Programming for Arduino

Raspberry Pi Home Automation with Arduino
Raspberry Pi Home Automation with Arduino

Raspberry Pi Home Automation Blueprints
Raspberry Pi Home Automation Blueprints

BeagleBone Robotic Projects
BeagleBone Robotic Projects

Learning ROS for Robotics Programming
Learning ROS for Robotics Programming

Raspberry Pi Robotic Projects
Raspberry Pi Robotic Projects

Robot Framework Test Automation
Robot Framework Test Automation

Robotium Automated Testing for Android
Robotium Automated Testing for Android


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software