Filter a sheet based on criteria selected on another sheet and carried as an array

Question

A Google sheet I've designed for use in analyzing decennial censuses requires that cells be merged and that two rows be used to handle each census record. Because of the merged cells, my users can't use the normal Google Sheets filter process, so I need to create an alternate way to filter records. I'm hitting a snag. I've created a sheet that allows the user to set up the desired filter criteria. But I can't get the choices to translate into a usable GAS script that works. (I won't complicate this with all of the code that extracts the two-line records to another sheet and filters out all but one line per record, which can then be used to filter and rebuild the two-line records in their filtered form.)

The filtering criteria sheet (see below) allows up to nine levels of criteria. To the right, I have displayed columns that will be hidden from the user, but allow me to capture the information I need to create the filter in the script. Column I has the operator text needed in the script, column G is the column where (in the case of the first line) the Census Year is in the sheet being filtered, and column C has the criteria. I've collected the values in an array I'm calling filterArray.

I need to go through each line of the criteria, determining if there was a selection made. If so, I need to filter for the criteria. Here is my For loop:

  // Gather filters from Filter Criteria
   for (var i = 0; i <= 9; i = i + 1) { 
     if(filterArray[i][0]!==""){
       myFilterText = SpreadsheetApp.newFilterCriteria()+"."+filterArray[i][8]+"("+filterArray[i][2]+")"
       myFilter.setColumnFilterCriteria(filterArray[i][6],myFilterText)
     }
   }

The last line triggers this message:

Exception: The parameters (number,String) don't match the method signature for SpreadsheetApp.Filter.setColumnFilterCriteria.

If I was not using the array as a variable, I'd want it to basically look like this as it goes through the first loop:

   for (var i = 0; i <= 9; i = i + 1) { 
     if('A7'!==""){
       myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberLessThan(1850);
       myFilter.setColumnFilterCriteria(10,myFilterText)
     }
   }

In addition to trying the first code, with the array content, I've tried to create the whole string "SpreadsheetApp.newFilterCriteria()."+filterArray[i][8]+"("+filterArray[i][2]+")" as the contents of myFilterText. I get the same error.

How can I turn the criteria the user is selecting into a code that can run to filter records?

Answer 1

I found a solution, which is VERY code-heavy, unfortunately. I'm using a switch-case to evaluate every possible filter condition and to code the variable I need. The section of the code related to the question I posted is now this:

/**
   * Activating Filter and pasting records to new sheet called "Filtered Records_Prep"
   */
  var filterRange = spreadsheet.getActiveRange().getA1Notation();
    var myFilter = spreadsheet.getRange(filterRange).createFilter();
  var myFilterText = ""
  var sheet = spreadsheet.getActiveSheet();
  // Filter selection to row 1 of each set, eliminating 2s and blanks
  var Filter_Criteria0 = SpreadsheetApp.newFilterCriteria().whenNumberEqualTo(1);
  myFilter.setColumnFilterCriteria(3,Filter_Criteria0);
  // Gather filters from Filter Criteria
   for (var i = 0; i <= 9; i = i + 1) { 
     if(filterArray[i][0]!==""){
       myCol = filterArray[i][6];
       myFilterCondition = filterArray[i][8];
       myFilterCriteria = filterArray[i][2];
       switch(myFilterCondition) 
        {            
        case "whenNumberEqualTo":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberEqualTo(myFilterCriteria);
              break;    
        case "whenNumberNotEqualTo":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberNotEqualTo(myFilterCriteria);
              break;
        case "whenNumberContains":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberContains(myFilterCriteria);
              break;
        case "whenNumberDoesNotContain":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberDoesNotContain(myFilterCriteria);
              break; 
        case "whenCellEmpty":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenCellEmpty();
              break;
        case "whenCellNotEmpty":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenCellNotEmpty();
              break;
        case "whenNumberGreaterThan":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(myFilterCriteria);
              break;
        case "whenNumberGreaterThanOrEqualTo":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThanOrEqualTo(myFilterCriteria);
              break;    
        case "whenNumberLessThan":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberLessThan(myFilterCriteria);
              break;
        case "whenNumberLessThanOrEqualTo":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberLessThanOrEqualTo(myFilterCriteria);
              break;
        case "whenNumberStartsWith":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberStartsWith(myFilterCriteria);
              break; 
        case "whenNumberEndsWith":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenNumberEndsWith
          (myFilterCriteria);
              break; 
        case "whenTextEqualTo":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(myFilterCriteria);
              break;    
        case "whenTextNotEqualTo":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenTextNotEqualTo(myFilterCriteria);
              break;
        case "whenTextContains":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenTextContains(myFilterCriteria);
              break;
        case "whenTextDoesNotContain":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenTextDoesNotContain(myFilterCriteria);
              break; 
        case "whenTextStartsWith":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenTextStartsWith(myFilterCriteria);
              break; 
        case "whenTextEndsWith":
          myFilterText = SpreadsheetApp.newFilterCriteria().whenTextEndsWith
          (myFilterCriteria);
              break; 
        default:
              ui.alert("This criteria was not found.")
      }
       myFilter.setColumnFilterCriteria(myCol,myFilterText);
     } else {
       return false;
     }
   }