Aside from the data contained in its cells, a spreadsheet includes many other types of data, such as:
- Cell formats
- Cell borders
- Named ranges
- Protected ranges
- Conditional formatting
These are just some of the many kinds of data that control the appearance and
operation of a spreadsheet. The batchUpdate
method lets you update any of
these spreadsheet details. Changes are grouped together in a batch so that if
one request fails, none of the other (potentially dependent) changes is written.
Categories of operation
The particular operations supported by batchUpdate can be grouped into the following broad categories:
Category | Description |
---|---|
Add (and Duplicate) | Add new objects (sometimes based on old ones, as in the Duplicate requests). |
Update (and Set) | Update certain properties of an object, usually leaving the old properties alone (whereas Set requests will overwrite the prior data). |
Delete | Remove objects. |
These categories are used in the next section to describe the behavior of specific operations.
Batch update operations
The batchUpdate method works by taking one or more Request objects, each one specifying a single kind of request to perform. There are many different kinds of requests. Here's a breakdown on the types of requests, grouped into different categories.
There are also some additional requests that mimic user actions for manipulating data:
- AutoFillRequest
- CutPasteRequest
- CopyPasteRequest
- FindReplaceRequest
- PasteDataRequest
- TextToColumnsRequest
- SortRangeRequest
Field masks
Many of the "Update" requests require field masks. These are a comma-delimited list of fields that you want to update. The mask is required to make sure only fields you want to edit are updated. You can use a "*" as short-hand for updating every field (which means a field may revert to its default state if you don't specify a value for it in the request).
For example, to update just the Title of a spreadsheet, this would be the request:
Request:
POST .../v4/spreadsheets/spreadsheetId:batchUpdate
Request body:
{
"requests": [{
"updateSpreadsheetProperties": {
"properties": {"title": "My New Title"},
"fields": "title"
}
}]
}
Responses
When updating a spreadsheet, some kinds of requests may return responses. These are returned in an array, with each response occupying the same index as the corresponding request. Some requests do not have responses. For those requests, the response will be empty.
Typically the "Add" requests will have responses, so that you know information (such as the ID) of the newly added object. See Response for the list of supported responses.
Example
The example below performs the following actions:
- Updates the spreadsheet's title using the
title
variable. - Finds and replaces cell values in the spreadsheet using the
find
andreplacement
variables.