Sheets.spreadsheets.values.batch Get By Data Filter

Returns one or more ranges of values that match the specified data filters. The caller must specify the spreadsheet ID and one or more DataFilters. Ranges that match any of the data filters in the request will be returned.

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

Use it in a workflow

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

Request

Path parameters

spreadsheetIdstringrequired

The ID of the spreadsheet to retrieve data from.

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 data filters used to match the ranges of values to retrieve. Ranges that match any of the specified data filters are included in the response.

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!`.

dateTimeRenderOptionstring

How dates, times, and durations should be represented in the output. This is ignored if value_render_option is FORMATTED_VALUE. The default dateTime render option is SERIAL_NUMBER.

One ofSERIAL_NUMBERFORMATTED_STRING

majorDimensionstring

The major dimension that results should use. For example, if the spreadsheet data is: `A1=1,B1=2,A2=3,B2=4`, then a request that selects that range and sets `majorDimension=ROWS` returns `[[1,2],[3,4]]`, whereas a request that sets `majorDimension=COLUMNS` returns `[[1,3],[2,4]]`.

One ofDIMENSION_UNSPECIFIEDROWSCOLUMNS

valueRenderOptionstring

How values should be represented in the output. The default render option is FORMATTED_VALUE.

One ofFORMATTED_VALUEUNFORMATTED_VALUEFORMULA

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
      }
    }
  ],
  "dateTimeRenderOption": "SERIAL_NUMBER",
  "majorDimension": "DIMENSION_UNSPECIFIED",
  "valueRenderOption": "FORMATTED_VALUE"
}

Response

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

spreadsheetIdstring

The ID of the spreadsheet the data was retrieved from.

valueRangesarray<object>

The requested values with the list of data filters that matched them.

dataFiltersarray<object>3 fields

The DataFilters from the request that matched the range of values.

valueRangeobject3 fields

Data within a range of the spreadsheet.

json

{
  "spreadsheetId": "string",
  "valueRanges": [
    {
      "dataFilters": [
        {
          "a1Range": "string",
          "developerMetadataLookup": {
            "locationMatchingStrategy": "DEVELOPER_METADATA_LOCATION_MATCHING_STRATEGY_UNSPECIFIED",
            "locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
            "metadataId": 0,
            "metadataKey": "string",
            "metadataLocation": {
              "dimensionRange": null,
              "locationType": null,
              "sheetId": null,
              "spreadsheet": null
            },
            "metadataValue": "string",
            "visibility": "DEVELOPER_METADATA_VISIBILITY_UNSPECIFIED"
          },
          "gridRange": {
            "endColumnIndex": 0,
            "endRowIndex": 0,
            "sheetId": 0,
            "startColumnIndex": 0,
            "startRowIndex": 0
          }
        }
      ],
      "valueRange": {
        "majorDimension": "DIMENSION_UNSPECIFIED",
        "range": "string",
        "values": [
          [
            null
          ]
        ]
      }
    }
  ]
}

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

Was this page helpful?