I am trying to create a filter view with advanced sheets API. Everything I can find on it is in Python or some other language though, and I am NOT that advanced, I am barely making it through with googling questions! The rest of my code searches for a new tab that gets added each day, this codes purpose is to create a filter view automatically on that tab with some conditions.

I'm using the "Sheets.Spreadsheets.batchUpdate()" method, and everything in this code works for me except the "filterSpecs[]" attribute. I tried the criteria attribute first but then found out it was no longer in use? Please help!

              const resource = {
                requests: {
                  addFilterView: {
                    
                    filter: {
                      filterViewId: '0000000',
                      title: 'Chris Johnson',
                      range: {
                        sheetId: st1.toString(),
                        startRowIndex: 0,
                        endRowIndex: 500,
                        startColumnIndex: 0,
                        endColumnIndex: 8
                      }
                      filterSpecs: [{
                          3: {condition: {
                                  type: 'TEXT_CONTAINS',
                              values: {
                                    userEnteredValue: 'Chris Johnson'
                                    }}}
                                   }],
            }
            }}}

CodePudding user response：

Modification points:

• }filterSpecs: [{ has an error. , is required to be added.
• Element of filterSpecs is not correct.
• Even when filterViewId is not included, the filter view is added. But when filterViewId is used, you can give the original ID.
• Property of requests is an array.

When these points are reflected in your request body, it becomes as follows.

Modified script:

function myFunction() {
  const spreadsheetId = "###"; // Please set your Spreadsheet ID.
  const resource = {
    "requests": [
      {
        "addFilterView": {
          "filter": {
            "filterViewId": 12345,
            "title": "Chris Johnson",
            "range": {
              "sheetId": 0,
              "startRowIndex": 0,
              "endRowIndex": 500,
              "startColumnIndex": 0,
              "endColumnIndex": 8
            },
            "filterSpecs": [
              {
                "filterCriteria": {
                  "condition": {
                    "type": "TEXT_CONTAINS",
                    "values": [
                      {
                        "userEnteredValue": "Chris Johnson"
                      }
                    ]
                  }
                },
                // "columnIndex": 0 // If you want to use the column, please use this.
              }
            ]
          }
        }
      }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
}

Reference:

• AddFilterViewRequest