CODEWITHSUNDEEP
arraysmultidimensional-arraygoogle-apps-scriptgoogle-sheets
DanCue
DanCue

Reputation: 772

Apply Filter to Column with Numeric Values

I have a solution for filtering on this question.

This works perfectly with a column that has string values. When I try to filter a column with numeric values it will not work. I'm assuming it is because .setHiddenValues() will not accept numeric values. I could be wrong.

Let me explain my scenario:

The user inputs a value on an HTML interface, let's say 6634. The HTML calls my function on .gs and passes the numeric value the user inputted.

google.script.run                                                                            //Executes an Apps Script JS Function
 .withSuccessHandler(updateStatus)                                                           //function to be called upon successfull completion of Apps Script function
 .withFailureHandler(failStatus)
 .withUserObject(button)                                                                     //To pass the event element object
 .projectSearch2(projectID);           //Apps Script JS Function
return;

The function (on the linked question above) will take that value and bump it up against the values in a column deleting the value if it is found. What I am left with is an array of values that I do not want filtered.

function projectSearch2(projectID){
    var ss = SpreadsheetApp.getActive();
    var monthlyDetailSht = ss.getSheetByName('Data Sheet');
    var monLastCN = monthlyDetailSht.getLastColumn();
    var monLastRN = monthlyDetailSht.getLastRow();    
    var data = monthlyDetailSht.getRange(1,1,1,monLastCN).getValues();//Get 2D array of all values in row one
    var data = data[0];//Get the first and only inner array
    var projectIDCN = data.indexOf('Project Id') + 1;

    //Pull data from columns before filtering
    var projectIDData = monthlyDetailSht.getRange(2,projectIDCN,monLastRN,1).getValues();

    //Reset filters if filters exist
    if(monthlyDetailSht.getFilter() != null){monthlyDetailSht.getFilter().remove();}

    //Start Filtering
    var projectIDExclCriteria = getHiddenValueArray(projectTypeData,projectID); //get values except for    
    var rang = monthlyDetailSht.getDataRange();
    var projectIDFilter = SpreadsheetApp.newFilterCriteria().setHiddenValues(projectIDExclCriteria).build();//Create criteria with values you do not want included.
    var filter = rang.getFilter() || rang.createFilter();// getFilter already available or create  a new one

    if(projectID != '' && projectID != null){
      filter.setColumnFilterCriteria(projectIDCN, projectIDFilter);
    }


};

function getHiddenValueArray(colValueArr,visibleValueArr){
  var flatUniqArr = colValueArr.map(function(e){return e[0];})
  .filter(function(e,i,a){return (a.indexOf(e.toString())==i && visibleValueArr.indexOf(e.toString()) ==-1); })
  return flatUniqArr;
}

That array is used in .setHiddenValues() to filter on the column. Nothing is filtered however. This works for all columns that contain string values, just not columns with numeric values. At this point I'm lost.

Attempted Solutions:

Here is a solution. Changing the following:

.filter(function(e,i,a){return (a.indexOf(e.toString())==i &&    visibleValueArr.indexOf(e.toString()) ==-1); })

To:

.filter(function(e,i,a){return (a.indexOf(e) == i && visibleValueArr.indexOf(e) == -1); })

That works! Thank you Tanaike. The next question is will this impact columns that are not numeric. I have tested that and it works as well.

Upvotes: 1

Views: 893

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this modification?

From :

.filter(function(e,i,a){return (a.indexOf(e.toString())==i && visibleValueArr.indexOf(e.toString()) ==-1); })

To :

.filter(function(e,i,a){return (a.indexOf(e) == i && visibleValueArr.indexOf(e) == -1); })

Note :

  • In this modification, the number and string can compared using each value.
  • If you want to use return (a.indexOf(e.toString())==i && visibleValueArr.indexOf(e.toString()) ==-1), you can achieve it by modifying from colValueArr.map(function(e){return e[0];}) to colValueArr.map(function(e){return e[0].toString();}).
    • In this modification, colValueArr.map(function(e){return e[0].toString();}) converts the number to string, so the number is used as a string.

Reference :

Upvotes: 3

Related Questions