Monitor Temperature with Google Sheets

Overview

Monitoring your home's temperature and humidity is helpful, it allows the inhabitants to be able to adjust heating in order to create a perfectly heated atmosphere. It is nice to look at a screen which informs you on the current temperature and humidity, but what if you could visualise it live, on a Google Spreadsheet, so you can always check your home's status by a click or two on any device.

This project aims to make all of that easy and accessible for anyone and everyone. Utilising Temboo's Google Sheets choreo, it is easy to program your Arduino Yun to append values containing your home's temperature and humidity to a Google Sheet.

Functionality

TemperatureVisualiser will read the reading from a DHT11 temperature and humidity sensor and send the data to Temboo. Then Temboo will redirect the data to Google APIs and then Google Sheets, displaying the information on a spreadsheet. Here is an image illustrating the process.

Here is an illustrated image of the project's code overview.

• Read Temp and Humidity will read the temperature and humidity from the sensor and will then store the data.

• Process data will process the Temperature and Humidity values and will input them in a buffer together with the son format required to send.

• Parse Data to Temboo will send the buffer as a choreo to Temboo.

• The micro controller will wait for an amount of time before repeating the action

Temboo and Google requires an array of arrays to be sent in json format with the values. The formatted buffer will look like this.

[ [ temp, hum, ] ]

Temp is the temperature reading received from the DHT11 sensor. Hum is the humidity reading received from the DHT11 sensor.

Benefits

The users operating this project will benefit in:

• Easy access to data anywhere and at any time

• Ease of use - only power the Arduino Yun

• Easy Visualisation with Google Sheets

Constructing the Project

Step 1: Required Apparatus

This project contains very few hardware, so it is easy to build. For this project you will need:

• jumper wires

• 1 Arduino Yun

• 1 DHT11 Temperature and Humidity Sensor

• 1 Power Source (I used a USB cable connected to a Mac)

Step 2: Connecting the Circuit

Only 3 components = easy assembly. Here are the schematics for this project: I am going to power my DHT11 sensor with 5v, so make sure that your sensor supports 5v, and if it does not, use 3.3v.

Step 3: Acknowledging the Code

There are three sections to the code:

• read sensor

• create buffer

• parse buffer

The projects consists of two sketches, the main code and a header file containing all the user's accounts data on Google and Temboo. Here is a closer look at the sections of code, the header file will be set up in "Setting Up The Variables" below.

• Read Sensor Value

overview = DHT.read11(11); // Read the sensor output 
temp = DHT.temperature,1; // store temperature 
hum = DHT.humidity,1; // store humidity 

This section of code reads the data from the DHT11 sensor using a library. When the results are read, they are stored in seperate variables for temperature and humidity (temp and hum ).

• Append to Buffer

sprintf(values,"[[%02d,%02d,]]",temp,hum); // insert temp and hum in the json packet required 

This is a line of code which appends the sensor values to a buffer, sprintf will add characters to a buffer. [[%02d,%02d,]] is the layout required by Google to be able to read the values.

• Parse Buffer to Temboo

TembooChoreo AppendValuesChoreo; // initialise which choreo to begin 
 AppendValuesChoreo.begin(); // begin the choreo 
 // Set Temboo account credentials - Set in Acounts.h 
 AppendValuesChoreo.setAccountName(TEMBOO_ACCOUNT); 
 AppendValuesChoreo.setAppKeyName(TEMBOO_APP_KEY_NAME); 
 AppendValuesChoreo.setAppKey(TEMBOO_APP_KEY); 
 // Set Choreo inputs - Set in Accounts.h 
 AppendValuesChoreo.addInput("RefreshToken", GOOGLE_CLIENT_REFRESH_TOKEN); 
 AppendValuesChoreo.addInput("ClientSecret", GOOGLE_CLIENT_SECRET); 
 AppendValuesChoreo.addInput("Values", values); 
 AppendValuesChoreo.addInput("ClientID", GOOGLE_CLIENT_ID); 
 AppendValuesChoreo.addInput("SpreadsheetID", GOOGLE_SPREADSHEET_ID); 
 AppendValuesChoreo.setChoreo("/Library/Google/Sheets/AppendValues");
 // identify choreo to run 
 AppendValuesChoreo.run(); // run the choreo 

This section of code initialises Temboo, begins the choreo, sets the Temboo Account Credentials and the Google Account Credentials and runs the choreo. All the accounts' credentials can be set in the Accounts.h file.

Setting Up the Variables

All variables are set up in the Accounts.h header file which comes with the project's main sketch, all you have to do is define the variables in the header file to make the choreo to run. The inputs will be explained in "Backend Program" below, where we will setup Temboo and Google APIs for the project.

Libraries

Temboo - copyright (c) 2017 Temboo Inc. under the Apache 2.0 Licence. View this licence

DHT - Author Rob Tillaart this library is in the public domain

Bridge - Arduino LLC this library is in the public domain

Setup

For this project, you will need an Arduino Yun which has been set and initialised. View this guide on getting started with the Yun. Here is a link with information about Temboo and Running Choreos on your Arduino Yun. Once you are ready, connect your Yun to a PC/Mac and run the sketch below.

Backend Program

To run this project we will need to configure two platforms: Temboo and Google APIs. We will do this step by step. If the setup does not work out for you, try it again (it took me a while to succeed).

• Step 1: Create your Temboo Account

The first step is to create a Temboo Account, to do this visit this page. In the box in the middle of the page, write a username. Click "Try it Out" and fallow the instructions on the fallowing page for setting up your account. Temboo is free to use on the basic plan, which will provide you with 250 choreo runs er month.

• Step 2: Create and Setup your Google Account

To create a Google Account, visit this page. After you set up your account. Go to Google Accounts. Sign in if you have to, now your window should look like this.

Now, navigate to the Sign-in & Security header and select Signing into your Google Account. Now make sure that you enable "Two Step Verification" in your account. When you are done, your page should look like this.

• Step 3: Setup Google APIs

This is the most important and confusing step, take your time! Visit Google Console. The page may ask you to sign in, do so. You will see a blank page, if you do not have a project created, please do so by clicking the "Create a Project" button. Now fallow the steps implemented to create a project. After you wait for your project to load, you should see a window like this.

Now the first thing we have to do is click on "Credentials", on the left slide-bar. This should bring you to a window like this.

Now click "Create Credentials" and select OAuth Client ID from the drop menu. You will now be asked which type of application you want to create. Select "Web Application". Now give your app a name, I called it Temboo. Underneath, you should see a pitch saying "Authorised redirect URIs", in it write:

https://ACCOUNT_NAME.temboolive.com/callback/google

Place your Temboo Account's name instead of ACCOUNT_NAME, when you are done, your URL should contain your username, e.g. https://myaccount.temboolive.com/callback/google

Now your window should look like this, do not fill in "Authorized JavaScript origins".

Now click "Create". When you do so, you will be redirected to your dashboard and a window will appear, giving you your client ID and client secret. Make sure you keep these codes as they are necessary in the next steps. Now click on "Dashboard" again and then click on enable APIs at the top of the page. Now your window should look like this.

Search for "Google Sheets" in the search field. Now click on the Google Sheets API. After you clicked it, your window should look like this.

Click the "Enable" button. Now you will be redirected to your dashboard.

• Step 4: Initialise OAuth

Now go to Temboo and Sign in, your page should look like this.

Now on the menu on the left hand side, scroll down until you see "Google" and then click on the arrow next to it. After you do so, you will open a drop menu, open OAuth, and then click on InitialiseAuth. Your window should look like this.

Now place you Google ClientID in the "ClientID" field and then scroll down until you see a section labelled "notes", there you can find the wanted scope for your project. We will be using Google Spreadsheets, so copy all the contents of the box, which should be:

https://spreadsheets.google.com/feeds/

https://docs.google.com/feeds/

https://docs.googleusercontent.com/

Now paste them in the scope box at the top. Your window should look like this.

Now click "Run Now" to run your code. When the code finished running, you will receive two fields, a URL and a callbackID, make sure that you also save your callbackID for further use. Now visit the URL that appears in the box above. You should get a request from Google requesting you to sign in, do so. Now Google may inform you that the website is not safe, if it does, click "advanced" at the bottom of the page and click "open temboolive.com" below that. Now you will receive another notification telling you that the app wants to have access to documents and files, click "Allow". Now you should see a blank page, if you do, proceed to the next part of the setup, if you do not see a blank page, start this step from the beginning.

• Step 5: Finalise OAuth

To finalise your OAuth go to Temboo. On the left side menu select Google - OAuth - Finalise OAuth. Your window should look like this.

Fill in the fields with the data collected previously and click on "Run Now", if the code works, you should receive an Access Token and a RefreshToken, make sure that you save both of them as they will be needed in the next step. Well done, the hard part is over. If you succeed, you can go on to the next step, if you do not, try this step again from the beginning, make sure that the URL in the "Authorised Redirect URL" field on Google is typed correctly and that your clientID and client secret are correct.

• Step 6: Run a Probe

To check that your configuration is correct, we will try a test sketch. Visit Temboo and go to Google - Sheets - AppendValues, your page should look like this.

Now fill in the ClientID, Client Secret and Refresh Token received in the previous setups. Next, go to Google Docs. Create a new document, after you did so, give it a name. The spreadsheet ID is located in the URL of the spreadsheet. eg. if the spreadsheet's URL is

https://docs.google.com/spreadsheets/d/1wn9R5vPJLIulgxKjDflqnN6X84Xe7zu6925mRQO-tVo/edit#gid=0

Then the ID is 1wn9R5vPJLIulgxKjDflqnN6X84Xe7zu6925mRQO-tVo, now copy your spreadsheet ID and paste it in the spreadsheet ID box in Temboo. Lastly, copy this array and place it in the "Values" field.

[ [ "100", "200", "300" ] ]

For more data on how to format the values, scroll down to the notes on the page. Your window should look like this.

Now scroll down until you see a box with code labelled "Code", copy all the contents of the box and paste it in the Arduino IDE. Below that box is another box labelled "Header File", copy the contents of that box, and in the Arduino IDE, create a new tab and call it "TembooAccount.h" and paste the contents into the new tab, compile the code.

your Arduino Yun to your PC/Mac via a USB type A to C cable and wait until your Yun has connected to wifi, this is indicated by the rapid flash of a blue LED labelled WLAN. When your Yun is connected to wifi, upload the code and open the Serial Monitor. If the project worked, you should get a response in the monitor from Temboo. Now go back to Google docs, and click on the spreadsheet you created earlier. You should see three values printed, 100 200 300 in separate cells.

If this works, you are a step away from success.

• Step 7: The Final Step

Now open the code below and make sure to edit the Header file to your preference, including the values that were previously received. The default delay between choreo runs is one minute, you can change it if you want. I would recommend to turn proDebug on (setting it to 1), on the first tries and open the Serial Monitor to receive progress data and debug info. Using this project without connecting to a computer requires proDebug to be set to 0 (its initial value). This is it, just upload the sketch to the Yun and you are all set. Temperature and Humidity anywhere.

Final

As the project has little hardware I decided not to build an enclosure, but if you want, feel free to make one. This is the Finished Piece.

Background

I created this project as a tutorial to make IOT easier to understand, personally, it took me a while to work it out and so I thought of saving you some time. I think that checking temperature and humidity on a spreadsheet is very nice and easy, all the data together and easy to access.