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
-
Create a blank Google sheet.
-
In the first column, title it ‘Time’. The second column, title it ‘Temperature’. And the third, ‘Device’.
-
Freeze this top row View->Freeze->1 row
-
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.
-
Press the ‘Save’ icon in the script editor to save the script.
-
Press Publish->Deploy As App in the script editor window.
-
In the presented window, only change ‘Who has access to the app’ and change that to ‘Anyone, even anonymous’.
-
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.
- 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.
- Make sure you run a ‘refresh’ to get the latest data.
- Save the $now to a new variable ‘Time’.
- Save the temperature to a new variable ‘Temperature’.
- 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.
- The method should be set to POST as we are ‘sending’ or ‘posting’ something to our spreadsheet.
- The ‘Content Type’ should be set to FORM
- Select the data you wish to send. In our example, we are sending the Time, Temperature, and Device.
- 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.