How does one automatically update rows/data with a Slicer filter when a spreadsheet is edited?

Question

How does one automatically update a Slicer when data is changed on a spreadsheet?

I would like it so that when I change cells and data on the spreadsheet, the Slicer filter will automatically update (filtering out rows I don't want to see, updating graphs that depend on the data, etc.). Currently you have to click on the slicer UI and click "OK" every time the data is changed to tell the Slicer to reread and refilter the data.

How does one make this process automatic?

Answer 1

(The Slicer feature on Google Sheets (as of 2021) is a new feature with many significant bugs that may prevent normal use. If you are willing to risk it, it can be a nice alternative to Filters. However, if you want to do anything complicated involving a custom formulas, you will also need some help from appscript; there's currently a bug that corrupts all formulas input by shifting the rows up by one each time you edit and click 'OK' to update the filter (including when you first update the filter, so you have to type B2 if you mean A2 for example). There are similar bugs that foil use of absolute references e.g. $A$2, depending how complicated you want to be.)

I attempted to re-set the criteria to itself; that didn't work. I also tried to use criteria.copy()...build() to create a clone via FilterCriteriaBuilder; that didn't work. It seems some form of caching makes the spreadsheet app ignore manifestly similar criteria. Perhaps if we could easily read the formula we could rewrite it.

A solution that worked for me quite nicely, is based on this answer. Basically we destroy and recreate the slicer each time. This avoids the Google bugs mentioned above.

Basically with a layout where you dedicate a row (or more) to values you might want as parameters to filter by... (or even an entire sheet with complicated parameters), you can merely do this:

      1 [subs? ][         ][atLeast]
      2 [Bob   ][*        ][50000  ]  filter.???, e.g. *=filter.position
      3 [      ][         ][       ]  useless buffer
      4 [name  ][position ][salary ]
      5 [      ][         ][       ]  maybe useful for arrayformulas
        ---------------------- frozen
      6 [Alice ][manager  ][60000  ]
      7 [Bob   ][coder    ][60000  ]
      ? [.name ][.position][.salary]  diagram

  let filters = [
    `REGEXMATCH(this.name,filter.name)`,
    `this.salary>=filter.salary`,
    // ... add as necessary
  ];

  WHENEVER YOU EDIT SOMETHING, THE ROWS WILL DISAPPEAR TO OBEY THE FILTERS
  (you may add more complicated logic to make filters optional, have multiple params, etc.)

To do this, first set your appsscript.json file (make visible via config) to include "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.currentonly"]; I'm not sure if you will require more permissive scopes.

Create a simple trigger onEdit function (you may add code to filter it so it doesn't do this monkey dance on every edit; only relevant edits on the relevant sheets.)

function onEdit() {
  let sheet = SpreadsheetApp.getActiveSheet();
  
  let slicers = sheet.getSlicers();
  let slicer = slicers[0];
  if (slicers.length>1)
    throw 'error';
  // add more complicated logic here to identifier the slicer you wish to delete
  
  let slicerRange = sheet.getRange("5:235"); // code to get your slicer range (or autodetect from deleted slicer)
  
  slicer.remove();
  slicer = sheet.insertSlicer(slicerRange, 1,1); // create new slicer; anchor visually to A1
  
  // I put column names (header row) two rows above the first data row
  let headerRange = sheet.getRange(sheet.getFrozenRows()-1,1, 1,sheet.getMaxColumns());
  let cols = headerRange.getValues()[0]; // get header row
  //Logger.log(cols) //optional
  let colNum = {};  // e.g. {name:1, position:2, salary:3}
  for(let i=0; i<cols.length; i++)
    colNum[cols[i]] = i+1;
  
  let c={}, f={};
  for(let k of Object.keys(colNum)) {
    // this is necessary to work around a Google bug that prevents using and saving proper absolute and relative referencing under some circumstances
    c[k] = `INDEX($1:$325,ROW(),${colNum[k]})`;  // column
    f[k] = `INDEX($1:$325,2,${colNum[k]})`;  // column filter param
  }

  function parse(x) {  // replaces a subformula with filter.X or this.X with a subformula that will look up X as appropriate
    return x.replace(/(filter|this)\.(\w+)/g, (_,ctx,k)=> 
      ({filter:f,this:c}[ctx][k])
    )
  }
  // referring to filter.X will filter the row based on column X, e.g.:
  // REGEXMATCH(this.name, "Bob") will return rows where the name includes Bob
  // REGEXMATCH(this.name, this.filter) will return rows where the name includes whatever is entered in row 2, which we use as a filter parameter

  /*
      1 [subs? ][         ][atLeast]
      2 [Bob   ][*        ][50000  ]  filter.???, e.g. *=filter.position
      3 [      ][         ][       ]  useless buffer
      4 [name  ][position ][salary ]
      5 [      ][         ][       ]  maybe useful for arrayformulas
        ---------------------- frozen
      6 [Alice ][manager  ][60000  ]
      7 [Bob   ][coder    ][60000  ]
      ? [.name ][.position][.salary]  diagram
  */
  
  let filters = [
    `REGEXMATCH(this.name,filter.name)`,
    `this.salary>=filter.salary`,
    // ... add as necessary
  ];

  let whitelistFormula = '+(this.position="manager")';  // would always include managers, regardless of whether they filter or not

  let formula = '=' + filters.map(x=> 
    '('+parse(x)+')'
  ).join('*') + parse(whitelistFormula);

  slicer.setColumnFilterCriteria(1, SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied(formula).build());
}