Download a schedule?

Schedules & Zones
21

1 and 2 are questions I just asked in the API section of the forum. Not sure what they are, i speculate that they may be part of the moisture report for a zone but I hope someone will share what they are in that thread. I do not use them in calculation but kept them in the sheet as I’m investigating them.

3 is there run time for a zone. I think it’s what is reported in the schedule as runtime. I calculated that out in the sheet and compare my calculations based on zone settings vs what the rachio computes. To me this is a sanity check, maybe I’ll remove it.

22

I do not know that I would personally remove anything, especially the runtime. It would be good to see that.

23

I don’t see anything wrong with your math; it all appears correct, near as I can tell.

I can only guess where the difference between your times and Rachio’s are (but I think it’s a good guess): It appears to me that when defining inches of water applied (and possibly all values of inches of water, including Crop Evapotranspiration, Rain, etc.), Rachio is using an 2 decimal value, or an amount rounded off to 2 decimals. Depending on what language they use for their code, many times variables are specified, not just as Integers or Floats (variable decimal points), but values with specific decimal points, in this case accurate to 0.01". All the values they /show/ are displayed only to the nearest 0.01". I think their calculations, probably due to the variable definition, rather than a specific function to round them, rounds the value to that. I think that’s common to C+/++, isn’t it? You declare a variable with a value, and that determines the accuracy (decimal places) of the variable? Haven’t done C for quite a while, but that seems familiar to me.

For your Depth of Water, you use 0.44" and 0.38" (the values shown within the program), but you /calculate/ these values as 0.443333333" (displayed as 0.443) and 0.375". Guess what? The differences are the same as you’re reporting with the times!

Zone 5 doesn’t quite turn out that way, but it’s pretty close.

Also realize that no one but you can see the actual Rachio times using the API; we real world people have to look at the displayed values, which is only to the nearest minute, and may even be rounded incorrectly. :wink:

24

Thanks for checking, I suspected rounding errors but wanted to be certain.

. All values seems to be with a high precision, Java is 6 digits and c is 7 (assuming some form of ansi c ). I process the data in a spreadsheet and I assume they compute in double precision. I do the microseconds to seconds in single precision but that can’t create these types of errors. Under 2% deviation is fine with me.

I really enjoy fetching the data using an app script and populating a sheet with the data. It makes it easy to compare zones and scan for errors. I found an error in a zone config right away at a glance of the page (the soil type was set to clay but water per inch/inch had accidentally been adjusted to 0.2 -probably a fat finger on that app screen).

Again, thanks for checking my math!

25

I just told you where the error/problem was, didn’t I? It’s not in your spreadsheet, it’s in Rachio’s calculations, I’m certain. Their inches of water are being rounded, after calculation, to the nearest 0.01". Before the next calculation is done. It’s pretty much the only thing it can be, as we know the math.

26

:smiley: I tried to agree with you and thank you. Intent isn’t always best conveyed via the internet (and frequently I’m not that great at expressing gratitude either).

27

I am seeing that the API returns some things exactly how I entered it, for example:

“customNozzle”: {
“name”: “ROTARY_NOZZLE”,
“inchesPerHour”: 0.7
},

and some things with much higher precision:

"efficiency": 0.800000011920929,

Are you massaging the data at all from how it comes in from the API? I do not know how Rachio stores the data internally (but would guess it to be float/double), what language(s) they might be using, nor if they change precision on output to JSON (not uncommon to do). I am a software guy and know C/C++/C#/Java were mentioned. Most languages store according to integer, float, double, etc. and if they are rounded for output, that is typically done with a function/method. Fixed point is harder to work with on most languages.

28

I’m using javascript (google calls it “apps script” but its basically javascript) that i run as an extension to a spreadsheet via the google apps engine.

Once the JSON is in the javascript i don’t really massage it anyway. i output it to cells in the precision it is stored in javascript. Then it becomes limited to whatever precision google spreadsheets has. I’m sure its precision is at least 6 digits.

In sheets i adjusted the visible precision to two or three digits, but you can alter that in the cell to show you whatever precision you want. if you do math using the cell as an input you get the full precision per the usual spreadsheet rules.

I don’t think precision will be an issue. :slight_smile:

I got to do some work today, but tonight i hope to get some time to add the watering schedules to the script and publish it for those who want to see it in a spreadsheet form. Its pretty easy to push configs back to rachio from spreadsheets as well, that is not my goal but it should be super easy to do, maybe i’ll take an unused zone and hack that in so other people at least get a stub.

On the docket tonight is to I need to finish cell formatting from within the script, build an entity table so i can use zone ID’s)as keys into a 2D array for mapping zone configs to schedule configs. Given that it was about 7 hours since i wrote my absolute first line of javascript and i’ve never used google apps engine, i think my productivity should be increased by today. Or at least i hope so. This language is pretty cute.

par

29

It does sound like precision should not be an issue on your end. It still could be something on the JSON output side of the API. Good work.

30

My suspicion is that if you touch the sliders in the gui in any way, you get the precision issue. its invisible in the GUI due to rounding. Either way, when i round the number to lets say two decimal points, i get acceptable data so i decided not to worry about it.

31

@ParB, it seems that you did not make any changes since your post 15 above, correct?

32

I’ve done lots and lots of updates, but i’m in the middle of debugging them -stay tuned for a new version later this week. Mostly new functionality though, the stuff i posted is working fairly well.
The work now is to get the call from the rachio when it runs the schedule and populate a spreadsheet with a per zone log of run-times.

I have a bug in the code which i need to sort out before i post it.

33

I do wish there was a better way to post this kind of thing.

May I suggest GetPersonID() store the ID in a field so it that field exists, it does not need to perform the query (pull it out like fetchAPIKey())?

Even though most of the Zone settings are not needed in the calculations, I would like the option to see all of the fields. I know, I can modify it if I want.

34

You mean that instead of fetching the bearer ID from the API key each time you suggest making the bearer key a persistent data object? I worry about that, i don’t know what guarantees i have that the bearer key is valid for the long term. I guess i could write an error handler for it, but given how light weight that call is, and that its only called once per invocation is it really worth opimizing that function?
I’m more worried about the range updates, and how inefficient the code is in that area. I haven’t moved to batch updates but i could.

on the point about making each returned json object a column, i actually think that is a good idea. Its easy to hide columns, and the overhead of more columns at these quantities is pretty minimal.

35

Good point about the IDs in general. I have wondered if they would ever change. However, with how they are formatted, they look like they would “never” change to me. I guess I am thinking about how I keep the various IDs stored locally so that I can use them for other tests. I am also thinking about some ways I might want to expand the script & sheet.

36

i wrote the function to get the bearer ID from the key with re-use in mind.

I’m highly encouraging community adding to this.
I can write a little function that unpacks the entire json payload into a sheet if you want, i think that would be fairly easy to do.

right now i’m working on listening for webhooks and creating a watering journal in a spreadsheet, for every zone being watered. I have some formatting errors, and it was way too hot here over the weekend to do much else besides being in the pool (we had 105 where i live over the weekend).

1 Like
37

Okay, I decided to jump in and have some fun too a couple of days ago. I made a ton of changes and forgot to post yesterday. I do have more changes coming though after a few days to make more things generic.

I have not given permission yet for the script to access the spreadsheet, so am just returning data. Consequently, this is how I have the spreadsheet set up:

Cell Contents Cell Contents
A1 API Key: B1 [my-api-key]
A2 Person ID: B2 =GetPersonId(fetchApiKey())
A4 =GetRachioZoneSettings() 
function fetchAPIKey() {
  // Setup the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var range = sheet.getRange('B1');
  var value = range.getValue();
  Logger.log(value);
  return (value);
}

function GetPersonID(Key) {
  // Setup the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var range = sheet.getRange('B2');
  //var value = range.getValue();
  //if (value == "") {
    var url = "https://api.rach.io/1/public/person/info";
    var headers = {
      "contentType": "application/json",
      "headers": {"Authorization": "Bearer " + Key}
    };
    var response = UrlFetchApp.fetch(url, headers);
    var PersonID = JSON.parse(response.getContentText());
    value = PersonID.id;
    //range.setValue(value)
  //}
  return (value)
}

function length(obj) {
  return (Object.keys(obj).length);
}

function FROM_UNIX_EPOCH(epoch_in_ms, timeZone) {
  if ((typeof epoch_in_ms != "number") || (epoch_in_ms == 0)) {
    return (epoch_in_ms);
  }
  var date = new Date(epoch_in_ms);
  return (Utilities.formatDate(date, timeZone, "dd-MMM-yyyy HH:mm:ss"));
}

function GetRachioZoneSettings() {
  // Setup the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var APIKey = fetchAPIKey();
  var PersonID = GetPersonID(APIKey);

  // get Device(s) information this person has access to
  var url = "https://api.rach.io/1/public/person/" + PersonID;
  var headers = {
    "contentType": "application/json",
    "headers": {"Authorization": "Bearer " + APIKey}
  };

  var response = UrlFetchApp.fetch(url, headers);
  var dataSet = JSON.parse(response.getContentText());

  var DeviceCount = length(dataSet.devices);
  Logger.log("devices = %s", DeviceCount);

  var rows = [];
  var RowNum = 0;

  // Do the headers first so we know the data
  var showDisabled = true;

  rows[RowNum] = new Array();
  rows[RowNum++] = ["Person", "->", "->", "->", "->", "->"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["ID", "Username", "FullName", "Email", "CreateDate (UTC)", "Deleted"];
  rows[RowNum] = new Array();
  var n = 0;
  rows[RowNum][n++] = dataSet.id;
  rows[RowNum][n++] = dataSet.username;
  rows[RowNum][n++] = dataSet.fullName;
  rows[RowNum][n++] = dataSet.email;
  //rows[RowNum][n++] = dataSet.createDate;
  rows[RowNum][n++] = FROM_UNIX_EPOCH(dataSet.createDate, "+0000");
  rows[RowNum][n++] = dataSet.deleted;

  RowNum++;

  // Empty Row
  rows[RowNum++] = new Array();

  // Iterate on each Rachio device
  rows[RowNum] = new Array();
  rows[RowNum++] = ["Device", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["#", "Name", "CreateDate", "ID", "Status", "TimeZone", "Latitude", "Longitude", "SerialNumber", "RainDelayExpirationDate",
                    "MacAddress", "On", "Model", "ScheduleModeType", "Deleted", "RainSensorTripped", "HomeKitComatible", "UtcOffset"];
  for (ii = 0; ii < DeviceCount; ii++) {
    rows[RowNum] = new Array();
    var n = 0;
    rows[RowNum][n++] = ii + 1;  // Device number
    rows[RowNum][n++] = dataSet.devices[ii].name;
    //rows[RowNum][n++] = dataSet.devices[ii].createDate;
    rows[RowNum][n++] = FROM_UNIX_EPOCH(dataSet.devices[ii].createDate, dataSet.devices[ii].timeZone);
    rows[RowNum][n++] = dataSet.devices[ii].id;
    rows[RowNum][n++] = dataSet.devices[ii].status;
    rows[RowNum][n++] = dataSet.devices[ii].timeZone;
    rows[RowNum][n++] = dataSet.devices[ii].latitude;
    rows[RowNum][n++] = dataSet.devices[ii].longitude;
    rows[RowNum][n++] = dataSet.devices[ii].serialNumber;
    //rows[RowNum][n++] = dataSet.devices[ii].rainDelayExpirationDate;
    rows[RowNum][n++] = FROM_UNIX_EPOCH(dataSet.devices[ii].rainDelayExpirationDate, dataSet.devices[ii].timeZone);
    rows[RowNum][n++] = dataSet.devices[ii].macAddress;
    rows[RowNum][n++] = dataSet.devices[ii].on;
    rows[RowNum][n++] = dataSet.devices[ii].model;
    rows[RowNum][n++] = dataSet.devices[ii].scheduleModeType;
    rows[RowNum][n++] = dataSet.devices[ii].deleted;
    rows[RowNum][n++] = dataSet.devices[ii].rainSensorTripped;
    rows[RowNum][n++] = dataSet.devices[ii].homeKitCompatible;
    rows[RowNum][n++] = dataSet.devices[ii].utcOffset;

    RowNum++;
  }

  // Empty Row
  rows[RowNum++] = new Array();

  // Iterate on each Rachio device for each zone
  rows[RowNum] = new Array();
  rows[RowNum++] = ["Device",
                    "Zone", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->",
                    "Nozzle", "->", "Soil", "Slope", "->", "Crop", "->", "Shade", "WateringAdjustment", "->", "->", "->", "->"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["#",
                    "ZoneNumber", "Name", "ID", "Enabled", "AvailableWater", "RootZoneDepth", "ManagementAllowedDepletion", "Efficiency",
                      "YardAreaSquareFeet", "ImageUrl", "LastWateredDate", "ScheduleDataModified", "FixedRunTime", "SaturatedDepthOfWater",
                      "DepthOfWater", "MaxRuntime", "RuntimeNoMultiplier", "Runtime",
                    "Name", "Inches per Hour", "Name", "Name", "SortOrder", "Name", "Coefficient", "Name", "1", "2", "3", "4", "5"];
  for (ii = 0; ii < DeviceCount; ii++) {
    var Zones = length(dataSet.devices[ii].zones);
    // iterate on the zones in each Rachio device for this account
    for (jj = 0; jj < Zones; jj++) {
      // only output to the spreadsheet zones that are enabled
      if ((dataSet.devices[ii].zones[jj].enabled == true) || showDisabled) {
        rows[RowNum] = new Array();
        var n = 0;
        rows[RowNum][n++] = ii + 1; // Device number

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].zoneNumber;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].name;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].id;
        if (showDisabled) {
          rows[RowNum][n++] = dataSet.devices[ii].zones[jj].enabled;
        }
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].availableWater;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].rootZoneDepth;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].managementAllowedDepletion;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].efficiency;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].yardAreaSquareFeet;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].imageUrl;
        //rows[RowNum][n++] = dataSet.devices[ii].zones[jj].lastWateredDate;
        rows[RowNum][n++] = FROM_UNIX_EPOCH(dataSet.devices[ii].zones[jj].lastWateredDate, dataSet.devices[ii].timeZone);
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].scheduleDataModified
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].fixedRuntime;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].saturatedDepthOfWater;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].depthOfWater;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].maxRuntime;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].runtimeNoMultiplier;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].runtime;

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customNozzle.name;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customNozzle.inchesPerHour;

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customSoil.name;

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customSlope.name;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customSlope.sortOrder;

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customCrop.name;
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customCrop.coefficient;

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].customShade.name;

        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].wateringAdjustmentRuntimes["1"];
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].wateringAdjustmentRuntimes["2"];
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].wateringAdjustmentRuntimes["3"];
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].wateringAdjustmentRuntimes["4"];
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].wateringAdjustmentRuntimes["5"];
        rows[RowNum][n++] = dataSet.devices[ii].zones[jj].wateringAdjustmentRuntimes["6"];

        RowNum++;
      }
    }
  }

  // Empty Row
  rows[RowNum++] = new Array();

  // Iterate on each Rachio device for each schedule rule
  rows[RowNum] = new Array();
  rows[RowNum++] = ["Device",
                 "ScheduleRule"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["#"];
  for (ii = 0; ii < DeviceCount; ii++) {
    var rules = length(dataSet.devices[ii].scheduleRules);
    //iterate on the zones in each Rachio device for this account
    for (jj = 0; jj < rules; jj++) {
        rows[RowNum] = new Array();
        var n = 0;
        rows[RowNum][n++] = ii + 1; // Device number

        RowNum++;
    }
  }

  // Empty Row
  rows[RowNum++] = new Array();

  // Iterate on each Rachio device for each Flex schedule rule
  rows[RowNum] = new Array();
  rows[RowNum++] = ["Device",
                    "FlexScheduleRule", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->", "->"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["#",
                    "#", "ID", "Operator", "Summary", "CycleSoak", "StartDate", "Name", "Ennabled", "StartDay", "StartMonth", "StartYear",
                      "StartHour", "StartMinute", "TotalDuration", "ExternalName", "Type"];
  for (ii = 0; ii < DeviceCount; ii++) {
    var rules = length(dataSet.devices[ii].flexScheduleRules);
    //iterate on the zones in each Rachio device for this account
    for (jj = 0; jj < rules; jj++) {
      rows[RowNum] = new Array();
      var n = 0;
      rows[RowNum][n++] = ii + 1; // Device number

      rows[RowNum][n++] = jj + 1; // Flex Schedule Rule number
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].id;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].operator
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].summary;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].cycleSoak;
      //rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].startDate;
      rows[RowNum][n++] = FROM_UNIX_EPOCH(dataSet.devices[ii].flexScheduleRules[jj].startDate, dataSet.devices[ii].timeZone);
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].name;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].enabled;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].startDay;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].startMonth;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].startYear;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].startHour;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].startMinute;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].totalDuration;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].externalName;
      rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].type;

      RowNum++;
    }
  }

  // Empty Row
  rows[RowNum++] = new Array();

  // Iterate on each Rachio device for each Flex schedule rule's scheduleJobTypes
  rows[RowNum] = new Array();
  rows[RowNum++] = ["Device",
                 "FlexScheduleRule",
                 "ScheduleJobTypes"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["#",
                    "#", "ID", "Operator", "Summary", "CycleSoak", "StartDate", "Name", "Ennabled", "StartDay", "StartMonth", "StartYear",
                      "StartHour", "StartMinute", "TotalDuration", "ExternalName", "Type"];
  for (ii = 0; ii < DeviceCount; ii++) {
    var rules = length(dataSet.devices[ii].flexScheduleRules);
    //iterate on the zones in each Rachio device for this account
    for (jj = 0; jj < rules; jj++) {
      rows[RowNum] = new Array();
      var n = 0;
      rows[RowNum][n++] = ii + 1; // Device number
      rows[RowNum][n++] = jj + 1; // Flex Schedule Rule number
      var types = length(dataSet.devices[ii].flexScheduleRules[jj].scheduleJobTypes);
      for (kk = 0; kk < types; kk++) {
        rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].scheduleJobTypes[kk];
      }

      RowNum++;
    }
  }

  // Empty Row
  rows[RowNum++] = new Array();

  // Iterate on each Rachio device for each Flex schedule rule's zones
  rows[RowNum] = new Array();
  rows[RowNum++] = ["Device",
                    "FlexScheduleRule",
                    "Zone", "->", "->"];
  rows[RowNum] = new Array();
  rows[RowNum++] = ["#",
                    "#",
                    "ID", "SortOrder", "Duration (secs)"];
  for (ii = 0; ii < DeviceCount; ii++) {
    var rules = length(dataSet.devices[ii].flexScheduleRules);
    //iterate on the zones in each Rachio device for this account
    for (jj = 0; jj < rules; jj++) {
      var zones = length(dataSet.devices[ii].flexScheduleRules[jj].zones);
      for (kk = 0; kk < zones; kk++) {
        rows[RowNum] = new Array();
        var n = 0;
        rows[RowNum][n++] = ii + 1; // Device number
        rows[RowNum][n++] = jj + 1; // Flex Schedule Rule number
        rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].zones[kk].zoneId;
        rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].zones[kk].sortOrder;
        rows[RowNum][n++] = dataSet.devices[ii].flexScheduleRules[jj].zones[kk].duration / 60.0;

        RowNum++;
      }
    }
  }

  //destinationRange = sheet.getRange(20, 1, 20 + RowNum, n);
  //destinationRange.setValues(rows);
  return (rows);
}
38

Fun! I’m looking forward to seeing what you’ll build from it!