[OBSOLETE] Log events to Google Sheets [see post /36719/154 for Github repo and v1.1]

Try turning on “Plot Null Values”. Sometimes it works, and sometimes the chart gets “confused” and has to be reset or recreated.

Consider me inspired, LOL! Just a couple of simple formulas can automatically condense each month out of the standard data and create these gauges.

Assume you have a sheet with a tab called “FullData”, with three columns, (A, B, C) containing DATE, POWER (watts), ENERGY (kilowatt hours). You don’t even need column B, actually.

The first formula creates a unique array of the months represented in column A. It will automatically fill in a row for each unique month:

=ARRAYFORMULA(UNIQUE(MONTH(FullData!A2:A)))

Now on each row that received a unique month, a copy of the second formula automatically totals up the ENERGY (kwh) used that month, and ignores any blank cells to avoid throwing an error:

=MAX(FILTER(FullData!C:C, MONTH(FullData!A:A)=A2, NOT(ISBLANK(FullData!C:C))))-MIN(FILTER(FullData!C:C, MONTH(FullData!A:A)=A2, NOT(ISBLANK(FullData!C:C))))

Now that you have the kwh consumed, a third formula can estimate your bill if you know how yours is calculated. Mine is $17, plus Delivery charges at .05 per kwh (with a 2% tariff), plus Supply charges at .06 per kwh. I guestimated this from my past bills, and it’s usually within a few dollars of the actual bill:

=17 + (G2 * .05) * 1.02 + (G2 * .06)

Now just insert a graph and feed it that number. If you want the name of the month instead of the number, use something like this (assuming the month’s number is on a new sheet in cell A2):

=IF(A2="","—",CHOOSE(A2,“Jan”,“Feb”,“Mar”,“Apr”,“May”,“Jun”,“Jul”,“Aug”,“Sep”,“Oct”,“Nov”,“Dec”))

3 Likes

Figured I should respond to the “saving last values” question now that I figured it out. This section of code will do it:

// Get the names of the columns, and get the values from the last row to reuse in case of incoming blanks.
var rFirstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn());  
var rHeaders  = rFirstRow.getValues();
var rLastRow  = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn());
var rLastData = rLastRow.getValues();

// Columns start at one, but arrays start at 0, so this is the right number for inserting
// into an array that will be placed into the columns.
var ColumnCount=sheet.getLastColumn();

// Initialize new row to be inserted before it gets filled with data.
var aNewRow = new Array(rHeaders[0].length);

// At one time, it was necessary to fill every cell with a hyphen, but that seems to have
// broken the line chart sometime in 11/17, so for now populate the new row with the data
// from the last row, which should fix some of the charts that break when they find blank
// cells *and* the data is coming from non-contiguous columns.
// for(var x=0; x<ColumnCount; x++) aNewRow[x] = "-";
for(var x=0; x<ColumnCount; x++) aNewRow[x] = rLastData[0][x];
1 Like

tamathumper,

Thanks for all your cool contributions. I did a kind-of similar thing when I showed how to copy down a specific set of columns, which I’ve referenced above. For me, these were not data columns; they were computed columns, such as the time between this record and the previous one.

I personally would not want to fill in the blanks for when data has not changed, because I can do that later in post-processing. But, to each his own. For one thing, you shouldn’t “ditto” things like motion sensors. Importantly, the absence of data is most problematic across times when the net or power goes down. Let life be hard instead of comfortable when it comes to data, I say.

I set up a lot of sensors (~20 devices with ~100 sensors) and later found it’s too much to handle. I have yet to find a good visualization tool that can let me see my data on long term scales, such as across years. So I’m just collecting it and not otherwise using it except for an occasional curiosity look.

To me, CSchwer’s log was and is a wonderful thing - but underlines how difficult it is to work with data like this across very long scales (years), including the fact that locations of devices might have changed, temperature registration on a particular sensor has obviously changed over time, etc. (Ask if you want to know more on this.)

I think that all of this data is very powerful. And I love the CSchwer log for that. But it’s almost as if we’ll need personal AIs to help it all make sense. Or pay a lot of money to some person or service.

You present some cool graphs but I don’t find them real useful. I don’t need dials for power costs. I don’t need over-time graphs of temperature. I don’t need NOAA graphics.

What I do need is a graph of my house’s internal (8 sensors) and external (6 sensors) temps versus local estimated temperatures. Versus time of day, estimated cloud cover, amount of sun versus cloud (some sensors are in attic), months of the the year, and other sophisticated measures. Shown by time of day or month of year, and extended across data gaps in coverage, or movement of devices.

Add humidity and luminosity if you want (not recommended). All on top of the fact that devices may have been moved, deleted, upgraded, etc. For the record, I don’t have a smart HVAC controller per se because I’m not sure I need it in theory, with all these other sensors, plus a simple programmable thermostat (and I keep manual notes).

Anyway. Thank you for contributing! Here’s where I am at. I look forward to a great future where all this great data works together.

Mike

Sorry you don’t find the graphs useful…

The function to copy rows of data down is important if you are building graphs in Google Sheets using columns that aren’t all next to one another (contiguous). Gaps in the data with non-contiguous columns will make the charts randomly stop updating. With the data-copy function they work flawlessly day after day.

I don’t log data for all my sensors, I just log my temperatures and energy usage, and even then I archive it quarterly. So far, it calculates my energy bill within about 40 cents for the whole month, and it helps me manage my energy utilization.

Thank you for this code to fill in the data gaps. I have been trying to get it to work, but I wonder if I am inserting this into the wrong section of the google sheets script. Can you help me properly implement this? Thanks!

Happy to help. Don’t forget to re-publish the script after you change it, otherwise it just keeps running the old code.

1 Like

Oh of course…that was what I was forgetting. Thanks again!

So I am still not able to get the data to fill/backfill with the old value. I know it is user error…at what location in the google sheets script should this code be placed? beginning, end, middle? I have broken the script a couple times trying to figure out how to get the data to fill in the gaps.

So I have been trying to get this to work for a while now. I am wondering if it has to do with the type of interface between ST and the temp sensors. I am collecting all of my temp data through 2 ecobee4 thermostats as well as each one has an associated remote temp sensor.

I have attempted to use this code to copy the previous temp value when nothing is reported however I am not successful.

My question is, where in the original script code does this additional code get inserted? Or is there a better way to accomplish back-filling this data? Thanks!

Here is my full code, for reference.

function doGet(request) {
if(request != null) {

//*****************************************************************************************************
// NOTE: When updating this code, don't forget to Debug, Save, then Publish (Deploy as web app) with
// the "Version" set to 'new', "Execute as" set to 'me', and "Access" set to 'Anyone, even anonymous'.
//*****************************************************************************************************

// Point to the sheet with the data on it.
var ss = SpreadsheetApp.openById("insert your own id here");
var sheet = ss.getSheetByName("FullData");

// Get the names of the columns, and get the values from the last row to reuse in case of incoming blanks.
var rFirstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn());  
var rHeaders  = rFirstRow.getValues();
var rLastRow  = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn());
var rLastData = rLastRow.getValues();

// Columns start at one, but arrays start at 0, so this is the right number for inserting
// into an array that will be placed into the columns.
var ColumnCount=sheet.getLastColumn();

// Initialize new row to be inserted before it gets filled with data.
var aNewRow = new Array(rHeaders[0].length);

// At one time, it was necessary to fill every cell with a hyphen, but that seems to have
// broken the line chart sometime in 11/17, so for now populate the new row with the data
// from the last row, which should fix some of the charts that break when they find blank
// cells *and* the data is coming from non-contiguous columns.
// for(var x=0; x<ColumnCount; x++) aNewRow[x] = "-";
for(var x=0; x<ColumnCount; x++) aNewRow[x] = rLastData[0][x];

// Walk through the parameters passed in.
for (var paramName in request.parameters) {
  var foundMatch=false;
  // Walk through the columns for each parameter.
  for(var x=0; x<rHeaders[0].length; x++) {
    // If a parameter name matches a column name, put the value in the array.
    if(paramName.toString().toLowerCase()==rHeaders[0][x].toString().toLowerCase()) {
      aNewRow[x] = request.parameter[paramName];
      foundMatch = true;
      // Take out this break, and we can have data fill more than one column (of the same name)
      // which will help work around the fact that charts break when new data isn't completely filled
      // in, and the ranges that comprise the chart aren't in a contiguous set of columns.
      // break;
    }
  }
  // If there was no existing column for the data, make a new one.
  if(foundMatch==false) {
    if(sheet.getLastColumn()==sheet.getMaxColumns()) {
      sheet.insertColumnAfter(sheet.getLastColumn());
    }
    // Set the column name in the array and the header row.
    rHeaders[0][ColumnCount] = paramName;
    aNewRow[ColumnCount] = request.parameter[paramName];
    rFirstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()+1);
    rFirstRow.setValues(rHeaders);
    rFirstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn());
    rHeaders  = rFirstRow.getValues();
    // Add one to the column count.
    ColumnCount++;
  }
}

// At one time it was necessary to check for an auto-filled hyphen (from initialization procedure
// above, but that seems to have broken sometime in 11/17.
// if(aNewRow[0]==""||aNewRow[0]=="-") {
if(aNewRow[0]=="") {
  Logger.log("setting date");
  aNewRow[0] = new Date();
}

// Append a new row to the bottom of the spreadsheet containing the values in the array.
sheet.appendRow(aNewRow);

}
}

Got it!
Need to edit this line to point to the childstartpage:
return dynamicPage(name: "parentPage", title: "", nextPage: "childStartPage", install: true, uninstall: true) {

This did not work for me. Still got the spinning wheel. Any other suggestions?

AEOTEC & Samsung Temp/Motion/Water Sensors–>
Smartthings Hub–>
“Sharp Tools” App–>
“Tasker” App–>
Google Sheets Plug-in–>
Conditional formatting

2 Likes

I am having all sorts of issues this afternoon. One of them is my logging times seems to be stuck. I suspect the issue is on the ST side. I do not expect to see a temp update on every sensor each 5 mins (my update interval).

But look at that timestamp. I am getting updates but the time is hours behind and stuck.

I am seeing this in Live Logging. I don’t think I’ve seen it before.

6:30:32 PM: error Error sending value: java.net.SocketTimeoutException: Read timed out

Is anyone else having issues with the timestamp like I am? And as I posted yesterday, I am seeing the above error in Live Logging now and it must be related. I have changed nothing.

My updates are coming over but every single one is now stuck at 2/26/2018 15:45:23.

@Charles_Schwer

I haven’t been monitoring my data closely for a while. Took a quick glance, recent data timestamps look okay…

2/28/2018 8:56:06 53
2/28/2018 9:10:20 54
2/28/2018 9:18:06 68
2/28/2018 9:26:06 68 55

Okay, thanks for replying.

I’m still stuck in Groundhog Day here. No idea what’s causing it.

I kinda forget, are the timestamps part of the GoogleSheets script, I think so. Just for a test do some testing bypassing the SmartApp, log data manually via the Google Sheet URL like…

https://script.google.com/macros/s/<URL_KEY>/exec?temperature=72

Guessing this would have bad timestamp too.

Kevin,

Thanks for this. I did what you suggested and I got a good timestamp.

Here’s where it gets even more weird. I did not check my sheet before running the URL test you suggested. When I did look I noticed that my sheet started recording with some new timestamp info prior to my test (I’ve since deleted the test line so it doesn’t show below). It’s still wrong though. So it was stuck for days then this (ignore the columns getting out whack - that’s just a paste error):

2/26/2018 15:45:23	843.920	395.710	$33.64		72	69	75	71	73	71	67	71	73	72
2/26/2018 15:45:23	778.800	395.725	$33.64		72	69	75	71	73	71	67	71	73	72
2/26/2018 15:45:23	972.070	395.838	$33.65		72	69	75	71	73	71	67	71	73	72
2/26/2018 15:45:23	995.720	395.915	$33.65		72	69	75	71	73	71	67	71	73	72
2/26/2018 15:45:23	928.400	396.030	$33.66		72	69	75	71	73	71	67	71	73	72
2/26/2018 15:45:23	884.950	396.092	$33.67		72	69	75	71	73	71	67	71	74	72
2/26/2018 15:45:23	884.730	396.180	$33.68		72	69	75	71	73	71	67	71	74	72
2/28/2018 10:55:23	1,387.760	396.241	$33.68											
2/28/2018 10:55:23	1,350.690	396.354	$33.69											
2/28/2018 10:55:23	1,344.090	396.400	$33.69											
2/28/2018 11:10:54	1,173.150	396.603	$33.71											
2/28/2018 11:10:54	1,183.160	396.712	$33.72											
2/28/2018 11:21:24	845.570													
2/28/2018 11:21:24	786.280													
2/28/2018 11:33:24	914.650	396.922	$33.74											
2/28/2018 11:33:24	1,055.230	396.922	$33.74											
2/28/2018 11:33:24	895.950	397.232	$33.76											
2/28/2018 11:33:24	918.060	397.306	$33.77											
2/28/2018 11:33:24	901.230	397.367	$33.78											
2/28/2018 11:33:24	1,019.370	397.466	$33.78											
2/28/2018 11:33:24	1,356.080	397.566	$33.79				74			72			75	
2/28/2018 11:33:24	1,315.270	397.654	$33.80				74			72			75	

I have had a circuit or two off in the house this morning while I install another device but I can guarantee that neither the ST hub nor the HEM that is part of what goes to my sheet where powered off at any point. Everything else feeding it is battery powered.

It has to be connected with this error in Live Logging:

12:24:04 PM: error Error sending value: java.net.SocketTimeoutException: Read timed out

EDIT: I just noticed this in the browser when I run the URL test (and the test takes quite a while to execute):

I’m wondering if this is some kind of delay/timing issue with Google.

EDIT: So after reading more I realize the ‘script completed but did not …’ message is the expected result on the test.

I’m still left with the java error noted above and my Sheets updates are still a mess with the timestamps. Something changed somewhere to give me timeouts but I cannot tell where. Before this I had this running for over a year without issue.

Just to close out my own issue … I deleted the Google Sheets Logging SmartApp and reinstalled it (didn’t touch the Google Sheets Web stuff) and now it looks like the java error has stopped and the data is properly populating the Sheet again. I guess something in the data stream the SmartApp was sending was causing problems but all seems okay now.


Scratch that. After 4 or 5 good updates I’m back to java errors when trying to process the queue to Google Sheets again. Sigh.