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

(Jacques Aucamp) #196

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

Didn't do much more troubleshooting yet...

(Mike) #197

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]);

  } else {

      isNaN(value) ? value = new String(value) : value = new Number(value);


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.

(Jacques Aucamp) #198

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);
      if(foundRow==false) {
        if(sheet.getLastColumn()==sheet.getMaxColumns()) {
        firstRowRange=sheet.getRange(1, 1, 1, sheet.getLastColumn()+1);
        firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());

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

(John) #199

@RedKnight @Jacques_Aucamp Is this the correct change?

[RELEASE] Simple Event Logger
(Mike) #200

It looks good to me John, thanks!

(Mike) #201

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.

(John) #202


(Kevin) #203

Mike - Thanks for posting your setup! I couldn't get the copy to work but will take another stab at it later.

(Mike) #204

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


function onOpen() {

  var ui = SpreadsheetApp.getUi();
      .addItem('BottomRow', 'menuItem1')


function menuItem1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  SpreadsheetApp.setActiveRange(sheet.getRange(sheet.getLastRow(), sheet.getActiveCell().getColumnIndex()));

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

(JTB) #205

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?

(JTB) #206

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

(John) #207

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.

(Mike) #208

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.

(JTB) #209

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 :wink:

(Mike) #210

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. :slight_smile:

(Kevin) #211

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.

(Mike) #212

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

(Jacques Aucamp) #213

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??

(Kevin) #214

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?

(Jacques Aucamp) #215

Thanks, I'll try that and feedback. Yes I made some edits to the script. (additional functions)