Remove un-wanted delimiters from concatenation function google script

Question

I am trying to concatenate two columns delimited by , and post back the results to a third column

I am getting:

colA     colb      concatenated
aa bb    ww ww     aa bb, ww ww
mm                 m, 
         qq         ,qq
zz oo              zz oo, 
                   ,
ss       vv zz     ss, vv zz

how to remove the un-wanted delimiters and spaces so I get:

colA     colb      concatenated
aa bb    ww ww     aa bb, ww ww
mm                 mm
         qq        qq
zz oo              zz oo

ss       vv zz     ss, vv zz

If it is helpful here is a Google sheet with some data https://docs.google.com/spreadsheets/d/12Hn9bVy5GmRTVxMcrZ_bdkVSlfrem-Jr4cyev_gg6BE/edit?usp=sharing

Thanks

function ConCat() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Elements");
var lc = sheet.getLastColumn();
var lr = sheet.getLastRow();
var values = sheet.getRange(2, 1, lr,lc).getValues();
var result = [];                                      //Create a empty array to be filled concatenated elements

//Add items to results
for(var i=0; i<lr; i++){
   result[i] = [values[i][0]+", "+values[i][1]];
}

//Post back to column 3 starting on row 2
sheet.getRange(2, 3, lr, 1).setValues(result);
}

Answer 1

I'm skipping the google spreadsheet stuff because I don't think it's actually relevant to answering your question.

It looks like values contains your items to be joined. I renamed it to vals (since values is already a bound function) and just tested this in the javascript console of firefox. If vals contains empty strings (like this):

vals = [ ['aa bb', 'ww ww'], ['mm', ''], ['', 'qq'], ['zz oo', ''], 
         ['', ''], ['ss', 'vv zz'] ];

Then I can reproduce your problem using join (which is simpler to read than your manually implemented version):

>> for (var i=0; i<vals.length; i++)  
    console.log(vals[i].join(','))
aa bb,ww ww 
mm, 
,qq 
zz oo, 
, 
ss,vv zz

So, really you just want to filter your list of empty strings before joining:

>> filterEmpties = function(acc, el) {
       if(el != "")
           acc.push(el);
       return acc;
   }
>> for (var i=0; i<vals.length; i++){
    console.log(
        vals[i].reduce(filterEmpties, []).join(', '))
   }
aa bb, ww ww
mm
qq
zz oo

ss, vv zz

And I think that's basically what you were trying to achieve, right? I used Array.prototype.reduce to remove the empty elements before using join.

Cheers!

EDIT:

It seems like my usage of the console caused some confusion in implementing my answer. I'll integrate my solution with your code to help you see what I meant (I fixed your indentation ;)):

var filterEmpties = function(acc, el) {
    if(el != "")
        acc.push(el);
    return acc;
}

function ConCat() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet()
        .getSheetByName("Elements");
    var lc = sheet.getLastColumn();
    var lr = sheet.getLastRow();
    var values = sheet.getRange(2, 1, lr,lc).getValues();
    var result = [];  // Create a empty array to be filled concatenated elements

    // Add items to results
    for(var i=0; i<values.length; i++){
        result[i] = values.reduce(filterEmpties, []).join(', ');
    }          
    // Post back to column 3 starting on row 2
    sheet.getRange(2, 3, lr, 1).setValues(result);
}

And that's all you should have to do, assuming I understood your input correctly. Hope this helps.

Answer 2

result[i] = [!values[i][0]? 
    !values[i][1]?"":values[i][1]
  :!values[i][1]? values[i][0]:
    values[i][0] + ", " + values[i][1]];

Instead of: result[i] = [values[i][0]+", "+values[i][1]];

Answer 3

Try this:

function ConCat() {
var sheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Elements");
var lc = sheet.getLastColumn();
var lr = sheet.getLastRow();
var values = sheet.getRange(2, 1, lr,lc).getValues();
var result = [];                                      //Create a empty array to be filled concatenated elements

//Add items to results
for(var i=0; i<lr; i++){
 if(values[i][0]!='' && values[i][1]!=''){
    result[i] = [values[i][0]+", "+values[i][1]];
}
  else if(values[i][0]!='' && values[i][1]==''){
    result[i] = [values[i][0]]
}
else if(values[i][0]=='' && values[i][1]==''){
    result[i] = [values[i][1]]
}  
 else{                 
   result[i]=['']
}
}  
//Post back to column 3 starting on row 2
sheet.getRange(2, 3, lr, 1).setValues(result);
}