Introduction: Nest Thermostat History Data Logger
Nest thermostat tracks temperature, humidity and furnace/AC usage and users are able to see historical data only for 10 days. I wanted to collect historical data (>10 days) and came across google spreadheets script that pings nest every set time and also gets local weather data from openweathermap.org and saves in the spreadsheet.
Everything was going well for a year, and the script suddenly stopped collecting data. After some google searching, I realized that one row every 5 minute for google spreadhseet means hitting the max limit of cells a google spreadsheet can accomodate. I updated the original script to still ping Nest every 5 mins, and but collect data in 1 row per day. Script checks the last row and if it is the same day, then it appends the data to the same row rather than adding a new row.
Credit for the original script. I just made a few edits to suit my needs.
// michael-pesce's work: https://gist.github.com/michael-pesce/a4ba55d4fc4...
// --> BEEZLY's work: https://gist.github.com/beezly/9b2de3749d687fdbff...
Keywords: Nest Thermostat History, Nest Heat, Nest Temperature History, Nest Thermostat Hack, Nest Thermostat Tips, Nest Energy History, Nest Daily Use, Nest Daily Usage, Download nest thermostat data
Step 1: Create a New Google Sheet
Name it whatever you'd like, e.g., "Nest Data"
Feel free to start with my shared google sheet (Do save a copy and save in your google drive). You can skip step #2 in that case.
Step 2: Copy Script
- On the menu bar click Tools -> Script Editor... to open Script Editor (new window)
- In Script Editor delete all the default scripts/files, and create a new one (I called it "NestScript.gs")
- Cut and paste this entire text from the attached file into NestScript.gs, then SAVE THE SCRIPT
Step 3: Deploy As Web App
- On the menu bar click Publish -> Deploy as Web App
- Select "Execute the App as Me"
- Select Who has access to the app: "Anyone, even anonymous"
Copy/take note of the link to your new web app and add to the runDataCollection routine below (the first code)
Step 4: Triggers
This is where you define how often to collect data.
- On the menu bar click Current Project's Triggers
- Click add new trigger
- For Run select the function runDataCollection, Events: time-driven, and select the rest per your preference (I do every 5 mins)
Step 5: Additional Information in Script
Add user specific information in the script as below.
Other needed information (line numbers should be correct if start of this script "runDataCollection()..." is 40:
- Line 45: add webapp link in the runDataCollection routing
- Line 53: Nest username and password
- Line 77: Thermostat device ID
You can get the id for each thermostat by going to the Nest dashboard, clicking on the Thermostat, clicking the gear icon on the top right then copying the "Serial no."" field. It will look something like: 02XX01XX471XXX3S
- Line 90: City ID (see instructions in the lines above.)
To find city ID go to "http://openweathermap.org/find?q=" search for your city, click on the city link and the ID will be the 7 digit number in the URL
- Line 103: Google sheet ID (see instructions in the lines above.)
The Sheet id can be grabbed from the shee URL See this pattern for where the Sheet id is in the URL:https://docs.google.com/spreadsheets/d/THIS_IS_WHERE_THE_SHEET_ID_IS/edit#gid=123456789
Step 6: Finalize the Spreadsheet
These two lines need to be in the spreadsheet for the code to work.
First line (Header row): Space separates columns
Date/Time Month Day Year Temp Humidity OutsideTemp OutsideHumidity Heat_Usage AC_Usage Weather AutoAway
Add yesterday's date in the first column and zeros in the remaining columns.
That is it. Let the script run and it should add one row per day and pinging your thermostat and local weather for data per the trigger frequency you set.
If you redeploy the webapp, use the new revision. I had problems using the same revisions with script not running.