How to add data filter to my google sheets header

Question

I want to add a filter to the header in my google sheet. I found THIS ANSWER using C# and tried to build the JSON from it. This is what I come up with but no luck :

{'AddFilterViewRequest' : {
      'AddFilterViewResponse': {
      'Filter' : {
                  'FilterView': {
                  'title' : "Hide rows with errors",
                  'range': {'sheetId': sheet_id,
                            'startRowIndex': 0,
                            'endRowIndex': 2,
                            'startColumnIndex': 1,
                            'endColumnIndex': 31}
                  }
      }
      }
}}

Anyone knows what I'm going wrong?

Answer 1

Here's my approach with gspread while researching on how to do this.

# authenticate to googlesheets
from google.colab import auth
from google.auth import default
auth.authenticate_user()
creds, _ = default()

# import gspread to interact with gsheets and authenticate gspread to google
import gspread
gc = gspread.authorize(creds)

#get spreadsheet
ss = gc.open_by_key('<longassspreadsheetsheetid>')
#get worksheet id (aka : SheetId as per google)
ws_id = ss.worksheet('<wsname>').id
# set the action on sheet to be requested
requests = [
  {
    "setBasicFilter": 
    {
      "filter":
      {
        "range": 
        {    
          "sheetId": ,
          "startColumnIndex": 0, #column A
          "endColumnIndex": 4 #column D
        }
      }
    }
  }
]
#prepare the body object with the request to send it to google
body = {
  'requests': requests
}
#send the update
ss.batch_update(body)

sources:

• Automate Google Sheets Autofilter with Sheets API in Python

• Set Basic Filter Request

Answer 2

From the Google Sheets API documentation:

"addFilterView": { # Adds a filter view. # Adds a filter view.
      "filter": { # A filter view. # The filter to add. The filterViewId
          # field is optional; if one is not set, an id will be randomly generated. (It
          # is an error to specify the ID of a filter that already exists.)
        "title": "A String", # The name of the filter view.
        "namedRangeId": "A String", # The named range this filter view is backed by, if any.
            #
            # When writing, only one of range or named_range_id
            # may be set.
        "filterViewId": 42, # The ID of the filter view.
        "range": { # A range on a sheet. # The range this filter view covers.
            #
            # When writing, only one of range or named_range_id
            # may be set.
            # All indexes are zero-based.
            # Indexes are half open, e.g the start index is inclusive
            # and the end index is exclusive -- [start_index, end_index).
            # Missing indexes indicate the range is unbounded on that side.
            #
            # For example, if `"Sheet1"` is sheet ID 0, then:
            #
            #   `Sheet1!A1:A1 == sheet_id: 0,
            #                   start_row_index: 0, end_row_index: 1,
            #                   start_column_index: 0, end_column_index: 1`
            #
            #   `Sheet1!A3:B4 == sheet_id: 0,
            #                   start_row_index: 2, end_row_index: 4,
            #                   start_column_index: 0, end_column_index: 2`
            #
            #   `Sheet1!A:B == sheet_id: 0,
            #                 start_column_index: 0, end_column_index: 2`
            #
            #   `Sheet1!A5:B == sheet_id: 0,
            #                  start_row_index: 4,
            #                  start_column_index: 0, end_column_index: 2`
            #
            #   `Sheet1 == sheet_id:0`
            #
            # The start index must always be less than or equal to the end index.
            # If the start index equals the end index, then the range is empty.
            # Empty ranges are typically not meaningful and are usually rendered in the
            # UI as `#REF!`.
          "endRowIndex": 42, # The end row (exclusive) of the range, or not set if unbounded.
          "endColumnIndex": 42, # The end column (exclusive) of the range, or not set if unbounded.
          "sheetId": 42, # The sheet this range is on.
          "startColumnIndex": 42, # The start column (inclusive) of the range, or not set if unbounded.
          "startRowIndex": 42, # The start row (inclusive) of the range, or not set if unbounded.
        },
        "sortSpecs": [ # The sort order per column. Later specifications are used when values
            # are equal in the earlier specifications.
          { # A sort order associated with a specific column or row.
            "sortOrder": "A String", # The order data should be sorted.
            "dimensionIndex": 42, # The dimension the sort should be applied to.
          },
        ],
        "criteria": { # The criteria for showing/hiding values per column.
            # The map's key is the column index, and the value is the criteria for
            # that column.
          "a_key": { # Criteria for showing/hiding rows in a filter or filter view.
            "hiddenValues": [ # Values that should be hidden.
              "A String",
            ],
            "condition": { # A condition that can evaluate to true or false. # A condition that must be true for values to be shown.
                # (This does not override hiddenValues -- if a value is listed there,
                #  it will still be hidden.)
                # BooleanConditions are used by conditional formatting,
                # data validation, and the criteria in filters.
              "values": [ # The values of the condition. The number of supported values depends
                  # on the condition type.  Some support zero values,
                  # others one or two values,
                  # and ConditionType.ONE_OF_LIST supports an arbitrary number of values.
                { # The value of the condition.
                  "relativeDate": "A String", # A relative date (based on the current date).
                      # Valid only if the type is
                      # DATE_BEFORE,
                      # DATE_AFTER,
                      # DATE_ON_OR_BEFORE or
                      # DATE_ON_OR_AFTER.
                      #
                      # Relative dates are not supported in data validation.
                      # They are supported only in conditional formatting and
                      # conditional filters.
                  "userEnteredValue": "A String", # A value the condition is based on.
                      # The value will be parsed as if the user typed into a cell.
                      # Formulas are supported (and must begin with an `=`).
                },
              ],
              "type": "A String", # The type of condition.
            },
          },
        },
      },
    }

I haven't tried using a filter myself but it seems like the keys AddFilterViewRequest, AddFilterViewRequest and Filter aren't required.