Range is not defined error

Question

Here is the script in Google Sheets I am trying to run...

Can you please give some advice on this error?

function myFunction() {
//IF target range D23 is edited...
//
  if (range.getRow()!=23 || range.getColumn()!=4) return;
//Set B23 to current date and time
//
  range.offset(0,-2,1,1).setValue(new Date());
//Clear range B37:T37
//
  range.offset(14,-2,1,19).clearContent();//<<<not really necessary
//Copy range B23:T36
//Paste to range B24:T37
//
  range.offset(0,-2,14,19).copyTo(range.offset(1,0));
//Clear range B23:T23
  range.offset(0,0,1,19).clearContent();
}

So this looks like an onEdit function but the event parameter has not been included. The range is e.range. — Cooper, Apr 10 '18 at 01:49

score 1 · Answer 1 · answered Apr 10 '18 at 07:27

In this line

if (range.getRow()!=23 || range.getColumn()!=4) return;

You're trying to fetch the row and column from range variable, but you have not defined that.

So that's why it's throwing that error.

You must define range before trying to use it.

If you're running this function based on some trigger then maybe you can get the range from the event object like this

function functionName(eventObject) {
    var range = eventObject.range;
    //Your other code follows below
}

Otherwise, you can also use : getRange()

References :

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column

https://developers.google.com/apps-script/guides/triggers/events

score 0 · Answer 2 · answered Apr 10 '18 at 01:52

Perhaps your function should look something like this:

function myFunction(e) {
  if (e.range.getRow()!=23 || e.range.getColumn()!=4) return;
  e.range.offset(0,-2,1,1).setValue(new Date());
  e.range.offset(14,-2,1,19).clearContent();
  e.range.offset(0,-2,14,19).copyTo(e.range.offset(1,0));
  e.range.offset(0,0,1,19).clearContent();
}

Range is not defined error

2 Answers2