CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfilterviewhidden-field
Freerunner
Freerunner

Reputation: 1

Google Script to create filters that hide newly added values. (sheets)

I am viewing data that comes from google forms in google sheets. I want to create filter views, in google sheet, to display only blank values in a column. The problem is that when a new value is inputted in google sheet, the filter view automatically adds this value in the filter, and I need to manually adjust the filter again.

To resolve this, I tried to create a script, that I would link to a button. The idea behind the script is to first get all the values in my column, and then setting those values in a hide function.

Right now, the script is showing all values:

function applyCustomFilter() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1'); 
  
  // Create a filter if it doesn't exist
  var filter = sheet.getFilter() || sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).createFilter();
  

  // Get all the values in Column M (column index 13)
  var valuesColumnM = sheet.getRange("M2:M" + sheet.getLastRow()).getValues();
  
  // Determine the hidden values for Column M based on the data
  var hiddenValuesColumnM = [];
  for (var i = 0; i < valuesColumnM.length; i++) {
    if (valuesColumnM[i][0] !== 'DATES' && valuesColumnM[i][0] !== '') {
      hiddenValuesColumnM.push(valuesColumnM[i][0]);
    }
  }

  // Define the filter criteria for column M (column index 13) with dynamically determined hidden values
  var criteriaColumnM = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues(hiddenValuesColumnM) // Show 'DATES' and blanks in column M
    .build();

  // Apply the filter criteria to the respective columns
  filter.setColumnFilterCriteria(13, criteriaColumnM);
}

I am expecting the filter to hide all values and display only blanks (plus my the tittle of my table ("ValueA, ValueB").

Any ideas?

Upvotes: 0

Views: 325

Answers (1)

Cooper
Cooper

Reputation: 64140

This works for me:

function applyCustomFilter() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('sheet1');
  var filter = sh.getFilter() || sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).createFilter();
  var vsm = sh.getRange("M2:M" + sh.getLastRow()).getValues().flat();
  var hvsm = vsm.filter(v => v);//filters out the ones that are truthy
  var crm = SpreadsheetApp.newFilterCriteria().setHiddenValues(hvsm).build();
  filter.setColumnFilterCriteria(13, crm);
}

Upvotes: 0

Related Questions