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