Method: spreadsheets.values.batchUpdate

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

HTTP request

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

The URL uses Google API HTTP annotation 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(ValueRange)
    }
  ],
  "includeValuesInResponse": boolean,
  "responseValueRenderOption": enum(ValueRenderOption),
  "responseDateTimeRenderOption": enum(DateTimeRenderOption),
}
Fields
valueInputOption

enum(ValueInputOption)

How the input data should be interpreted.

data[]

object(ValueRange)

The new values to apply to the spreadsheet.

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

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

Authorization

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 Auth Guide.

Send feedback about...