HOW-TO Store data in Google Sheets directly from RM5

So.... then this happened. I wrote an app for the phone that manages the battery data (thats external to HE). I only pull the HE list of battery devices, their DNI, name and type. Still a lot to do - like a pick list for the batteries, cleanup images/icons etc. Currently the app is a private app - not sure if it should be published or not ... @LibraSun tell me what you think bud!

I don't think I can post video here so this is a link to my google drive.

https://drive.google.com/file/d/1lWUU5dS1-C1-zXlJnEby0eV5evm6waP1/view?usp=sharing

Also - no idea if anyone outside me can see this but heres the web version:

1 Like

Now that's some mega-cool shiznel right there! Nice work. Always fun to see things get extended in the direction of practical uses for automation, eh?!

1 Like

Yeah, very cool.

I'm only gonna say this - given that device battery management & monitoring is one of the biggest system management hassles/concerns discussed...you have provided a template for how this could/should be implemented on the HE Cx platform in the future (without resorting to an outside/cloud resource).

1 Like

Thanks for the vote of confidence - it's just for personal use ATM but it has some teeth I think. Right now struggling getting the HE list connected and managing the records in the sheet using DNI. Still working on the HE If I delete an device from the pick list etc.
I've reworked the template to include a battery manufacturer and a battery qty. as I surveyed my environment I realized some devices has multiple batts... like 2 AAA (philips hue motion sensor) or 4 AA (Bosch PIR EG:). Anyways. The external sheet has now been converted to an app that can be put on a mobile device, as well as run as a web app in a browser and it's holding it's own. Some layout glitches I'd like to fix - but the pick list for the battery type is nice. I also added graphs by manufacturer and shortly I'll add graphs of date changes someone else suggested but that a bit farther off.
Now, I'm concentrating on the HE side. I've got the initial app started and I can select from a list of devices that report battery capability but I've not yet learned how groovy will let me get selective over the array of devices - some of them are virtual but report battery such as Owntracks (which reports the cell phone battery). I sorta wanted the user to have the abiliity to not have to select devices, it would just crank out a list and build out / update the table. I asked if anyone could help me create an exclude but so far I can't find any examples of how thats done.
Thats the status! I'll be working on this off and on through the week and post up if I can make this usable for others. right now, it's working well for my stuff!

Here's an updated video of my app on the phone so far.
https://drive.google.com/file/d/1mz-8yw5XWKtzRkO_bkcsM0t4-TjV1VeV/view?usp=sharing

I just wish I could offer you guys a convenient method for round tripping values out of the cloud and back into the Hubitat. Unfortunately, unless you use Groovy, there is no direct way to pull values via HTTP request and store them in a variable.

1 Like

So much individual effort here, and useful. Hats off to you guys. But what if.....

2 Likes

@LibraSun many thanks for your effort here. That said, try following your step-by-steps to you see if every detail is accurate as you have them listed?

1 Like

I proofread it carefully and made a few tweaks, but think the step-by-step outline holds together. You'll let me know if I omitted something important or worded it weirdly?

@LibraSun, is there an easy way at the set variable step to deal with spaces and other symbols that break the exporting of data to gSheets? Energy duration has that space between 3 and upper case D.
image

I can see how that might be a problem, since HE doesn't have built-in string substitution (e.g. putting underscores _ for spaces ' '). Although webCoRE may? Still, you're so close, perhaps you'd be willing to just do a quick-and-dirty wholesale replacement of all whitespace with underscores or plus signs '+' in your RM actions.

PRO TIP: See judicious use of URLEncode method, below.

yep, thanks @LibraSun

1 Like

Also worth noting how this is also in RM.

1 Like

Much better! Didn't know that operator exists. Thanks for posting that alternative, which is far superior.

1 Like

@bertabcd1234 you were kind enough in other threads to comment about the use of State and RM. Has anything changed since you last mentioned this in other threads? Webcore does provide access to State for this use case where data is written out to Google Sheets but I'm not seeing how to use State along with RM to accomplish this.

I don't know how's webCoRE would be doing this, and I just fired up and can't figure out where in thr UI that is even located (I tried creating an action to set something and a condition to check something and didn't see a way to look at state in either case). But to directly answer your question, I'm not aware of anything that has changed. I'm assuming you're talking about state or atomicState, not just device event history (sometimes also loosely called states). There is a way to get that history from an app, though at the platform level there is no guarantee it will actually be there (I think a user can set the history to store 1 or maybe even 0).

1 Like

Here's an example of how I got\used WC for state values.

Is that not just the value of the temperature attribute under "Current States"? If so, that is not state (or atomicState) in the sense of "State variables" as you'd see on the app or device page. It's just the value of an attribute, which you can already do with Rule Machine, though you need to store it in a variable yourself before you can use it as part of another expression.

This is what I see when I look at the driver for that Smartthings Motion sensor.

Yep, so those are "Current States," just the current attributes and their values. It is not state in the state variables sense, which I may have been referring to elsewhere (but without a link I can't say for sure). You can definitely read attribute value with Rule Machine or a custom app (or most stock apps if they need it for their purpose).

Very impressed! I’ve been wanting to collect Home Energy Monitoring data for a while, I think this will finally allow me to do that! Thanks!

The instructions were really clear and easy to follow. There is only one thing I had to do differently in the 6th bullet of Step 2:

For me, the instruction that would have shown what I had on the screen:
With Payload highlighted, you will see the data fields under Form Data

1 Like