Enable Advanced Services

Scripting Google Sheets Filters...

This one was way, way harder than I would have figured, but I should come to expect no less from Google's App team.

So, Filters are a great feature in a spreadsheet.  I love them.  You love them.   We all love them. 

For some reason, Alteryx's Google Sheets Output Tool is honoring filters.  That is, if you have a row filtered out, and You ask the Google Sheets Output Tool to "Overwrite Sheet (Drop)" which sounds an awful lot like "nuke that shit from orbit", well, it won't nuke row 2, because it's filtered.

Yeah, that's kinda bad. One upside to this whole "honor thy mother and filter" concept is that it does keep the formatting on the sheet, which is, probably good?  I dunno.

So, I needed to script the "clearing" but probably not the "removal" of every filter in the spreadsheet.  Seems simple; I've done all kinds of crazy formatting, so this seemed like a trivial task.  Nope.  You can do it though.  You just have to go through some crazy hoops.  Here we go:

You have to "Enable Advanced Services"  What follows is a lot of annotation, which will quickly become obsolete, because Google hates me^tm.

To use an advanced Google service, follow these instructions:

  1. In the script editor, select Resources > Advanced Google services....
  2. In the dialog that appears, click the on/off switch next to the service you want to use. 
  3. At the bottom of the dialog, click the link for the Google API Console.
  4. In the console, click into the filter box and type part of the name of the API (for example, "Google Sheets API")
  5. On the next screen, click Enable API.
  6. Close the API Console and return to the script editor. Click OK in the dialog. The advanced service you enabled is now available in autocomplete.

Step 2.

Step 3

Step 4a

Step 4b

Step 5

Bad news, too: You have to do this for  every "project" (spreadsheet) that you want to script these sorts of filter clearing behaviors on, and find a way to run the script before Alteryx outputs its data.  Not especially cool...  Hopefully Alteryx can come up with a better solution.

Anyway, once you've got all that done.  You can run this script:

function clearFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetId = ss.getActiveSheet().getSheetId();
  var requests = [{
    "clearBasicFilter": {
      "sheetId": sheetId
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}