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

It doesn’t work that way.

Each device sends events at whatever time they happen, depending upon the device and what you are logging. I have 60+ Iris sensors (motion and contact) that send temperature. They are battery powered and the updates are sporadic. Thus, something (like this SmartApp) listening for temperature events will get them arriving sporadically.

Some mains-powered devices will have an option to send some events regularly. E.g., the Aeon Multisensor 6 has a report interval you can set when it is USB powered, so that it will regularly send updates. Battery powered devices don’t generally do that as it will cause them to consume too much power.

As you note, you can set the queue delay in this SmartApp. This sets how long events are queued up in the SmartApp before it sends a new row to the Google Sheet. This allows multiple values to appear in a row, but also affects the timestamp seen in the Sheet (since it will be the time the row was sent). Also, if a device sends 2 events in the same queue time, only the last one is logged.

I use this to my advantage. I have a sheet collecting battery values. I have the queue time on that set long (IIRC I think 4 hours (*)), since I really only care about those values infrequently. I have another sheet collecting temperatures; those I log every 15 minutes.

(*) the checked-in version of the SmartApp doesn’t provide 4 hours as an option. Just modify line 97 to include additional values in the dropdown.

2 Likes

While I understand some devices used the COV approach you outline above, many others have fixed intervals (mine are mostly line powered as I avoid batteries like the plague). I also have some items whose power is switched off and thus don’t report at all time steps. The bigger problem is that the smart app is not consistently outputting data. I’ve attached a screen shot of what I get with 15 minute queue up time:

12/23/2020 0.298 7:09:33 0.31583
12/23/2020 0.315 7:34:19 0.41278
12/23/2020 0.340 8:09:33 0.58722
12/23/2020 0.354 8:29:23 0.33056
12/23/2020 0.354 8:29:23 0.00000
12/23/2020 0.382 9:09:30 0.66861
12/23/2020 0.402 9:39:34 0.50111
12/23/2020 0.423 10:09:33 0.49972
12/23/2020 0.434 10:24:42 0.25250
12/23/2020 0.451 10:49:47 0.41806
12/23/2020 0.465 11:09:33 0.32944
12/23/2020 0.479 11:29:49 0.33778
12/23/2020 0.496 11:54:52 0.41750
12/23/2020 0.517 12:24:56 0.50118
12/23/2020 0.521 12:29:58 0.08382
12/23/2020 0.531 12:45:03 0.25139
12/23/2020 0.548 13:09:33 0.40833
12/23/2020 0.559 13:25:07 0.25944

Any ideas how to get fixed output from the smart app?

I suggest (again) to look at (and post) the IDE logs for the SmartApp in order to understand what is going on.

I don’t have a good answer for you, try:
-make sure you have only one instance of the inside the smartapp (unless you intend to have more)
-delete the instances in the smartapp, confirm the sheets logging stops, then add new instance with one device selected
-abandon the smartapp and use webcore to log to the Sheets URL, that way you can force all devices to log at desired rate with no holes in the data (charts should look better)

I just want to say that this is a really nice solution to a long term complaint I’ve had w/ ST since day 1. Not having basic long term logging and trending ability was such a bummer for me.

Anyway, with this I’ve started to set up trends on the most important things.

My biggest issue currently is that I think I will be approaching the google spreadsheet cell limits and:

  1. I won’t know when it hits, thus I’ll just miss out on logging until I check (Is there an alarm or notification that can tell us when we’re getting close, for example?)
  2. There is no way to automatically create a new sheet and just keep things going.
  3. Every time we make a new sheet we ALSO have to make a new code.gs copy/paste + new web app and key - and change it in the app. It’s not difficult or anything, but does take time.

Are there any improvement plans for any of these in the plan? Making this “full proof/automated” would be huge! Thanks again, happy holidays.

@krlaframboise has an awesome implementation of how to handle all that in his [OBSOLETE] Simple Event Logger (Simple Event Logger). The logic is all on the Google JS side. But he has stopped supporting that app because of the breaking changes SmartThings is making to the platform. It would be possible to implement something that logic in this SmartApp.

Someone (I don’t think they are in this forum) has done something like that already. It is sitting in this pull request: Added ability to rotate to a new sheet each month by tsolid · Pull Request #7 · loverso-smartthings/googleDocsLogging · GitHub . His change makes it rotate automatically each month. I haven’t accepted that request yet because I’d prefer to make it optional (rather than every sheet splitting itself each month). You could try that version of the code.

Personally, I have one sheet of all my temperature data that used to fill up in 1.5 years, so I’ve been manually rotating it yearly for the last 4 years. Since I added 40 more Iris contact sensors earlier this year, it looks like it is now filling a sheet every 10 months, so now I’m rotating it every 6 months.

1 Like

What I do is … approximately every 3-6 months, I open my logging spreadsheet, and File - Make A Copy, then name it as SmartThingsLoggingSheet 202010-202012. Then re-open the original SmartThingsLoggingSheet and delete all/most of the data rows. This keeps the same sheet code so nothing in the configuration/logging changes.

Could probably added into the script, but it only takes a minute or two.

1 Like

When you make a new sheet though, you also have to go back into the app and change the ID right? I assume each new “Sheet” has a unique “ID” ?

I’m also not super clear on the max amount a sheet can handle. If I have empty cells, do those cells still count?

I have 20 sensors reporting every 5 minutes at this stage, and some still only report every 15, 30, or 60 minutes due to device limitations.

His code copies the current sheet to a new one and then empties the current sheet. This way the id used by the SmartApp doesn’t change.

My sheet for logging temperatures currently has 92 devices logging it into. I logged 4300 rows into it in January (with a 10 minute max queue time in the SmartApp). I manually rotate every 6 months; the sheet for 07/01/2020-12/31/2020 has 16000 rows.

If i don’t rotate, my sheet fills up (can add a new row) at about 10 months, which would be about 2.5M cells. Thus, I suspect empty cells (or cells with a blank value) count in the limit.

Hi again all.
I recently added the new nest integration from SmartThings on my account. So my best thermostat shows in SmartThings now. However, it is now an option for any category within this logging smart app. I can’t find set point, mode, temperature, or humidity.

Any ideas? Thanks!

Hi Guys, I have been using the “Simple Event Logger” from Kevin LaFramboise and just today it stopped working from me. I got 0 event logged and if I re-install the app it does bring and log some older data and then it stops again.
I decided to give a go to your app @jlv . It installed all right and I see events been added to the queue in ST live log. However, when the events are uploaded to Google Sheets, only the very last even before the upload is logged. Any ideas how to fix that?
Thank you!!

The app does not fail. The device history of events since yesterday fails, it shows them 5 days delay and therefore does not capture them.

I have opened a ticket in UK support

Thank you @Mariano_Colmenarejo . I do also have a live stream for the same sensors setup via Initialstate.com and this is working just fine. So I was wondering if there was something wrong with the app…

EDIT: by the way right now my Simple Event Logger started to log events to the Google sheets once again!

1 Like

The history of events disappeared on July 29 at 11 am in my location, I don’t know if it also happened in other areas, and it has not yet been recovered, now it is delayed

It it helps, I can tell you it seems to be working fine for me (U.S.). I don’t see any gaps in the past couple of days.

1 Like

Thanks!
I am in the US as well and for me it has stopped logging for a couple of days. Now it seems to be back…

Just a correction; this is not my app, but created by @Charles_Schwer (who later decided to stop supporting it).

I keep a few Simple Event Loggers running. Mine are currently working - I have data logged just 2 minutes ago. But I do see a gap - it looks like everything was lost from about 10 PM EDT last night to 4 PM this afternoon. So it looks like the SmartThings backend had a device history outage again, and lost data again.
image

Kevin has said that he no longer supports SEL on SmartThings because their platform changes would make it unreliable and/or completely stop working. :frowning: Anyway, it’s working again (at the moment).

That’s the nature of how this app works – it only keeps the last event per logged item until the polling interval. You can shorten the poll interval in order to avoid losing as many interim values, at the risk of getting rows of mostly empty data in your sheet.

1 Like

Hi, I tried for many hours today to work through all the steps, created at GitHub account, etc but after many times of starting from scratch at google sheets, I still cannot even get the test url to insert a row into my sheet. " (Optional): Test out your new webapp, add this to the end of the URL from step 6: `?Temp1=15&Temp2=30"
i did get the message returned "The script completed but did not return anything. " however nothing appears in my google sheet.
Two questions:

  1. when the how-to describes "Open script: Click Tools → Script Editor. " I only have within the sheets menu list a “Extensions\Apps Script” which brings up a script editor. Is this the right editor?
  2. When the script is ready I then click on the blue “Deploy” button top right of screen. It doesn’t offer me the webapp to “Anyone, even anonymous”. I can select webapp and I execute as “Me” but my security who has access options are “Only myself” “Anyone with a google account” “Anyone”

I select “Anyone”

Any advice on how to get the test url to insert a row of test data into my google sheet?
I called the sheet filename “Logging” which shouldn’t matter, and left the default sheet name as Sheet1.

Thanks!

I have this running well, but I get duplicate times occasionally. I am using it with my Aeon Energy monitor. The “Energy” and "Power values update, only the time values get duplicated. Is anyone else having this issue?

Hi all. I am new to all this so I need some help. As a Parkinson sufferer, I want to record my restless night time actions, number of times awake or getting up. I have sensors working through Smartthings as well as a Aqara mini switch which I activate when I awake in the night. I want to produce a graph to see if there is an establish pattern to discuss with my Neurologist. Can anyone talk me through the process?
Many thanks
D