11-16-2021 02:39 PM
I recently saw that I could download Nest data to a Google Sheet. Is there similar code that can do this to an Excel spreadsheet?
11-22-2021 12:59 PM
I'd love to know how to do this as well, to tie up with our oil consumption!
The app used to give much fore data than it does now, and it's a real pain having to go over every 10 days to manually put it in a spreadsheet
11-22-2021 01:08 PM
Just found Google Takeout. I haven't seen what it provides yet (they send an email when the file is prepared), but says it includes csv, which will go directly into Excel. Nest is well down the list.
Happy bunny here 🐰🐰🐰
12-09-2021 01:24 PM
It can download a takeout-date-code.zip file. But when you unzip that you will end up in a xx.html directory just giving online directions. What you want is back in the original directory as year-month- sensors.csv . It is a lot of data since it is every 15 minutes of every day in 24 hr format. You may need to widen the first date column.
My Nest E has yet to revise the first schedule I entered but I am not sure that is a good place to start. Oh, there was a x.json file that I was able to look at with Notepad (probably not correctly) that appears to be the code for changes during each day. I have no idea what that detail is. Thanks for giving me the clue that the data actually was in the zip file.
08-03-2022 05:59 AM
I've had awful trouble trying to figure out when the heating is actually running (like the history given in the app).
Have you been able to glean that from the data?
If so any pointers as to what I've been missing in processing the data would be VERY appreciated!
09-25-2022 11:18 AM
I downloaded the data and processed the json into a SQL database....
on a comparison of 3 days worth of data, it simply does not match up... example... on the 24th, the Nest app shows my thermostat heating 1 hour... in the json... the duration for the single event is 86400 seconds... or 24 hours...
does anyone have any info on how to interpret this data ? I see many reddit posts that would indicate it is simply invalid, incorrect data
10-13-2022 11:17 AM
Sorry for the late reply. I wanted to follow up and see I could be of any help. I found an article with more details. Please let me know if you are still having any trouble from here, as I would be happy to take a closer look and assist you further.
10-13-2022 01:39 PM
Your help would be REALLY appreciated!
Like others, I've had a look at the Sensor Data tables. They do not allow you to figure out when the heat was actually operating.
I thought I had cracked it when I saw the option for downloading TimeOfUseMetadata. However, even when this is downloaded individually it is a zip file with no data. So either this isn't what I'm looking for, or there is an issue resulting in the file not being included in Takeout data.
It seems bonkers that the most sought after piece of data simply isn't there. Or are we all missing it somehow?
The first helpful step would be to advise us which file this is actually in, I guess 😄
10-13-2022 11:51 AM
Thanks for the reply... But absolutely no help... I am well ware of the history in app... But it does not match the data in the json I get from Google takeout... At least I cannot find a pattern to match the json data to the app history... This appears to be consistent with others who have tried... The consensus is either the data in takeout is bad... Or there is a super secret pattern to the data that no one has figured out yet...
10-22-2022 09:05 AM
+1 from me too. I've just spend several hours parsing the json files in Python and found inconsistencies between my analysis and the 'last 10 days' data available from Nest.
The json files claiming to contain the relevant data are organised as monthly summaries. The top-level of the data hierarchy is the day.
At this per-day level there's a parameter called "totalHeatingSeconds". Unfortunately this appears largely useless - it's not consistently populated and what data is there seems unrealistic.
There is data in the 'cycles' tree - this does seem to be useful - it (claims to) include start and end-times for heating both in time format and via a 'plainText' field which contains entries such as "heating cycle from 6:01 AM to 8:09 AM".
That data from the 'cycles' data structure does mostly agree with the Nest website view, except for a couple of days where the 'cycle' data was blank (and another where I don't believe the figure in the cycle data).
As an aside there is an 'energy_latest' json file in my download, which appears to be the 10 days of data as displayed on the Nest Website at time of download - that does have apparently correct data in the "totalHeatingSeconds" field - but disagrees with the 'cycles' data on a couple of the days.
So... my plan is to keep an eye on the 10 day-website history for a while and then do another data request and see if I can do some more data analysis.
A bit bloody useless though from Nest/google.
10-23-2022 04:03 AM
After a bit more geeking out, I can confirm that the 'cycles' data is probably mostly correct, but is incomplete and somewhat unreliable (in my case at least). The two main issues appear I've spotted so far seem to be associated with missing heating data.
The data dump includes temperature (as well as humidity, motion sensor, ambient light and some other stuff I don't understand). From the temperature data it is easy to see when the heating is running, so that's an independent way of checking the cycles data.
So, all-in-all, it looks as if about 1/3rd of my 'cycles' dataset is rubbish, but maybe 2/3rds is OK.
If you were even more of a data-analysis geek than me, you might even be able to process the temperature sensor data to pull out heating times.
11-29-2022 06:54 PM
I'm happy to find someone who has had the same experience I have! The cycle data is definitely incomplete and flaky.
I have a heat pump, backed up by a propane furnace as "Alt Heat" for use during very cold weather. My main interest in downloading Nest data is to figure out how many hours the heat pump was used, vs. the propane furnace, on each day, since they use different kinds of fuel. The Nest app does not distinguish between different heat sources.
I downloaded the Nest takeout data a few times, and wrote a Python script to extract the cycles data, which does have boolean flags that say whether it was using "heat1" (the heat pump), or "altHeat" (the propane furnace). It converts the timestamps into localtime, which is necessary in order to correlate its output with what I get from the Nest app, to confirm that the data is complete.
As AberangSteve said, sometimes cycles are randomly missing. It's especially annoying because it has fields like "isComplete" and "cyclesIncomplete", which NEVER admit that anything is incomplete. "isComplete" is always true, and "cyclesIncomplete" is always false, even when I have determined by comparing to the Nest app's data that some cycles are in fact missing. So the only way I can really determine if it's complete is to add up the durations (with some fiddling in exactly how it rounds to a multiple of 15 minutes) and compare to what the Nest app says.
I have found that different exports may be more complete than others for certain cycles. On 11/26 I did a Takeout export, and it had complete data for 11/25, as well as data that was complete for the parts of 11/26 that came before I ordered the export (which was most of it). Then on 11/29 I did another export, and although it had complete data from 11/27 and 11/28, its data from 11/25 and 11/26 was incomplete, and lacked some of the cycles that my previous export had had.
I'm still new to this, but it seems that maybe I need to do an export every couple of days and then union the datasets or something....
11-30-2022 06:06 PM
Thanks for documenting this!
Before discovering this thread I'd spent some time independently parsing the json file(s) in the takeout and landed on the same cycles data structure to parse the 'duration' field; this thread confirms the inconsistencies I am finding between the 10-day history reported on the app and the seemingly detailed json output. Most of the cases where the duration is upwards of an hour (3600s+) seem very sus -- there's absolutely no way my Nest was on for 14 hours straight, as the json indicated. The Nest app for the same day showed usage for a total of 6 hours which is way more believable. I can't seem to land on a reasonable discriminator to work around through the rubbish data -- the heat sources for me show 'heat1' as true all the time.
Just disappointing -- hoping there's some effort from Nest's side to fix this going forward at least.
12-01-2022 01:22 AM
I have bought a Nest hoping to have access to my heating data and it has been disappointing.
Will Google Nest do something about it? Is there a way to report issues and ask for fixing?