How To Log Temperature Changes To Google Sheets Using Web Request

logging

(Alan) #1

Case Study - How To Log Temperature Changes To Google Sheets Using Web Request

This example will demonstrate how to set up a Google sheet to receive data from a Piston that is posting data to it using Web Request and POST.

The first section will cover setting up the Google sheet and the script. The second part will cover the creation of the piston and what is needed to pass data from CoRE to the spreadsheet.

What is important to keep in mind with this example is the names of the columns in your Google sheet must match exactly the names of the variables you are passing to it. In our example, we will create and pass the variables Time, Temperature, and Device.

Required:
-1 sensor capable of giving temperature
-basic working knowledge of spreadsheets, how to create one

Part 1: Google Sheet and Script Setup

This was a good article to check out: Getting Started with Google Apps Script

  1. Create a blank Google sheet.

  2. In the first column, title it ‘Time’. The second column, title it ‘Temperature’. And the third, ‘Device’.

  3. Freeze this top row View->Freeze->1 row

  4. Open the script editor Tools->Script Editor. You will be presented with a blank area into which you will paste the script. Copy and paste the cschwer script into this blank area.

  5. Press the ‘Save’ icon in the script editor to save the script.

  6. Press Publish->Deploy As App in the script editor window.

  7. In the presented window, only change ‘Who has access to the app’ and change that to ‘Anyone, even anonymous’.

  8. Press Deploy. You will be sent through a few windows to make sure you understand what you are doing. Say yes. When you presented the below window, first copy the entire URL and save that somewhere then press ‘OK’.

.
9. Within the newly pasted script, toward the top, you will find a line that includes “REPLACE ME WITH SPREADSHEET ID”. Since we have not launched the script, we can’t replace this just yet. We will start and stop the script now to achieve this. To stop the script, press Publish->Deploy As Web app and press the blue ‘Disable web app’.

.
10. Next, edit the script and replace what is between the quotes of “REPLACE ME WITH SPREADSHEET ID” with the long string part of that saved URL:

.https://script.google.com/macros/s/<-THIS VERY LONG SECRET STRING HERE IS WHAT YOU WHAT TO COPY->/exec

ONLY COPY THE SECTION OF THE STRING AS INDICATED ABOVE, NOT THE ENTIRE URL!

Your script should go from this:
var ss = SpreadsheetApp.openById(“REPLACE ME WITH SPREADSHEET ID”);

To something like this:
var ss = SpreadsheetApp.openById(“km51UwFyWxL84GCTzkPDtVMG8b0uvOe3zzsm”);
.
11. Now, press the ‘Save’ icon again to save your work.
12. Re-publish your work again, Publish->Deploy As Web app again setting the ‘Who Has Access’ to ‘Anyone, even anonymous’ and press OK.

The script is now cycling in the background and awaiting data from your motion sensor. You have completed setting up the Google sheet and its’ script!

Part 2: CoRE and the Piston

Before starting piston, set CoRE for expert mode: Settings->ExpertFeatures turn ON Expert Mode

Pison Mode: Basic

Two different views of the same piston.

  1. Create a piston that checks for temperature changes. Make sure you save the device list to the variable ‘Device’. This is located under ‘save matching device list’ under the Advanced Options of the Trigger of the motion sensor.
  2. Make sure you run a ‘refresh’ to get the latest data.
  3. Save the $now to a new variable ‘Time’.
  4. Save the temperature to a new variable ‘Temperature’.
  5. Create the ‘Make a web request’. In the URL, place that ENTIRE URL copy you took down when publishing your Google sheet script. This includes the /exec/ at the end of that url.
  6. The method should be set to POST as we are ‘sending’ or ‘posting’ something to our spreadsheet.
  7. The ‘Content Type’ should be set to FORM
  8. Select the data you wish to send. In our example, we are sending the Time, Temperature, and Device.
  9. Save your work. You are complete!

If you have the range, try putting your motion sensor in the refrigerator for a few minutes or cupped in your hands, blow into them to warm the sensor. Give it a few cycles before you start to see data in your sheet.

IMPORTANT!!! That script is still running! Don’t forget about it. If you update your piston or simply don’t want to use it any more, make sure you Disable the script when you are not using it or when it is done with it.


CoRE - Get peer assistance here with setting up Pistons
[SHARE] CoRE - show & tell all about Pistons
#2

That looks great. You can also use IFTTT. A little simpler. I do that for my motion sensor stats.


(Alan) #3

Please do share! Here would be a great place to add your methods. Thank you!


#4

With pleasure.


(Alan) #5

Perfect, thank you for adding to this thread.


(Kevin) #6

There are also 2 SmartApps dedicated to logging data to Google Sheets:


(Alan) #7

Correct, thank you. They both served as inspiration and ‘spare parts’ for my example. We were missing information on how to do this method and perhaps this will inspire more POST and GET examples from folks.

You know what else would be great to see is the .gs code side of things.

Answer me this…

How would I have a door sensor trigger fetching data from a specific cell in a Google sheet and then use that as a variable in my piston. What is involved in the .gs side? How do you specify Getting a specific cell number? Thank you!


(B Hopping) #8

Aeotec & Samsung Temperature Sensors -->
Smartthings Hub/App —>
“Sharp Tools” App—>
“Tasker” App ----->
Google Sheets

Took some time "& creativity but results are fun!


(Ninjaivxx) #9

The link to the code for the Script Editor is broken. Is there a different link we can use or something?


(Brandon) #10

If you’re referring to the linked github script from ‘cschwer’, it has moved to this link: https://raw.githubusercontent.com/cschwer/apps/master/googleSheetsLogging/Code.gs


(Jacob N Smith) #11

I can’t seem to get this working with WebCore (see below). Initially the Sheets script was throwing an error on line 5

var ss = SpreadsheetApp.openById("That Code Here");

until I used the ID from https://docs.google.com/spreadsheets/d/THIS ID HERE/edit#gid=0. I did still use the “macro” ID in the piston though.

Can anyone help?