Nest temperature logging

I had been wanting a Nest thermostat for awhile and this past October we finally decided to get one.  Mainly because our local energy smart program with our utility providers offered a 50% off rebate on it!  But even without a rebate Nest thermostats can save $131-$145/year (or less than a two year ROI and Nest now has the data to prove it).

We had just bought this house so I did not have any historical utility bills to validate these savings.  I could have went a year to get a baseline but who wants to do that.  I use google sheets to do my monthly bills/budgeting and I have over 5 years of longitudinal data, if we had stayed in the same house and bought the nest it would have been interesting to actually see the savings.

I was just curious and wanted to see the how the temperature fluctuates based on the schedule and the weather.   I thought it would be interesting to see how outside temp impacts inside temp and how hard my furnace has to work to keep up.  The Nest app does a nice job of simple showing you how much time your furnace has run each day, and if you tap on a day it will show you the breakdown of what time it ran for and how long.  It's interesting but I was curious how much the indoor temp fluctuated as an indicator to how well my house was insulated.  Or even just a graph that shows the indoor temp over the course of a day or week.  Below is screen shot from my Nest app's Energy History, pretty basic.

So naturally I searched around for a way to pull this data and found a method using a LAMP stack and an unofficial nest-api to query this data from your nest account and add it the mysql db then present it on a php webserver local or public.  This worked as expected but then I searched around some more and found a thread on the Nest Community which uses Google Sheets... brilliant.  I had not used the Google Apps Script yet so thought this was a perfect opportunity to see what it can do.  Luckily the script on the Nest Community site worked pretty much out of the box just by adding your nest credentials and zip code.  

The only thing missing was having this script get data on a schedule w/o out errors.  When I set it up it worked great and pulled data every time I manually ran the script, but if I set it to run hourly it would work at best 20% of the time.  It didn't make sense, why would it run on demand without error when ran manually but not during the trigger?  I kept thinking about it and thought maybe its how it gets run via trigger when I am not there (not logged in) vs when I manually do it while logged in.  

More searching... and found this little hint on Issue 2758: Trigger based UrlFetchApp.fetch() fails, where you just make your main function a doGet() and publish as a web app (it seems that you do have to execute it as yourself and let anyone access it, even anonymous for it to work).

So basically you just put the meat of the script in a doGet() function, publish you web app then create another function that does a UrlFetchApp.fetch('webapp url') then you can set your timebased() trigger on and it should run on that schedule.

 

So without further ado here's the script I use to query the indoor temperature, target temperature and humidity along with outdoor temperature and humidity.

	function doGet() {
	// you only need to modify the next four lines of this code then publish web app
	var email = 'email address'; //what you use to login to nest
	var password = 'password' ////what you use to login to nest
	var zip = 'zip code';
	var sheetid = 'sheet id'; //on your spreadsheet url its everything between /d/ <sheet id> /edit
	/* to publish web app just:
	1) Make sure the four variables are set above before you publish
	2) Click Publish --> Deploy as web app
	3) Describe the web app and save a new version
	4) Execute the app as: me (your google account)
	5) Who has access to the app: Anyone, even anonymous (this what allows the timebased() triggers to run as expected)
	6) Click Deploy
	7) Set your timebased() trigger to run getData() which just does a url fetch of this script and invokes doGet()
	*/
	var login_auth = performLogin(email,password);
	var headers = {
	"Authorization" : 'Basic '+login_auth['access_token'],
	"X-nl-user-id" : login_auth['userid'],
	"X-nl-protocol-version" : '1',
	'Accept-Language': 'en-us',
	'Connection' : 'keep-alive',
	'Accept' : '*/*',
	};
	var options = {
	'headers' : headers
	};
	var request=UrlFetchApp.fetch(login_auth['urls']['transport_url']+'/v2/mobile/user.'+login_auth['userid'], options);
	var result=JSON.parse(request.getContentText());
	var structure_id = result['user'][login_auth['userid']]['structures'][0].split('.')[1]
	var device_id = result['structure'][structure_id]['devices'][0].split('.')[1]
	var current_temp = result["shared"][device_id]["current_temperature"];
	var target_temp = result["shared"][device_id]["target_temperature"];
	var humidity = result["device"][device_id]["current_humidity"]/100;
	var auto_away = result["shared"][device_id]["auto_away"];
	var heater_state = result["shared"][device_id]["hvac_heater_state"];
	var time = new Date();
	var wxrequest=UrlFetchApp.fetch('http://api.openweathermap.org/data/2.5/weather?q=' + zip + '&units=imperial');
	var wxresult=JSON.parse(wxrequest.getContentText());
	var outside_temp = (wxresult["main"]["temp"]);
	var ss = SpreadsheetApp.openById(sheetid);
	var sheet = ss.getSheets()[0];
	// Appends a new row with 3 columns to the bottom of the
	// spreadsheet containing the values in the array
	sheet.appendRow( [ time, current_temp, target_temp, outside_temp, humidity, heater_state, auto_away ] );
	}
	function performLogin(email, password) {
	var payload = {
	"username" : email,
	"password" : password
	};
	var options = {
	"method" : "post",
	"payload" : payload
	};
	var response = JSON.parse(UrlFetchApp.fetch('https://home.nest.com/user/login', options).getContentText());
	if ('error' in response) {
	throw "Invalid login credentials";
	}
	return response
	}
	function getData(){
	var url = ScriptApp.getService().getUrl();
	var response = UrlFetchApp.fetch(url);
	}

view raw gistfile1.js hosted with ❤ by GitHub