Googles Sheets - simultaneous multi-sheet sorting and filtering

Question

I have a spreadsheet template for people to store data about Pokemon collections. There are two main ways people sort their Pokemon, alphabetically and numerically, and I would like to cater to both for easy comparison with other people spreadsheets. I also want to allow people to easily hide the Pokemon not currently in their collection. The issue is the sheer size of the spreadsheet and the number of sheets. Further complicating issues is the summarizing checklist page that refers to most of the other pages for it's data. Further complicating matters are that I want to break the data into seven sections with header rows when sorted numerically but want to hide the header rows when sorting alphabetically. I have tried:

1) Using a script to iterate through each sheet and hide all entries not marked "FT" (For Trade) in column 2. (column 1 is a hidden index for easy reference page use). The sheet is so large and hiding each row one at a time takes so long the script times out before it can finish executing. The same problem occurs if I hide all of them at once and show the rows I want one at a time

2) Using filter views so that view only users can toggle them on and off at their leisure. This is suboptimal because users have to toggle each page individually, but even if that worked using a filter on the checklist page causes it to break as it references cells on other pages and thus necessarily outside of the filter

I have not tried using a filter via scripts, but I don't know how to do that without breaking the cell to cell correlation between the checklist and the myriad pages it necessarily must reference. I did look into using a filter VIEW via scripts, but alas, the API doesn't support that yet.

Below is a link to a copy of the spreadsheet with open editing privileges. There is a lot to explain so viewing may be necessary:

https://docs.google.com/spreadsheets/d/1NpsabimIJ4T_ZoeSxaq_dk6ZkIoHey2Dl6U3LGpVTc4/edit?usp=sharing

Is there a good way to hide the rows not marked "FT" that won't time out or break my formulas? Here are the scripts I already tried that took too long. They each have a twin that reverses their effects:

function browseMode() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheets = spreadsheet.getSheets();

  var sheet = sheets[2];
  var range = sheet.getRange("B3:B392");
  var data = range.getValues();
  sheet.hideRow(range);
  for (var i = 0; i < 390; i++) {
    if (data[i][0] == "FT") sheet.showRows(i+3);
  }
  for (var i = 3; i <= 26; i++) {
    sheet = sheets[i];
    range = sheet.getRange("B5:B76");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 72; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+5);
    }
    range = sheet.getRange("B78:B128");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 51; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+78);
    }
    range = sheet.getRange("B130:B192");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 63; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+130);
    }
    range = sheet.getRange("B194:B226");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 33; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+194);
    }
    range = sheet.getRange("B228:B300");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 73; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+228);
    }
    range = sheet.getRange("B302:B339");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 38; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+302);
    }
    range = sheet.getRange("B341:B380");
    data = range.getValues();
    sheet.hideRow(range);
    for (var j = 0; j < 40; j++) {
      if (data[j][0] == "FT") sheet.showRows(j+341);
    }
  }
  sheet = sheets[27]
  range = sheet.getRange("B3:B104");
  data = range.getValues();
  sheet.hideRow(range)
  for (var i = 0; i < 102; i++) {
    if (data[i][0] == "FT") sheet.showRows(i+3);
  }
}

function sortAlphabetically() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheets = spreadsheet.getSheets();

  var sheet = sheets[1];
  sheet.hideRows(4);
  sheet.hideRows(77);
  sheet.hideRows(129);
  sheet.hideRows(193);
  sheet.hideRows(227);
  sheet.hideRows(301);
  sheet.hideRows(340);
  sheet.getRange("A4:AB380").sort(2);

  sheets[2].getRange("A3:AG392").sort(3);

  for (var i = 3; i <= 26; i++) {
    sheet = sheets[i];
    sheet.hideRows(4);
    sheet.hideRows(77);
    sheet.hideRows(129);
    sheet.hideRows(193);
    sheet.hideRows(227);
    sheet.hideRows(301);
    sheet.hideRows(340);
    sheet.getRange("A4:AB380").sort(3);
  }

  sheets[27].getRange("A4:AN104").sort(3);
}

Thank you for taking the time to read this and please forgive me if this is similar to another question, I'm new here and though I searched the forum for similar questions and didn't find any, I'm not certain I know what to look for or how to look yet.

Tanaike Tanaike · Accepted Answer · 2017-04-06T06:17:48

At first, create a map for hideRows(). Using this map, hideRows() is run. By this, I think that the cost of time and script becomes low. If I misunderstand your question, I'm sorry. If errors occur, please tell me.

And how will you use about sortAlphabetically()?

Sample script :

function browseMode(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var s=3; s<sheets.length; s++) { 
    var sheet = ss.getSheets()[s];
    try {
      var data = sheet.getRange(1, 2, sheet.getLastRow(), 1).getValues();
    } catch(e) {} // If there are no data in the sheet, do nothing.
    var c1 = 0;
    var hrow = [];
    data.forEach(function(e, i){ // Here, create a map for hiding rows.
      if (e[0]) {
        hrow.push([(c1 + 1), (i - c1)]);
        c1 = i + 1;
      }
    });
    [sheet.hideRows(i[0], i[1]) for each (i in hrow) if(i[1]>0)]; // Here, rows are hidden using the map.
  }
}

Googles Sheets - simultaneous multi-sheet sorting and filtering

1 Answers