Search Array for String When Values have Line Breaks

Question

I have an HTML user form select and input fields whose input I need to search within a string in google sheets.

Here is what the cell looks like.

I have the following function that does a great job of searching through cells and returning values I don't want in an array that I can use with .setHiddenValues() when filtering.

function getHiddenValueArray(colValueArr,visibleValueArr){
  //colValueArr = SpreadsheetApp.getActive().getSheetByName('Monthly_Detail').getRange(2,64,359,1).getValues();    
  //visibleValueArr = ['last1']; //In this case user will only input 1 name
  //strictLevel = 'lenient'

  //will find a match within a cell
    var flatUniqArr = colValueArr.map(function(e){return e[0].toString();})
    .filter(function(e,i,a){
      return (a.indexOf(e) == i && !(visibleValueArr.some(function(f){
        return e.search(new RegExp(f,'i'))+1;
      })));
    });
    Logger.log(colValueArr); 
    Logger.log(flatUniqArr); 
    return flatUniqArr;
  }

I believe the reason why this function does not work is because of the 3 line breaks and the one trailing line break in the cell's data.

Here are my Questions:

I'm still a beginner and RegEx is still very foreign to me. Is there a way to use regex to bypass the leading and trailing line breaks so that the rest of the function will work? I've deleted some manually and filtering works on those cells.
How can I have it search for Member Type: first or Member Type: last?

I'm Ok with having a third if within this if needed with an extra variable for Member Type.

Clarifications:

I'm aware that I could manually or programatically delete these new lines, but this data is refreshed daily. I'd like to see the string search function is possible first.

Notes from comments:

Logs show that the function is correctly excluding those that match what I eventually want to filter the column on. This leads me to believe that the issue lies with having .setHiddenValues with newlines or google sheets cannot filter on cells that have newlines.
To test the above I deleted newlines from flatUniqArr using flatUniqArr[i]= flatUniqArr[i].replace(/(\r\n|\n|\r)/gm,""); in a loop. Logs show this eliminated newlines. However, still no go. That narrows it down to google sheets not being able to filter on cells that have newlines.

Is this data very large? If it is small, you could split your lines and filter the results like you did with columns: 'string|with|breaks|||'.split('|').filter(item => !!item) style. — wizebin
Searching for a string with multiple newlines is quite simple in regex, \n+ means look for at least one newline followed by any quantity more, \n* means 0 or more newlines. — wizebin
Dataset is currently 360 rows. It will grow over time. will the split approach be better than going with regex? — DanCue
If your data will stay below a couple MB absolutely split first. — wizebin
@I'-'I What's interesting is that the logs show that it is excluding from flatUniqArr properly. I'm thinking the issue is with either .setHiddenValues and strings with newlines or the actual filtering on the column in google sheet. — DanCue

DanCue DanCue · Accepted Answer · 2018-08-07T12:05:33

The Problem:

It turns out that although .setHiddenValues() will accept arrays with strings that have newlines in them, google sheets will not allow these strings to be applied in the filtering of columns.

The Solution:

Delete newlines from cells that have them. In my case it was in a column so I used a for loop to trim() each value. I used trim because the issue is only with leading and trailing newlines. If it helps anyone else here is the code:

var teamArr = new Array()
var teamArr = sht.getRange(teamCS + '2:' + teamCS + lastRow).getValues().map(function (row) { return row.map(function (val) { return val.toString().trim(); }); });

sht.getRange(teamCS + '2:' + teamCS + lastRow).setValues(teamArr);

Updated to tehhowch's faster implementation per comment.

Search Array for String When Values have Line Breaks

1 Answers