Google sheets cell reference out of range on getActiveCell()

Question

I have a google sheet that checks which cell is being changed, and if it falls in the correct range, then it updates the date in the last column out. The code looks like this:

function onEdit() {
var firstProductCol = 6;
var lastProductCol = 30;

var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Skills Map" ) { //checks that we're on the correct sheet
  var activeCell = s.getActiveCell();
  Logger.log("activeCell: " + activeCell.getA1Notation());

  var seName = activeCell.offset(0, 3 - activeCell.getColumn()).getValue();
  Logger.log('value of B: [' + seName + "]");
}

And the error suggests it's on Line 7 which is just a getActiveCell... so I'm not sure how that can cause an error to be thrown given that if there isn't an active cell, you can't be changing the value of the field.

When I run the script myself, I never run into the error. The problem is every once in a while, when others use the sheet (I don't know what they do in particular), I receive an end of day report on this error...

Using the debugging tools in Script Editor, I can only see the logs from the last run... although there is a View -> Console Logs, but that doesn't seem to store any of the logs from previous runs... any way to do that?

Any suggestions on the error itself? Cheers.

Rubén Rubén · Accepted Answer · 2016-03-30T13:23:42

Looks that the line number 7 is

var seName = activeCell.offset(0, 3 - activeCell.getColumn()).getValue();

Since column number should be a positive numbers it's very likely that the error occurs when the user edit a cell other than the columns A, B or C.

One way to handle this is to check that the value that returns activeCell.getColumn() is less than 3.

Google sheets cell reference out of range on getActiveCell()

1 Answers