The Sheets API allows you to write values and formulas to cells, ranges, sets of ranges and entire sheets. The examples on this page illustrate how some common write operations can be achieved with the spreadsheets.values collection of this API.
Note that is also possible to write cell values using the spreadsheet.batchUpdate method, which can be useful if you want to simultaneously update cell formatting or other properties that the spreadsheets.values collection does not affect. However, for simple value writes it is easier to use spreadsheets.values.update or spreadsheets.values.batchUpdate.
In these examples, the placeholder spreadsheetId is used to indicate where you would provide the spreadsheet ID, which can be discovered from the spreadsheet URL. The ranges to write to are specified using A1 notation in the request URL.
Write a single range
Starting with a new, blank spreadsheet, the following spreadsheets.values.update request will write the values to the range Sheet1!A1:D5. The ValueInputOption query parameter is required and determines if the values written will be parsed (for example, whether or not a string is converted into a date).
The request body is a ValueRange
object that describes range values to write.
The majorDimension
field determines if the arrays included are interpreted as arrays of columns
or rows. Existing values in the target range will be overwritten.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement writes in different languages using the Google API client libraries.
PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D5?valueInputOption=USER_ENTERED
{
"range": "Sheet1!A1:D5",
"majorDimension": "ROWS",
"values": [
["Item", "Cost", "Stocked", "Ship Date"],
["Wheel", "$20.50", "4", "3/1/2016"],
["Door", "$15", "2", "3/15/2016"],
["Engine", "$100", "1", "30/20/2016"],
["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
],
}
The response will consist of a UpdateValuesResponse object such as this one:
{
"spreadsheetId": spreadsheetId,
"updatedRange": "Sheet1!A1:D5",
"updatedRows": 5,
"updatedColumns": 4,
"updatedCells": 20,
}
The resulting sheet will look like this:
| A | B | C | D | |
| 1 | Item | Cost | Stocked | Ship Date |
| 2 | Wheel | $20.50 | 4 | 3/1/2016 |
| 3 | Door | $15 | 2 | 3/15/2016 |
| 4 | Engine | $100 | 1 | 3/20/2016 |
| 5 | Totals | $135.5 | 7 | 3/20/2016 |
Write selectively to a range
It is possible, when writing values to a range, to avoid changing some of the
existing cells by setting the corresponding array elements to null. It is also
possible to clear a cell by writing an empty string to it.
Starting with a sheet containing the same data produced by the above example, the following spreadsheets.values.update request will write the values to the B1:D4 range, selectively leaving some cells unchanged and clearing others. The ValueInputOption query parameter is required and determines if the values written will be parsed (for example, whether or not a string is converted into a date).
The request body is a ValueRange
object that describes range values to write. The
majorDimension
field indicates that arrays are lists of values organized by column.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement writes in different languages using the Google API client libraries.
PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!B1?valueInputOption=USER_ENTERED
{
"range": "Sheet1!B1",
"majorDimension": "COLUMNS",
"values": [
[null,"$1","$2", ""],
[],
[null,"4/1/2016", "4/15/2016", ""]
]
}
The values field here lists the changes made to each column in the range.
The first array indicates that B1 is to be left unchanged (due to the
null array element), while B4 is to be cleared (empty string). B2 and B3
have their values updated. The third array performs the same operations on
column D, while the second empty array indicates that column C is
to be left unchanged.
The response will consist of a UpdateValuesResponse object such as this one:
{
"spreadsheetId": spreadsheetId,
"updatedRange": "Sheet1!B1:D5",
"updatedRows": 3,
"updatedColumns": 2,
"updatedCells": 6,
}
The resulting sheet will look like this:
| A | B | C | D | |
| 1 | Item | Cost | Stocked | Ship Date |
| 2 | Wheel | $1.00 | 4 | 4/1/2016 |
| 3 | Door | $2 | 2 | 4/15/2016 |
| 4 | Engine | 1 | ||
| 5 | Totals | $3.00 | 7 | 4/15/2016 |
Note that the "Totals" row, while not directly changed by this request, does change because its cells contain formulas that are dependent on the altered cells.
Write to multiple ranges
Starting with a blank sheet, the following
spreadsheets.values.batchUpdate
request will write values from the ranges Sheet1!A1:A4, and
Sheet1!B1:D2. Existing values in the target
range will be overwritten. The request body will consist of an object with the
ValueInputOption used for the
write and an array of
ValueRange
objects corresponding to each range written.
The majorDimension
field determines if the arrays included are interpreted as arrays of columns
or rows.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement writes in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values:batchUpdate
{
"valueInputOption": "USER_ENTERED",
"data": [
{
"range": "Sheet1!A1:A4",
"majorDimension": "COLUMNS",
"values": [
["Item", "Wheel", "Door", "Engine"]
]
},
{
"range": "Sheet1!B1:D2",
"majorDimension": "ROWS",
"values": [
["Cost", "Stocked", "Ship Date"],
["$20.50", "4", "3/1/2016"]
]
}
]
}
The response will consist of an object that lists the updated cell statistics and an array of UpdateValuesResponse objects, one for each updated range. For example:
{
"spreadsheetId": spreadsheetId,
"totalUpdatedRows": 4,
"totalUpdatedColumns": 4,
"totalUpdatedCells": 10,
"totalUpdatedSheets": 1,
"responses": [
{
"spreadsheetId": spreadsheetId,
"updatedRange": "Sheet1!A1:A4",
"updatedRows": 4,
"updatedColumns": 1,
"updatedCells": 4,
},
{
"spreadsheetId": spreadsheetId,
"updatedRange": "Sheet1!B1:D2",
"updatedRows": 2,
"updatedColumns": 3,
"updatedCells": 6,
}
],
}
The resulting sheet will look like this:
| A | B | C | D | |
| 1 | Item | Cost | Stocked | Ship Date |
| 2 | Wheel | $20.50 | 4 | 3/1/2016 |
| 3 | Door | |||
| 4 | Engine | |||
| 5 |
Write values without parsing
Starting with a blank sheet, the following
spreadsheets.values.update
request will write the values to the range Sheet1!A1:E1, but uses the RAW
ValueInputOption
query parameter to prevent the written strings from being parsed as formulas,
booleans or numbers; they will appear and be justified as strings in the sheet.
The request body is a ValueRange
object that describes range values to write.
The majorDimension
field determines if the arrays included are interpreted as arrays of columns
or rows. Existing values in the target range will be overwritten.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement writes in different languages using the Google API client libraries.
PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:E1?valueInputOption=RAW
{
"range": "Sheet1!A1:E1",
"majorDimension": "ROWS",
"values": [
["Data", 123.45, true, "=MAX(D2:D4)", "10"]
],
}
The response will consist of a UpdateValuesResponse object such as this one:
{
"spreadsheetId": spreadsheetId,
"updatedRange": "Sheet1!A1:E1",
"updatedRows": 1,
"updatedColumns": 5,
"updatedCells": 5,
}
The resulting sheet will look like this:
| A | B | C | D | E | |
| 1 | Data | 123.45 | TRUE | =MAX(D2:D4) | 10 |
| 2 |
Notice how "true" is centered and become the native TRUE boolean value, whereas "123.45" is right-justified because it is a number, and "10" is left-justified because it is a string. The formula is not parsed, and also appears as a string.
Append Values
Start with a sheet looking like the below table:
| A | B | C | D | |
| 1 | Item | Cost | Stocked | Ship Date |
| 2 | Wheel | $20.50 | 4 | 3/1/2016 |
| 3 |
Given the above, the following spreadsheets.values.append request will add two news rows of values after starting with row 3. The ValueInputOption query parameter is required and determines if the values written will be parsed (for example, whether or not a string is converted into a date).
The request body is a ValueRange
object that describes range values to write.
The majorDimension
field determines if the arrays included are interpreted as arrays of columns
or rows.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement writes in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:E1:append?valueInputOption=USER_ENTERED
{
"range": "Sheet1!A1:E1",
"majorDimension": "ROWS",
"values": [
["Door", "$15", "2", "3/15/2016"],
["Engine", "$100", "1", "3/20/2016"],
],
}
The response will consist of an AppendValuesResponse object such as this one:
{
"spreadsheetId": spreadsheetId,
"tableRange": "Sheet1!A1:D2",
"updates": {
"spreadsheetId": spreadsheetId,
"updatedRange": "Sheet1!A3:D4",
"updatedRows": 2,
"updatedColumns": 4,
"updatedCells": 8,
}
}
The resulting sheet will look like this:
| A | B | C | D | |
| 1 | Item | Cost | Stocked | Ship Date |
| 2 | Wheel | $20.50 | 4 | 3/1/2016 |
| 3 | Door | $15 | 2 | 3/15/2016 |
| 4 | Engine | $100 | 1 | 3/20/2016 |
| 3 |