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

This is what I was hoping for but to date I can’t figure out where or how.

FYI, in the case of the many multiple entries for the same date like we are describing here I actually got an error in Live Logging. I reported it further up in this thread …

I am so sorry to bump this thread, but I am stuck.

I have followed all the code to get the data to use last value if there is not a new reported value. However when I do this, it applies the last value to the time stamp as well and thus the time never updates. Could someone share or help with their code that allows me to back-fill the data if there is no change between reporting intervals but refresh the timestamp on each new row?

Can this be used with webcore to log a few variables to a spreadsheet?

@rswing, sorry for the delay - I was busy back then and forgot -

My post a few messages before talks about how the time stamp in the existing code has problems, and I posted workarounds. It’s a long post (due to including a lot of code, much of it the same old CSchwer base code), but shows how to put alternate time stamps into your CSchwer ST code and a separate one in your Google Sheets code, if you want.

@Wajo357, I don’t know about it working with webcore. I don’t know what that is.

The CSchwer log code works directly with SmartThings to output data to a Google Sheet you make. It’s all free. I don’t think you’d want a third app in there… it would probably get messy.

Maybe someone who knows Webcore can speak up. But I think the answer is probably No. Nor would you probably want to involve some other app … you’d have to edit both CSchwer and Webcore to talk to each other then, I bet.

Yes, this is the Google Sheet Log, code is here:

Step 16 in the instructions shows you how you can log data via a URL. set up web core to use that url and whatever data you would like to log. This bypasses the smartapp.

2 Likes

Thanks! That was perfect!

Hi there,
For anyone out there trying to use the “sensors” and “sensor attributes (comma delimited)” sections, I’ve worked out that you can’t use them to get multiple items of data from one sensor.
The way to use it is to tick the sensors you want to log, and then list the attributes for each of those sensors in the order they appear.
You can’t get two items of data from one sensor, as you can’t make it appear in the list twice.
(I am planning to have a little look at the code to see if I can produce a mod, as I want to be able to get multiple attributes from multiple sensors).

Hi @jamesxheath, could you clarify a little? I have been getting multiple sensor data from the ones I have, for a long time. Such as temp, hum, light, tamper, and battery from Aeon Multisensors. You mean if you have “normal” sensor output AND put a text string into the comma delimited section? I never could get that to work.

For the record, if anyone doesn’t know yet: Google updated Sheets limit to allow 5 million cells in Dec 2018. It was 2M for a long time, and search results will still find a lot of that.

How do I know? Because I forgot to check in with my CSchwer log and found it had 3M cells worth of data, and was still perking along, lol. So I looked into it.

But for me, with ~100 columns of output, it starts to bog down somewhere between 1M and 2M cells total; it starts repeating the Time variable, and also dropping some datapoints. So I’ve set a schedule to make a copy of the output Sheet, then pare it back, about twice a year. (5M cells would be about 17 months for me.)

1 Like

Hi - i can try!!

I have two non standard sensors - a netatmo weather station and a foobot air quality sensor.
Both give temp & humidity, which i can log by adding them in the pre-defined attributes section (which is limited to capabilities that are defined by SmartThings themselves)
However both have other interesting data that I’d like to log.
I can only log one extra attribute from each, as that’s the way the smartapp is coded.
So in the “sensors” section I select both, then I add the attribute I want from each in the sensor attributes section, in the order the sensors are listed:

The smartapp then parses the attributes list and pairs them with the sensors when subscribing for updates.
This will ONLY get you one value per sensor - not really what you (or I) want.

At some point I plan to mod the Smartapp code (once I’ve taught myself Groovy) to allow you to subscribe to multiple attributes from each sensor.

Do note that I am not logging anything like the quantity of data you are!!

OK - just ignore EVERYTHING I’ve posted about logging extra sensors.
I wrote some code mods, couldn’t make them work, and so backed out the changes gradually to try and understand what was wrong.
In doing this, I discovered that the original code works perfectly for me. I apologise unreservedly for doubting Charles Schwer’s original code.
I have ticked my two sensors in the “Sensors” section, and then listed out all the attributes I wanted in the “Attributes” section (no spaces) and it works. I get values from the sensors that have matching attributes.
I do feel like a bit of an idiot!

No problem James, I’ve “learned” a lot of things, too. :wink:

So you could get that comma delimited field to work, for sensors otherwise not defined by a device handler? And it looked just like your 7/9/19 screen cap - a comma and no spaces, right?

I never could get it to work, then later realized I could take a different approach. But one of the things I was never sure of is, What exactly should it look like when it does work? (Had I phrased it wrong?) So at least, now I see that.

Hope everything works great for you!

Hello All,

My Sheets has been steadily tracking my Energy Meter for most of the year, but its been recently repeating the Time variable like @RedKnight mentioned a few posts up. I’m guessing that this is a Google issue and not due to this smart app. Any best practices out there for fixing the issue?

Hi @benzyne, thanks for your PM. I’d like to post my code as an attachment here, but the forum only allows picture attachments and I’d prefer not to stick code almost 400 lines long in a message.

For others here - What’s the easiest way to post long code files, folks? I don’t want to go through all the Github rigmarole. Basically I want to attach a text file. How would I do that?

I’m new to this smart app and performed the installation as supplied in the github readme file. Everything seems to be working EXCEPT for the automated write to the google sheet. The ST logs seem to indicate everything is working fine:

10:45:57 AM: debug Google accepted event(s)
10:45:57 AM: debug 200
10:45:57 AM: debug https://script.google.com/macros/s/---/exec?Time=10%2F26%2F2019+10%3A40%3A57&x181+(humidity1)+humidity=44.1&x181+(humidity2)+humidity=44.6&x181+(temperature1)+temperature=64.12&x181+(temperature2)+temperature=64.4

Of course the — is replaced with my unique ID. If I grab this single line and execute in a browser, the spreadsheet will update correctly and I correctly get the following response:

The script completed but did not return anything.

It just does not work automatically as part of the groovy script. Anyone else seen this?

Uninstalled/reinstalled app
Created a new/different spreadsheet with script

Nothing seems to work.

TIA - Dig

Update: Found the issue as I did not have a setting in step 12 correct.

Change Who has access to the webapp to “Anyone, even anonymous”. Please note, if any one gets a hold of your published endpoint, they will be able to send data to your spreadsheet, but they will not be able to view any of it.

Many thanks for the logging app.

Any chance you can put this code on a text file or email it to me? I’d like to improve the original script and seems like it would be good to go from your script :slight_smile:

Hi All,

here my code an output in the log if you look at the stop it not filling in any idea

time Start Stop Room Pump
1/27/2020 14:47:58 Mon, Jan 27 2020 @ 2:47:46 PM CST 1/27/2020 0:00:00 Beau’s Room Light
1/27/2020 17:12:31 Mon, Jan 27 2020 @ 5:12:24 PM CST 1/27/2020 0:00:00 Beau’s Room Light

Hi!
Some problem 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, hew hub, same GoogleSheet app. New Google sheet, but it does not work.

No idea.

Suggestions??

Thanks!

did you install a second instance of the app and a second google sheet?

edit-sounds like you did that after re-reading… can one SmartApp work on two hubs? I also recently bought a second hub and added second location but haven’t tried using it this way. I was planning to use webCoRE…

If you are a webCoRE user you can skip the SmartApp and log directly from webCoRE to one or many sheets.