If you have existing apps based on the Google Sheets API v3, you can migrate to Google Sheets API v4. The v4 version is JSON-based, has an easier-to-use interface, and adds a substantial amount of functionality that is not possible in the v3 version.
This page provides a mapping between the older Sheets API v3 commands and their equivalent operations in Sheets API v4. The mapping focuses largely on the spreadsheets.values collection, which provides direct cell read and write functionality. Other aspects, such as adding sheets or updating sheet properties are handled by the spreadsheets collection. Note that the v4 API's JSON structures are not backward-compatible with the XML structures used in v3.
For more information about the resources available in the Sheets v4 API, see the API Reference.
Notation and terms
The v3 API refers to sheets within a particular spreadsheet as "worksheets". This is synonymous with the term "sheets" that the v4 API uses.
The APIs often require you to specify a spreadsheet ID of the spreadsheet you are working with. They also often require the ID of the sheet being manipulated. These values appear either as part of the API endpoint URL, as query parameters, or as part of a request body. In this page, the placeholders spreadsheetId and sheetId refer to the spreadsheet and sheet IDs, respectively. When using the methods described on this page, substitute in the actual IDs in these locations.
The v3 API also assigns an ID to rows retrieved using its list feed; this is represented in this page by the rowId placeholder.
Authorize requests
When your app runs, it asks users to grant certain permissions; the scopes that you specify in your application determine which permissions it asks for.
v3 API
The Sheets API v3 operates with a single authorization scope:
https://spreadsheets.google.com/feeds
which is an alias for
https://www.googleapis.com/auth/spreadsheets
Either scope format can be used.
v4 API
The Sheets API v4 uses one or more of the following set of scopes:
https://www.googleapis.com/auth/spreadsheets.readonly https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive.readonly https://www.googleapis.com/auth/drive
Use read-only scopes if the your application does not need to make edits to a user's sheets or sheet properties. Use spreadsheets scopes instead of Drive scopes if the application does not need general Drive access.
Visibility
In older versions of the API, the term visibility is used to refer to the availability of a given spreadsheet.
v3 API
The Sheets API v3 expresses visibility directly in its endpoints. A public
spreadsheet has been "Published to the Web" and thus can be accessed by the
API without authorization, while a private
spreadsheet does require
authentication. Visibility is specified in the endpoint after the
spreadsheet ID:
https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full
v4 API
In the new Sheets API v4, there is no explicit declaration of visibility. API calls are made using spreadsheet IDs. If the application does not have permission to access specified spreadsheet, an error is returned. Otherwise the call proceeds.
Projection
The term projection is used by the Sheets API v3 to refer to the set of data that is returned by a given API call—either all of it, or a fixed subset defined within the API. The Sheets API v4 does not use projection; rather, it allows you more control over what data is returned.
v3 API
There are only two possible projection settings in the Sheets API v3. full
projection returns all the available information, whereas basic
returns a
smaller, fixed subset of data (for the worksheets, list, and cells feeds).
Like visibility, the projection must be specified in the API endpoint
(after the visibility setting):
https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/public/basic
The smaller subset of data provided by the basic
projection is valuable
for making code more efficient, but cannot be customized.
v4 API
While the Sheets API v4 can return a full data set, it does not define fixed
subsets analogous to the basic
visibility setting the Sheets API v3.
Methods in the spreadsheet
collection restrict the amount of data they return through the use of
a fields query parameter.
For example, the following query only returns the titles of all the sheets in a particular spreadsheet:
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?fields=sheets.properties.title
Create a spreadsheet
v3 API
The Sheets API v3 does not provide a means to create new spreadsheets;
instead, the Drive API Files.create
method can be used to create new spreadsheet files. This requires the
application to declare the https://www.googleapis.com/auth/drive
scope.
v4 API
The Drive API Files.create method can
also be used with the Sheets API v4, but requires the application to provide
the https://www.googleapis.com/auth/drive
scope.
As an equivalent alternative, the Sheets API v4 provides a spreadsheets.create method, which can also optionally add sheets, set the spreadsheet and sheet properties, and add named ranges. For example, the following creates a new spreadsheet and gives it the name "NewTitle":
POST https://sheets.googleapis.com/v4/spreadsheets
{ "properties": {"title": "NewTitle"} }
List spreadsheets for the authenticated user
v3 API
The Sheets API v3 feed allows an application to retrieve a list of all the spreadsheets accessible by the authenticated user. The spreadsheet feed endpoint is:
GET https://spreadsheets.google.com/feeds/spreadsheets/private/full
v4 API
The Sheets API v4 does not provide this specific operation. We recommend migrating your app to use the drive.file scope in combination with the Google Picker for spreadsheet selection.
In the cases where listing spreadsheets is required, it can be replicated
via the Drive API Files.list method, using
a mimeType
query:
GET https://www.googleapis.com/drive/v3/files ?q=mimeType='application/vnd.google-apps.spreadsheet'
Using the Drive API files.list method to list all of a user’s spreadsheets requires a restricted scope.
Retrieve sheet metadata
The Sheets API v3 provides a feed to access the sheet metadata contained within a given spreadsheet (row and cell data is accessed through a separate feed). The metadata includes information such as sheet titles and size information.
The Sheets API v4 spreadsheets.get method provides access to this information, and much more.
v3 API
The worksheet feed is accessible from this API endpoint (using an appropriate authorization header):
GET https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full
The response to this request has a structure similar to this, with
each sheet's data contained in a separate <entry>
:
<feed xmlns="http://www.w3.org/2005/Atom"
xmlns:openSearch="http://a9.com/-/spec/opensearch/1.1/"
xmlns:gs="http://schemas.google.com/spreadsheets/2006"
xmlns:gd="http://schemas.google.com/g/2005"
gd:etag='W/"D0cERnk-eip7ImA9WBBXGEg."'>
<id>https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full</id>
<updated>2006-11-17T18:23:45.173Z</updated>
<title type="text">Groceries R Us</title>
<link rel="alternate" type="text/html"
href="https://spreadsheets.google.com/ccc?key=spreadsheetId"/>
<link rel="http://schemas.google.com/g/2005#feed"
type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full"/>
<link rel="self" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full"/>
<link rel="http://schemas.google.com/g/2005#post" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full"/>
<author>
<name>Fitzwilliam Darcy</name>
<email>fitz@example.com</email>
</author>
<openSearch:totalResults>1</openSearch:totalResults>
<openSearch:startIndex>1</openSearch:startIndex>
<openSearch:itemsPerPage>1</openSearch:itemsPerPage>
<entry gd:etag='"YDwqeyI."'>
<id>https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId</id>
<updated>2006-11-17T18:23:45.173Z</updated>
<title type="text">Sheet1</title>
<content type="text">Sheet1</content>
<link rel="http://schemas.google.com/spreadsheets/2006#listfeed"
type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full"/>
<link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed"
type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full"/>
<link rel="self" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId"/>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId/version"/>
<gs:rowCount>100</gs:rowCount>
<gs:colCount>20</gs:colCount>
</entry>
</feed>
v4 API
The spreadsheets.get
method can be used to acquire sheet properties and other metadata—much
more than what is available using the Sheets API v3. If you only
want to read the sheet properties, set the includeGridData
query
parameter to false
to prevent the inclusion of the spreadsheet cell data:
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?includeGridData=false
The Spreadsheet
response contains an array of Sheet
objects; the sheet titles and size information specifically can be found
under the SheetProperties
element of these objects. For example:
{ "spreadsheetId": spreadsheetId, "sheets": [ {"properties": { "sheetId": sheetId, "title": "Sheet1", "index": 0, "gridProperties": { "rowCount": 100, "columnCount": 20, "frozenRowCount": 1, "frozenColumnCount": 0, "hideGridlines": false }, ... }, ... }, ... ], ... }
Add a sheet to a spreadsheet
Both APIs allow you to add new sheets to an existing spreadsheet.
v3 API
The Sheets API v3 can add new worksheets to a spreadsheet by making the
following (authenticated) POST
request. You can specify the size of the
new sheet:
POST https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full
<entry xmlns="http://www.w3.org/2005/Atom"
xmlns:gs="http://schemas.google.com/spreadsheets/2006">
<title>Expenses</title>
<gs:rowCount>50</gs:rowCount>
<gs:colCount>10</gs:colCount>
</entry>
v4 API
You can add new sheets by making an AddSheet request in the spreadsheets.batchUpdate method. As part of request body, you can specify the sheet properties for the new sheet; all properties are optional. It is an error to provide a title that is used for an existing sheet.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{ "requests": [{ "addSheet": { "properties": { "title": "Expenses", "sheetType": "GRID", "gridProperties": { "rowCount": 50, "columnCount": 10 } } } }], }
Change a sheet title and size
The Sheets API v3 lets you update sheet titles and size; the Sheets API v4 allows this as well, but can also be used to update other sheet properties. Note that reducing a sheet's size may cause data in the cropped cells to be deleted without warning.
v3 API
To change the title or size of a worksheet, begin by retrieving the
worksheet feed and
finding the desired worksheet entry, which contains an edit
URL.
Update the worksheet's metadata and send it as the body of a PUT
request
to the edit URL. For example:
PUT https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId/version
<entry>
<id>
https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId
</id>
<updated>2007-07-30T18:51:30.666Z</updated>
<category scheme="http://schemas.google.com/spreadsheets/2006"
term="http://schemas.google.com/spreadsheets/2006#worksheet"/>
<title type="text">Expenses</title>
<content type="text">Expenses</content>
<link rel="http://schemas.google.com/spreadsheets/2006#listfeed"
type="application/atom+xml" href="https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full"/>
<link rel="http://schemas.google.com/spreadsheets/2006#cellsfeed"
type="application/atom+xml" href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full"/>
<link rel="self" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId"/>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId/version"/>
<gs:rowCount>45</gs:rowCount>
<gs:colCount>15</gs:colCount>
</entry>
v4 API
To update the size, title, and other sheet properties, make an
updateSheetProperties
request in the
spreadsheets.batchUpdate
method. The POST
request body should contain the properties to be
changed, and the fields
parameter should explicitly list those properties
(if you want to update all properties, use fields:"*"
as a shorthand for
listing them all). For
example, the following specifies that the sheet title and size
properties should be updated for the sheet with the given ID:
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{ "requests": [ { "updateSheetProperties": { "properties": { "sheetId": sheetId, "title": "Expenses", "gridProperties": { "rowCount": 45, "columnCount": 15, } }, "fields": "title,gridProperties(rowCount,columnCount)" } } ], }
To retrieve a sheet's sheetId, use the spreadsheet spreadsheets.get method.
Delete a sheet
Either API can remove sheets from a given spreadsheet.
v3 API
To delete a worksheet, begin by retrieving the
worksheet feed, then
send a DELETE
request on the edit
URL of the target worksheet entry.
DELETE https://spreadsheets.google.com/feeds/worksheets/spreadsheetId/private/full/sheetId/version
v4 API
To delete a sheet, make a
DeleteSheet
request in the
spreadsheets.batchUpdate
method. The POST
request body should only contain the sheetId for the
sheet to be deleted. For example:
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{ "requests": [ { "deleteSheet": { "sheetId": sheetId } } ], }
To retrieve an individual sheet's sheetId, use the spreadsheet spreadsheets.get method.
Retrieve row data
The list rows feed is one of the two methods the Sheets API v3 provides to access data within a spreadsheet's cells (the other being the cells feed). The rows feed is meant to support common spreadsheet operations (reading row by row, appending rows, sorting), but makes certain assumptions that make it unsuitable for some tasks. Specifically, the list feed assumes that blank rows are feed terminations, and that mandatory headers are present in the first row of a sheet.
In contrast, the Sheets API v4 does not use access methods that are row-specific. Instead, sheet cell data is accessed by referencing the specific ranges required using A1 notation. The ranges can be blocks of cells, entire rows, entire columns, or entire sheets. The API can also access disjoint sets of cells.
v3 API
To determine the URL of a list-based feed for a given worksheet, retrieve the worksheet feed and find the list feed URL in the worksheet entry of interest.
To retrieve a list-based feed, send a GET
request to the list feed URL,
using an appropriate authorization header. For example:
GET https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full
The response to this request contain, among other things, entries corresponding to specific rows. Individual cells are referenced by the names provided in the (mandatory) sheet header row. For example, here is a single row entry:
<entry gd:etag='"S0wCTlpIIip7ImA0X0QI"'>
<id>rowId</id>
<updated>2006-11-17T18:23:45.173Z</updated>
<category scheme="http://schemas.google.com/spreadsheets/2006"
term="http://schemas.google.com/spreadsheets/2006#list"/>
<title type="text">Bingley</title>
<content type="text">Hours: 10, Items: 2, IPM: 0.0033</content>
<link rel="self" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full/rowId"/>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full/rowId/version"/>
<gsx:name>Bingley</gsx:name>
<gsx:hours>10</gsx:hours>
<gsx:items>2</gsx:items>
<gsx:ipm>0.0033</gsx:ipm>
</entry>
By default the rows returned in the list feed are returned in row order. The Sheets API v3 provides query parameters to change that order.
Reverse order:
GET https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full?reverse=true
Order by a specific column:
GET https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full ?orderby=column:lastname
The Sheets API v3 also allows filtering of specific rows via a structured query (referenced by column headers):
GET https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full ?sq=age>25%20and%20height<175
v4 API
With the Sheets API v4, rows can be retrieved by range using the spreadsheets.values.get or spreadsheets.values.batchGet methods. For example, the following returns all rows in "Sheet1":
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1
The response to this request has a structure similar to:
{ "range": "Sheet1", "majorDimension": "ROWS", "values": [["Name", "Hours", "Items", "IPM"], ["Bingley", "10", "2", "0.0033"], ["Darcy", "14", "6", "0.0071"]] }
Trailing empty cells are not included in the response when retrieving entire rows, columns, or sheets.
The Sheets API v4 does not have equivalents for the row-order query
parameters provided by the Sheets API v3. Reverse-order is trivial; simply
process the returned values
array in reverse order. Order by column is not
supported for reads, but it is possible to sort the data in the sheet (using
a SortRange)
request and then read it.
The Sheets API v4 does not currently have a direct equivalent for the Sheets API v3 structured queries. However, you can retrieve the relevant data and sort through it as needed in your application.
Add a new row of data
You can add a new row of data to a sheet using either API.
v3 API
To determine the URL of a list-based feed for a given worksheet, retrieve the worksheet feed and find the post URL in the worksheet entry of interest.
To add a row of data, send a POST
request to the post URL,
using an appropriate authorization header. For example:
POST https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full
The body of the POST
request should contain an entry for the row data to
add, with individual cells referenced by column headers:
<entry xmlns="http://www.w3.org/2005/Atom"
xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">
<gsx:hours>2</gsx:hours>
<gsx:ipm>0.5</gsx:ipm>
<gsx:items>60</gsx:items>
<gsx:name>Elizabeth</gsx:name>
</entry>
New rows are appended to the end of the specified sheet.
v4 API
With the Sheets API v4, you can append rows using the spreadsheets.values.append method. The following example writes a new row of data below the last table in "Sheet1" of a spreadsheet.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/append/Sheet1
{ "values": [["Elizabeth", "2", "0.5", "60"]] }
Additionally, the Sheets API v4 also lets you append cells with specific properties and formatting using the AppendCells requests in a spreadsheets.batchUpdate.
Edit a row with new data
Both APIs allow row data to be updated with new values.
v3 API
To edit a row of data, examine the list feed to locate the entry for the row you wish to update. Update the contents of that entry as needed. Be sure that the ID value in the entry you use exactly matches the ID of the existing entry.
Once the entry has been updated, send a PUT
request with the entry as the
request body to the edit
URL provided in that row entry,
using an appropriate authorization header. For example:
PUT https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full/rowId/version
<entry gd:etag='"S0wCTlpIIip7ImA0X0QI"'>
<id>rowId</id>
<updated>2006-11-17T18:23:45.173Z</updated>
<category scheme="http://schemas.google.com/spreadsheets/2006"
term="http://schemas.google.com/spreadsheets/2006#list"/>
<title type="text">Bingley</title>
<content type="text">Hours: 10, Items: 2, IPM: 0.0033</content>
<link rel="self" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full/rowId"/>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full/rowId/version"/>
<gsx:name>Bingley</gsx:name>
<gsx:hours>20</gsx:hours>
<gsx:items>4</gsx:items>
<gsx:ipm>0.0033</gsx:ipm>
</entry>
v4 API
With Sheets API v4, you can edit a row using the A1 notation of the row you wish to edit and issuing a spreadsheets.values.update request to overwrite that row. The range specified need only refer to the first cell in the row; the API infers the cells to update based on the values provided with the request. If you instead specify a multi-cell range, the values you provide must fit within that range; if not the API returns an error.
The following example request and request body adds data to the fourth row of "Sheet1":
PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A4
{ "values": [["Elizabeth", "2", "0.5", "60"]] }
You can also update row data from the spreadsheet.values.batchUpdate method; it is more efficient to use this method if you are making multiple row or cell updates.
Additionally, the Sheets API v4 also lets you edit the cell properties and formatting of cells using the UpdateCells or RepeatCell requests in a spreadsheets.batchUpdate.
Delete a row
Both APIs support the deletion of rows. A deleted row is removed from the spreadsheet, and rows below it are pushed up one.
v3 API
To delete a row, first retrieve the row to delete from the
list feed,
then send a DELETE
request to the edit
URL provided in the row's entry.
This is the same URL used to update the row.
DELETE https://spreadsheets.google.com/feeds/list/spreadsheetId/sheetId/private/full/rowId/version
If you want to make sure that you don't delete a row that has been changed by another client since you retrieved it, include an HTTP If-Match header that contains the original row's ETag value. You can determine the original row's ETag value by examining the entry element's gd:etag attribute.
If you want to delete the row regardless of whether someone else has updated it since you retrieved it, then use If-Match: * and don't include the ETag. (In this case, you don't need to retrieve the row before deleting it.)
v4 API
Deleting rows with the Sheets API v4 is handled by a spreadsheet.batchUpdate method call, using a DeleteDimension request. This request can also be used to remove columns, and developers and choose to only remove part of a row or column. For example, the following removes the 6th row of a sheet with the given ID (the row indices are zero-based, with the startIndex inclusive and endIndex exclusive):
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{ "requests": [ { "deleteDimension": { "range": { "sheetId": sheetId, "dimension": "ROWS", "startIndex": 5, "endIndex": 6 } } } ], }
A sheet's sheetId can be retrieved using the spreadsheet.get method.
Retrieve cell data
The Sheets API v3 provides a cell feed for basic access to all data stored in a
spreadsheet. For read access, the cell feed can provide the entire sheet
content or a range of the sheet's cells defined by a set of query parameters,
but only as a single block—disjoint ranges have to be retrieved
separately using additional GET
requests.
The Sheets API v4 can retrieve any set of cell data from a sheet (including multiple disjoint ranges). The Sheets API v3 can only return cell contents as input values (as would be entered by a user at a keyboard) and/or the outputs of formula (if numeric); the Sheets API v4 grants full access to values, formulas, formatting, hyperlinks, data validation, and other properties.
v3 API
To determine the URL of a cell-based feed for a given worksheet, examine the worksheet feed and find the cell feed URL in the worksheet entry of interest.
To retrieve a cell-based feed, send a GET
request to the cell feed URL,
using an appropriate authorization header. For example:
GET https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full
Cells are referenced using row and column number. Fetching a single specific
range can be done by using max-row
, min-row
, max-col
and min-col
query parameters. For example, the following retrieves all cells in column
4 (D), starting with row 2:
GET https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full ?min-row=2&min-col=4&max-col=4
The Sheets API v3 returns the inputValue
of retrieved cells—the
value that a user would otherwise type into the Google Sheets user
interface to manipulate the cell. The inputValue
can be a literal value
or a formula. The API also sometimes returns a numericValue
; for example,
when a formula results in a number. For example, a response may include cell
entries similar in structure to the following:
<entry gd:etag='"ImB5CBYSRCp7"'>
<id>https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R9C4</id>
<updated>2006-11-17T18:27:32.543Z</updated>
<category scheme="http://schemas.google.com/spreadsheets/2006"
term="http://schemas.google.com/spreadsheets/2006#cell"/>
<title type="text">D4</title>
<content type="text">5</content>
<link rel="self" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R9C4"/>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R9C4/srevc"/>
<gs:cell row="4" col="4" inputValue="=FLOOR(C4/(B4*60),.0001)"
numericValue="5.0">5</gs:cell>
</entry>
v4 API
Retrieve cell data by calling a spreadsheets.values.get or spreadsheets.values.batchGet method for the range or ranges of interest, respectively. For example, the following returns the cells in column D of "Sheet2", starting with row 2, in column-major order and returning formulas as entered (trailing empty cells are omitted):
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet2!D2:D?majorDimension=COLUMNS&valueRenderOption=FORMULA
The response to this request is similar in structure to:
{ "spreadsheetId": spreadsheetId, "valueRanges": [ {"range": "Sheet2!D2:D", "majorDimension": "COLUMNS", "values": [["Widget", 234, "=FLOOR(C4/(B4*60),.0001)", "=D4\*1000"]] }] }
It is more efficient to use spreadsheet.values.batchGet if you intend to retrieve multiple ranges of cell data. If you want to access cell properties such as formatting, the spreadsheet.get method is required.
Edit a cell
The Sheets API v3 lets you edit cell content by issuing a PUT
command to
the cell feed with the modified cell entry as the request body.
The Sheets API v4, in contrast, provides spreadsheets.values.update and spreadsheets.values.batchUpdate methods for changing cell content.
v3 API
To edit a single cell's content, first find the cell's entry in the
cell feed.
The entry contains an edit URL. Update the entry to reflect the contents
you want the cell to have, and then issue a PUT
request to the edit url
with the updated cell entry as the body of the request. For example, the
following updates cell D2 (R2C4) to contain a SUM
formula:
PUT https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full//R2C4/srevc<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006"> <id>https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R2C4</id> <link rel="edit" type="application/atom+xml" href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R2C4"/> <gs:cell row="2" col="4" inputValue="=SUM(A1:B6)"/> </entry>
v4 API
Single cell editing in the Sheets API v4 can be done with the
spreadsheets.values.update
method. This method requires a ValueInputOption
query parameter, which
specifies whether the input data is treated as if entered into the
Sheets UI (USER_ENTERED
), or left unparsed and taken as is (RAW
). For
example, the following updates cell D2 with a formula:
PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/D2?valueInputOption=USER_ENTERED
{"values": [["=SUM(A1:B6)"]]}
If you are making multiple cell edits, use the spreadsheets.values.batchUpdate method to issue them in one request.
Edit multiple cells via batch request
Both APIs provide the means to make changes to the content of multiple cells with a single (batch) request. The cells referred to by a batch request are not required to be in a contingous range.
In the case where one or more of the cell edits in the batch fail, the Sheets API v3 allows others to succeed. However, the Sheets API v4 returns an error if any of the batched updates fail, and does not apply any of them in that case.
v3 API
To edit multiple cells, first retrieve a cell feed
for the worksheet. The entry contains a batch URL. Send a POST
request to this URL, along with a request body describing the cells you
wish to update and the new cell content. The POST
request and request body
have a structure similar to the following:
POST https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/batch
<feed xmlns="http://www.w3.org/2005/Atom"
xmlns:batch="http://schemas.google.com/gdata/batch"
xmlns:gs="http://schemas.google.com/spreadsheets/2006">
<id>https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full</id>
<entry>
<batch:id>request1</batch:id>
<batch:operation type="update"/>
<id>https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R2C4</id>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R2C4/version"/>
<gs:cell row="2" col="4" inputValue="newData"/>
</entry>
...
<entry>
<batch:id>request2</batch:id>
<batch:operation type="update"/>
<id>https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R2C5</id>
<link rel="edit" type="application/atom+xml"
href="https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/private/full/R2C5/version"/>
<gs:cell row="5" col="2" inputValue="moreInfo"/>
</entry>
</feed>
The batch:id
field should uniquely identify the request within the batch.
The batch:operation
field should be update
for cell edits. gs:cell
identifies the cell by row and column number and provides the new data
to insert there. id
contains the full URL to the cell to be updated.
link
must have a href
attribute that contains the full path to the
cell's ID. All of these fields are required for each entry.
v4 API
The Sheets API v4 provides batch editing of cell values through the spreadsheets.values.batchUpdate method.
Editing multiple cells can be done by issuing a POST
request with the
data changes specified in the request body. For example:
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values:batchUpdate
{ "valueInputOption": "USER_ENTERED" "data": [ {"range": "D4", "majorDimension": "ROWS", "values": [["newData"]] }, {"range": "B5", "majorDimension": "ROWS", "values": [["moreInfo"]] } ] }
If you specified a single cell as the range, all values provided are written to the sheet starting with that cell as the upper-left coordinate. If you instead specify a multi-cell range, the values you provide must fit that range exactly; if not the API returns an error.