CODEWITHSUNDEEP
google-apps-script
kulekhani
kulekhani

Reputation: 15

I keep on getting this error: Cannot read property 'getRange' of null

when the cell value that the app script reads is empty, its showing the error in spite of making a check whether or not the cell is empty. It works fine for couple of sheets whose cell values are empty but it got stuck after iterating to 32nd sheets. Help please.

https://docs.google.com/spreadsheets/d/18r55S9sNoQwWzLyWm0SQsTltwtoXQP-KW8OZ5msNgKc/edit?usp=sharing

var II=2

function LoopForData(shTemp,codeNo,shMerchant) {
  var ss=SpreadsheetApp.getActive();

  // for loop for each tab
  var temp=ss.getSheetByName(shTemp);
  
  // get last row for each sheet
  var endrow=8;
  while(temp.getRange(endrow,2).isBlank()==false){
    endrow++;
  }

  if(endrow==8){
    return
  }


  // writing codeNo and name once for each sheet
  shMerchant.getRange(II,1).setValue(codeNo)
  shMerchant.getRange(II,2).setValue(temp.getName())
  II++;



  // writing values to summary sheet
  for(var i=8; i<=endrow-1; i++){
    shMerchant.getRange(II,3).setValue(temp.getRange(i,2).getValue())
    shMerchant.getRange(II,4).setValue(temp.getRange(i,3).getValue())
    shMerchant.getRange(II,5).setValue(temp.getRange(i,4).getValue())
    shMerchant.getRange(II,6).setValue(temp.getRange(i,5).getValue())
    II++;
  }
}



function LoopForSheets(){
  var ss = SpreadsheetApp.getActive();

  //define list sheet and Destination Sheet
  var shList = ss.getSheetByName("Main")
  var shMerchant = ss.getSheetByName("M")

  shMerchant.clear
  
  // Write headers
  shMerchant.getRange(1,1).setValue("codeNo")
  shMerchant.getRange(1,2).setValue("NAME")
  shMerchant.getRange(1,3).setValue("INVOICE DATE")
  shMerchant.getRange(1,4).setValue("INVOICE NUM")
  shMerchant.getRange(1,5).setValue("MERCHANDISE")
  shMerchant.getRange(1,6).setValue("PAID")

  var j=2
  while(shList.getRange(j,2).getValue()!=""){
    var shTemp = shList.getRange(j,2).getValue(); 
    var codeNo = shList.getRange(j,1).getValue();   
    LoopForData(shTemp,codeNo,shMerchant)
    j++;
  }

}

Upvotes: 0

Views: 944

Answers (1)

doubleunary
doubleunary

Reputation: 18784

The Spreadsheet.getSheetByName() method returns null when there is no sheet by the name given as argument. Chances are that you are at some point calling LoopForData() with an shTemp or shMerchant value that does not match any of the sheet names in the spreadsheet.

You can use console.log(arguments) as the first thing in the function to see what values those two parameters take.

Upvotes: 1

Related Questions