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

I have a very flakey problem. I went through the install instructions to the point of successfully running the spreadsheet test and installing the SmartApp. But, when I try to set the app parameters, they enter just fine and I hit DONE. I think everything’s great.

But nothing is happening, so I check the app and none of my Inputs have been saved. Repeat…fail. Many, many times.

I removed the app via IDE and reinstall. Same result.

I thought “maybe I hit a dead zone in the SmartThings cloud”, so I waited a couple of daya. Same issue.

Any debugging ideas?

Happily, I think my SmartApp issues were a from a larger ST problem. In any case, they magically went away. So, now I have the app running (and retaining its input values), but still no joy in the form of spreadsheet activity.

The test URL reliably updates the spreadsheet, so that part is good. The SmartApp has that exact URL key. I have a variety of devices configured to log.

I don’t know if this will tell you anything at all (I don’t see much that makes sense), but here is a log after I hit DONE.

4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:15 PM: debug The GET path is: /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:15 PM: debug Executing 'updateClientStatus'
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:15 PM: debug Polling for PHT state
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:09 PM: debug getChildDevices(false), children=0
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:09 PM: debug Parsing /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:09 PM: debug Parsing /clients
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:35:07 PM: debug
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:35:07 PM: trace REPONSE TYPE: text/xml;charset=utf-8
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:35:07 PM: trace urn:samsung.com:device:RemoteControlReceiver:1 - null
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:35:07 PM: trace [mac:0011323940DD, headers:SFRUUC8xLjEgMjAwIE9LDQpDb250ZW50LVR5cGU6IHRleHQveG1sO2NoYXJzZXQ9dXRmLTgNCkNvbnRlbnQtTGVuZ3RoOiA4Mw0KQ29ubmVjdGlvbjogS2VlcC1BbGl2ZQ0KS2VlcC1BbGl2ZTogdGltZW91dD0yMA0KWC1QbGV4LVByb3RvY29sOiAxLjANCkNhY2hlLUNvbnRyb2w6IG5vLWNhY2hl, body:PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPE1lZGlhQ29udGFpbmVyIHNpemU9IjAiPgo8L01lZGlhQ29udGFpbmVyPgo=, hub:9d6ea9ad-d96b-4c1f-bc97-1faae4a7d0cc]
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:06 PM: debug The GET path is: /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:06 PM: debug Executing 'updateClientStatus'
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:35:06 PM: debug Polling for PHT state
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:54 PM: debug
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:54 PM: trace REPONSE TYPE: text/xml;charset=utf-8
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:54 PM: trace urn:samsung.com:device:RemoteControlReceiver:1 - null
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:54 PM: trace [mac:0011323940DD, headers:SFRUUC8xLjEgMjAwIE9LDQpDb250ZW50LVR5cGU6IHRleHQveG1sO2NoYXJzZXQ9dXRmLTgNCkNvbnRlbnQtTGVuZ3RoOiA4Mw0KQ29ubmVjdGlvbjogS2VlcC1BbGl2ZQ0KS2VlcC1BbGl2ZTogdGltZW91dD0yMA0KWC1QbGV4LVByb3RvY29sOiAxLjANCkNhY2hlLUNvbnRyb2w6IG5vLWNhY2hl, body:PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPE1lZGlhQ29udGFpbmVyIHNpemU9IjAiPgo8L01lZGlhQ29udGFpbmVyPgo=, hub:9d6ea9ad-d96b-4c1f-bc97-1faae4a7d0cc]
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:54 PM: debug getChildDevices(false), children=0
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:54 PM: debug Parsing /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:54 PM: debug Parsing /clients
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:54 PM: debug The GET path is: /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:54 PM: debug Executing 'updateClientStatus'
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:54 PM: debug Polling for PHT state
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:44 PM: debug
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:44 PM: trace REPONSE TYPE: text/xml;charset=utf-8
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:44 PM: trace urn:samsung.com:device:RemoteControlReceiver:1 - null
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:44 PM: trace [mac:0011323940DD, headers:SFRUUC8xLjEgMjAwIE9LDQpDb250ZW50LVR5cGU6IHRleHQveG1sO2NoYXJzZXQ9dXRmLTgNCkNvbnRlbnQtTGVuZ3RoOiA4Mw0KQ29ubmVjdGlvbjogS2VlcC1BbGl2ZQ0KS2VlcC1BbGl2ZTogdGltZW91dD0yMA0KWC1QbGV4LVByb3RvY29sOiAxLjANCkNhY2hlLUNvbnRyb2w6IG5vLWNhY2hl, body:PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPE1lZGlhQ29udGFpbmVyIHNpemU9IjAiPgo8L01lZGlhQ29udGFpbmVyPgo=, hub:9d6ea9ad-d96b-4c1f-bc97-1faae4a7d0cc]
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:44 PM: debug getChildDevices(false), children=0
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:44 PM: debug Parsing /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:44 PM: debug Parsing /clients
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:44 PM: debug The GET path is: /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:44 PM: debug Executing 'updateClientStatus'
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:44 PM: debug Polling for PHT state
01c1330c-d48b-4767-bf9b-9ef36dcc4144  5:34:38 PM: debug Initialized
01c1330c-d48b-4767-bf9b-9ef36dcc4144  5:34:38 PM: debug Updated
01c1330c-d48b-4767-bf9b-9ef36dcc4144  5:34:36 PM: debug Initialized
01c1330c-d48b-4767-bf9b-9ef36dcc4144  5:34:36 PM: debug Updated
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:34 PM: debug
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:34 PM: trace REPONSE TYPE: text/xml;charset=utf-8
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:34 PM: trace urn:samsung.com:device:RemoteControlReceiver:1 - null
c442fa5a-361f-4df5-b7b1-68f7753b3886  5:34:34 PM: trace [mac:0011323940DD, headers:SFRUUC8xLjEgMjAwIE9LDQpDb250ZW50LVR5cGU6IHRleHQveG1sO2NoYXJzZXQ9dXRmLTgNCkNvbnRlbnQtTGVuZ3RoOiA4Mw0KQ29ubmVjdGlvbjogS2VlcC1BbGl2ZQ0KS2VlcC1BbGl2ZTogdGltZW91dD0yMA0KWC1QbGV4LVByb3RvY29sOiAxLjANCkNhY2hlLUNvbnRyb2w6IG5vLWNhY2hl, body:PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPE1lZGlhQ29udGFpbmVyIHNpemU9IjAiPgo8L01lZGlhQ29udGFpbmVyPgo=, hub:9d6ea9ad-d96b-4c1f-bc97-1faae4a7d0cc]
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:34 PM: debug getChildDevices(false), children=0
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:34 PM: debug Parsing /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:34 PM: debug Parsing /clients
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:34 PM: debug The GET path is: /status/sessions
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:34 PM: debug Executing 'updateClientStatus'
4906bf41-5adf-4f9c-a1ec-9d5a42a003f1  5:34:34 PM: debug Polling for PHT state

Works great thanks Charles it’s much more effective than an IFTTT solution.

@Charles_Schwer I foresee a possible problem for smart outlets. I have the Samsung ST outlet. Power will always fluctuate and logging power draw in watts, so for all the smart outlets, i think, will log every second or so. Luckily, I only have one and I use it for a night light, but I do plan on getting more and may become a problem when I have something on for a long periods of time. I’m suggesting maybe a keep the continues logging part and add a user specified X second delay option when you initially turn something on because you will see a log of a 100 watts device start logging at start-up maybe 4 watts, 50 watts until it reaches 100 watts where it will go up and down a little from there. Also, to add an option to log it once every X minutes, where the user can specify to minimize amount of logging. What do you think? Hopefully, its possible and I wanted to pass on the idea to you.

Also, did you see my wish list items?

  1. Battery Percentage Reporting
  2. User option to customized outputs in the smartapp. example open/close sensor i can unselect default and select custom and I specify it to be “door is open”/“door is closed”

Awesome App!!!

My outlet (aeon) reports about every 5-8 minutes.

@dc1515 what do you have plug into it? It really depends on the device plug into it, based off my ST outlet. If its a continues load that’s on it might log every 5-8 minutes depending the tolerance of power change, definitely if its off 5-8 for you. If you have different devices plugged into that is constantly changing in power, which will trigger a change in state, it should log continuously. I guess a good example would be is plug in your vacuum, motors will have a constant change in power. I just put the idea out there because I notice it when I plug in my vacuum and it logged it for every second i had it on. Or maybe the aeon is different, might have to look into that product. How do you like it btw?

I believe it depends on the load you plug into it - it reports on every Wh change. A vacuum would pull a lot so it would update quickly. I have only a 20W load plugged into mine so it reports about every 3 minutes…

My television and soundbar - the point of it is to kill the vampire draw when I am at work.

So when I am home, but the tv is not on, it draws about 9-12w. It reports 0 too when it is off.

What’s the best way to spawn multiple versions for multiple sheets?

I’d like to keep my temperature in one, my energy in another.

I have four sheets already, just add new device, smartapps, choose the google logging. Then setup the second instance like the first with the new info from the new sheet link, and all that other stuff.

[quote=“Charles_Schwer, post:72, topic:36719”]
So it looks like it’s fine. You have to wait for a sensor to change before an event will be sent to the smartapp. ie, if you are monitoring a temperature sensor the temp will have to change by at least a degree in order for an event to be sent to the logger.
[/quote]I apologize in advance for the volume of this post - way too many pics! But, I don’t know what is relevant and what is not, so here’s everything I can find. The net is that I have verified everything I know to test, but the spreadsheet never updates. Note in the last screenshot below, every event is called a scheduleFailure (that can’t be good, right?) Please take a look and see if there is some obvious thing I’m doing wrong. Thank you!




Thanks. I’ve triple checked the urlKey (plug it into the test url, which always works) and made sure I copied it, didn’t type it new; so I’m very confident in that. The sheet has just the one tab, FullData, and that’s where the test line goes. I’ll look more closely at the scheduleFailures to see if there are any non-failures (i think not, but need to look more thoroughly). Next up is Live Logging. Is there anything in particular I should do to provoke logging some useful data?

1 Like

I haven’t given up. Here’s my latest log. (Just as one more, last desperate attempt to verify the urlKey, I cut and pasted from the log to the test URL, and it worked.)

Can you give me some pointers -as much detail as possible - on how to debug? I’m willing to insert log.debug code to try to determine where this goes off the rails. Any better ideas???

01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:20 PM: debug [Time:2-6-2016+15%3A08%3A19, Aeon+HEMv1+SWv3+energy:1782.728] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:20 PM: debug Logging to queue Aeon+HEMv1+SWv3+energy = 1782.728 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:18 PM: debug Google accepted event(s) 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:18 PM: debug 200 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:18 PM: debug https://script.google.com/macros/s/*DELETED*/exec?vSwitch1+For+Alexa+Helper+switch=off&ST+Motion+Sensor+temperature=69&Time=2-6-2016+15%3A03%3A16&Aeon+HEMv1+SWv3+energy=1782.692&Aeon+Siren+switch=off 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:18 PM: debug Processing Queue 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:01 PM: debug [vSwitch1+For+Alexa+Helper+switch:off, ST+Motion+Sensor+temperature:69, Time:2-6-2016+15%3A03%3A16, Aeon+HEMv1+SWv3+energy:1782.692, Aeon+Siren+switch:off] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:01 PM: debug Logging to queue Aeon+Siren+switch = off 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:08:01 PM: debug handling string event physicalgraph.app.EventWrapper@10a986cd 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:55 PM: debug [vSwitch1+For+Alexa+Helper+switch:off, ST+Motion+Sensor+temperature:69, Time:2-6-2016+15%3A03%3A16, Aeon+HEMv1+SWv3+energy:1782.692, Aeon+Siren+switch:on] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:55 PM: debug Logging to queue Aeon+Siren+switch = on 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:55 PM: debug handling string event physicalgraph.app.EventWrapper@3aaa157f 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:53 PM: debug [vSwitch1+For+Alexa+Helper+switch:off, ST+Motion+Sensor+temperature:69, Time:2-6-2016+15%3A03%3A16, Aeon+HEMv1+SWv3+energy:1782.692] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:53 PM: debug Logging to queue vSwitch1+For+Alexa+Helper+switch = off 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:53 PM: debug handling string event physicalgraph.app.EventWrapper@1a0cb587 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:53 PM: debug [vSwitch1+For+Alexa+Helper+switch:on, ST+Motion+Sensor+temperature:69, Time:2-6-2016+15%3A03%3A16, Aeon+HEMv1+SWv3+energy:1782.692] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:53 PM: debug Logging to queue vSwitch1+For+Alexa+Helper+switch = on 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:53 PM: debug handling string event physicalgraph.app.EventWrapper@1d02dc1 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:20 PM: debug [ST+Motion+Sensor+temperature:69, Time:2-6-2016+15%3A03%3A16, Aeon+HEMv1+SWv3+energy:1782.692] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:07:20 PM: debug Logging to queue Aeon+HEMv1+SWv3+energy = 1782.692 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:06:28 PM: debug Initialized 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:06:28 PM: debug Updated 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:06:25 PM: debug Initialized 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:06:25 PM: debug Updated 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:06:20 PM: debug [ST+Motion+Sensor+temperature:69, Time:2-6-2016+15%3A03%3A16, Aeon+HEMv1+SWv3+energy:1782.656] 01c1330c-d48b-4767-bf9b-9ef36dcc4144 3:06:20 PM: debug Logging to queue Aeon+HEMv1+SWv3+energy = 1782.656

Hello @Charles_Schwer. I love the chart you created showing the various temp’s throughout the day. Is there any way you would explain how you built that chart.

Thank you very much!!

[quote=“Charles_Schwer, post:88, topic:36719”]
Can you try changing the smart app to only log one temperature, and set the queue time to 0.[/quote]

I set the queue time to 0 and have it log only a temperature sensor.01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:54:27 PM: debug 200 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:54:26 PM: debug https://script.google.com/macros/s/*DELETED*/exec?Monoprice+Downward+PIR+temperature=54. 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:54:26 PM: debug Logging to GoogleSheets Monoprice+Downward+PIR+temperature = 54.. I also set it to log a motion detector, and got the following log:

01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:40:39 PM: debug 200 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:40:39 PM: debug https://script.google.com/macros/s/*DELETED*/exec?ST+Motion+Sensor+motion=inactive 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:40:39 PM: debug Logging to GoogleSheets ST+Motion+Sensor+motion = inactive 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:40:25 PM: debug 200 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:40:24 PM: debug https://script.google.com/macros/s/*DELETED*/exec?ST+Motion+Sensor+motion=active 01c1330c-d48b-4767-bf9b-9ef36dcc4144 7:40:24 PM: debug Logging to GoogleSheets ST+Motion+Sensor+motion = activeIt doesn’t look much different than what I had before, so I doubt it tells you much… but, maybe?

Success!

In the middle of the night, I had an epiphany. What was different about my setup? My spreadsheet was in my private domain Google For Work account, i.e. it’s not an @gmail account.

I recalled yesterday wondering where the domain name was in the app HTTP call, then dismissed the thought. But, that is the key. The test script worked, note the domain (XYZ.com):https://script.google.com/a/macros/XYZ.com/s/*DELETED*/exec?Temp1=19&Temp2=39But, what the app was sending assumed the gmail default:https://script.google.com/macros/s/*DELETED*/exec?ST+Motion+Sensor+motion=inactiveSo, I just recreated everything in a default gmail account, and voila! Is this something you can accommodate in the code when you build the HTTPS url?

I’m pretty sure the “/a” is constant with whatever unique domain, just as you have it. Thanks for hanging with me on this.

@gjpinoy & @Charles_Schwer, I just want to point out that you can already do this with sensors:

Which results in:

and, ultimately:

I have not gotten this to work with Open/Close sensors, but they are not accessible under "Log Other Devices → “Sensors”.

Does that help?

Great job on this script! I’m not very familiar with Google Apps scripting, but this has helped me learn a bit.

I do have a question for you though. When the file begins to fill up, I have been copying the data to another location and then wiping out the old data. I found that if the sheet is completely blank, the script won’t run. I have to add something (a space, etc) to the first cell (1,1) and then the script runs just fine. I tried to find a way to fix this minor annoyance in the script, but was unsuccessful.

Any thoughts? Do you have a recommendation for how to archive data? I suppose I could just leave the header row, but that seemed unnecessary since it is auto-generated.

Thanks again!

1 Like