google-sheetsgoogle-apps-scriptfiltering

App Script for filtering the rows in Google Sheets and applying it for multiple sheets


I am currently using the script to filter all the rows in the plan, if there is a checkmark ticked. In addition to that I have a code also showing me all the hidden rows, in case I require to double-check some previous data. For the two filter there is an additional tab created in the google spreadsheet "Custom filter".

The thing is, that the filter only works on a specific sheet, and by simply copying it and creating the same script with different name of the sheet does not provide a desired result. And my lack of knowledge unfortunately leads to nowhere.

Could you please check the code and give me an idea, how can it be adjusted to be able to work for multiple sheets? I would really appreciate your help!

The code which is currently used:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Custom Filter")
    .addItem("Filter rows", "filterRows")
    .addItem("Show all rows", "showAllRows")
    .addToUi();
}

function filterRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Warehouse_1")
  var data = sheet.getDataRange().getValues();
  for(var i = 1; i < data.length; i++) {
    if(data[i][10] === true) {
      sheet.hideRows(i + 1);
    }
  }
}

function showAllRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Warehouse_1");
  sheet.showRows(1, sheet.getMaxRows());
}

I tried to duplicate the code, create separate script and enter another sheet name "Warehouse_2", but in that case custom filter created is only applied randomly for one of the sheets.


Solution

  • For active sheet (per comment on original post), try calling just the active sheet as in code below:

        function onOpen() {
      SpreadsheetApp.getUi().createMenu("Custom Filter")
        .addItem("Filter rows", "filterRows")
        .addItem("Show all rows", "showAllRows")
        .addToUi();
    }
    
    function filterRows() {
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var sheet = ss.getActiveSheet();
      var data = sheet.getDataRange().getValues();
      for(var i = 1; i < data.length; i++) {
        if(data[i][10] === true) {
          sheet.hideRows(i + 1);
        }
      }
    }
    
    function showAllRows() {
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var sheet = ss.getActiveSheet();
      sheet.showRows(1, sheet.getMaxRows());
    }