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

Hi need some help. New to this sorry
Spreadsheet does not populate. I have a Aeotec smart switch 6 attached to my dryer and a Samsung socket on my living room lamp.
Exception shown in app “called monitor”

Steps done…
Got spreadsheet to work fine under testing as per the instructions.
Added Script api key into section (“Google Sheets”) { in My Smartapps code
Created new smart app in smartthings app on phone and added api key
Set to log switches and dimmer switches in smartthings phone app
Live logging shows
LogsClear
ea43ccda-4e5f-4804-be30-a551b6d867d7 22:15:55: debug 'zw device: 02, command: 2003, payload: FF ’ parsed to [[name:switch, value:on, descriptionText:Dryer was turned on, isStateChange:false, displayed:false, linkText:Dryer], [name:level, value:255, unit:%, isStateChange:false, displayed:false, linkText:Dryer, descriptionText:Dryer level is 255%]]
App log shows
LogsClear
9d791589-fce1-4a67-a154-b6ec24d519d3 22:15:28: error Error sending value: groovyx.net.http.ResponseParseException: Not Found
9d791589-fce1-4a67-a154-b6ec24d519d3 22:15:18: debug https://script.google.com/macros/s/AKfycbzKkT1rM4xc6H31UOC2mlzfhMzf1bMqHNUvk4yws0B4KYc-Er77/exec?Time=3%2F29%2F2017+19%3A00%3A08&Dryer+energy=0.035&Dryer+switch=on&Living+Room+Lamp+power=3.2&Dryer+level=255&Dryer+power=2
9d791589-fce1-4a67-a154-b6ec24d519d3 22:15:18: debug url [https://script.google.com/macros/s/AKfycbzKkT1rM4xc6H31UOC2mlzfhMzf1bMqHNUvk4yws0B4KYc-Er77/exec?]
9d791589-fce1-4a67-a154-b6ec24d519d3 22:15:18: debug Processing Queue

Andy, I’m no expert but it looks like the SmartThings app can’t find the Google Apps Script. Are you sure you got the URL right? There is a place where you can get confused. See this earlier post on it.

Hi Mike, thanks for quick response. You were correct the api key URL was incorrect. I get no errors now in the logs,and things are logging, but the spreadsheet still does not update.

If I take the script url that is seen in the logs, say for a temperature and paste into my browser, the link returns “The script completed but did not return anything” and populates the spreadsheet correctly.
It seems to be set up correctly now so I am confused what else could be wrong.

Any other ideas?

Well, here’s what my log CSchwer app log looks like around a spurt to GAS:

 8:34:46 PM [Time:03%2F30%2F17+20%3A34%3A05, MPS2+temp:89, LastTime:03%2F30%2F17+20%3A34%3A45, AMS3+temp:74.9]
 8:34:46 PM Logging to queue MPS2+temp = 89
 8:34:05 PM [Time:03%2F30%2F17+20%3A34%3A05, AMS3+temp:74.9]
 8:34:05 PM Logging to queue AMS3+temp = 74.9
 8:33:41 PM Google accepted event(s)
 8:33:41 PM 200
 8:33:37 PM https://script.google.com/macros/s/[API Key]/exec?AMS7+temp=77.9&AMS4+battery=75&LastTime=03%2F30%2F17+20%3A33%3A36&AMM+power=609&AES1+energy=8.581&AES3+power=197&AMS4+hum=52&AES3+energy=267.251&AES0+energy=37.072&AMS5+temp=81.4&AMS4+temp=75.1&Time=03%2F30%2F17+20%3A23%3A36&MPS2+temp=90&AMS1+temp=76.6&AMM+energy=1795.239&AMS7+motion=inactive&AES2+energy=188.035&AES4+energy=16.413&SendTime=03%2F30%2F2017+20%3A33%3A3&PostTime=03%2F02%2F3902+00%3A00%3A00
 8:33:37 PM Processing Queue
 8:33:37 PM [AMS7+temp:77.9, AMS4+battery:75, LastTime:03%2F30%2F17+20%3A33%3A36, AMM+power:609, AES1+energy:8.581, AES3+power:197, AMS4+hum:52, AES3+energy:267.251, AES0+energy:37.072, AMS5+temp:81.4, AMS4+temp:75.1, Time:03%2F30%2F17+20%3A23%3A36, MPS2+temp:90, AMS1+temp:76.6, AMM+energy:1795.239, AMS7+motion:inactive, AES2+energy:188.035, AES4+energy:16.413]
 8:33:38 PM [AMS7+temp:77.9, AMS4+battery:75, LastTime:03%2F30%2F17+20%3A33%3A37, AMM+power:617, AES1+energy:8.581, AES3+power:197, AMS4+hum:52, AES3+energy:267.251, AES0+energy:37.072, AMS5+temp:81.4, AMS4+temp:75.1, Time:03%2F30%2F17+20%3A23%3A36, MPS2+temp:90, AMM+energy:1795.291, AMS1+temp:76.6, AMS7+motion:inactive, AES2+energy:188.035, AES4+energy:16.413]
 8:33:38 PM Logging to queue AMM+energy = 1795.291
 8:33:38 PM [AMS7+temp:77.9, AMS4+battery:75, LastTime:03%2F30%2F17+20%3A33%3A37, AMM+power:617, AES1+energy:8.581, AES3+power:197, AMS4+hum:52, AES3+energy:267.251, AES0+energy:37.072, AMS5+temp:81.4, AMS4+temp:75.1, Time:03%2F30%2F17+20%3A23%3A36, MPS2+temp:90, AMS1+temp:76.6, AMM+energy:1795.239, AMS7+motion:inactive, AES2+energy:188.035, AES4+energy:16.413]
 8:33:38 PM Logging to queue AMM+power = 617

You did not use the actual example URL in the link to my old message, right? You used your own API key? Just checking, seeing as how it’s not making sense.

When you say the spreadsheet is not updating, are any new rows at all being made? Have you checked to make sure they are not being added way at the bottom of a lot of blank rows? (Kill all blank rows at end if you want. It will add new ones as needed.)

Past that, I’m not sure what you’re saying. Can you paste the messages around the spurt, like I did? (Obscure the [API Key] like I did at 8:33:37, just to be sure.) If there is data there, you should be seeing it in your Spreadsheet. Did you follow every single little instruction in the instructions?

It may or may not help now (or might later), but here are some various other ways to see what’s going on with your SmartThings data in my old message 269 of this thread.

All working now. Issue was nothing in the end. I was logging only batteries and did not realise that they only report infrequently. Once I added other things the whole thing sprang to live. :slight_smile:
Thanks for your help. Now to try changing the formating and editing.

I am having a problem logging anything to Google spreadsheets. I have tried with both the URL keys one from when the sheet is created and one when it’s published. Still no luck. I am logging temperatures and humidity from weather station, hive and xiaomi sensors.

Another question to one that has been asked before but I can’t find it in this thread.

I have random spikes on my graph that are not accurate.

Is there something I can select to ignore anything over say 1,000kWhr?

I’m getting more and more of these too, specifically kWh

1 Like

This is very fast workaround that you can apply directly in the Google Sheet. Create new column and use this formula:
=MIN(D1,1000)
and expand it for all rows. Then use this new column in your chart. Hope it will help you.

Thank you!

[quote=“sapstar, post:305, topic:36719, full:true”]
I am having a problem logging anything to Google spreadsheets. I have tried with both the URL keys one from when the sheet is created and one when it’s published. Still no luck. I am logging temperatures and humidity from weather station, hive and xiaomi sensors. [/quote]
sapstar, if you have not yet figured it out,

You are mentioning keys from when the sheet is created and published. Just to be clear, do you realize the second time that the instructions talk about a URL, it’s the URL of the script/webapp? Not the sheet? See “Create The Spreadsheet”, Steps 14 and 15. The SmartThings app is handing data to a google script app, then that app populates the sheet.

Hope that helps. It’s hard to know what’s wrong if you followed every single step of the instructions to the letter, and it still doesn’t work. Way back when, I was brand new to Google Apps Scripts but I went through the instructions carefully and it worked for me. Although there were a few points where I thought it was unclear and so I asked them to clarify the writeup and they did (great guys!).

Is your SmartThings data going to the exact same worksheet that’s also getting your weather station data?

Would anyone mind if I ask if we have a more-specific name for this wonderful log? Past the fact that it is a Google log that logs SmartThings. It’s superb and I plan to use it forever. But I have also been using and loving @krlaframboise’s Simple Event Logger; it’s a great compliment to this one, as they do different things. (This one is a great overall view at regular intervals; Kevin’s is great for pinpointing some or all data from specific devices).

Both of these are Google Sheets SmartThings logs. Yet I am sure there will be more SmartThings Google Sheets logs in the future; it’s such a good medium. And besides, that’s a lot of words to write, laugh.

.
In some previous posts, I called this the CSchwer log, just to have a short, specific name for it. But it might also be called the Loverso log for his more recent work, which is also greatly appreciated.

So if no one minds, I will throw out a few proposed names. And anyone else please chime in with anything you like.

All I ask/hope is that it is specific, and short (hopefully one name or acronym). Ideas:

  • CSchwer Log or
  • Schwer Log
  • Loverso Log
  • CLS log (CSchwer Loverso Sheets log) or
  • SLS log (Schwer Loverso Sheets log)

Or maybe something more use-specific, not tied to names? But please keep it short or make an acronym of it.

.
Ideas? I can start a voting thread if we get more candidates, and folks want a poll.

cc’ing @Charles_Schwer and @jlv - you guys please chime in if you have preferences!

I really did very little work on this; I just resurrected the github and accepted some pulls. It’s the Google Sheets Logger to me, but you could call it the CSchwer logger.

(Also, FWIW, I’m case sensitive about my name: LoVerso :grinning:)

1 Like

Thanks so much for speaking up, John! Duly noted. Thanks for the tip on your name… that point is not real obvious here or on github.

I guess CSchwer Logger is the leading candidate. But if anyone else has ideas, please chime in!

If nobody cares enough to speak up in a few weeks, I guess that’ll be it.

I am having problems setting this up. I have the ‘Simple Event Logger’ working but for some of my requirements I think this will work better. I have checked and can’t find any silly mistakes (yet). In step 15 it states ‘Extract URL key for your new webapp, it is between /s/ and /exec.’ but I haven’t got a /s/ or an /exec. My URL is[URL]
Any help greatly appreciated.

On “Deploy as web app” screen shown in Step 14? I see /s/ and /exec on it as of today.

Ok. Thanks, I have now found that silly mistake. I was trying to take address from wrong place.

I had a strange Time issue in my Log starting yesterday (6/22) from 3:40 am to 11:56 am (all times Eastern EST).

The Time variable started to get stuck on a particular value (6 rows at 4:25, 24 rows at 5:28, 13 rows at 9:40), then at 11:56, it resumes the usual 10-minute interval I have.

Meanwhile, the other time variables I set up (like LastTime and also one I stuck in the Google Sheets script for an “independent” time confirmation) kept going along at incrementing 10 minutes as expected, no problem. Clearly it was something specific to the Time variable, and not the time function itself.

Would one or two of you mind checking your log - do you see weirdness from 4 am to noon EST, yesterday morning? Or is it just me?

Hoping someone can help me, this is the 2nd time this has happened. My time stamps are getting stuck sending the same time over and over along with time out error. Any body have any ideas on this? I have not changed anything this just started.

EDIT: Yes! I am experiencing long bouts of the variable “Time” being the same on my CSchwer Log. Today and yesterday. As of right now (11 am), rows have being saying Time is 2:01 am, since 2:01 am. There are many other long instances of groups of repeating Time yesterday. But my other time variables are making sense.

Sounds like what I just posted, right above your message, right?

How does your log look, or is the info even getting to your log?

P.S. I hope everyone else on this thread a while has put some other timestamp in their log, one way or another. All my other ones are doing fine.