author
2Instructables9,971Views22CommentsNeenah, WIJoined May 14th, 2009
When given the choice between being right or being kind, choose kind. - Dr Wayne W. Dyer

Achievements

  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger11 days ago
    Nest Thermostat History Data Logger

    To folks who are having issues with authorization: Try the script in the following file. It has additional functionality related to Nest's new 2.0 authorization protocols. I have not tried it, so if you come across any questions or issues, please post in the comments section. Credit to mcr2582.https://www.dropbox.com/s/8rbtg7pb0xl9n9x/nest%20temp%20logger%20edited.txt?dl=0

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

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

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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...

    see more »

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger4 months ago
    Nest Thermostat History Data Logger

    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");

    View Instructable »
  • bmw220 followed bmw2205 months ago
      • Build a Carrom Board
      • Nest Thermostat History Data Logger
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger5 months ago
    Nest Thermostat History Data Logger

    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?

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger5 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger5 months ago
    Nest Thermostat History Data Logger

    @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...

    see more »

    @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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger5 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger5 months ago
    Nest Thermostat History Data Logger

    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.

    View Instructable »
  • bmw220 commented on bmw220's instructable Nest Thermostat History Data Logger5 months ago
    Nest Thermostat History Data Logger

    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).

    View Instructable »
  • bmw220's instructable Build a Carrom Board's weekly stats: 6 months ago
    • Build a Carrom Board
      688 views
      16 favorites
      5 comments
  • bmw220 entered Build a Carrom Board in the Homemade Gifts Contest 2017 contest 6 months ago
  • bmw220 commented on bmw220's instructable Carrom Board6 months ago
  • bmw220 posted an instructable Carrom Board6 months ago