Batch Get By Data Filter

23 variables

10 variables

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

Authorization

To use this building block you will have to grant access to at least one of the following scopes:

See, edit, create, and delete all of your Google Drive files
View and manage Google Drive files and folders that you have opened or created with this app
View and manage your spreadsheets in Google Drive

Input

This building block consumes 23 input parameters

Name	Format	Description
`spreadsheetId` Required	`STRING`	The ID of the spreadsheet to retrieve data from
`majorDimension`	`ENUMERATION`	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` will return `[[1,2],[3,4]]`, whereas a request that sets `majorDimension=COLUMNS` will return `[[1,3],[2,4]]`
`dataFilters[]`	`OBJECT`	Filter that describes what data should be selected or returned from a request
`dataFilters[].developerMetadataLookup`	`OBJECT`	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
`dataFilters[].developerMetadataLookup.metadataLocation`	`OBJECT`	A location where metadata may be associated in a spreadsheet
`dataFilters[].developerMetadataLookup.metadataLocation.spreadsheet`	`BOOLEAN`	True when metadata is associated with an entire spreadsheet
`dataFilters[].developerMetadataLookup.metadataLocation.sheetId`	`INTEGER`	The ID of the sheet when metadata is associated with an entire sheet
`dataFilters[].developerMetadataLookup.metadataLocation.locationType`	`ENUMERATION`	The type of location this object represents. This field is read-only
`dataFilters[].developerMetadataLookup.locationMatchingStrategy`	`ENUMERATION`	Determines how this lookup matches the location. If this field is specified as EXACT, only developer metadata associated on the exact location specified is matched. If this field is specified to INTERSECTING, developer metadata associated on intersecting locations is also matched. If left unspecified, this field assumes a default value of INTERSECTING. If this field is specified, a metadataLocation must also be specified
`dataFilters[].developerMetadataLookup.locationType`	`ENUMERATION`	Limits the selected developer metadata to those entries which are associated with locations of the specified type. For example, when this field is specified as ROW this lookup only considers developer metadata associated on rows. If the field is left unspecified, all location types are considered. This field cannot be specified as SPREADSHEET when the locationMatchingStrategy is specified as INTERSECTING or when the metadataLocation is specified as a non-spreadsheet location: spreadsheet metadata cannot intersect any other developer metadata location. This field also must be left unspecified when the locationMatchingStrategy is specified as EXACT
`dataFilters[].developerMetadataLookup.metadataKey`	`STRING`	Limits the selected developer metadata to that which has a matching DeveloperMetadata.metadata_key
`dataFilters[].developerMetadataLookup.metadataId`	`INTEGER`	Limits the selected developer metadata to that which has a matching DeveloperMetadata.metadata_id
`dataFilters[].developerMetadataLookup.visibility`	`ENUMERATION`	Limits the selected developer metadata to that which has a matching DeveloperMetadata.visibility. If left unspecified, all developer metadata visibile to the requesting project is considered
`dataFilters[].developerMetadataLookup.metadataValue`	`STRING`	Limits the selected developer metadata to that which has a matching DeveloperMetadata.metadata_value
`dataFilters[].a1Range`	`STRING`	Selects data that matches the specified A1 range
`dataFilters[].gridRange`	`OBJECT`	A range on a sheet. 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!`
`dataFilters[].gridRange.startRowIndex`	`INTEGER`	The start row (inclusive) of the range, or not set if unbounded
`dataFilters[].gridRange.startColumnIndex`	`INTEGER`	The start column (inclusive) of the range, or not set if unbounded
`dataFilters[].gridRange.sheetId`	`INTEGER`	The sheet this range is on
`dataFilters[].gridRange.endRowIndex`	`INTEGER`	The end row (exclusive) of the range, or not set if unbounded
`dataFilters[].gridRange.endColumnIndex`	`INTEGER`	The end column (exclusive) of the range, or not set if unbounded
`valueRenderOption`	`ENUMERATION`	How values should be represented in the output. The default render option is ValueRenderOption.FORMATTED_VALUE
`dateTimeRenderOption`	`ENUMERATION`	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 [DateTimeRenderOption.SERIAL_NUMBER]

= Parameter name
= Format

spreadsheetId STRING Required

The ID of the spreadsheet to retrieve data from

majorDimension ENUMERATION

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 will return [[1,2],[3,4]], whereas a request that sets majorDimension=COLUMNS will return [[1,3],[2,4]]

dataFilters[] OBJECT

Filter that describes what data should be selected or returned from a request

dataFilters[].developerMetadataLookup OBJECT

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

dataFilters[].developerMetadataLookup.metadataLocation OBJECT

A location where metadata may be associated in a spreadsheet

dataFilters[].developerMetadataLookup.metadataLocation.spreadsheet BOOLEAN

True when metadata is associated with an entire spreadsheet

dataFilters[].developerMetadataLookup.metadataLocation.sheetId INTEGER

The ID of the sheet when metadata is associated with an entire sheet

dataFilters[].developerMetadataLookup.metadataLocation.locationType ENUMERATION

The type of location this object represents. This field is read-only

dataFilters[].developerMetadataLookup.locationMatchingStrategy ENUMERATION

Determines how this lookup matches the location. If this field is specified as EXACT, only developer metadata associated on the exact location specified is matched. If this field is specified to INTERSECTING, developer metadata associated on intersecting locations is also matched. If left unspecified, this field assumes a default value of INTERSECTING. If this field is specified, a metadataLocation must also be specified

dataFilters[].developerMetadataLookup.locationType ENUMERATION

Limits the selected developer metadata to those entries which are associated with locations of the specified type. For example, when this field is specified as ROW this lookup only considers developer metadata associated on rows. If the field is left unspecified, all location types are considered. This field cannot be specified as SPREADSHEET when the locationMatchingStrategy is specified as INTERSECTING or when the metadataLocation is specified as a non-spreadsheet location: spreadsheet metadata cannot intersect any other developer metadata location. This field also must be left unspecified when the locationMatchingStrategy is specified as EXACT

dataFilters[].developerMetadataLookup.metadataKey STRING

Limits the selected developer metadata to that which has a matching DeveloperMetadata.metadata_key

dataFilters[].developerMetadataLookup.metadataId INTEGER

Limits the selected developer metadata to that which has a matching DeveloperMetadata.metadata_id

dataFilters[].developerMetadataLookup.visibility ENUMERATION

Limits the selected developer metadata to that which has a matching DeveloperMetadata.visibility. If left unspecified, all developer metadata visibile to the requesting project is considered

dataFilters[].developerMetadataLookup.metadataValue STRING

Limits the selected developer metadata to that which has a matching DeveloperMetadata.metadata_value

dataFilters[].a1Range STRING

Selects data that matches the specified A1 range

dataFilters[].gridRange OBJECT

A range on a sheet. 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!

dataFilters[].gridRange.startRowIndex INTEGER

The start row (inclusive) of the range, or not set if unbounded

dataFilters[].gridRange.startColumnIndex INTEGER

The start column (inclusive) of the range, or not set if unbounded

dataFilters[].gridRange.sheetId INTEGER

The sheet this range is on

dataFilters[].gridRange.endRowIndex INTEGER

The end row (exclusive) of the range, or not set if unbounded

dataFilters[].gridRange.endColumnIndex INTEGER

The end column (exclusive) of the range, or not set if unbounded

valueRenderOption ENUMERATION

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

dateTimeRenderOption ENUMERATION

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 [DateTimeRenderOption.SERIAL_NUMBER]

Output

This building block provides 10 output parameters

Name	Format	Description
`spreadsheetId`	`STRING`	The ID of the spreadsheet the data was retrieved from
`valueRanges[]`	`OBJECT`	A value range that was matched by one or more data filers
`valueRanges[].valueRange`	`OBJECT`	Data within a range of the spreadsheet
`valueRanges[].valueRange.range`	`STRING`	The range the values cover, in A1 notation. For output, this range indicates the entire requested range, even though the values will exclude trailing rows and columns. When appending values, this field represents the range to search for a table, after which values will be appended
`valueRanges[].valueRange.majorDimension`	`ENUMERATION`	The major dimension of the values. For output, if the spreadsheet data is: `A1=1,B1=2,A2=3,B2=4`, then requesting `range=A1:B2,majorDimension=ROWS` will return `[[1,2],[3,4]]`, whereas requesting `range=A1:B2,majorDimension=COLUMNS` will return `[[1,3],[2,4]]`. For input, with `range=A1:B2,majorDimension=ROWS` then `[[1,2],[3,4]]` will set `A1=1,B1=2,A2=3,B2=4`. With `range=A1:B2,majorDimension=COLUMNS` then `[[1,2],[3,4]]` will set `A1=1,B1=3,A2=2,B2=4`. When writing, if this field is not set, it defaults to ROWS
`valueRanges[].valueRange.values[]`	`OBJECT_ARRAY`
`valueRanges[].dataFilters[]`	`OBJECT`	Filter that describes what data should be selected or returned from a request
`valueRanges[].dataFilters[].developerMetadataLookup`	`OBJECT`	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
`valueRanges[].dataFilters[].a1Range`	`STRING`	Selects data that matches the specified A1 range
`valueRanges[].dataFilters[].gridRange`	`OBJECT`	A range on a sheet. 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!`

= Parameter name
= Format

spreadsheetId STRING

The ID of the spreadsheet the data was retrieved from

valueRanges[] OBJECT

A value range that was matched by one or more data filers

valueRanges[].valueRange OBJECT

Data within a range of the spreadsheet

valueRanges[].valueRange.range STRING

The range the values cover, in A1 notation. For output, this range indicates the entire requested range, even though the values will exclude trailing rows and columns. When appending values, this field represents the range to search for a table, after which values will be appended

valueRanges[].valueRange.majorDimension ENUMERATION

The major dimension of the values.

For output, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS will return [[1,2],[3,4]], whereas requesting range=A1:B2,majorDimension=COLUMNS will return [[1,3],[2,4]].

For input, with range=A1:B2,majorDimension=ROWS then [[1,2],[3,4]] will set A1=1,B1=2,A2=3,B2=4. With range=A1:B2,majorDimension=COLUMNS then [[1,2],[3,4]] will set A1=1,B1=3,A2=2,B2=4.

When writing, if this field is not set, it defaults to ROWS

valueRanges[].valueRange.values[] OBJECT_ARRAY

valueRanges[].dataFilters[] OBJECT