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

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.

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

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.!

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?

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).

Just had my first real-world test of having a LastTime timestamp added to the log (#176 in this thread). My hub burped, probably due to my internet going down. I didn’t otherwise see any status broadcast SMSs from SmartThings HQ so I assume ST servers themselves were okay and it was only my local internet connection, correct me if wrong.

For the record, my reporting (queue) interval is 10 minutes.

At 00:47 EST this a.m., I got a ST phone notification saying my hub was down, then at 00:52, another saying it was back up. (It takes 10 minutes or more to time out and give the “down” message, but the “up” message is usually pretty real-time.)

On the Google Logging side, the Time field showed a gap of 35:37 mm:ss (00:16:49 to 00:52:26). I will call the script’s Time field “FirstTime” here (the time the first data was collected for a row) to keep things separate from my LastTime field (the last time data was collected, on a row).

With the LastTime field I made in #176, I could see that the difference between the last LastTime (at 00:24:16) and first FirstTime (at 00:52:26) across the gap was 28:10 mm:ss of no data.

Due to having LastTime, I can now tell that it wasn’t a 35 minute gap that might or might not have been filled with data (it could have simply been an abnormally wide logging period). Now I know with certainty that there was a 28-minute gap in data reporting, in that 35 minute span.

I can tell from all the rest of my usual LastTime to FirstTime gaps that there are usually only a few seconds to a couple of minutes at most for one of many sensors to report something,as you might expect. This means my 28:10 gap was probably more like a 27 minute data lost gap.

I have also found that the script’s Time field sometimes gets changed based on events in the script. So while 999 times out of 1000 the Time field is FirstTime for data, like you expect, on the rare occasions when it is not the expected interval, it may in fact be due to the script having changed Time to something else. I don’t know exactly what causes this in the script, but clearly it is sometimes not the first data time - and it’s also likely to to happen right when the inter-row interval looks real screwy. So, as currently implemented, it’s not a good measurement of when data is being received… it specifically is not often right when there’s a problem (and you want it to be what it’s supposed to be).

Don’t get me wrong, I am not dissing anybody or the script. I think the script and folks here are magically wonderful! I’m just saying that the script could supply more info to troubleshoot problematic data.

My LastTime field helps with problem intervals. But what I really wish the script had was a timestamp for when a given row’s time period started and ended, period (regardless of data timing). And also the FirstTime and LastTime for data. Then it should be 100% clear what is happening relative to data reporting when weird things happen.

I tried putting this in the script, but could not get it to work. If anyone can find a way, that would be great. Like I said, I’d love 4 timestamps (Begin and End row and First and Last data). Then there will be a lot of ways to figure what happened in hindsight, if things look screwy but you only notice it well after the fact.

John, if you’re still around, I don’t care about that format issue as much as this timestamp one. Format is a cosmetic thing. But if your data is screwy and you can’t tell what happened, it’s more than a cosmetic thing.

Thanks again for this fantastic script!

P.S. In my example, you can also see how the additonal time fields can help clarify what happened, for how long, as a proxy log of your internet and power being down, too. With only the Time field, you can’t say for sure it means the time of the first (only?) data, or the time the time-out period started (or ended?). But LastTime (and the other time fields) make all this very clear.

I had a random weird event, although I haven’t tried to trace it back, I log all my temperatures to a spreadsheet to track room trends, and make sure things aren’t getting too hot for the pets.

The last week is below, with a single concerning datapoint. I really hope my balcony didn’t get to 78 C…

The sensor probably just reported in F for a single event, but its a good excuse to share my logging.

How did you get this chart? The only way I have only been able to get a plot chart because there multiple data sets for one day due to the temperature changing.

It appears to have broken…
I was using a “Time Line Chart” which allowed for dynamic scaling, and interpolated the points for me, but I now just get what looks like an X-Y scatter.

Yeah thats all I get as well.

Log Date Format Option Request.

I’m not sure if this is the correct place but I am hoping the author, Charles_Schwer, could add an option to select between two or more different date formats. The options could be:

Present date/time format: "9/19/2016 10:26:11"
Optional date/time format: “Mon, Sep 19, 2016, 10:26:11 AM”

I am presently changing the date format manually on the sheet after log updates.

I was thinking of making a branch of my own from his main and coding the date format myself. But I would need better knowledge of github. Also not sure doing this is acceptable or possible. I would appreciate anyone thoughts on this, also github links to help/examples about this.

Thanks.

Hi Boston,

To me, your issue is very similar to what I’ve been writing about in, e.g., message #177 above. I asked if the spreadsheet could be made to copy the numeric format of the row above. If this could be made to work, all you would have to do is change it on the bottom row of your spreadsheet once, and it would keep propagating the last line’s format after that. It could be any format you want, and you could change it at will. Date or number. There’s a date format I’d like different, and also, I want Sheets to remember the number of decimal points I want to see for some electricity meters. (These are all “numeric” formats, as far as I know.) No specific format coding would ever be needed if we can just get the bottom row’s formats to propagate.

Would that work for you?

But apparently there is a problem inasmuch I believe folks have the impression Sheets is supposed to be automatically doing that, when in fact it doesn’t. It may even be a deeper problem; it may be that Google Sheets can’t do that, for some reason.

I tried playing with it in #177 and couldn’t get it to work, but I am a novice. I’ve also asked several times in this thread if someone can verify whether they can get numeric formats to propagate naturally, as some here appear to think it works. But no one has directly tested for me to say if they can (or can’t) get it to work, even though it would be easy to do. (One came very close to testing; JLV said “I’m pretty sure it worked, I’ll test it again tonight”, but then didn’t get back about it.)

If I can’t get it to work, but others imply that’s how it works for everybody, I would really like someone to try exactly what I can’t get to work, and reply to me about whether indeed it does or doesn’t work for them. Otherwise it’s very confusing to me. Is it just me? Maybe I’m completely missing something, or forgot about some unusual environmental setting of mine.

Other posts like yours imply it isn’t working, but of course you weren’t specifically testing for it per se.

Anyway, I hope the Sheets numeric formatting propagation issue can be addressed, somehow. But the bottom line remains that this Google Sheets Logging is super fantastic. I love it to death and will keep it, even if the formatting isn’t perfect. Long live CSchwer, JLV , and others for making this fantastic capability!

Recently I found that the Google Sheets Log was queuing and reporting at 5 minute intervals, not the 10 that I wanted. If anyone else is having interval problems, here’s a workaround…

The problem involves this line in the groovy code:

input "queueTime", "enum", title:"Time to queue events before pushing to Google (in minutes)", options: ["0", "1", "5", "10", "15"], defaultValue:"5"

I had edited the choices to 0, 1, 2, 3, 5, 10, and 15 minutes, and was using 10. This had been working fine for months but on Sept. 24 I happened to reinitialize it, and the interval insisted on being 5 minutes even though I was selecting 10. Even the dropdown that shows the selected value when you “minimize” it would say 5 as I selected 10.

To make a long story short, I found it is now returning the ordinal position (including a leading 0). My 10 value was the 5th position of possible choices I had, if you start counting at 0. (I inserted those extra choices months ago, when I first started using this wonderful app.)

Once you see the problem, the workaround is simple: Just edit the code to list each number from 0 to whatever you want, then select what you want.

As for what caused it - I had not otherwise touched my code in months, as of my Sept. 24 reset. The only thing I can think of is that there was a hub update on Sept. 15, but the problem didn’t surface until I reinitialized on the 24th. But this is only a guess.

Anyone else see this?

@RedKnight @jlv

What is up with this app? Have you heard anything from the author? Are you guys still using his code or have you made updates? I just converted his code into a parent/child app and did a pull request, not sure if he will notice since it doesn’t look like he has been active here…

I just saw the pull request. Why make it a parent/child app?

I believe in order to log to different files you need multiple installs. Parent / child keeps your smartapp list from getting clogged up.

Have you guys updated your code for more features?

The repo version is what I run.

I’ve got 2 instances of the smart app, so I didn’t think multiple installs was much of an an issue - but I’ve got 8 instances of the ThingSpeak logger, so I can see where this change might help. I’ll check out the change later on (but I just had oral surgery today, so I’m not changing any code for a few days).

OK, no problem. I belive if you did update to my code you old have to reconfigure your setup.

I’ve merged it. I won’t be able to test it for a few days.

Consider making an update to the doc (README.md).

Hi BostonMike and anyone else interested,

Here’s how to get formats to propagate in the Google Sheets log. (So that formats you impose on columns are used by new rows that the Logging script appends.) Script expert Riël Notermans of the Google Apps Scripting (GAS) community saw the problem and then re-coded the entire Sheets script, condensing it.

[Edit: I don’t recommend using that re-coded code unless you want to grapple with problems in it; see message 197 below. Instead, just do the specific fixes mentioned next, if all you want to do is fix the formatting issue.]

The root problem is that numbers and dates are being handled as strings. Recasting the variables solves the problem for numbers (so the sheet remembers decimal points, etc.) but does not (yet) fix it for dates. First, change:

      newRow[x]=request.parameter[i];

to

      var currentpar = request.parameter[i];
      isNaN(currentpar) ? newRow[x] = currentpar : newRow[x] = Number(currentpar);

Now, both your numbers and dates are no longer strings. Number formats will propagate fine, already.

Then for dates: Google apparently imposes particular date formats, perhaps at the point when you AppendRow. I’m not sure what’s happening here… maybe it’s imposing your localized date-time format? But, no matter… because Riël made them numeric, they are now amenable to a final simple Copy Format command, AFTER the row is appended (thanks to G+'s Sebastian Corona Fernandez for catching this).

So, after:

sheet.appendRow(newRow);

put

sheet.getRange(sheet.getLastRow()-1, 1, 1, sheet.getLastColumn()).copyFormatToRange(sheet, 1, sheet.getLastColumn(), sheet.getLastRow(), sheet.getLastRow());  // New from SEBASTIAN CORONA FERNANDEZ 11/8/16 in Sheet chat (!)

After these simple revisions, you can freely change formats for numbers or dates in the Sheet, and new rows will continue to use those formats.

Strictly speaking, it uses the formatting of the last row (before you appended the new one). Not the column, per se. Which is to say, if you have a very long sheet, early parts can be in one format. Later parts (or new data) can be in a different one. Only the format of the last row is propagated.

Easy, eh?

I, for one, want my timestamps to use two-digit month, day, and hour numbers, so they all align when scanning the data. Also I want numbers to have the number of decimals consistent with the data’s precision, and not be ragged because sometimes there’s a zero in the last decimal place.

Netizens,

I don’t know Github… If one of you who knows it could do us all a favor - first, verify that this works for you. Then incorporate it in the latest version of the Logging code. I can’t imagine it can hurt anyone, and would only help folks who care about formats. You might also put in my little fix for the queue times in message 188.

Thanks again for this great code! And community!!