Somebody could post the full solution please?
I dont understand how to log events in google Sheets, bypassin the app.
Thanks!
Hi to everybody!
Hi!
I need some help, loggin HEM.
I have one location, 2 HEMs, Google Sheets app installed and every thing works fine for 2 years.
Now I add a new location, new hub, same GoogleSheet app. New Google sheet, but it does not work.
I got this error
Line 283 said this:
No idea whats wrong. Any Clue?
Thanks!
Hi all, This app is great. It worked first time for me and has been running fine for a week now.
Is it possible to have more than one instance of the logging app?
I would like to log my heating devices to one google sheet and my lighting devices to a separate google sheet.
Many Thanks Tim
EDIT-added some webcore examplesâŚ
Since the first post no longer points to this SmartApp, posting the link to working version againâŚ
Also a reminder that you can easily log to Google Sheets via WebCore, just follow the SmartApp instructions up to step 16 and use the test URL in WebCore. There are several topics in the WebCore forum but basically I do this:
const string GSLOG=
"https://script.google.com/macros/s/GOOGLE_SPREADSHEET_ID/exec?"
Then use this structure to add multiple data points as an expression for make a web request
{GSLOG}ColumnHeader=Data&Column2Header=Data2
This will log current deviceâs data
Make A Get Request To "{GSLOG}{$currentEventDevice} {$currentEventAttribute}={$currentEventValue}"
This will log multiple device data values at one time
Make A Get Request To "{GSLOG}Room1 Temp=[Room1 : temperature]&Room2 Temp=[Room2 : temperature]"
It looks like posts have been deleted, because what was post 154 back in 2016 is now post 125 and dropping. Just use the URL with /36719/154 at the end:
@RedKnight would you kindly be able to tell me what to adjust in the code to export a British dd/mm/yyy format? Iâve tried adjusting line 238 with various combinations but the results just turn out jibberish.
My Sheet is set to a UK time zone plus column formatting seems to have no effect. Any other ideas? Many thanks.
Hi Shinedown78, are you talking about what you want to see in the Google Sheet output? Thatâs handled by simply formatting that column in the Sheet itself. I can show you how easily, if you canât figure it out.
Sometimes a problem can be that you think the CSchwer code is outputting a date-numeric value (not format per se!), but actually the Sheet doesnât receive it that way (typically it might consider it text). In which case the Sheet wonât be able to reformat it as a date. Know what I mean?
If you need it specifically output by the CSchwer code in some format, that would be harder for me to answer. You could look it up as well as me, shrug.
Before I type more ⌠which of the above applies?
Thanks for the quick response.
My issue is that the date data placed into the sheet is in American Format - I understand how to format the column dates, the issue is that when dates such as the 1st Dec list (11/01/2020) Google thinks it is the 11th of January and does not format it in any way, as it thinks that is the date I am looking for. This then pulls into Google Data Studio as 11th Jan, instead of the 1st Dec.
So really I need the code to record the data in a UK format (I had the same issue in âsimple event loggerâ before I edited a line that sorted this out), but playing with line 238 which seemingly outlines the date style format published into Google sheets, doesnât seem to work as it did with SEL. Does that make sense?
I completely understand the problem with mm/dd vs dd/mm; of course, everyone wants it in their preferred format. In fact I use yyyy/mm/dd for dates in my SheetâŚ
Are you sure youâre getting a date value in the Google Sheet? To test, see if you can reformat it to any other date format. If you canât, youâre looking at text - not a date value.
That said, I donât know Google Data Studio. So maybe itâs actually a problem with how GDS talks to the Sheet. Or it could be âwarring default date formatsâ where one or more places along the line keep making assumptions about US vs UK (actually, US vs the rest of the world, laugh).
I tried looking for line 238 in the Google Sheets logging code but all I see is the end bracket for âdef handleLockEvent(evt)â. Are we talking about that def?
Come back?
Thanks Red Night. No, it doesnât actually change the date format, despite being in a format that is readable as a date for sheets. However, it is interpreted as a date by GDS, albeit an incorrectly. Showing January 11th instead 1st November. Itâs weird because both platforms are set to UK time formats.
Re. the code line, I was referring to the Smart app itself, and actually line 283, not 238! Namely this part:
if ( atomicState.queue == [:] ) {
// format time in the same wasy as sheets does
def eventTime = URLEncoder.encode(evt.date.format( âM/d/yyyy HH:mm:ssâ, location.timeZone ))
addToQueue(âTimeâ, eventTime)
}
I tried changing the date section to various formats of dd/mm/yyyy, i.e. dd/M/YYYY, d/M/YYYY etc, etc. But no joy yet. I guess Iâll just keep playing with this alongside the Google formatting to see if I get anywhere. Thanks for trying to help though!
Right okay I see it now ⌠for what itâs worth, my code there is:
if (atomicState.queue != [:]) {
def url = "https://script.google.com/macros/s/${urlKey}/exec?"
for ( e in atomicState.queue ) { url+="${e.key}=${e.value}&" }
def eventSendTime = URLEncoder.encode((new Date()).format( 'MM/dd/yyyy HH:mm:ss', location.timeZone ))
[etc]
Notice how my code says ânew Date()â instead of âevt.date.formatâ in there. I think I had trouble in the past with values that should be dates, coming across as text. So I found a way to force a date.
Also, I am causing the time sent to be the time that that line was run ⌠not the time previously defined by evt. This may change what youâre doing and it does make a new output field (âeventSendtimeâ), so be careful.
I actually defined several new time values in my code because the default code kept repeating the time of the previous iteration, if the Sheet output gets long. My is 68k rows and 100 columns at the moment and starts repeating the actual eventTime as early as Sheets row 6k ⌠eventually it never refreshes (staying stuck at some ancient time) unless/until my hub goes down due to internet being out, or whatever.
You said âNo, it doesnât actually change the date format, despite being in a format that is readable as a date for sheets.â I assume you mean that you cannot arbitrarily change the format of the date in your output Sheet (correct me if wrong). See, this says that Sheets considers it text, and not a date value. If it was considered a date value, then you could change the date format at will. The display format, that is. Know what I mean?
By the way - and this is for anyone asking a specific question about code - it really helps to include a URL to the code in question (if itâs on the web) so that 1) every reader thatâs interested doesnât have to dig all over to find the URL to it (when you have it right in front of you, if youâre citing line numbers!), and 2) it also makes crystal clear exactly which code youâre talking about. They can be called a lot of different things by a lot of people, here server-side code or CSchwer code or Smart app code or Google Sheets logging code or whatever, the list goes on. But if the Original Poster simply includes a link (whenever possible), everything is greatly simplified for everyone else thatâs interested. Said another way: If youâre asking others to do work by asking a question about code, do them a favor and give a link to the code (or whatever). Donât leave them hanging with it not even clear whatâs being discussed.
Okay end of lecture. 
Let me know if any of this helps.
P.S. Itâs been literally years since I worked on my code. I may be confused in the above. But I can readily change my date-column format in Sheets to be what you want (d/m/y), so thereâs something.
Thanks will give it a try! FYI, this is what I am doing with Data Studio - effectively pulling the data from the G.Sheet to visulaise an interactive dashboard. Take a look here: Smartthings Event Reporter
I just set up the current version of the logging program. It is not working. Are there some troubleshooting steps that I can do to figure out what is wrong?
Can you be more specific than ânot workingâ? Did you test the Google Sheet as in step 16?
I did not try step 16, as it was not clear what it would confirm. If that is the first step, I can certainly try it.
It confirms that you set up the Google Sheetâs webapp to correctly accept incoming values via a web hook. If that isnât set up correctly then nothing will appear to be logged from the SmartApp.
ok, this step works as indicated. What are the next steps I should try?
Since you have step 16 manually adding data to the Sheet via URL, I would go into the smartApp and add a simple device you can control easily like a contact sensor for a door(set value to log open/close) or thermostat (select heating setpoint). Then go open the door or change the thermostat heat set point. Then wait 5 minutes (I think this is the default âtime to queue eventsâ setting), and check the log.
Do you use WebCore? You can manually log anything you want to the Sheet via WebCore.
Do that, while also having the logging tab open in the IDE. What is logged by the SmartApp?
Was having a similar issue as whatâs mentioned above and the way I got it working was on the Apps Scrip window in the right hand corner under your profile picture I clicked âUse Legacy editorâ. When I followed the instructions as written, this slightly changed the web app URL key making it 17 characters shorter. When I pulled the new key from the legacy web app deployment URL, the data started logging correctly. Let me know if this helps.