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

Don't you just want to use `join` (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/join) rather than implementing it yourself? — JawguyChooser, Jan 07 '18 at 19:52
Yes that would be `great` but how to join two columns, not just a list, this would also still leave me with `, ` when both cells are empty I think — xyz, Jan 07 '18 at 20:01
See my answer below: the trick is to remove empty strings before you call `join`. Then `[].join(', ')` just returns an empty string `""`. — JawguyChooser, Jan 07 '18 at 20:25
@JawguyChooser [tag:fencepost] has't a tag excerpt/wiki. How this tag helps this question? — Rubén, Jan 07 '18 at 23:41
@Rubén I'm new to the tagging system, but I assumed that the fencepost tag referred to the fencepost problem in computer science (https://en.wiktionary.org/wiki/fencepost_problem). If that's not correct, we can remove the tag. — JawguyChooser, Jan 08 '18 at 00:06
@JawguyChooser : Since there isn't a tag excerpt/wiki there is no way to say that it's correct to include it here by the other hand "fencepost" or the fencepost problem are not explicitly mentioned on this Q&A. I don't know if there are more tags for well known computer science problems but this could be *more* helpful if the tag include an excerpt/wiki :) — Rubén, Jan 08 '18 at 00:11

JawguyChooser · Answer 1 · 2018-01-08T03:48:25.837

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.

JawguyChooser, thanks, I tried to implement this probably not doing it correctly as I am not sure how to combine two columns to get `vals` tried `vals = sheet.getRange(2, 1, lr,2).getValues();` colA & colB. Then for post back have `sheet.getRange(2, 3, lr, 1).setValues([vals]);` but I get error `Incorrect range height, was 1 but should be 190 ` — xyz, Jan 08 '18 at 01:45
@Tim I was just trying to create a minimal example without having to wade into the google spreadsheet land. From what I could tell, your `values` had exactly the same structure as what I wrote in `vals`. Your original code should work with my solution. Just define the `filterEmpties` function, then and say `values` where I said `vals` in your for loop. — JawguyChooser, Jan 08 '18 at 03:34
@TIm my solution is essentially the same as what @Ed Nelson suggested, but I use built in functions like `join` and `reduce` in order to make your code simpler and cleaner. — JawguyChooser, Jan 08 '18 at 03:35

score 1 · Accepted Answer · answered Jan 07 '18 at 20:41

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

ScampMichael · Answer 3 · 2018-01-07T23:52:25.667

1

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]];

edited Jan 07 '18 at 23:52

answered Jan 07 '18 at 21:03

ScampMichael

3,688
2
16
23

ScampMichae, thanks, this works great also, can't quite follow it so will study it – xyz Jan 08 '18 at 01:40

Remove un-wanted delimiters from concatenation function google script

3 Answers3