Google Sheets Scripts onOpen(e) Add New Row to Sheet

Question

I have a custom function that inserts a new row under the last non-empty row in a sheet and copies a specific range to this new row. This functions works fine when I trigger it with a custom menu item.

However, when I add this custom function to the onOpen(e) trigger the script does not work. I troubleshooted and added an alert in my function - the alert triggers so therefore the function is read in the right place.

Please advise how to have my script work onOpen?

Here's my code for my custom function:

function onOpen(e) {
  autoAddRow();
}

function autoAddRow() { 
  var updateFrequency = 3;
  var lRow = getLastRow('A'); 
  var lDate = all.getRange(lRow,1).getValue();  
  var today = new Date();

  Date.prototype.addDays = function(days) {
    var dat = new Date(this.valueOf());
    dat.setDate(dat.getDate() + days);
    return dat;
  }
  var newDate = lDate.addDays(updateFrequency);

  if (today >= newDate) {
    var lCol = all.getLastColumn();
    var src = all.getRange(3,1,1,6);
    var dest = all.getRange(lRow+1,1,1,6);

    if(lRow > 20) {
      all.insertRowsAfter(lRow, 1);
    }

    src.copyTo(dest, {contentsOnly:false});
  } else {
    return;
  }
}

Open the spreadsheet, then go into the script editor and use View > Logs to see what's happening during execution. Might be an invalid range or something called which would halt the execution. — Brian
Run your code in the in the script editor. It will identify lots of errors that you can work on. — Cooper

Randy D Randy D · Accepted Answer · 2017-11-17T23:04:05

So if your example isn't psuedo-code, it wont work. It dies at the second line of your function because the method isn't attached to a class.

Here is my version of your code (I'm using the first sheet in the spreadsheet app).

function autoAddRow() { 
  var updateFrequency = 3

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var data = sheet.getDataRange();  
  var lRow = data.getLastRow();
  var lDate = new Date(sheet.getRange(lRow,1).getValue());
  var today = new Date();
  Logger.log(lDate);
  Logger.log(lRow);
  Logger.log(sheet.getRange(lRow,1).getValue());

  Date.prototype.addDays = function(days) {
    var dat = new Date(this.valueOf());
    dat.setDate(dat.getDate() + days);
    return dat;
  }
  var newDate = lDate.addDays(updateFrequency);

  if (today >= newDate) {
    Logger.log("passed if statement");
    var lCol = data.getLastColumn();
    var src = sheet.getRange(lRow,1,1,6);
    var dest = sheet.getRange(lRow+1,1,1,6);

    if(lRow > 20) {
      all.insertRowsAfter(lRow, 1);
    }

    src.copyTo(dest, {contentsOnly:false});
  } else {
    return;
  }
}

The main problems were in the calls to the range and data. They didn't go anywhere. The variable lDate needed to be parsed as a date (in my version, my date column was the second column, not the first *edit: changed the code quick to point to your original date cell *end edit), but you have to call the range and get the value to parse it, but after fixing the class issues with regards to getting your data, the rest fell into place. It even works if you put in in the onOpen.

Update: I didn't fix the line

if(lRow > 20) {
  all.insertRowsAfter(lRow, 1);
}

In fact, I'm not really sure what it does because it looks like it just inserts the row after the last row, which the rest of the function does. But the code 'as is' will break after 20 lines unless you change 'all' to reference the worksheet object.

Google Sheets Scripts onOpen(e) Add New Row to Sheet

1 Answers