Remove all filters in google sheets using google apps script

Question

I didn't want to revive an old thread posted years ago, so I'm starting a new one. The thread is over here

In that thread, I found my answer. I needed the following script to remove filters in a sheet:

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);
}

The problem is, the above code only removes filters in the current active sheet I'm using. What I want is to remove filters in ALL sheets.

This is what I've tried:

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


   for(var i = 0; i < ss.length; i++) {

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

Here I am trying to add a for loop to iterate through the entire workbook, but my for loop isn't exactly correct. Can someone please help me on this?

score 3 · Accepted Answer · answered Feb 05 '18 at 05:50

3

I understood that you want to remove filters for all sheets in the Spreadsheet. If my understanding is correct, how about this modification?

Modified points :

You can retrieve all sheets in the Spreadsheet using getSheets().
Each sheet ID is required to be imported to sheetId of "sheetId": sheetId.

Modified script :

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

Note :

This script supposes that Sheets API has already been enabled at Advanced Google Services and API console.

Reference :

getSheets()

If I misunderstand your question, I'm sorry.

answered Feb 05 '18 at 05:50

Tanaike

105,090
8
51
83

You understood my question perfectly, I tried running your modified script but got the error message "Insufficient tokens for quota 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:362873827256'. (line 110, file "Code")" I already enabled the advanced google services, so there should be no problem – johndoe666 Feb 05 '18 at 06:02
@johndoe666 Can you confirm whether Sheets API is enabled at API console again? And about the error message, there are users with the same situation. You can see the detail at [https://stackoverflow.com/questions/44414185/insufficient-tokens-for-quota-administrator-and-limit-client-project-100s-of](https://stackoverflow.com/questions/44414185/insufficient-tokens-for-quota-administrator-and-limit-client-project-100s-of). I confirmed that the script works again. Please don't worry about the script. – Tanaike Feb 05 '18 at 06:35
The old unmodified script works fine, I'm sure the sheets API is enabled and working correctly, this is a quota issue, I have 117 sheets in my workbook, does it somehow affect my quota? Does disabling filters in all 117 sheets counts against my quota? – johndoe666 Feb 05 '18 at 12:09
@johndoe666 I think that it affects to the quota because Sheets API is used. But I cannot confirm about it. I'm sorry for this situation. If you post it as a new question, users who had the same situation may answer for you. – Tanaike Feb 05 '18 at 21:59
Well, I am definitely running into a quota issue, your modified script works fine. Now, I just need to increase my quota. Thank you very much for the modified script, you've been really helpful. – johndoe666 Feb 06 '18 at 01:26
@johndoe666 I apologize that I couldn't clearly answer about the quota issue. – Tanaike Feb 06 '18 at 05:17
I've finally solved the issue, please refer to this thread I created [https://stackoverflow.com/questions/49375108/im-running-into-some-quota-issue/49434590#49434590] and look at my solution. I've learned from your script, and now you get to learn more efficient coding. Thank you, we both learned something in the end.. – johndoe666 Mar 22 '18 at 17:16
@johndoe666 Thank you for your additional information. – Tanaike Apr 14 '18 at 03:51

Remove all filters in google sheets using google apps script

1 Answers1

Modified points :

Modified script :

Note :

Reference :