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

POST

spreadsheets

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

Request Example

Shell

JavaScript

Java

Swift

curl --location --request POST 'https://sheets.googleapis.com//v4/spreadsheets//values:batchUpdate' \
--header 'Content-Type: application/json' \
--data-raw '{
    "data": [
        {
            "majorDimension": "DIMENSION_UNSPECIFIED",
            "range": "string",
            "values": [
                [
                    null
                ]
            ]
        }
    ],
    "includeValuesInResponse": true,
    "responseDateTimeRenderOption": "SERIAL_NUMBER",
    "responseValueRenderOption": "FORMATTED_VALUE",
    "valueInputOption": "INPUT_VALUE_OPTION_UNSPECIFIED"
}'

Response Example

{
    "responses": [
        {
            "spreadsheetId": "string",
            "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 (ValueRange) {3}]

optional

The new values to apply to the spreadsheet.

majorDimension

enum<string>

optional

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.

Allowed values:

DIMENSION_UNSPECIFIEDROWSCOLUMNS

range

string

optional

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.

values

array [array]

optional

The data that was read or to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell. For output, empty trailing rows and columns will not be included. For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string.

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 (UpdateValuesResponse) {6}]

optional

One UpdateValuesResponse per requested range, in the same order as the requests appeared.

spreadsheetId

string

optional

The spreadsheet the updates were applied to.

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

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

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