Google Apps Script - Creating and Saving Filters for Google Sheets

0
votes

I feel like a bit of a chump, but I cannot work this out...

I have been given the job of producing a new master analysis sheet each month from a supplied XML file that combines with various columns of our (multiple) sheets. No problems, so far. I have got all of that working the way I want. :-)

My issue is that we also have about 6-8 filters saved with a specific sheet that allow our auditors to focus on specific areas (and as you can understand, our auditors want these to work EXACTLY as they specify).

I have tried using createFilter() but there doesn't appear any way to save multiple filters to that sheet (maybe I am missing something). No joy! :-(

I have tried recording a macro which I could then run to create the filters. No joy here either :-(

Do I have to tell these pesky auditors to create there own filters each month (they do know how, but it's beneath them), or is there a way I can script them up and get them off my back?

Unfortunately (as much as I would like to) I cannot share our sheets or scripts as we have significant IP embedded there.

I would really appreciate some guidance as to how you might approach this (if it is possible).

Kind regards

Ian

2
google-apps-scriptgoogle-sheets
Are you talking about 'Create new filter view', so everybody can make their own, or the actual filter which applies to everyone? – Michiel the Temp
why so complicated ? create a custom menu with all Menu's that act like your filters, wouldn't it be much easier? like e.g. developers.google.com/apps-script/guides/menus – CrissCrossCrass

2 Answers

0
votes

If you're indeed talking about the 'Create new filter view', I suggest making an template sheet. So instead of creating a new sheet every month, make one template spreadsheet and add all the filter views your auditors desire. Then copy that spreadsheet, and paste the new data in it.

0
votes

The correct way to create a filter using Apps Script and the createFilter() is this one:

function setFilters() {
  var ss = SpreadsheetApp.getActiveSheet();
  var rangeFilter = ss.getRange("INPUT_YOUR_RANGE_HERE");
  var filter = rangeFilter1.createFilter();
  var filterCriteria = SpreadsheetApp.newFilterCriteria();
  filterCriteria.ADD_YOUR_CRITERIA_HERE;
  filter.setColumnFilterCriteria(columnPosition, filterCriteria.build());
}

As you can see, you must use build() in order to build the criteria for the filter you have created.

You can also use the Sheets advanced services and create the filters using the Sheets API, something similar to this:

  var filterSettings = {
  //YOUR FILTER SETTINGS
  };
  var request = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];

And as for calling the Sheets service and applying the above filter, you can use this:

Sheets.Spreadsheets.batchUpdate({'requests': request}, SPREADSHEET_ID);

Reference