Sheets.spreadsheets.values.batch Clear By Data Filter

Clears one or more ranges of values from a spreadsheet. The caller must specify the spreadsheet ID and one or more DataFilters. Ranges matching any of the specified data filters will be cleared. Only values are cleared -- all other properties of the cell (such as formatting, data validation, etc..) are kept.

POST/v4/spreadsheets/{spreadsheetId}/values:batchClearByDataFilter

Use it in a workflow

Add a step and choose the Google Sheets connector.
Pick the Sheets.spreadsheets.values.batch Clear By Data Filter action (under spreadsheets).
Fill in the fields below, then reference the result from later steps as {{sheetsSpreadsheetsValuesBatchClearByDataFilter.response.data}}.

Request

Path parameters

spreadsheetIdstringrequired

The ID of the spreadsheet to update.

Query parameters

$.xgafvstring

V1 error format.

One of12

access_tokenstring

OAuth access token.

altstring

Data format for response.

One ofjsonmediaproto

callbackstring

JSONP

fieldsstring

Selector specifying which fields to include in a partial response.

keystring

API key. Your API key identifies your project and provides you with API access, quota, and reports. Required unless you provide an OAuth 2.0 token.

oauth_tokenstring

OAuth 2.0 token for the current user.

prettyPrintboolean

Returns response with indentations and line breaks.

quotaUserstring

Available to use for quota purposes for server-side applications. Can be any arbitrary string assigned to a user, but should not exceed 40 characters.

upload_protocolstring

Upload protocol for media (e.g. "raw", "multipart").

uploadTypestring

Legacy upload protocol for media (e.g. "media", "multipart").

Request body

Enter the body as JSON in the step's Body field.

dataFiltersarray<object>

The DataFilters used to determine which ranges to clear.

a1Rangestring

Selects data that matches the specified A1 range.

developerMetadataLookupobject7 fields

Selects DeveloperMetadata that matches all of the specified fields. For example, if only a metadata ID is specified this considers the DeveloperMetadata with that particular unique ID. If a metadata key is specified, this considers all developer metadata with that key. If a key, visibility, and location type are all specified, this considers all developer metadata with that key and visibility that are associated with a location of that type. In general, this selects all DeveloperMetadata that matches the intersection of all the specified fields; any field or combination of fields may be specified.

gridRangeobject5 fields

A range on a sheet. All indexes are zero-based. Indexes are half open, i.e. 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 123456, then: `Sheet1!A1:A1 == sheet_id: 123456, start_row_index: 0, end_row_index: 1, start_column_index: 0, end_column_index: 1` `Sheet1!A3:B4 == sheet_id: 123456, start_row_index: 2, end_row_index: 4, start_column_index: 0, end_column_index: 2` `Sheet1!A:B == sheet_id: 123456, start_column_index: 0, end_column_index: 2` `Sheet1!A5:B == sheet_id: 123456, start_row_index: 4, start_column_index: 0, end_column_index: 2` `Sheet1 == sheet_id: 123456` 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!`.

json

{
  "dataFilters": [
    {
      "a1Range": "string",
      "developerMetadataLookup": {
        "locationMatchingStrategy": "DEVELOPER_METADATA_LOCATION_MATCHING_STRATEGY_UNSPECIFIED",
        "locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
        "metadataId": 0,
        "metadataKey": "string",
        "metadataLocation": {
          "dimensionRange": {
            "dimension": "DIMENSION_UNSPECIFIED",
            "endIndex": 0,
            "sheetId": 0,
            "startIndex": 0
          },
          "locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
          "sheetId": 0,
          "spreadsheet": false
        },
        "metadataValue": "string",
        "visibility": "DEVELOPER_METADATA_VISIBILITY_UNSPECIFIED"
      },
      "gridRange": {
        "endColumnIndex": 0,
        "endRowIndex": 0,
        "sheetId": 0,
        "startColumnIndex": 0,
        "startRowIndex": 0
      }
    }
  ]
}

Response

Returns 200 with an object. Read it in later steps with {{sheetsSpreadsheetsValuesBatchClearByDataFilter.response.data.<field>}}.

clearedRangesarray<string>

The ranges that were cleared, in [A1 notation](/sheets/api/guides/concepts#cell). If the requests are for an unbounded range or a ranger larger than the bounds of the sheet, this is the actual ranges that were cleared, bounded to the sheet's limits.

spreadsheetIdstring

The spreadsheet the updates were applied to.

json

{
  "clearedRanges": [
    "string"
  ],
  "spreadsheetId": "string"
}

Need more? See the Google Sheets guide for connection setup and behaviour shared by every action.

Was this page helpful?