Hi all,
here is a little script for google sheets that will give the ability for Google sheets to connect to rachio and get your zone configuration. I have 25 zones and i find it useful to check for errors and mistakes at a glance in google sheets. It will also list if you are using the default runtime or if you overruled your schedule.
Steps to use the script:
- Open a blank google sheet (sheets.google.com click “blank”).
- Get your API key. Login to rachio via the web and get the API key by clicking on the human size icon up near the right corner. Select “get my API key”. It looks like this: 1f43dde1-9999-5555-b525-b5473aaabbb. This key is not a real key, just an example. It has to be your key that belongs to your account.
- In your google spreadsheet, click on tools->Script Editor. A new window will open. If will have something called “function MyFunction()” and another 4 lines. Delete all of those lines (select them with your mouse and hit the delete button.
- Copy and paste the 213 lines of script below these instructions into the window. (make sure you get all the lines from //START to //END
- Take your API key and put that into the quotation marks"" on line 5, the line that says APIKEY="". Make sure there are no erroneous spaces inside those quotation marks, it should only have your API key and nothing else.
- Hit save, its the floppy disk drive looking thing right above and slightly to the left of where you put in your API key.
- Name your project. I called mine Rachio -you can call yours anything you want. The name won’t be public.
- Click on the menu to the right of the spider looking thing menu button. A list will pop down. Select the entry that says “onOpen”.
- Clock the play button immediately to the left of the spider looking button. A popup should come “Authorization required”. XXX needs your permission to access your data on google (where XXX is the name of the function you saved in step 7. This gives your script the right to put data into the spreadsheet you opened. It only gives permission for this script to put data into this spreadsheet and nothing else in case you are worried. Select “Review Permissions”.
- A new window will popup, “Choose an account to continue with XXX” (XXX is the name you gave the script". I selected my personal google account.
- Google will tell you that this app has not been verified. Only proceed if you know and trust the developer. Its up to you if you trust the code. The full source code is below for you to inspect.
If you trust it, then select “Advanced” and click on “Go to XXX” where XXX is the name you gave the project.
- Google will now tell you that you will now allow this script to see spreadsheets in google drive, and connect to an external service. If you are okay with that then click “Allow”.
Go back to your spreadsheet. Next to the help menu item in the spreadsheet is a new menu called “Rachio”. Open that menu and select “Get Zone Config”. The script will connect to your rachio using your API key and populate the active worksheet with your zone configuration.
You can close the script window. From hereon after this script can be run by clicking on that menu item. Every time you do so, it will call rachio using your API key and populate the active sheet within this spreadsheet with the current settings from rachio. Note that no other sheets will have this menu, if you want another sheet to have it you either need to duplicate this spreadsheet, or create a new spreadsheet and go through the steps above.
If you want to remove the script you can delete the spreadsheet that the script is attached to, or you can open the menu “Tools -> Script editor” and select code.gs pop-down and click “delete”.
i found this useful. Maybe you will too?
Bugs errors and mistakes are entirely my own. I make no guarantees this will work for you. It works well for me and what i wanted to do.
//START
//
//You can declare the key here, or name a range on the Sheet APIKEY.
//if you put the API key here it will take precedence.
var APIKEY = "";
//Set these two to wherever you want to start putting data in your sheet (1,1 = A1)
var ROW = 1;
var COL = 1;
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
options = [
{name:"Get Zone Config", functionName:"FetchRachioZoneSettings"}
];
ss.addMenu("Rachio", options);
}
//This function looks inside the active sheet for a range named API key and fetches its value
function fetchAPIKey(){
//I'm keeping the log entries in this function to help debugging.
// I suspect APIKEY errors will be easier to catch in the short term log
if (APIKEY == ""){
var f = SpreadsheetApp.getActiveSheet().getRange("APIKEY").getValue();
Logger.log("using API key range named APIKEY in spreadsheet, key = %s", f);
return(f)
}
Logger.log("using API key from script, key =%s", APIKEY);
return(APIKEY);
}
//This function fetches the person ID from the rachio account.
// PersonID + API key gives us access to the rachio settings.
function GetPersonID(Key) {
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());
return (PersonID.id)
}
//A quick helper for counting object lenghts.
function length(obj) {
return Object.keys(obj).length;
}
//This helper function transposes a 2D array of objects.
//The original intent was to make it easy to transpose things with it.
function transpose(original){
var copy = [];
for (var i = 0; i < original.length; ++i) {
Logger.log("i = %s", i);
for (var j = 0; j < original[i].length; ++j) {
// skip undefined values to preserve sparse array
if (original[i][j] === undefined) continue;
// create row if it doesn't exist yet
if (copy[j] === undefined) copy[j] = [];
// swap the x and y coords for the copy
copy[j][i] = original[i][j];
}
}
return copy;
}
//Getting some stuff out of the body of the program for readability.
//This builds the header.
function buildHeader(){
// var a = ["Controller Name", "Zone Number", "Zone Name", "SqFt", "Exposure",
// "Soil In/In", "Spray head", "Irrigation Inches/hr", "Efficiency", "Crop",
// "Crop Coefficient", "Root Depth", "Depletion", "Run Time (mins)",
// "RunTime Override", "Saturated depth of water", "Depth of water" ];
var a = ["Controller Name", "Zone Number", "Zone Name", "SqFt", "Exposure",
"Soil In/In", "Spray head", "Irrigation Inches/hr", "Efficiency", "Crop",
"Crop Coefficient", "Root Depth", "Depletion", "Run Time (mins)", "RunTime Override"];
return(a);
}
//The main body of formatting and logic.
function FetchRachioZoneSettings(){
var APIKey = fetchAPIKey();
var PersonID = GetPersonID(APIKey);
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 DeviceID = JSON.parse(response.getContentText());
var DataOutput;
var DataWidth;
var DataHeight;
var dataSet = DeviceID;
// var rows = [],
// data;
//count number of controllers
var DeviceCount = length(dataSet.devices);
// Logger.log("devices = %s", DeviceCount);
//This is the key data structures used for the spreadsheet
//DataHeight, DataWidth is used by the range object in spreadsheet.
//Sheetdata is the 2D array that used to build rows and colums
DataHeight=1;
var SheetData = [];
SheetData[0] = buildHeader();
DataWidth = length(SheetData[0]);
var Zones; //use to iterate over the zones
//Iterate over each controller and process the zone for each.
for(i=0; i < DeviceCount; i++){
var Controller = dataSet.devices[i];
NumZones = length(Controller.zones);
//I got the first controller, and now i need to iterate over the zones for that controller
for(k = 0; k < NumZones; k++){
if(Controller.zones[k].enabled == true){
//I only want to look at zones that are enabled
//From here on i select fields out the json and put it into the 2D array that will fill
//the spreadsheet.
var Zone = Controller.zones[k];
SheetData[DataHeight] = new Array(DataWidth);
var n = 0;
SheetData[DataHeight][n++] = Controller.name;
SheetData[DataHeight][n++] = Zone.zoneNumber;
SheetData[DataHeight][n++] = Zone.name;
SheetData[DataHeight][n++] = Zone.yardAreaSquareFeet;
// a little bit of javascript ju-jitsu to format the string (replacing all '_' with a space).
SheetData[DataHeight][n++] = Zone.customShade.name.toString().toLowerCase().split('_').join(' ');
SheetData[DataHeight][n++] = Zone.availableWater;
SheetData[DataHeight][n++] = Zone.customNozzle.name.toString().replace("_", " ").toLowerCase();
SheetData[DataHeight][n++] = Zone.customNozzle.inchesPerHour;
SheetData[DataHeight][n++] = Zone.efficiency;
SheetData[DataHeight][n++] = Zone.customCrop.name;
SheetData[DataHeight][n++] = Zone.customCrop.coefficient;
SheetData[DataHeight][n++] = Zone.rootZoneDepth;
SheetData[DataHeight][n++] = Zone.managementAllowedDepletion;
//changing runtime to minutes, and looking up if it has been adjusted
SheetData[DataHeight][n++] = Zone.runtime/60;
if(Zone.runtime == Zone.runtimeNoMultiplier){
SheetData[DataHeight][n++] = "No";
}else{
SheetData[DataHeight][n++] = "Yes";
}
//I'm dropping these two out of the report because i'm not sure what they actually are.
// SheetData[DataHeight][n++] = Zone.saturatedDepthOfWater;
// SheetData[DataHeight][n++] = Zone.depthOfWater;
DataHeight++;
}
}
}
//put the data into the sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var dest = sheet.getRange(ROW, COL, DataHeight, DataWidth);
dest.setValues(SheetData);
//Format the sheet to make it look prettier.
//make header row bold
var dest = sheet.getRange(ROW, COL, 1, DataWidth);
dest.setFontWeight("bold");
// format square footage with comma
var dest = sheet.getRange(ROW+1, COL+3, DataHeight-1, 1);
dest.setNumberFormat("#,##");
// format soil in/in column for two decimals
var dest = sheet.getRange(ROW+1, COL+5, DataHeight-1, 1);
dest.setNumberFormat("0.00");
// format Irrigation efficiency into %
var dest = sheet.getRange(ROW+1, COL+8, DataHeight-1, 1);
dest.setNumberFormat("0%");
// format crop coefficient efficiency into %
var dest = sheet.getRange(ROW+1, COL+10, DataHeight-1, 1);
dest.setNumberFormat("0%");
// Lets make root depth with two decimals
var dest = sheet.getRange(ROW+1, COL+11, DataHeight-1, 1);
dest.setNumberFormat("0.00");
// Depletion is a percentage
var dest = sheet.getRange(ROW+1, COL+12, DataHeight-1, 1);
dest.setNumberFormat("0%");
// No decimals for runtime
var dest = sheet.getRange(ROW+1, COL+13, DataHeight-1, 1);
dest.setNumberFormat("0");
// Hiding these since i don't really know what they are.
// // lets make saturated depth & depth of water 2 decimals
// var dest = sheet.getRange(ROW+1, COL+13, DataHeight-1, 2);
// dest.setNumberFormat("0.00");
//
return(0);
}
//
// END