user1664305 user1664305 - 1 year ago 58
Javascript Question

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

var selectedfirstRow = ss.getRange(editRange).getRowIndex();
var selectedHeigth = ss.getRange(editRange).getHeight()
var selectedFullRange = master.getRange(selectedfirstRow,1,selectedHeigth,1);
var data = selectedFullRange.getValues();
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:

// Do nothing

// Do something

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


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:

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

Answer Source

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 "".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download