What is causing Cannot call method "getRange" of null

Question

I'm attempting to split data in a sheet (MASTERS) by the data in column E. The data is a number. I have the destination sheets set up in the same workbook which are named correctly, (eg. 3, 4, 5 etc) but every time I run I am getting the error

TypeError: Cannot call method "getRange" of null. (line 12, file "02. split by column E"

var ss=SpreadsheetApp.getActiveSpreadsheet();
var master = ss.getSheetByName('MASTER');
var colWidth = master.getMaxColumns();    

function copyRowsOnCondition() {
  var data = master.getDataRange().getValues();
  for(n=0;n<data.length;++n){
    if(data[n][4].length<16){ 
    Logger.log(data[n][4])
     var dest = ss.getSheetByName(data[n][4].toString().replace(/ /g,''));
     var destRange = dest.getRange(dest.getLastRow()+1,1);
     master.getRange(n+1,1,1,colWidth).copyTo(destRange); 
     }
  }// loop
}

What is the dest sheet name? You are logging data[n][4] but setting the dest as data[n][4].toString().replace(/ /g,'') so you may be seeing a value in the log that is different than the value you are actually trying to get which would cause an error. The error is saying that your sheet by that name does not exist — random-parts
the names of the destination sheets are numbers - eg from 1 to 10, which correlate to the runs of data in column E of the master sheet. I've checked that data in column 4 is plain text but it does seem as though it's reading the values differently. Strangely I've lifted this script from another worksheet (with a different column layout) were it was working fine. — simon smith
what is the code at line 12? and double check that there is a sheet named MASTER. the error has to do with there not being a sheet object to call getRange on — random-parts
this is the offending line: var destRange = dest.getRange(dest.getLastRow()+1,1); — simon smith

Antoine Colson Antoine Colson · Accepted Answer · 2017-12-12T04:50:22

In at least 1 case, no sheet is named whatever this returns: data[n][4].toString().replace(/ /g,'')

After line 9 (after your Logger.log(data[n][4])), you could remove everything and write:

var str = data[n][4].toString().replace(/ /g,'');
var dest = ss.getSheetByName(str)
if (dest) {
  Logger.log(str + " is an actual sheet's name");
  var destRange = dest.getRange(dest.getLastRow()+1,1);
  master.getRange(n+1,1,1,colWidth).copyTo(destRange); 
} else { 
  Logger.log(str + " is not an actual sheet's name.")
}
}// loop
}

The logs should now be helpful to find out what is wrong.

What is causing Cannot call method "getRange" of null

1 Answers