Ecobee reports it as heating, inactive, and cooling. I guess I can make a formula that translates that to 1,2,3 and graph that. Not sure why I didn’t think of that but thanks for the tip!
I’d go 1, 0 , -1 personally… formulas should be pretty easy.
Worked like a charm. Thanks!
@moblsu @Brian_Busch - which chart type are you using? I wish we could do a Bar Chart in the Sparkline format, but that doesn’t work. Using Line Chart with the numbering scheme you suggested seems to be the only way.
Thank you!
@kevin - Sorry for the delay in response. I was not aware of sparklines before you mentioned them here, though they look cool, I don’t know how you can use them.
The type of chart that I’m using is simply a line chart, though I was having quite a few difficulties getting the exact binary look that I desired… it seemed to work… I’ve since really abandoned that on my side, because it hasn’t been very useful. Also, I learned that Google Sheets can only have 400,000 cells at any given time in a given Sheet. Thus, I had to make some modifications to my methodology.
I’ve since created multile instances of Simple Event Logger to specifically focus on given data, like temp or motion… I’m also trying to utilize query, filter, arrayconstrain, and other formulas to narrow down the data within one cell rather than needing multiple sheets to narrow down the the same amount of data…
No problem. I have the line chart working pretty well using your technique of changing the text to numbers for each device…
I agree, I don’t know that its’ all that useful, I just like lots of charts I guess 
1 Like
Kevin, do you mind explaining how you get the even history on AT?
@idioffo89 This topic got me started…
It is a bit convoluted and not working very well…
-I have WebCoRE post the desired events to a top secret blog via IFTTT (weebly or blogger are two options)
-Find the RSS feed for your top secret blog
-Create an ActionTile MTG RSS media image
-Use that image in ActionTiles
Problems…
-my IFTTT applet keeps turning itself off, haven’t been able to figure out why
-the MTG RSS media image seems to have a bug where the order isn’t quite as expected
1 Like
Perfect thanks for the details, I will try to play around with it hopefully get something working
Can I use this Google Sheet Logging on Smartthings Hub v3 with new apps?
I know this is an old thread, but I cannot publish to image. I assume google made a change. Is anyone still using this technique or is there an alternative?
For posterity the question was answered here
2 Likes
I was looking for a way to provide a better visual of Motion Sensors, Contact Sensors, etc over time using Line Graphs in Google Sheets rather than the jagged sawtooth that is created when the data points in the log are:
Time1:Open
Time2:Closed
Time3:Open
Time4:Closed
etc
Because of the data, you just end up with up and down spikes as the graph connects the previous open/close event to latest open/close event. Additionally, you need to know how to interpret what the upswing or downswing in the chart relates to: either open/active or closed/inactive. (This is due to the fact that these devices tend to send logs on status change and not regular intervals).
My solution was to edit the Code.gs script such that these event types add a second log entry in order to terminate the previous state. So now my log contains:
Time1:Closed
Time1(+1s):Open
Time2:Open
Time2(+1s):Closed
Time3:Closed
Time3(+1s):Open
Time4:Open
Time4(+1s):Closed
etc
It does duplicate my log entries for these events, but you can see how the data can be graphed in a more visually usable way now where I can see the event and duration clearly. Ok, elephant in the room, why add +1s to the duplicate time event? Because the associated pivot table would group the 2 identical timestamp events and lose my corresponding data point detail that I need to graph. It does throw these events off by 1 second, but for my purposes of visually representing door open/closed and motion active/inactive over time… 1 second doesn’t matter to me. (An alternative to adding time to the duplicate event would be to add a rowID column in the main sheet that could be used in the Pivot Table to ensure the identical time entries were seen independently - but I am trying to limit additional columns to allow for longer term storage.
Resulting Output (Dark is Closed/Active and Light is Open/Inactive):
So, thoughts? Anyone found another way to represent this type of data clearly in Google Sheets natively?
For me I approached motion the same way that I did the Thermostat Operating state but instead converted open values to “99”. It produces a similar albeit denser chart than yours. For me I don’t rely on motion reporting much other than kinda seeing where the commotion has been in the house that day. As you can see below I also color coded similar rooms that have multiple sensors so this through color gives more of an overview of activity by room.
1 Like
Thanks for the response. I agree about graphing certain data types as well: Not all data needs to, or should be, graphed. Two Quick Questions: 1. What graph type are you using for the above representation? and, 2. Have you graphed contact sensors (doors/windows/etc), and if so, what have you found as the best way to visually see both the status/occurrence and duration of each ‘event’? I ask because this is really where I stumbled into the need to create the duplicate log entry in order to terminate the previous contact state (open/closed) graphically using line charts since the bar charts weren’t providing me the duration/timeline element I was looking for with this dataset.
Here is my stab at this, made a long time ago. I wanted each device to have its own “row”. Instead of graphing the actual data from sensor, I created and graphed formulas to offset each device to its own row. I likely borrowed this idea from someone else
So for LR instead of 0/1 it is 10/11, UP is 8/9, AL(SHM state) is 5/5.5/6, bottom 4 are doors.
Here is the equation. To adjust spacing, I didn’t use integers as I just said but rather…
=if($B2=“active”,10.9,10.1)
2 Likes
It is a “non stacked” column chart. I denoted non stacked but it doesnt actually state that. The stacked and 100% stacked do though so just wanted to clarify
I have not gotten to the contact sensors just because I run into the same issue that you are. I have a very rough start based on the motion charting approach but not happy with it since it does not portray duration in any logical manner. Still cracking away at it though when I have spare time.
Its funny because I remember a while back I lurked through some pages on here and saw this example but then was never able to find it again! I think this is a great way of going about it. So can you explain a bit more how you are organizing pivot tables and where you are doing the conversion equation?
Contact Sensors (at least the ones I have) only report on state changes. When the door opens, I get a single Time Stamped Event Type of “contact” with an event Value of “open”. Then 10 minutes later, when the door is finally closed, I get another single Time Stamped Contact:Closed event in the log. Typically we would just generate a pivot table for the device(s) and “contact” Event Type coupled with a Calculated Field Value of 1 or 0 that is a translation of “closed” and “open”.
The problem with this is that when you graph it, you get a jagged line that transitions from open to closed or closed to open, and visually, you need to know that a if the line is going ‘up hill’ it was open this whole time … while a downhill line means that the door was closed this entire time. Additionally, you need to know that the steepness of the slope is related to duration -> steeper is shorter duration between transitions.
What I wanted was a clear visual that showed just Open vs Closed that made sense to anyone looking at it. It should also be clear how long it was open or closed as well. In order to accomplish this, I found I need another datapoint in the log. Another entry just before the state change that basically terminated the previous state.
So, instead of graphing a line that when from:
Time1 (closed) to Time2 (open)
which produces a slope…
I wanted a line that went from
Time1::Closed to Time2::Closed to Time2(+small increase)::Open
This allows me to keep the graphed line at either 0 or 1 along the timeline until the next change and gives the visual you see above in my “Back Deck Door” Graph. BTW - I graphed using an Area Chart so the Closed door timeline is graphed solid as opposed to the Open door time being an opening in the chart.
How did I add the extra event into my google sheet?
I edited the Code.gs script to look for Event Types that need this extra event inserted (for now I have Contact and Motion events doing this) and added an additional entry to the sheet for these Events just prior to the real event being inserted. This pre-event is modified to have the opposite event value such that a Closed event would require an Open event inserted just prior. Look at the graphic above and just to the left you can see the additional event added 1 second prior to the real event.
I can share the code.gs modification but I want to clean it up a little. It has been working flawlessly though, no issues at all.
Definitely a nice multi-device ‘Single Chart’ methodology. Thanks! Can you explain the different transitions I see? Sometimes a sharp rise while other times a sloping angle?
Having trouble replying, I think I crashed the forum 
No pivot table. I usually use Google Sheets Logging smartapp which has one column for each device attribute. But I had borrowed the concept and Sheets Script to create a Event History sheet that is populated by WebCore (no smartapp). The Event History sheet is formatted more like Simple Event Logger.
In a second tab I put this in A1 to filter only the past day:
=filter(Sheet1!A:B,Sheet1!A:A>minus(now(),1))`
Then I added the offset/conversion formulas in columns C, D, E…:
Column C is: =if($B14=“Living Room Motion active”,10.9,10.1)
Column E is: =if($B14=“SHM Armed/Away”,6,if($B14=“SHM Armed/Stay”,5.5,if($B14=“SHM Disarmed”,5,if($B14=“AlarmTriggered on”,8,E13))))
Then chart it.
The WebCore part is like this:
if listOfDevices changes
make web request get “https://script.google.com/macros/s/SECRET_SHEET_ID/exec?Event= {$currentEventDevice} {$currentEventValue}”