CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user1664305
user1664305

Reputation: 179

If cell is empty, skip and go to the next

I am working with some code that will take a predefined range and copy data to a sheet with the same name as in column A. The issue that I am running into is if Column A has a blank cell, which is needed, then I get an error in which it reports back saying "Cannot call method "getRange" of null." If there is data in that cell then it works perfectly.

Some things that I have tried include filtering the range, however, the code still reads the range even though it is hidden. I have also tried writing an if/then statement but was not successful.

I have a sample Google Sheet ready to go here to illustrate the blank space.

Here's a screenshot of the blank cell I am referring to:

Screenshot of the blank cell I am referring to.

Code in use:

var ss=SpreadsheetApp.getActiveSpreadsheet();
var master = ss.getSheetByName('Home');
var colWidth = master.getLastColumn();// last used col in masterSheet
var sheets = ss.getSheets();// number of sheets
var editRange = 'B2:B15';


function onOpen() {
  var menuEntries = [ {name: "Copy selected Rows to sheets", functionName: "copyRowsOnConditionV2"},
                     ];
  ss.addMenu("Copy functions",menuEntries);// custom menu
}

function copyRowsOnConditionV2() {
  var sheetNames = [];// array of existing sheet names
  var sheets = ss.getSheets();// number of sheets
  for(s=0;s<sheets.length;++s){sheetNames.push(sheets[s].getName())};

  var selectedfirstRow = ss.getRange(editRange).getRowIndex();
  var selectedHeigth = ss.getRange(editRange).getHeight()
  var selectedFullRange = master.getRange(selectedfirstRow,1,selectedHeigth,1);
  var data = selectedFullRange.getValues();
  for(n=0;n<data.length;++n){
    if(data[n][0].length<16){
     var dest = ss.getSheetByName(data[n][0].toString().replace(/ /g,''));//find the destination sheet
     Logger.log(data[n][0].toString().replace(/ /g,''))
     var destRange = dest.getRange(dest.getLastRow()+1,1);// define range
     master.getRange(selectedfirstRow+n,2,1,1).copyTo(destRange);// and make copy below last row
    }
  }
}

I guess what I am looking for is some sort of "If cell is blank then go to next cell" sort of code.

What I was thinking would work:

If(data[n][0].length==""{
// Do nothing

if(data[n][0].length<16){
// Do something
}
}

Any ideas on how I can get something like this to work?

Edit

I found that the original IF statement I posted just above was not in the correct syntax. I have since corrected it, however, still get the same error.

New If Else if Statement:

if(data[n][0].length=0){
//do nothing 
}
else if(data[n][0].length<1){
//do something
}

Upvotes: 0

Views: 5700

Answers (2)

Anton Dementiev
Anton Dementiev

Reputation: 5706

Just use 

    if (data[n][0]) {

     // do something;


     }

In the 'if' operator, the statement between the brackets will be evaluated as 'false' if the value is null, undefined or an empty string "".

Upvotes: 1

Alan Wells
Alan Wells

Reputation: 31300

The JavaScript "not" operator will test a value for null, undefined and an empty string, and return true if it finds any of those conditions. The "not" operator is an exclamation point.

if(!data[n][0]){
  Logger.log('tested true for NOT')
  continue;//execute the next loop
}

Upvotes: 1

Related Questions