SmartThings Community

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

(John) #161

Ah, I didn’t realize your snippet of code was in the SmartWeather Tile.

0 Likes

(John) #162

[quote=“kevin, post:160, topic:36719”]
i saw that but didn’t expiriment with it, I’ll give it a try too
[/quote]I just tested it and it works. What I found is that it seems that values are only sent when they change, so if percentPrecipLastHour is currently 0, it won’t show up until it does non-zero (and then eventually back to 0).

1 Like

(John) #163

[quote=“0121stephen, post:131, topic:36719”]
One thing, the app seems to give me a “snap shot in time”, i.e. if there is no motion when the app push fires I don’t get a reading and it is possible for somebody to walk through a room, trigger a motion sensor but not provide a reading or open and door and close it gain and I don’t get a reading unless I set the push delay to 0.

Is it possible to get the app collate instances in the last 5 minutes or longer and return a positive reading if there has been any motion during this period?[/quote]

Maybe I’m confused, but that shouldn’t be how it behaves. I’m not logging from any motion sensors, so I can’t say this for sure. BUT… the code (like all the other SmartApps) only subscribes to events sent by the DTHs. E.g., when your motion sensor senses motion, the DTH publishes this event, and SmartThings delivers the event to everything that has subscribed to it. This SmartApp immediately notices the event; depending upon if you have a queue time sent, it will either immediately sent it to Sheets or delay sending it a short while. No matter what, the event should be logged; this should never miss an event that has been raised (unless there is an error posting the event to Google).

Basically, this SmartApp does not poll devices.

UPDATE: Ah, I see the problem now. If multiple events come in on a device, all of them are reported to Sheets in a single request. However, only the last value is saved since in the end they are all being reported at the same time, and thus there is only 1 cell to report them in. Thus, if you have a 5 minute queue time and at 2 minutes it reports motion and then at 3 minutes it clears and reports no motion, at the 5 minute mark both events will get posted to Sheets, and will result in an entry of no motion. You can tell this has happened because otherwise if there had been no other events on the device, the cell at that report time should be blank.

Basically, some devices need to have their status latched; e.g., there has been any motion in the last 5 minutes. This is basically what you asked, but now I understand why it happens and what it will take to fix it.

This won’t happen with some other loggers, like GroveStreams or InitialState, since those send a single message with multiple values, but each value has it’s own timestamp. This Sheets logger, when queuing, sends a single message with all the device values, but only includes the timestamp of the time the message is sent. Thus, they all get stored in the same spreadsheet row; otherwise it would be consuming many more cells and generating many more rows (of a single data value each).

1 Like

(Mike) #164

The Sheet logging has been great. Purely because of this, I got 4 more Aeon Multisensor 6s (on top of my 3 current environment sensors) so I can log house conditions real well. Suddenly my SmartThings is a real workhorse friend, instead of being a problematic novelty. In the future I will pair sensor data with Efergy 1-minute electricity logging of every breaker in my house to get a full energy/heat/cooling view.

Here, I’m going to post a number of small requests and questions. Not a single one is critical, so John, Kevin, and others, you can ignore all of this if busy. But if you get time or it’s easy, please go for it. I sort of experimented with doing some of this myself but found there’s so much going on, I’m not sure what’s what… some things simply stopped reporting, etc., until I revert.

Without further ado,

#1. INSERT ROWS, don’t overwite them:

Is it possible to have a new row be inserted instead of overwriting the next blank row? So we don’t have to keep adding new lines to Google Sheets for fear of running out.

One would hope there’s a way around this micromanagement… plus its hidden potential for disaster. I would like to collect years of data to make multi-year energy use assessment. I don’t want to find out two years from now, when it’s all very s.o.p., that it stopped logging a month ago because I forgot to do my quartlerly log in-to add rows.

You might say “just add 40k rows already” or whatever, but that makes the Sheet unwieldy for scrolling… now you have a vast empty tracts and your actual data is only a tiny part of the sheet.

So, can’t it simply add a new row to the sheet instead of overwriting an existing one?

#2. Optionally, OVERWRITE LAST ROW:

This would be an option which contradicts #1, for those who want it…

This could help Stephen, when he only wanted a Row 2, forever. But it could be a little more sophisticated. If it could be turned off or on, a person could sometimes log, and sometimes not (just show the most recent report). Maybe even depending on conditions, like, for certain times of day, if some condition is met, or every 20th report, capture 5 in a row.

This would be optional because the current approach (adding, not overwriting) is the best default. You could put a note in the code of how to turn it on or off, if it’s hard to make into an actual app switch.

This is not for me, but for Stephen and others who might want it.

#3. INSERT ROWS FROM TOP:

Enough scrolling to the bottom of long spreadsheets. Insert new rows up top (at Row 2), pushing other data down, so you always see your latest info easily when opening the Sheet. Plus, Sheets doesn’t have Excel’s nice ability to simply hit End and then Down to zoom to the end of your data. Once there are many thousands of data lines, checking your latest data is a real pain.

You’re welcome to make this optional if you want. But I personally would want it as my default.

#4. KEEP NUMERIC FORMATTING:

When a new row is posted, it keeps the centering format of a column (left, right, center) but does not keep number/date/time format of the column. Is there some way to keep it either by not having the new row impose its own format, or by copying row format prior to posting the new row, then painting that format back onto the new row once it is copied in?


You can see how some of the above elements could be combined. If you insert rows from top, you may have to play a bit to keep formatting of the data rows (Row 2 and onward) and not the header row (Row 1). My header rows are formatted different from my data rows. This can be gotten around by: 1) Copy Row 2. 2) Insert a copy of Row 2 at Row 2 (pushing down the rest of data; Row 2 data is now also on Row 3). 3) Delete data (but not format) from Row 2. 4) Copy in new data on Row 2. 5) Paint original format over it (if needed). And 6) you’re done.

#5. RENAME SENSORS?

So the spreadsheet columns aren’t so wide, I would like to shorten, e.g., these three names:

temperature to temp or temp.
illuminance to illum or illum.
humidity to hum. or RH%

This isn’t an issue that wrapping can help with, not the way I like to do it. I have short sensor names like “AMS4” and everything can be nice and compact with narrow columns, if I wrap (e.g., “AMS4” on first line, “temp” on second one) and can get the names of the data types short. Then I can see a ton of data on my PC screen at a glance. At this point I have 7 temperature and 5 humidity sensors, and about 30 others (and more will be coming).

I only see these particular words in one place of the Sheets logging code but if I change it there, that data type stops being reported. So obviously these particular words must essentially be keywords that are important elsewhere.

I see a couple of lines like

def keyId = URLEncoder.encode(evt.displayName.trim()+ " " +evt.name)

that probably formulate the column headers. It occurred to me to change these both with some IF/THEN sort of statements based on the three names. Sort of fooling the code after its main work is done; just changing final output steps. But the language is a little tricky and I will probably mess it up.

This one is kind of a nicety for me, but maybe others would like it, too. You might set up an IF/THEN example of how to do it in the code (where appropriate), and comments on how to do it for more, if you want. (Have it show them how to change something without changing it - change “temperature” to “temperature”.) Then people can readily see how to change names however they might want.

#6. LOG HUB UP AND DOWN:

I would love to log some sort of feedback on whether my hub is up or down. For long term data collection, this is important in many ways. If you have big gaps in your data (which you only see reviewing months later), you will know if this was the problem. From my house monitoring standpoint across years, it’s also a surrogate for how much my power and/or internet has been down. Very important for troubleshooting or understanding data gaps long after the fact. And simply good to know all by itself - what’s my true hub/net/power up time, past all the many many factors that can mess with it?

I have no idea how this would be done. But I know I get a phone notification if my hub goes down, and when it comes back up. I don’t know who is doing what, where, for much of this scripting. Is the Sheets script running in Google’s cloud? Where’s the SmartThings app; on my phone or on ST servers? Are my phone notices on a purely local wifi basis??

Anyway, it would be great to log hub up/down. As its own entirely separate script and log, if necessary.

Can you capture the phone notices? Or would it have to poll the hub constantly? Still, I’d want this if that’s all there is. Perhaps polling at one-minute intervals. You wouldn’t want to log every up minute, but if you log it whenever it’s down (not responding), plus say when was the previous one or two timestamps it was down, you have some error correction there. (If one log entry is somehow lost, it’ll get covered by the next one.) I don’t like systems or logs that don’t have any indicator of data being lost… I don’t like systems that assume everything will otherwise work okay. Because in the real world, it doesn’t. Especially for me since I’m cursed, lol.

And something even more different:

#7. Just an explainer on what is going on where, for the scripts. For us newbs.

I have noticed that the timestamp going into the log is not the current time, it is the time minus the requested interval. (If I ask for 15-minute intervals, it will be a timestamp from 15 minutes ago.) So I just learned new values always replace old ones, which John just said for the motion sensor. So if I have selected “15 minutes” but something is changing fast (like my whole house electricity monitor; kWh are constantly increasing) what I’m actually seeing isn’t data from the time of the timestamp, it is actually more like data from timestamp + 15 minutes. Similar things happen to all other data, all dependent on how frequently they trigger (if it’s often less than the reporting interval).

Where is the Google Sheets script running? Is it actively sending requests for data, or passively accumulating something sent up by SmartThings?

Is the SmartThings apps on the phone or a ST server…

What kinds of things can go down or cause problems in the data, past my sensors and hub?

I have noticed that when I selected 15 minute intervals, it was actually reported 18 minutes apart about 10% of the time, and 30 minutes apart 1% of the time. But at a ten minute interval, it’s almost always within 11 minutes.

Who exactly is keeping time here? Why aren’t these always exactly the interval requested? Why would the 15-minute be so haphazard but not the 10-minute interval? (Both were done over periods of at least 12 hours.)

Questions like that, smile.

Okay, now I’ve overspent my welcome (smile). If someone can help with some of these, that would be so cool. If not, that’s okay too.

Thanks!!!

1 Like

(John) #165

You don’t need to do that. It will automatically add rows as needed. My current sheet has 4107 rows, the last of which was automatically added 7 minutes ago.

[quote=“RedKnight, post:164, topic:36719”]3.INSERT ROWS FROM TOP:
Plus, Sheets doesn’t have Excel’s nice ability to simply hit End and then Down to zoom to the end of your data.
[/quote]
CTRL-DOWN moves to the last row in a column that has data. If you do this in column A (Time), you will jump to the last line logged into the sheet. Alternately, CTRL-END moves to the last column of the last line that exists in sheet; however, this isn’t helpful if you add blank rows at the bottom (but you don’t need to do that). See Help->Keyboard shortcuts.

1 Like

(John) #166

I’m not applying any special formatting to the data, and most of my data is numbers. The default format seems to work, so I guess I’d need an example to understand better.

[quote=“RedKnight, post:164, topic:36719”]
5.RENAME SENSORS?
So the spreadsheet columns aren’t so wide, I would like to shorten…
This isn’t an issue that wrapping can help with, not the way I like to do it.[/quote]
Funny, I was about to ask for a device alias mechanism for the Ask Alexa SmartApp for much the same reason. I’d like to ask/set devices without speaking the long names.

However, I think you might be able to use wrapping to solve most of this. This is a snippet of my spreadsheet. I’ve just set row 1 to wrap text and then narrowed the columns as they got created. Yes, they could all be narrower if the words were abbreviated. But, IMHO I don’t think the end-goal is to view the data in the spread sheet; instead, I think the end goal is to store it there but then use graphs to visualize things. With that you don’t need to worry about the column widths.

That said, you could try something like adding a new subroutine like this:

private def shortEvtName(evt) {
    String name = evt.name
    if (name == "temperature")
        return "temp"
    if (name == "illuminance")
        return "illum"
    if (name == "humidity")
        return "RH%"
    return name
}

and then replace the two instances of evt.name with shortEvtName(). I’ve not tested this, however, and were I really to add it, I’d probably add a preference to control it. But this might give you a starting point.

1 Like

(John) #167

Offhand, I’m not sure if that event is made available; I’d have to search the doc. Realize the SmartApp is running on a SmartThings server somewhere (“in the cloud”)…

I had thought about making the queuing logger use the timestamp of when it sends the data, but the way it works now makes (somewhat) more sense. If you only have 1 event queued, then right now it is sent with the correct time. If you have multiple events queued, then they will all get logged with the timestamp of the first queued event. It’s a trade-off between the number of rows in the database and timestamp accuracy.

For instance, I have an SDR that reads my electric meter so that I can measure my usage. I started logging it a year ago when our Solar PV array went live. I originally started logging every data point I collected; that quickly resulted in a few thousand data points a day. I then switched to only changed values. Even then, I have 100K points for a year of data. I finally decided to keep an every 15-minute value and a daily value. Just the daily value is enough to see the trend over time (at least for me):
https://thingspeak.com/channels/123559/charts/1?bgcolor=%23ffffff&color=%23d62020&dynamic=true&results=600&type=line

I’m still deciding how to do visualizations of my data. As you can see, I’m using ThingSpeak for some data. I’m setting up plots off the Google Spreadsheet, and I’m still playing with Grovestreams and local solutions.

1 Like

(John) #168

I’m a newb, too, but I’ll try to give my best explanation.

The SmartApps runs on the SmartThings server (“cloud”). It could possibly run locally on the hub IFF (1) it was an officially released SmartApp and (2) all the devices it used were also local. That isn’t likely to happen, so it will always run in the cloud.

Among the infrastructure that SmarttThings provides is an event driven, publish/subscribe system. Devices publish information and SmartApps subscribe to those feeds. This isn’t the only mechanism provided (some SmartApps directly query devices), but this SmartApp is entirely passive. It only gets events that other devices raise.

Time keeping is happening on the server. This uses runIn() to schedule the upload event, which (IIRC) purposely uses a random skew on when the event to avoid too many things firing off at the same time.

That said, if there are no events, then nothing will get logged, so it’s not impossible to see events 30 minutes apart. But this in checkAndProcessQueue() there is code to determine if the queue has not been processed in a long time (twice the queue time) and if so, force the upload. Were that to happen, it would be logged, but it might not be easy to see (maybe it should keep a counter of the number of times that fires called missedPolls, similar to failureCount).

The Google Sheets script runs on Google’s server (“cloud”). It does all the manipulation of the spread sheet, on behalf of the SmartApp. It only does something when the SmartApp sends an HTTP request.

1 Like

(Mike) #169

Thank you so much! This is extremely helpful, John. I will try to contribute to help repay…

  1. FWIW, I did specifically check that All list of PC shortcuts. Ctrl + Down is not there, sigh (but thanks!). Ctrl + End is, but I had added a thousand extra lines (per my #1), so it was going to the end of all that empty space.

I never actually tested that the script would add a new row… I didn’t want to test it (and chance losing data) when I could see it was not adding rows (but this was when it had blank rows to use). But of course it’s very useful that it does add a row…

See how things don’t work - only for me? lol … But now I know - thanks so much!

  1. For the formatting, see this example. The first three lines are my preferred format, the last one a new logged line that’s not:

See the difference in date and the forced single decimal point for all the temperatures and energy (kWh) readings. (The temps with no decimal point are .0 of course, but I’d like them to say .0, thanks very much.) Also, borders are not copying down with new lines, apparently.

My sheet extends to the right more, and I may get even more sensors. See what I mean by narrow columns now? If it was “temp” instead of “temperature” (etc.), columns could be much narrower. If you look carefully at how wide the temperature number itself is, relative to the white space around it, you see how a person can easily fit twice the columns in all that unused white space - and I’ve already narrowed it a lot compared to the default. Ultimately I want mine very dense. Please don’t tell anyone, human. Of course we are all humans here. Ha. Ha. Ha. Humor, it is to laugh.

Stepping back, it’s true that format isn’t a huge deal. I will also be using a data visualizer (T.B.D.), like you. But still, if all my data goes to the sheet first, I’ll still be looking at my log countless times, just the same. So it’d be nice if new data is formatted how I like. It’s why I wondered about painting the format after adding data… I doubted you were actually specifying a format change, which means it seems to be doing that itself, which means it probably needs to be deliberately re-applied after copying in.

If that’s possible.

  1. Thanks for that code on renaming sensors… I suspected it was something like that. Let me play with it and report back here if I can get it working, for anyone interested. You can see why I would like it, above.

  2. Very useful info on the timestamp and logging process, thanks! Right, you had to choose how and what to report… you can’t report it all.

If I’m understanding right, then:

  • The first time an event happens (in a new interval), the ST code captures that timestamp, and the timer countdown starts for the requested interval. (Tell me if wrong! It means the time between logging will often be longer than the requested time, if there is little or no data.)
  • The actual time will be slightly randomized, it won’t actually be exactly equal to the time, to prevent timing-based traffic jams. (If correct, it must be why I saw a few rare instances of time very slightly less than the requested interval.)
  • If there are no events at all, there will eventually be a forced log entry when two intervals have passed. (If I’m reading my log right, the output row could still have data, just data of a type that doesn’t force an interval. Like my energy readings which are always increasing, but no one specific reading is particularly important.)
  • The Google Sheets code just passively sits and waits for the SmartThings to send over a row. All the Sheets code does is take this, and put a new row on the Sheet. It never actively requests a row or data output.

Also I finally just noticed that the Log for the IDE App code shows each output row as it is being built, complete with timestamps. Useful for understanding just what’s happening here.

If I had chosen how to handle timestamps, I would do this (if it’s possible):

  • Timestamp when the push-queue interval began. The actual time it started, not time of first incoming data.
  • Output the length of time the row’s interval covered/lasted (mm:ss). (As you have it now, this should be 1-2 times the requested push interval.) Importantly, it’s not showing the interval a person wanted/selected… it’s showing the actual interval as defined by the start of interval (first timestamp) versus the time the script says “okay, I am outputting to Google now”.

I like stating real start time and the row’s interval because:

  1. When you have lots of data and changed the interval a number of times, now you have an easy number for checking how often something was reported relative to bin widths, etc. If your first-data-time is mixed together with changes in queue time occasionally over weeks or months, you have shifting intervals that are hard to use as any sort of denominator. But if you simply state what it actually is, it’s clear.
  2. Storing both lets someone make sure everything is working like it should. Later, you can QA that the requested push time actually does equal the time covered between rows (time of row - time of row before it). This length can increase if you doubled the interval if there’s no data, or whatever, that’s okay - because now you’re showing what the actual interval is; you have your data positively confirming that this is what happened. As opposed to something happening with the servers that left unexpected gaps in your time.

I hope this is making sense. One thing here is that while nobody expects problems in the servers performing their duty (and outputting on time), how will you know if there ever is a problem, if you don’t have any way to check? I would rather have something that both shows the actual interval width AND can at least let you know something weird happened (or that it never did!) for forensic QA purposes.

Past all that, it might make you crazy if I now said I’d like two more times (as mm:ss offset from Begin timestamp) - for the time of the first and last piece of data covered by that output row. First, having at least one data time serves as a reality check back to actual data coming from one’s hub. (These scripts are entirely in the cloud and can run forever without there even being a hub, maybe.) Two values give strong indicators of the length of time the hub was down, if one row captures the last time you got data, and the next row covers the first time. It’s not perfect; it can miss short down-times in the “middle” of the polling period. But it will give real insights into uptime (hub, internet, and even house power) over longer intervals.

Finally, showing the first and last data times gives people a lot of info on exactly when things happened if they don’t have many sensors. And if they do have a lot of data, it gives them some usable windows into statistics of how often data is being reported back (it gives them samples of how much “quiet time” there is).

So, right now you have one full timestamp (first data). I would have one full timestamp (begin interval) and three offsets from it (mm:ss for actual length of interval, and first and last data time). Then people can do QA and/or piece together quite a few things if they want, including long after the fact. (Summarizing a year’s worth of data, etc.)


OMG I sure can write too much.

If you do nothing more, you’ve already done tons. Thanks so much! I will be working on the substitute names thing, and report back. If you are can do anything with time reporting or formatting, that’d be very cool. And if not, I thank you from the bottom of my heart already. This automatic logging is so cool!

0 Likes

(Kevin) #170

Ctrl Down Arrow works for me, just click on a cell in the date column first.

You can change the heading font to Arial Narrow to save a little space (click fonts then more, search “narrow”). If you poke around in the smart app source code you could probably rename the column headings there also.

0 Likes

(John) #171

[quote=“RedKnight, post:169, topic:36719”]
For the formatting, see this example. The first three lines are my preferred format, the last one a new logged line that’s not[/quote]
This would require a change in the Google Apps Script. If you open the script back up, it basically takes the values passed from the SmartApp and fills them into an array called newRow, which it finally appends to the sheet with sheet.appendRow.

What I think would work is to add something to copy the format of the previous row on top of the newly added row. It looks like copyFormatToRange might make it easy. Something like this might work (this is untested); Change the last line to:

  var lastRowRange = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn());
  sheet.appendRow(newRow);
  lastRowRange.copyFormatToRange(sheet, 1, sheetGetLastColumn(), sheet.getLastRow(), sheet.getLastRow());

UPDATE
As I was testing this, I found this is unnecessary. If you don’t pre-add the rows, then it seels the appendRow will copy the format of the previous row when adding the new one. It looks like number formats, font style, alignment, and a few other items are copied automatically from the cell directly above. Cell borders are not copied, however.

1 Like

(Mike) #172

Thanks as always, John!

I am not finding that the formatting is carried forward when a new row has to be made (at end of spreadsheet). I find it to be doing the same thing I said above. It does keep alignment (left, right, middle), but overwrites numeric/date/time format. Maybe there’s some confusion if you prefer the defaults and/or don’t have something real obvious to see when it’s not doing what you want (like my kWh readings that I don’t want showing all three decimal points).

I saw a place where date formats are specified:
// format time the same way sheets does def eventTime = URLEncoder.encode(evt.date.format( 'M-d-yyyy HH:mm:ss', location.timeZone ))
Now I can change the timestamp to my preferred MM/dd/yy HH:mm:ss just fine.

Anyway, let me take the numeric formatting issue from here. (Unless you had one more thing you wanted to try.) It’s a pretty minor thing and you’ve given me some clues to how it may be approached, and I am trying to learn Groovy. You’ve already done so much.


Re: Groovy, I tried substituting the shortEvtName code but when I try variations on the suggested code, my app shuts down (no logging at all) until I revert. Let me play with it to see if I can get it. Like I say, I am digging in and trying to learn Groovy, which I guess is okay since you guys have shown this really useful code that’s worth learning how to modify. (Otherwise, I really tire of how the answer to so many questions in other spheres seems to be, to learn a new app. Or new language. One tires of forever needing hours or months of learning instead of actually doing something with their life, laugh.)

Wow, Groovy is more compact (=intense) than VBA. Which must make it good to use, but harder to learn. C’est la vie. Full speed ahead.


Kevin,

Thanks; yes, Control-Down does work for me. I was saying I had specifically looked for a shortcut key for that in Google’s Help, because I knew Excel had a shortcut for it. But Google does not show ^Down in their Help. Which understandably led me to conclude they didn’t have such a thing. Oh well, smile.

Many thanks for your note about weather data… I definitely want to get to that, but am focussing on regular logging at the moment. I will have new A/Cs put in, in 2 weeks. So I want to collect as much “before” data as a I can with the logger. Combined with all my electricity data, I hope I can tell how efficient the new ones are, compared to my current (old) A/Cs. (I can always pull down historical weather data later, but that’s not true for my own sensors.)

Finding this logger - free! and modifiable! - was wonderful timing, and very cool.

0 Likes

(John) #173

[quote=“RedKnight, post:172, topic:36719”]
I am not finding that the formatting is carried forward when a new row has to be made (at end of spreadsheet).[/quote]When I tested it, I was pretty sure it worked. What I was doing in my testing was to add a new Google Apps Script that created a fake row (var newRow = [ new Date(), 1.1, 2.2, 3.3 ]) and calling appendRow. I then added formatting to the last row – italics, bold, and changing the numeric format of the field, and then then just executed the script. I’ll try again tonight and save the result.

[quote=“RedKnight, post:172, topic:36719”]
I saw a place where date formats are specified:// format time the same way sheets does def eventTime = URLEncoder.encode(evt.date.format( ‘M-d-yyyy HH:mm:ss’, location.timeZone ))Now I can change the timestamp to my preferred MM/dd/yy HH:mm:ss just fine.

Anyway, let me take the numeric formatting issue from here. (Unless you had one more thing you wanted to try.) It’s a pretty minor thing and you’ve given me some clues to how it may be approached, and I am trying to learn Groovy. You’ve already done so much.
[/quote]On the date, realize there are two sources for it. The code above is only used when using queuing in the SmartApp. If you don’t use queuing (e.g., set the time to 0), then the date is generated in the JavaScript. And the example I gave above on using copyFormatToRange – that’s JavaScript (what Google Apps uses), not Groovy.

1 Like

(Mike) #174

Thanks greatly John, your draft worked great. For anyone who wants to change their column headers, do it like this. Search your code for the first instance of “evt.name” and insert this, up to the line with evt.name in it:

def shortEvtName(evt) {
    String name = evt.name
    if (name == "temperature")
        return "temp"
    if (name == "illuminance")
        return "illum"
    if (name == "humidity")
        return "hum"
    if (name == "acceleration")
        return "accel"
    return name
}

private sendValue(evt, Closure convert) {
	def keyId = URLEncoder.encode(evt.displayName.trim()+ " " +shortEvtName(evt))

There is also one more instance of evt.name to replace with shortEvtName(evt), be sure to get that, too.

You see the examples and can play with them. I found I could include a numeral or end the new name with a period and it worked okay.

If you have a lot of sensors, then edit your code but don’t save it until the next time google accepts data (load your simulator and watch it). Then Save your code and quickly change column headers before the next update and everything will proceed smooth as can be. (It won’t make new columns and orphan your previous names; it will just go right into the right columns whose names you just updated.) If you want, experiment on one column that’s updated a lot, first.

I also used Kevin’s suggestion of a different font for column headers, it does help some. In addition to the several “narrow” fonts (go to “more font” then filter on “narrow”), there’s a hella narrow one called BenchNine. I went with PT Sans Narrow at 12 point to match my Arial 10 for data:

I have 9 Aeon Multisensors each tracking 6-7 things plus some other sensors. Now you can see why I like it tight. At 10 minute intervals. Right now I am trying to calibrate their temperatures against each other, thus the graph. I found you have to repeat column headers to get them to show on an inserted graph if you are only graphing a selection of your data. That’s what I repeated the headers right in the data (which is where the graphed data starts).

As we discussed, next I will see if I can get cells’ numeric format to stay the same. And I can try to add those other timestamps myself. It may not seem like I did a lot above, but it’s a steep curve at first; even just the concept of what is on what servers. So I really appreciated your examples.

2 Likes

(Mike) #175

Anyone who can help - in the Logging script Preferences area, there’s a place to put additional sensors that says “Sensor Attributes (comma delimited”. I successfully added my Aeon Multisensor 6 accelerators by adding “accelerator” here. But if I try to also add their UV sensor, shown in their Device info as being called “ultravioletIndex”, I get the error

groovy.lang.MissingMethodException: No signature of method: org.codehaus.groovy.grails.web.json.JSONArray.tokenize() is applicable for argument types: (java.lang.String) values: [,] Possible solutions: takeWhile(groovy.lang.Closure), takeWhile(groovy.lang.Closure)

This is when I tried adding the sensor attribute string as “accelerator,ultravioletIndex” (without quotes). I also tried it with a space after the comma, but got the error. What am I doing wrong? Thanks if you can help!

0 Likes

(Mike) #176

Here’s another timestamp in the logging script, if anyone wants: An “EndTime” column for the time that the last data came in. (The script’s current Time column is when the first data came in.) In addition to the obvious, it gives time boundaries on that row’s data, helpful if there are unexpected big time gaps… now you have a clue if it was gathering data over the long period (which also has implications for what happened), or what. Same thing for unexpectedly short periods.

In the original code, look for the IF eventTime code in the first clause shown here, and then add the ELSE clause shown here:

		if( atomicState.queue == [:] ) {  // existing code
      		def eventTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))   // existing code
            addToQueue("Time", eventTime)   // existing code
    	}   // existing code
        else {    // new code
        	def eventLastTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))   // new code
            addToQueue("LastTime", eventLastTime)   // new code
		}   // new code

	addToQueue(keyId, value)   // existing code

Edit the date formats to your taste.

I tried very hard to make other timestamps for when the queue first starts and when it ends. (Not relative to incoming data; just plain old stop and start time for that row.) However I tried to put something like the two eventLastTime lines above into the ProcessQueue routine a dozen different times (at its start, for the EndTime, or its end, for BeginTime), but the script would never output. I could see it queuing data in the debug window, so it was still working… it just never would output the queue. Somehow I am missing some important functioning of Groovy, sigh.

0 Likes

(Mike) #177

John, I couldn’t get your formatting code from message 171 to work:

var lastRowRange = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()); sheet.appendRow(newRow); lastRowRange.copyFormatToRange(sheet, 1, sheetGetLastColumn(), sheet.getLastRow(), sheet.getLastRow());

It looks perfectly fine to me relative to documentation (what do I know) but it has no effect. I have to wonder if there isn’t something larger going on, like maybe that level of functionality simply isn’t included in Google Sheets. Remember how Sheets will copy column centering for me, but not numeric formats? That’s what I mean by “level of functionality”.

Did you say it does work for you - it does copy numeric formatting? If I could back up just a little,

Can someone / anyone using this logging script do me a favor… go into your Log and change some of your number formats to something entirely bizarre. Make sure it includes the last row of data.

Like, add three more decimal places than you realistically need. Or a custom date format you’d never really want.

Then see if the next automated script output row truly copies your bizarre numeric formats for real. Or does it revert back to some default norm? That’s what it does for me.

If someone can humor me - and apologies if you’re absolutely sure you’ve already done this, John - this is a test that Google Sheets is not copying numeric formats, only for me.

Thanks very much if you can help - Mike

0 Likes

(Mike) #178

I just wanted to say THANKS to John and others, Kevin and of course CScwher. But mainly John for such great help here. I’ve been using Google scripting a month now and it ROCKS.

Prior to this, SmartThings was a failed experiment for me… out of the box, it couldn’t do the very few things I’d really wanted from a smart home. (I also tried Wink before that, and it couldn’t either.)

But now it’s become an embarrassment of riches of free, flexible data monitoring… something I hadn’t even expected.

I love it!

Thanks so much!

Have a great 4th if you’re from the U.S.!

0 Likes

(John) #179

Hi. I just got back in town. I’ll look into this again sometime this weekend. Do you have a test spreadsheet you could share with me?

0 Likes

(Mike) #180

Okay … hmm … you want me to set up a test sheet? I can always share my current one with you (do you still have access?)

Did you try what I asked? Namely, give your last row a lot more decimal points than needed (for a number), and a goofy date format (for a date), and see if it keeps formatting when the script adds a new line.

No one has replied definitively on that score (but apologies if you do know it works for you for sure, already).

0 Likes