Method: spreadsheets.values.batchUpdateByDataFilter

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

HTTP request

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchUpdateByDataFilter

The URL uses gRPC Transcoding syntax.

Path parameters

Parameters
spreadsheetId

string

The ID of the spreadsheet to update.

Request body

The request body contains data with the following structure:

JSON representation
{
  "valueInputOption": enum(ValueInputOption),
  "data": [
    {
      object(DataFilterValueRange)
    }
  ],
  "includeValuesInResponse": boolean,
  "responseValueRenderOption": enum(ValueRenderOption),
  "responseDateTimeRenderOption": enum(DateTimeRenderOption)
}
Fields
valueInputOption

enum(ValueInputOption)

How the input data should be interpreted.

data[]

object(DataFilterValueRange)

The new values to apply to the spreadsheet. If more than one range is matched by the specified DataFilter the specified values will be applied to all of those ranges.

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).

responseValueRenderOption

enum(ValueRenderOption)

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

responseDateTimeRenderOption

enum(DateTimeRenderOption)

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

Response body

If successful, the response body contains data with the following structure:

The response when updating a range of values in a spreadsheet.

JSON representation
{
  "spreadsheetId": string,
  "totalUpdatedRows": number,
  "totalUpdatedColumns": number,
  "totalUpdatedCells": number,
  "totalUpdatedSheets": number,
  "responses": [
    {
      object(UpdateValuesByDataFilterResponse)
    }
  ]
}
Fields
spreadsheetId

string

The spreadsheet the updates were applied to.

totalUpdatedRows

number

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

totalUpdatedColumns

number

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

totalUpdatedCells

number

The total number of cells updated.

totalUpdatedSheets

number

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

responses[]

object(UpdateValuesByDataFilterResponse)

The response for each range updated.

Authorization Scopes

Requires one of the following OAuth scopes:

  • https://www.googleapis.com/auth/drive
  • https://www.googleapis.com/auth/drive.file
  • https://www.googleapis.com/auth/spreadsheets

For more information, see the OAuth 2.0 Overview.

DataFilterValueRange

A range of values whose location is specified by a DataFilter.

JSON representation
{
  "dataFilter": {
    object(DataFilter)
  },
  "majorDimension": enum(Dimension),
  "values": [
    array
  ]
}
Fields
dataFilter

object(DataFilter)

The data filter describing the location of the values in the spreadsheet.

majorDimension

enum(Dimension)

The major dimension of the values.

values[]

array (ListValue format)

The data to be written. If the provided values exceed any of the ranges matched by the data filter then the request will fail. If the provided values are less than the matched ranges only the specified values will be written, existing values in the matched ranges will remain unaffected.

UpdateValuesByDataFilterResponse

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

JSON representation
{
  "updatedRange": string,
  "updatedRows": number,
  "updatedColumns": number,
  "updatedCells": number,
  "dataFilter": {
    object(DataFilter)
  },
  "updatedData": {
    object(ValueRange)
  }
}
Fields
updatedRange

string

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

updatedRows

number

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

updatedColumns

number

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

updatedCells

number

The number of cells updated.

dataFilter

object(DataFilter)

The data filter that selected the range that was updated.

updatedData

object(ValueRange)

The values of the cells in the range matched by the dataFilter after all updates were applied. This is only included if the request's includeValuesInResponse field was true.

Send feedback about...

Need help? Visit our support page.