Search code examples
javascriptarraysgoogle-apps-scriptgoogle-sheetsfencepost

Remove un-wanted delimiters from concatenation function google script


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);
}

Solution

  • 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);
    }