Basic Writing

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