@RedKnight @Jacques_Aucamp Is this the correct change?
It looks good to me John, thanks!
If anyone is interested, hereās how to copy information from the last row, to a new row. This is useful for, e.g., continuing a row of ongoing calculations.
Put this after your appendRow statement. This example copies from your next-to-last row (after you just appended your new one), only copies one row (all we need), and for my data, starts with column 3 and copies 5 columns (so it copies the cells of columns C to G to the last row):
sheet.getRange(sheet.getLastRow()-1, 3, 1, 5).copyTo(sheet.getRange(sheet.getLastRow(), 3));
In my sheet, I do math to show time differences for successive rows in columns C to G, and conditional formatting for anything significantly shorter (yellow) or longer (red) than expected. In the screencap, the highlighted cells are where they recently upgraded the cloud servers and they were down for an hour, 10 Nov. 3-4 am Eastern time:
.
Thatās about it. Only continue reading if you want more details on my setupā¦
My Logging app is set to output records every 10 minutes.
I currently have 90 columns, 79 of which are from sensors. I have 9 Aeon gen5 Multisensor 6s called (āAMSā 0 - 8 in the sheet), 5 Aeon Smart Energy Switches (AES0-4), and other stuff. The other 11 columns are timestamps, notes, and calculations.
Now you see why I was so concerned about formatting issues.
The N column shows the number of sensor datapoints on a given row. The count is usually lower at nightā¦ most of my devices are sensors, and temperatures, humidity, light, and motion change less or more slowly at night than the day.
The LastTime column is mentioned in message 176 and message 181 and lets me see how āwideā the data in the row actually is. (Does the data in a row really encompass 10 minutes? No, usually a little less than that; see āLastTime - Timeā column.)
Also, if there is a large Time gap between rows, does the data in a row cover a wide period, or was most of the reporting time lost? You canāt tell with the vanilla code (except to assume it is doing what it usually does, wait up to two times the queue time, then force a row). But with LastTime, you can tell what happened with certainty.
Youāll also notice the occasional irregularity in what Time is used for, as seen at Row 1302. Two rows had the exact same Time. The code does this sometimes; I forget why. LastTime helps show what happened.
Edit, added: If you want to see how I shortened column headers like ātemperatureā to ātempā, see message 174. The data is using Arial 10 point, but the column headers are PT Sans Narrow 12 point.
One of these days Iād like to hire (pay) someone to revise the Log code to show more about time intervals involved (see messages 176 and 181). The revisions would be freely posted to Github or here, as usual. If anyone is interested, please PM me.
Mergedā¦
1 Like
Mike - Thanks for posting your setup! I couldnāt get the copy to work but will take another stab at it later.
If you want your Sheet to automatically move to the end of your Log when you open it, and also make a Menu item that also moves to the bottom (so you donāt have to switch from mouse to keyboard if you check it from time to time after tabbing to your Sheet),
At the beginning of the Code.gs for your Logging sheet, before
function doGet(request) {
Put:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('BottomRow')
.addItem('BottomRow', 'menuItem1')
.addToUi();
menuItem1()
}
function menuItem1() {
var sheet = SpreadsheetApp.getActiveSheet();
SpreadsheetApp.setActiveRange(sheet.getRange(sheet.getLastRow(), sheet.getActiveCell().getColumnIndex()));
return;
}
For more help, see https://developers.google.com/apps-script/guides/menus
I tried to find a way to have the Log sheet zoom to the last row any time I moved to its tab, because my normal mode is to check it once or twice a day (when a bunch of new rows have scrolled off the bottom of what was visible when it was opened or last viewed). But I couldnāt find any kind of āonActiveā method (or whatever), nor did anyone on Reddit seem to know of one. This onOpen-plus-menu approach is the best I could come up with.
Edit 11/26/16: Changed code so it zooms to last row, current column instead of last row, column 1
This is great stuff! However I have a problem using the timeline chart. It work the after the first few loggings, but then I get a message saying:
Any idea what to do?
My problem seems to be related to formatting of the data input in the sheet. It interpetes the values from sensors like dates, and not numbers. I tried changing the formatting, but with no luck. Might be related to the problem with the difference in 4,6 and 4.6, but not sureā¦
As I said above, the changes as suggested by @RedKnight and made by @Jacques_Aucamp were merged. This allows the spreadsheet to keep formatting of cells including newly added rows.
To get this change you need to update Code.gs attached to your spreadsheet by following step 5 of the instructions; basically, recreate the helper script.
Did you try the new code like John suggested, Joar? If it didnāt help, can you tell us what values give you an error, or take a screen shot?
Is it every single value in the column(s) of interest? If you can pin it down to specific values, it may tell you something.
Hi Mike
I got it to work, but to be honest I have noe idea how. I gave up one evening and decided to try again next morning and it worked. I tried earlier to change the sheets setting from Norway to USA, but it didnāt work immediatly. It might have been the solution, but Iām not sure. Now I donāt dare to touch anything 
I wonder if you got stuck between two standards (whatever Code.gs thought the standard was, and what your Sheet thought the standard was, when it actually gets appended)ā¦ one thinks commas mean itās not a number (or is a date), the other one expects numbers to have commas. Oh well.
Now that itās working, you could change one thing at a time. Then wait to make sure it still works. 
I have been trying to follow along, but confused still. I have not made this update yet my conditional formatting is added to new rows in the google sheet.
Hi Kevin, I mentioned conditional formatting in one of my posts simply as an example of what I was doing, but no one here has said the recent code changes are for conditional formatting.
The point of the new numeric and date formatting statements is only so that a preferred numeric or date formatting will propagate into new rows. This is not the same as conditional formatting.
Example: Some of my energy sensors are good to 3 decimal points (838.345). But if there are zeroes in the decimal places, it comes across as, e.g., 838, not 838.000. I want those decimal zeroes so that my digit places align and columns of numbers are not ragged (bouncing left to right).
Also I want my date elements to be two digit hours, minutes, days, and months, so that all those align and donāt bounce around either, when reading long columns.
These are small things but, by the same token, it should also not be a problem. It should just be what format I want.
So I found a fix for the problem.
There has never been a problem with conditional formatting (changing colors based on values).
There has also never been a problem with text justification propagating (left, right, or centered).
Thanks - Mike
1 Like
My script was working fine, but for some reason it suddenly started logging the same date every time the log is submitted to the sheet.
No idea what would be causing this.
The code in the script is:
if(newRow[0]=="") {
Logger.log("setting date");
newRow[0]=new Date();
}
I have tried republishing the script, but it had no effect.
Any advice??
Mine seems to be working okay still. If you look in the instructions for setting up sheet logging, it shows you the URL which this SmartApp uses to send the data over to Sheets. Maybe try using that URL from your browser and see if it is also creating rows with same dateā¦ that would completely rule out issues on the SmartApp side. Have you edited the Sheet script?
Thanks, Iāll try that and feedback. Yes I made some edits to the script. (additional functions)
I tried the URL and that works correctly. Seems the issue is on the smartapp side?
No idea where to go nextā¦ I have already removed the smartapp, and recreated it, but still have the same resultsā¦
Any suggestions?
BTW, i am using the laest github version of your code, unmodified.
With the smartapp, I am using the 1 minute interval.
The 5 minute interval behaves the same
I donāt have the problem, Jacque, so I donāt have a lot to add. Honestly I find it hard to imagine how it could do that. Doesnāt it get the time from a SmartThing (ST) server? You could watch your script run and take a look at what value is being populated in the SmartApp IDE environment (graph.api.smartthings.com/ide/apps) but thatās almost certain to say what you are seeing in Sheets. Either your ST server has the wrong time (which seems impossible) or the code asking for the time is not refreshing its time variable. But this latter also seems impossible if you are using the same code everybody else is.
The operable variable here is eventTime not queueTime:
if( atomicState.queue == [:] ) {
def eventTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))
addToQueue("Time", eventTime)
}
else {
def eventLastTime = URLEncoder.encode(evt.date.format( 'MM/dd/yy HH:mm:ss', location.timeZone ))
addToQueue("LastTime", eventLastTime)
}
Have you done anything weird with your location timezone by any chanceā¦ have a new PC or domain with it undefined?? Iām grasping at straws here.
Why not add my LastTime field? (See, e.g., my post 201.) You could at least have that as an alternative since itās almost the same thing, and far better than nothing. Plus you could see if LastTime is also broken (so its the ST server or app install somehow, not the code).
Wish you luck! That would be super annoying to me.