Batch Update By Data Filter

Sets values in one or more ranges of a spreadsheet

23 variables
29 variables

Sets values in one or more ranges of a spreadsheet. The caller must specify the spreadsheet ID, a valueInputOption, and one or more DataFilterValueRanges

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

  = Parameter name
  = Format

spreadsheetId STRING Required

The ID of the spreadsheet to update

responseValueRenderOption ENUMERATION

Determines how values in the response should be rendered. The default render option is ValueRenderOption.FORMATTED_VALUE

includeValuesInResponse BOOLEAN

Determines if the update response should include the values of the cells that were updated. By default, responses do not include the updated values. The updatedData field within each of the BatchUpdateValuesResponse.responses will contain the updated values. If the range to write was larger than than the range actually written, the response will include all values in the requested range (excluding trailing empty rows and columns)

valueInputOption ENUMERATION

How the input data should be interpreted

data[] OBJECT

A range of values whose location is specified by a DataFilter

data[].majorDimension ENUMERATION

The major dimension of the values

data[].values[] OBJECT_ARRAY

data[].dataFilter OBJECT

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

data[].dataFilter.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

data[].dataFilter.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

data[].dataFilter.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

data[].dataFilter.developerMetadataLookup.metadataKey STRING

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

data[].dataFilter.developerMetadataLookup.metadataId INTEGER

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

data[].dataFilter.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

data[].dataFilter.developerMetadataLookup.metadataValue STRING

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

data[].dataFilter.a1Range STRING

Selects data that matches the specified A1 range

data[].dataFilter.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!

data[].dataFilter.gridRange.startRowIndex INTEGER

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

data[].dataFilter.gridRange.startColumnIndex INTEGER

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

data[].dataFilter.gridRange.sheetId INTEGER

The sheet this range is on

data[].dataFilter.gridRange.endRowIndex INTEGER

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

data[].dataFilter.gridRange.endColumnIndex INTEGER

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

responseDateTimeRenderOption ENUMERATION

Determines how dates, times, and durations in the response should be rendered. This is ignored if response_value_render_option is FORMATTED_VALUE. The default dateTime render option is DateTimeRenderOption.SERIAL_NUMBER

Output

This building block provides 29 output parameters

  = Parameter name
  = Format

totalUpdatedCells INTEGER

The total number of cells updated

totalUpdatedColumns INTEGER

The total number of columns where at least one cell in the column was updated

spreadsheetId STRING

The spreadsheet the updates were applied to

totalUpdatedRows INTEGER

The total number of rows where at least one cell in the row was updated

responses[] OBJECT

The response when updating a range of values by a data filter in a spreadsheet

responses[].updatedCells INTEGER

The number of cells updated

responses[].dataFilter OBJECT

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

responses[].dataFilter.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

responses[].dataFilter.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

responses[].dataFilter.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

responses[].dataFilter.developerMetadataLookup.metadataKey STRING

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

responses[].dataFilter.developerMetadataLookup.metadataId INTEGER

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

responses[].dataFilter.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

responses[].dataFilter.developerMetadataLookup.metadataValue STRING

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

responses[].dataFilter.a1Range STRING

Selects data that matches the specified A1 range

responses[].dataFilter.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!

responses[].dataFilter.gridRange.startRowIndex INTEGER

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

responses[].dataFilter.gridRange.startColumnIndex INTEGER

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

responses[].dataFilter.gridRange.sheetId INTEGER

The sheet this range is on

responses[].dataFilter.gridRange.endRowIndex INTEGER

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

responses[].dataFilter.gridRange.endColumnIndex INTEGER

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

responses[].updatedData OBJECT

Data within a range of the spreadsheet

responses[].updatedData.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

responses[].updatedData.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

responses[].updatedData.values[] OBJECT_ARRAY

responses[].updatedRows INTEGER

The number of rows where at least one cell in the row was updated

responses[].updatedColumns INTEGER

The number of columns where at least one cell in the column was updated

responses[].updatedRange STRING

The range (in A1 notation) that updates were applied to

totalUpdatedSheets INTEGER

The total number of sheets where at least one cell in the sheet was updated