Sheets.spreadsheets.values.batch Update By Data Filter

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

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

Use it in a workflow

Add a step and choose the Google Sheets connector.
Pick the Sheets.spreadsheets.values.batch Update By Data Filter action (under spreadsheets).
Fill in the fields below, then reference the result from later steps as {{sheetsSpreadsheetsValuesBatchUpdateByDataFilter.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.

dataarray<object>

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

dataFilterobject3 fields

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

majorDimensionstring

The major dimension of the values.

One ofDIMENSION_UNSPECIFIEDROWSCOLUMNS

valuesarray<array<any>>

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

includeValuesInResponseboolean

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 contains the updated values. If the range to write was larger than the range actually written, the response includes all values in the requested range (excluding trailing empty rows and columns).

responseDateTimeRenderOptionstring

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

One ofSERIAL_NUMBERFORMATTED_STRING

responseValueRenderOptionstring

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

One ofFORMATTED_VALUEUNFORMATTED_VALUEFORMULA

valueInputOptionstring

How the input data should be interpreted.

One ofINPUT_VALUE_OPTION_UNSPECIFIEDRAWUSER_ENTERED

json

{
  "data": [
    {
      "dataFilter": {
        "a1Range": "string",
        "developerMetadataLookup": {
          "locationMatchingStrategy": "DEVELOPER_METADATA_LOCATION_MATCHING_STRATEGY_UNSPECIFIED",
          "locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
          "metadataId": 0,
          "metadataKey": "string",
          "metadataLocation": {
            "dimensionRange": {
              "dimension": null,
              "endIndex": null,
              "sheetId": null,
              "startIndex": null
            },
            "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
        }
      },
      "majorDimension": "DIMENSION_UNSPECIFIED",
      "values": [
        [
          null
        ]
      ]
    }
  ],
  "includeValuesInResponse": false,
  "responseDateTimeRenderOption": "SERIAL_NUMBER",
  "responseValueRenderOption": "FORMATTED_VALUE",
  "valueInputOption": "INPUT_VALUE_OPTION_UNSPECIFIED"
}

Response

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

responsesarray<object>

The response for each range updated.

dataFilterobject3 fields

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

updatedCellsinteger (int32)

The number of cells updated.

updatedColumnsinteger (int32)

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

updatedDataobject3 fields

Data within a range of the spreadsheet.

updatedRangestring

The range (in [A1 notation](/sheets/api/guides/concepts#cell)) that updates were applied to.

updatedRowsinteger (int32)

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

spreadsheetIdstring

The spreadsheet the updates were applied to.

totalUpdatedCellsinteger (int32)

The total number of cells updated.

totalUpdatedColumnsinteger (int32)

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

totalUpdatedRowsinteger (int32)

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

totalUpdatedSheetsinteger (int32)

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

json

{
  "responses": [
    {
      "dataFilter": {
        "a1Range": "string",
        "developerMetadataLookup": {
          "locationMatchingStrategy": "DEVELOPER_METADATA_LOCATION_MATCHING_STRATEGY_UNSPECIFIED",
          "locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
          "metadataId": 0,
          "metadataKey": "string",
          "metadataLocation": {
            "dimensionRange": {
              "dimension": null,
              "endIndex": null,
              "sheetId": null,
              "startIndex": null
            },
            "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
        }
      },
      "updatedCells": 0,
      "updatedColumns": 0,
      "updatedData": {
        "majorDimension": "DIMENSION_UNSPECIFIED",
        "range": "string",
        "values": [
          [
            null
          ]
        ]
      },
      "updatedRange": "string",
      "updatedRows": 0
    }
  ],
  "spreadsheetId": "string",
  "totalUpdatedCells": 0,
  "totalUpdatedColumns": 0,
  "totalUpdatedRows": 0,
  "totalUpdatedSheets": 0
}

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

Was this page helpful?