Google Sheets API
  1. spreadsheets
Google Sheets API
  • spreadsheets
    • /v4/spreadsheets
      POST
    • /v4/spreadsheets/{spreadsheetId}
      GET
    • /v4/spreadsheets/{spreadsheetId}/developerMetadata/{metadataId}
      GET
    • /v4/spreadsheets/{spreadsheetId}/developerMetadata:search
      POST
    • /v4/spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo
      POST
    • /v4/spreadsheets/{spreadsheetId}/values/{range}
      GET
    • /v4/spreadsheets/{spreadsheetId}/values/{range}
      PUT
    • /v4/spreadsheets/{spreadsheetId}/values/{range}:append
      POST
    • /v4/spreadsheets/{spreadsheetId}/values/{range}:clear
      POST
    • /v4/spreadsheets/{spreadsheetId}/values:batchClear
      POST
    • /v4/spreadsheets/{spreadsheetId}/values:batchClearByDataFilter
      POST
    • /v4/spreadsheets/{spreadsheetId}/values:batchGet
      GET
    • /v4/spreadsheets/{spreadsheetId}/values:batchGetByDataFilter
      POST
    • /v4/spreadsheets/{spreadsheetId}/values:batchUpdate
      POST
    • /v4/spreadsheets/{spreadsheetId}/values:batchUpdateByDataFilter
      POST
    • /v4/spreadsheets/{spreadsheetId}:batchUpdate
      POST
    • /v4/spreadsheets/{spreadsheetId}:getByDataFilter
      POST
  1. spreadsheets

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

POST
/v4/spreadsheets/{spreadsheetId}/values:batchUpdateByDataFilter
spreadsheets
Sets values in one or more ranges of a spreadsheet. The caller must specify the spreadsheet ID, a valueInputOption, and one or more DataFilterValueRanges.
Request Request Example
Shell
JavaScript
Java
Swift
curl --location --request POST 'https://sheets.googleapis.com//v4/spreadsheets//values:batchUpdateByDataFilter' \
--header 'Content-Type: application/json' \
--data-raw '{
    "data": [
        {
            "dataFilter": {
                "a1Range": "string",
                "developerMetadataLookup": {},
                "gridRange": {}
            },
            "majorDimension": "DIMENSION_UNSPECIFIED",
            "values": [
                [
                    null
                ]
            ]
        }
    ],
    "includeValuesInResponse": true,
    "responseDateTimeRenderOption": "SERIAL_NUMBER",
    "responseValueRenderOption": "FORMATTED_VALUE",
    "valueInputOption": "INPUT_VALUE_OPTION_UNSPECIFIED"
}'
Response Response Example
{
    "responses": [
        {
            "dataFilter": {
                "a1Range": "string",
                "developerMetadataLookup": {},
                "gridRange": {}
            },
            "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
}

Request

Path Params
spreadsheetId
string 
required
The ID of the spreadsheet to update.
Body Params application/json
The request for updating more than one range of values in a spreadsheet.
data
array[object (DataFilterValueRange) {3}] 
optional
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.
dataFilter
object (DataFilter) 
optional
The data filter describing the location of the values in the spreadsheet.
majorDimension
enum<string> 
optional
The major dimension of the values.
Allowed values:
DIMENSION_UNSPECIFIEDROWSCOLUMNS
values
array [array] 
optional
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.
includeValuesInResponse
boolean 
optional
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).
responseDateTimeRenderOption
enum<string> 
optional
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.
Allowed values:
SERIAL_NUMBERFORMATTED_STRING
responseValueRenderOption
enum<string> 
optional
Determines how values in the response should be rendered. The default render option is FORMATTED_VALUE.
Allowed values:
FORMATTED_VALUEUNFORMATTED_VALUEFORMULA
valueInputOption
enum<string> 
optional
How the input data should be interpreted.
Allowed values:
INPUT_VALUE_OPTION_UNSPECIFIEDRAWUSER_ENTERED
Examples

Responses

🟢200Successful response
application/json
Body
The response when updating a range of values in a spreadsheet.
responses
array[object (UpdateValuesByDataFilterResponse) {6}] 
optional
The response for each range updated.
dataFilter
object (DataFilter) 
optional
The data filter that selected the range that was updated.
updatedCells
integer <int32>
optional
The number of cells updated.
updatedColumns
integer <int32>
optional
The number of columns where at least one cell in the column was updated.
updatedData
object (ValueRange) 
optional
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.
updatedRange
string 
optional
The range (in A1 notation) that updates were applied to.
updatedRows
integer <int32>
optional
The number of rows where at least one cell in the row was updated.
spreadsheetId
string 
optional
The spreadsheet the updates were applied to.
totalUpdatedCells
integer <int32>
optional
The total number of cells updated.
totalUpdatedColumns
integer <int32>
optional
The total number of columns where at least one cell in the column was updated.
totalUpdatedRows
integer <int32>
optional
The total number of rows where at least one cell in the row was updated.
totalUpdatedSheets
integer <int32>
optional
The total number of sheets where at least one cell in the sheet was updated.
Previous
/v4/spreadsheets/{spreadsheetId}/values:batchUpdate
Next
/v4/spreadsheets/{spreadsheetId}:batchUpdate
Built with