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?
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!!
Didn’t seem to work well for me…
When running the test with ?Temp1=15&Temp2=30 after my URL, I first got this:
ReferenceError: "TYPES" is not defined. (line 30, file "Code", project "Log Smartthings Power")
I added the section:
var TYPES = {
"Time": "date",
"LastTime": "date",
// "AMS8 motion": "string"
}
now the line looks like this in the sheet
Time Temp2 Temp1
NOT_FOUND 30 15
Didn’t do much more troubleshooting yet…
Hi Jacques, I guess I should have clarified, I was only recommending doing the changes I wrote in my message (changes for the existing Logging code at Github), but I still provided the re-written code, in case someone wanted to see what Riel did.
If you are indeed trying to run Riel’s new re-write, you can replace the test data in its testMe routine. “Time” is the name for the timestamp variable that the existing Logging code outputs. Just ask if you need more help here.
I actually found two “operational” problems with Riel’s rewrite but my coding skill is so low that I did not want to bother him for such small things, when I did get the actual formatting problem itself solved. Riel doesn’t know SmartThings; he just re-wrote the code for the test data you see there. I call them “operational” because he couldn’t test/see the whole picture of how the Sheets script interacts with new SmartThings app data packets.
First, as you noted, if there is no data in a new data packet for an existing column (“Time”, in your example), it puts an obnoxious NOT_FOUND message in any such columns. This includes a Note field and additional calculation fields I made in my Sheet. In my sheet, I have about 80 columns, but only about 15-20 datapoints are output in any one 10-minute packet. So, all the NOT_FOUNDs are unacceptable.
(And yes, that Types clause will throw a kink in, if it mentions variables not in your data. You would simply want to delete any fields from the Types clause that are not in your data, for testing. But you do need to have any Date types defined there, if any of your variables are timestamps. Like Time, in the existing Github Logging code.)
Second, Riel thought that string data must be rare because I only had that one “inactive” datapoint in my test data. But actually there can be plenty of string data, and more added if you put in a new sensor. So I would have liked to have asked Riel to rewrite the code so that users only had to specify the name of timestamp fields, and then have the code itself categorize numbers versus strings, past that.
Timestamps are rare; there’s only one in the existing Logging code (Time). And if anyone put more (like me), it doesn’t happen often. So it’s not a big deal to explicitly tell the code which columns are of type Date, so you don’t have to write a lot of code to figure it out. Past that, I would think the code could easily tell numbers from strings. Using something maybe like:
if (TYPES[key] = 'date') {
//convert to date object
var d = value.split(" "); //creates ["mm/dd/yy","hh:mm:ss"]
var dp = d[0].split("/"); //creates ["mm","dd","yy"]
var dt = d[1].split(":"); //creates ["hh","mm","ss"]
value = new Date("20"+dp[2],dp[0]-1,dp[1],dt[0],dt[1],dt[2]);
break;
} else {
isNaN(value) ? value = new String(value) : value = new Number(value);
break;
}
But I never could get it this to work right. I am a real novice with javascript, there’s probably a simple mistake.
Be all that as it may,
Because I could not figure out the apparently easy second problem, and I had zero clue how to fix the first one… but I had figured out how to fix the formatting issue, which was the cause of it all in the first place… therefore, I just posted the straightforward fixes that solved the formatting issue. And I mentioned Riel’s re-write on the off-chance someone else might want to see what he did. Very sorry if I wasted your time. I updated message 195 so others won’t be tripped up by it.
But if that code, or mine above, does help anyone do a re-write, that would be great. Riel certainly did seem to simplify what had been there before.
Jacques, I can try to help you to get Riel’s new code to work, but unless you’re better than me, it would probably be blind leading the blind. However, I think you should be able to get the very specific fixes I mentioned in message 195 to work. Stick them into your existing Github Logging code at the places indicated.
On the plus side, I might point out that Riel’s code showed me the incalculable programming aid, of how to put a test of the Sheets code, right into my own code. So I can test changes to Sheets code at will on the fly, without having to wait for my next actual SmartThings data packet (and quite possibly screw it up, since I also have to use it for testing). If anyone else wants to see how to make their own little “in house” Sheets logging script debug testing, look at Riel’s code. I’m sure it’s all quite simple to a veteran javascript coder. But it was a real breakthrough for me (cough). hehe
Let me know if I can help more.
Oh, my bad… I’ve tried your suggestion and my sheet now has the following code… seems to keep formatting on cells correctly.
function doGet(request) {
if(request != null) {
var ss = SpreadsheetApp.openById("1h7bh7asIxvlbedsgfdsgdsbREByQm9bePi73A76FWz7_3M");
var sheet = ss.getSheetByName("Raw Values");
var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var sheetHeaders=firstRowRange.getValues();
// columns start at one, but arrays start at 0, so this is the right number for inserting into an array that will be placed into the columns
var newColumnArrayCount=sheet.getLastColumn();
// initialize new row to be inserted before it gets filled with data
var newRow = new Array(sheetHeaders[0].length);
for(var x=0;x<newColumnArrayCount;x++) newRow[x]="";
for (var i in request.parameters) {
var foundRow=false;
for(var x=0;x<sheetHeaders[0].length;x++) {
if(i.toString().toLowerCase()==sheetHeaders[0][x].toString().toLowerCase()) {
var currentpar = request.parameter[i];
isNaN(currentpar) ? newRow[x] = currentpar : newRow[x] = Number(currentpar);
foundRow=true;
break;
}
}
if(foundRow==false) {
if(sheet.getLastColumn()==sheet.getMaxColumns()) {
sheet.insertColumnAfter(sheet.getLastColumn());
}
sheetHeaders[0][newColumnArrayCount]=i;
newRow[newColumnArrayCount]=request.parameter[i];
firstRowRange=sheet.getRange(1, 1, 1, sheet.getLastColumn()+1);
firstRowRange.setValues(sheetHeaders);
firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
sheetHeaders=firstRowRange.getValues();
newColumnArrayCount++;
}
}
if(newRow[0]=="") {
Logger.log("setting date");
newRow[0]=new Date();
}
// Appends a new row to the bottom of the
// spreadsheet containing the values in the array
sheet.appendRow(newRow);
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 (!)
}
}
That’s how you intend to update it in github?