bmw220

Anyone else getting less than expected counts? Counts are in column "M" and that is how many times google wrote in that row. If you are recording every 5 minutes, the total counts for a day (row) should be (60/5)*24 = 288. I typically get between 220-260. It tells me that script fails to write to the sheet many times throughout the day.Is there anyone who is not experiencing this issue, and getting correct number of counts in column "M"?One potential cause could be that script is trying to ping nest when it is busy communicating with nest server, and script connection gets rejected. If you have more information on the cause and solution, please do post. I have tried changing trigger times to every 10 minutes without success.

Sorry, I am not sure why some people are getting this error. It looks like Nest is unable to authenticate your credentials. Google is your best bet (5 minutes of searching did not bring anything worth mentioning). Or, try to reset your thermostat and see if that fixes the issue.

Line 81 and 92 of the script converts C to F. You can delete "* 1.8000 + 32.00" from those lines to keep temperature in C.

Hello to you across the pond! Try changing the timezone in the script properties. Open script > File > Project Properties > Timezone

171 line writes the combined (avg. or sum) data of 1hr time interval (in your case) and line 183 writes the first reading of the hour. That is the reason for those to be different. Sorry, I don't have any other suggestions on you "undefined" issue.

That appears to be an initiation error. I suggest matching your code to the code in the shared spreadsheet. Specifically look for some variable (related to autoaway and heat usage) where it is assigned a value of 0 in the shared spreadsheet and add it to your code if that line does not exist. Also match the code lines that writes to the spreadsheet.

I don't think your accounts for nest and google sheet need to match. If you have already checked that your username and password specified in the script are correct, I am not sure what the cause of the error is.

I don't think I have seen this error before. Based on the error message, it looks like the username and/or password you have in script for the thermostat are incorrect.

Your second statement is correct, but instead of hour, I would say, it records the amount of time the thermostat was set to away mode (it will not necessarily be 0 or 1; it could be in between that because nest is pinged every 5 minutes).You can easily add another column in sheet and add a formula there =if(CELLID<0.5, "HOME", "AWAY"), where CELLID is your autoaway column. You can do that in the script as well, but I suggest you keep the running time there and add a new variable (a lot more work than just adding another column in the sheets.). Is script recording anything other than 0? If not, then it could be an issue with the script or you have autoaway disabled in nest or you guys never leave the house for extended time :). Reason I am asking is that I had s re...

Your second statement is correct, but instead of hour, I would say, it records the amount of time the thermostat was set to away mode (it will not necessarily be 0 or 1; it could be in between that because nest is pinged every 5 minutes).You can easily add another column in sheet and add a formula there =if(CELLID<0.5, "HOME", "AWAY"), where CELLID is your autoaway column. You can do that in the script as well, but I suggest you keep the running time there and add a new variable (a lot more work than just adding another column in the sheets.). Is script recording anything other than 0? If not, then it could be an issue with the script or you have autoaway disabled in nest or you guys never leave the house for extended time :). Reason I am asking is that I had s related script issue before and I know I have fixed it. Depending on where you got your script template code (text file or my shared sheet), you may have that issue in your script. I suggest opening my shared sheet, go to script, search for "autoaway" and make sure any code line related to "autoaway" in that script matches your code (mainly where it is setting a value to those variable). I don't recall exactly what I changed (should have kept better version control :( ), but I know I had an error where the actual value was overwritten by 0 everytime. If your sheet ever recorded other than 0 for autoaway, you don't have this issue.

I may not be understanding your question, but let me take a shot at it. Each row is for a full day. During the day, you may have a few hours of "AutoAway" status. The script keeps track of that and adds the total time the thermostat was in away status in column labeled "Autoaway". If your criteria is that >12 hrs of autoaway time means "Away" and otherwise, "Home", then you should be able to add a new column in the google sheets and add that formula in there.

try changing definition of cat1 and cat2 in the script (lines 139.140). HH is added to compare hour. var cat1 = Utilities.formatDate(time0, Session.getScriptTimeZone(), "MM-dd-YYYY-HH"); var cat2 = Utilities.formatDate(time, Session.getScriptTimeZone(), "MM-dd-YYYY-HH");

No wonder why my day's temperature was off from the weather site. Great catch! Thanks for bringing that to my attention, and I will look into addressing that in the script. Just curious, have you come across this issue before in another code?

I think so, but you will have to make a few changes. Line 103 converts the temp to F, so you can change that to C. For the date format, you should be able to alter the format of that column in google sheets to your preference without needing to change anything in the script.

Try redeploying the app again (step 3), and select new project version.

@seadraggin: The Q/A section is getting confusing with answers or new followup questions not getting posted in order. That section of instructable does not appear to be fully thought through because the "Answer" button is not shown in android. Anyways, I will use the comment section to respond to your question.Yes, you can record data in a new line every 5 mins. That is how I used to do until I ran across google sheet cell count limitation. If you decided to go that route, below are the challenges:1) you will only be able to log data for roughly a year and sheets will stop recording because it will have reached the cell count threshold. The old data will have to be archived to make space for new data before it will work again.2) The script has some algorithm to calculate runni...

@seadraggin: The Q/A section is getting confusing with answers or new followup questions not getting posted in order. That section of instructable does not appear to be fully thought through because the "Answer" button is not shown in android. Anyways, I will use the comment section to respond to your question.Yes, you can record data in a new line every 5 mins. That is how I used to do until I ran across google sheet cell count limitation. If you decided to go that route, below are the challenges:1) you will only be able to log data for roughly a year and sheets will stop recording because it will have reached the cell count threshold. The old data will have to be archived to make space for new data before it will work again.2) The script has some algorithm to calculate running sum of your furnace use, autoaway and average of environmental conditions. That will likely need to happen separately in a pivot table. If you are interested, and have some programming experience, I suggest you start looking into the script and update as it suits your needs. If you come across any stumbling block and need help, let me know and I can try to help.

It will log one new row per day. Each day's row will get updated every 5 mins with new data.

It appears to be working after a few edits (triggers were missing and spreadheet ID was listed as full weblink. I re-deployed the script and it is recording data as expected.

Hello, I would double check to make sure all the variables (about theremostat ID, spreadsheet ID etc are listed correctly in the script). The second step would be to run the script in debug mode, and see if it runs all the way through. Running in debug mode will also show your the output of each variable and will help you troubleshoot which part is not working (is it the issue with pinging thermostat, weather site or adding data to the spreadsheet). if you still have issues, and if you are ok with sharing your spreadsheet, I can take a look and try to troubleshoot for you (bmw220@gmail.com).

Hi Charles, I am not sure why it is necessary to have the script set it that way. To be honest, I don't think I tried by keeping the script with other setting. Feel free and if that works for you, it make sense to keep the script access to only you. For someone to access your script, I think they will have to figure out the script address, and that level of security was acceptable to me and thus I proceeded that way.Answer to your second question is likely because the script will need to write to your google spreadsheet and thus it needs your permission.Hope it helps.

Hi Charles, It is likely because the spreadsheet I shared is for read only. I would recommend you do a save as on my spreadsheet and save it to your google drive and then make the edits. Let me know if you still have issues. If you want to use excel, you will likely need some other smart device such as raspberry pi to ping nest thermostat and modify the script (not a straightforward task in my opinion). The beauty of google spreadsheets is that it can do those automated tasks, such as pinging your thermostat and weatherstation, without needing the external smart device.Let me know if the save as does not work and then I can try to access your spreadsheet for additional troubleshooting (will likely need access permission from you to do that).