Update Google Sheet Slicers with Apps Script

The slicers in the Google Sheets does not update its range automatically if we add new rows in the sheet manually. Hence the slicers does not filter all the rows in the sheet.

Here is a apps script code snippet that can be used to update the slicer's range to all the rows in the sheet.

This function can be set as a trigger to execute automatically at scheduled intervals or on sheet edit.

Apps Script Code

/*
** @OnlyCurrentDoc
*/

/*
**
**  Apps Script Labs
**  -- Suhail Ansari
**  [suhailans@gmail.com]
**
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Script')
    .addItem('Update Slicer Range', 'updateSlicerRange')
    .addToUi();
}
function updateSlicerRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  try {
    var slicers = sheet.getSlicers();
    var objArr = [];
    var slicerCount = slicers.length;
    var filterCriteria;

    slicers.forEach(function (slicer) {
      objArr.push({ title: slicer.getTitle(), columnPosition: slicer.getColumnPosition() });
      filterCriteria = slicer.getFilterCriteria();
      slicer.remove();
    })

    var range = sheet.getRange(8, 1, sheet.getLastRow()-8, sheet.getLastColumn());
    for (var i = 0; i < slicerCount; i++) {
      sheet.insertSlicer(range, 4 , 2 + (2 * i));
    }

    var slicers = sheet.getSlicers();
    slicers.forEach(function (slicer, index) {
      slicer.setColumnFilterCriteria(objArr[index].columnPosition, filterCriteria);
      slicer.setTitle(objArr[index].title);
    })
  } catch (e) {
    console.log('Exception', e);
  }
}