Update spreadsheets (original) (raw)
Aside from the value data contained in its cells, aspreadsheet includes many other types of data, such as:
- Dimensions
- Cell formats and borders
- Named ranges
- Protected ranges
- Conditional formatting
These are some of the many kinds of data that control the appearance and operation of a spreadsheet. The spreadsheets.batchUpdate method lets you update any of these spreadsheet details. Changes are grouped in a batch so that if one request is unsuccessful, none of the other (potentially dependent) changes are written.
This page describes the basics of using thespreadsheets.batchUpdatemethod. If you need to read and write cell value data, you can also use thespreadsheets.valuesresource described inRead & write cell values.
Categories of operation
The particular operations supported by spreadsheets.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 a Set request overwrites 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 spreadsheets.batchUpdate method works by taking one or moreRequestobjects, 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
Limits
To learn more about cell and row limits in Google Sheets, seeFiles you can store in Google Drive.
Field masks
Many of the "update" requests require field masks. These are a comma-delimited list of fields to update only certain fields in an object while leaving the other fields unchanged. A field mask of * is treated like a wildcard and is shorthand for specifying every field in a message (which means a field may revert to its default state if you don't specify a value for it in the request). For more information about field masks, see Use field masks.
The following sample uses theUpdateSpreadsheetPropertiesRequestto update only the title of a spreadsheet:
Request:
POST .../v4/spreadsheets/spreadsheetId:batchUpdate
Request body:
{
"requests": [{
"updateSpreadsheetProperties": {
"properties": {"title": "TITLE"},
"fields": "title"
}
}]
}
Replace TITLE with the new title of the spreadsheet.
Responses
When updating a spreadsheet, some kinds of requests might return responses. These are returned in an array, with each response occupying the same index as the corresponding request. Some requests don't have responses and for those the response is empty.
Typically, "add" requests have responses that return information such as the ID of the added object. For the list of supported responses, seeResponses.
Example
The following code sample performs these actions:
- Updates the spreadsheet's title using the
titlevariable. - Finds and replaces cell values in the spreadsheet using the
findandreplacementvariables.