Can this code be optimised so it runs faster?-CodePudding

I put together a script for Google Sheets which resets the filters on each sheet (YR5, YR6, YR7 and YR8) and then re-orders the sheets by 3 columns. It's needed as users often leave the sheets filtered and then mistakes are made when entering data as rows are hidden.

The script works as expected, but is there a way to optimise this so it will run any faster? I'm very new to Google Apps script and have put this together from various other people's work.

Thanks :)

This is the script:

function clearFilter(sheet) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var YRS = ['YR5','YR6','YR7','YR8'];
  var pos = 0;
  for (var i = 0; i <=3; i = i   1) {
    var sheet = ss.getSheetByName(YRS[pos]);
    var filter = sheet.getFilter();
    var range = filter.getRange();
    filter.remove();
    range.createFilter();
    var filter = sheet.getFilter();
    filter.sort(1, true);
    filter.sort(4, true);
    filter.sort(6, true);
    pos = pos   1
  }
SpreadsheetApp.getUi().alert("Ready to edit");
}

CodePudding user response：

I think your best bet is to try checking if the filters have been changed before recreating them. This wouldn't help in cases where every sheet's filter has been changed, though. Using filter.getColumnFilterCriteria(columnPosition) looks helpful for this.

Instead of removing the filter, is it enough to only re-sort the first, fourth, and sixth columns? This could make a reasonable dent in performance. But you'd probably want to call filter.removeColumnFilterCriteria(columnPosition) for each column before re-sorting, though, and I don't know if doing that will be slower than just removing and re-creating a filter.

(Other methods on the filter object)

Finally, I'll mention a couple very minor optimizations that probably won't make a dent, which are removing the 'sheet' parameter and making it a local variable, and removing the pos variable and using i instead for accessing the YRS array.

CodePudding user response：

If the problem is that users leave a sheet in a filtered state, you can avoid the whole issue by instructing them to use filter views instead of a regular filter. This feature lets multiple simultaneous users sort, filter and edit the sheet without disturbing each other.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.