Basic Writing

The Google 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 it's 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 the spreadsheets.values collection does not affect. However, for simple value writes it's 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 writes the values to the range Sheet1!A1:D5. The ValueInputOption query parameter is required and determines whether 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 are 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", "3/20/2016"],
    ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
  ],
}

The response will consist of an 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's possible, when writing values to a range, to avoid changing some of the existing cells by setting the corresponding array elements to null. It's 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 writes the values to the B1:D4 range, selectively leaving some cells unchanged and clearing others. The ValueInputOption query parameter is required and determines whether 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 an 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 depend on the altered cells.

Write to multiple ranges

Starting with a blank sheet, the following spreadsheets.values.batchUpdate request writes values from the ranges Sheet1!A1:A4, and Sheet1!B1:D2. Existing values in the target range are overwritten. The request body consists of a ValueInputOption object that shows how to interpret the input data 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 writes 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 as strings and the text alignment is justified 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 are 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 an 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

Note that "true" is centered and is a TRUE boolean value, whereas "123.45" is right justified because it's a number, and "10" is left justified because it's a string. The formula is not parsed, and also appears as a string.

Append Values

Start with a sheet like the table below:

A B C D
1 Item Cost Stocked Ship Date
2 Wheel $20.50 4 3/1/2016
3

The following spreadsheets.values.append request adds 2 new rows of values starting with row 3. The ValueInputOption query parameter is required and determines whether 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
5