CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
xyz
xyz

Reputation: 2300

Remove row if cell is empty from return

I have a function (sorry I do not recall where I got it), it outputs a delimited string to a grouped row

I need to remove all rows where the target column is empty

I have been tring but cannot get it

Thanks for any help

Range

ColA      ColB    ColC   
a1        b1      x\y\z
a2        b2      
a3        b3      x|z

output

ColA      ColB    ColC   
a1        b1      x
a1        b1      y
a1        b1      z
a2        b2      
a3        b3      x
a3        b3      z

Need

ColA      ColB    ColC   
a1        b1      x
a1        b1      y
a1        b1      z    
a3        b3      x
a3        b3      z


function groupedRow(range) {
var targetCol = 4
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
  var s = range[i][targetCol].split("|");    
  for(var j=0, jLen=s.length; j<jLen; j++) {
    var output1 = []; 
    for(var k=0, kLen=range[0].length; k<kLen; k++) {
      if(k == targetCol ) {
        output1.push(s[j]);
      } else {
        output1.push(range[i][targetCol]);
      }
  }
    output2.push(output1);
  }    
}
return output2;
}

Upvotes: 0

Views: 66

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

You may have to check, where the split function has an empty string as an argument, it returns the array with the empty string:

['']

I had to rebuild the code in order to get the proper result.

Code

function groupedRow(range, targetCol) {
  var result = [];
  var row = [];
  var rowNew = [];
  var val = '';

  for(var i=0, iLen=range.length; i<iLen; i++) {
      // get the row
      row = range[i];
      // split the targetCol element
      var s = row[targetCol - 1].split("|");  
      for(var j=0, jLen=s.length; j<jLen; j++) {
        val = s[j]; 
        // if element is not empty string   
        if (val !== '')
        {
          rowNew = row.slice(); // copy the array
          rowNew.splice(targetCol - 1, 1, val); // replace n'th element 
          result.push(rowNew);
        }
      }    
    }
    return result;
    }

Usage

As custom function from the sheet:

=groupedRow(A1:C3, 3)

Notes

Upvotes: 1

Related Questions