Reading & Writing Cell Values

Spreadsheets can have multiple sheets, with each sheet having any number of rows or columns. A cell is a location at the intersection of a particular row and column, and may contain a data value. The Google Sheets API provides the spreadsheets.values collection to enable the simple reading and writing of values.

This page describes the basics of using the spreadsheets.values collection. If you need to update formatting or other properties in a sheet, you will need to use the spreadsheets collection, which is described in Updating Spreadsheets.

Methods

The spreadsheets.values collection provides the following methods for reading and writing values, each with a specific task in mind:

Range Access Reading Writing
Single range spreadsheets.values.get spreadsheets.values.update
Multiple ranges spreadsheets.values.batchGet spreadsheets.values.batchUpdate
Appending spreadsheets.values.append

In general, it is a good idea to combine multiple reads or updates with the batchGet and batchUpdate methods (respectively), as this will improve efficiency.

You can find examples of each of these methods in the Basic Reading and Basic Writing samples pages.

Reading

To read data from a sheet, you will need the spreadsheet ID and the range(s) A1 notation. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API. The format of the output is controlled by three optional parameters:

Format Parameter Default Value
majorDimension ROWS
valueRenderOption FORMATTED_VALUE
dateTimeRenderOption SERIAL_NUMBER

Note that dateTimeRenderOption is only used if the valueRenderOption is not FORMATTED_VALUE.

The singular and batch get methods are described below. For examples of basic read operations, see the Basic Reading recipes page.

Reading a single range

To read a single range of data out of a spreadsheet, use a spreadsheets.values.get request:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Gets the values of the cells in the specified range
 * @param {string} spreadsheetId id of the spreadsheet
 * @param {string} range specifying the start and end cells of the range
 * @returns {*} Values in the range
 */
Snippets.prototype.getValues = function(spreadsheetId, range) {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).getValues(values) is more appropriate.
  try {
    const result = Sheets.Spreadsheets.Values.get(spreadsheetId, range);
    const numRows = result.values ? result.values.length : 0;
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    Logger.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/SpreadsheetSnippets.java
ValueRange result = service.spreadsheets().values().get(spreadsheetId, range).execute();
int numRows = result.getValues() != null ? result.getValues().size() : 0;
System.out.printf("%d rows retrieved.", numRows);

JavaScript

sheets/snippets/snippets.js
gapi.client.sheets.spreadsheets.values.get({
  spreadsheetId: spreadsheetId,
  range: range
}).then((response) => {
  var result = response.result;
  var numRows = result.values ? result.values.length : 0;
  console.log(`${numRows} rows retrieved.`);
});

Node.js

sheets/snippets/snippets.js
this.sheetsService.spreadsheets.values.get({
  spreadsheetId,
  range,
}, (err, result) => {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    const numRows = result.values ? result.values.length : 0;
    console.log(`${numRows} rows retrieved.`);
  }
});

PHP

sheets/snippets/src/SpreadsheetSnippets.php
$result = $service->spreadsheets_values->get($spreadsheetId, $range);
$numRows = $result->getValues() != null ? count($result->getValues()) : 0;
printf("%d rows retrieved.", $numRows);

Python

sheets/snippets/spreadsheet_snippets.py
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id, range=range_name).execute()
rows = result.get('values', [])
print('{0} rows retrieved.'.format(len(rows)))

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
result = service.get_spreadsheet_values(spreadsheet_id, range_name)
num_rows = result.values ? result.values.length : 0
puts "#{num_rows} rows received."

The response to this request is returned as a ValueRange object.

Reading multiple ranges

To read multiple discontinuous ranges, use a spreadsheets.values.batchGet, which lets you specify any number of ranges to retrieve:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Get the values in the specified ranges
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {list<string>} _ranges The span of ranges
 * @returns {*} spreadsheet information and values
 */
Snippets.prototype.batchGetValues = (spreadsheetId,
  _ranges) => {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).getValues(values) is more appropriate.
  let ranges = [
    //Range names ...
  ];
  try {
    const result =
      Sheets.Spreadsheets.Values.batchGet(spreadsheetId, {ranges: ranges});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    Logger.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/SpreadsheetSnippets.java
List<String> ranges = Arrays.asList(
        //Range names ...
);
BatchGetValuesResponse result = service.spreadsheets().values().batchGet(spreadsheetId)
        .setRanges(ranges).execute();
System.out.printf("%d ranges retrieved.", result.getValueRanges().size());

JavaScript

sheets/snippets/snippets.js
var ranges = [
  // Range names ...
];
gapi.client.sheets.spreadsheets.values.batchGet({
   spreadsheetId: spreadsheetId,
   ranges: ranges
}).then((response) => {
  var result = response.result;
  console.log(`${result.valueRanges.length} ranges retrieved.`);
});

Node.js

sheets/snippets/snippets.js
let ranges = [
  // Range names ...
];
this.sheetsService.spreadsheets.values.batchGet({
  spreadsheetId,
  ranges,
}, (err, result) => {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    console.log(`${result.valueRanges.length} ranges retrieved.`);
  }
});

PHP

sheets/snippets/src/SpreadsheetSnippets.php
$ranges = [
    // Range names ...
];
$params = array(
    'ranges' => $ranges
);
$result = $service->spreadsheets_values->batchGet($spreadsheetId, $params);
printf("%d ranges retrieved.", count($result->getValueRanges()));

Python

sheets/snippets/spreadsheet_snippets.py
range_names = [
    # Range names ...
]
result = service.spreadsheets().values().batchGet(
    spreadsheetId=spreadsheet_id, ranges=range_names).execute()
ranges = result.get('valueRanges', [])
print('{0} ranges retrieved.'.format(len(ranges)))

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
range_names = [
  # Range names ...
]
result = service.batch_get_spreadsheet_values(spreadsheet_id,
                                              ranges: range_names)
puts "#{result.value_ranges.length} ranges retrieved."

The response to this request is a BatchGetValueResponse, object which contains the spreadsheet ID and a list of ValueRange objects.

Writing

To write to a sheet, you will need the spreadsheet ID, the range(s) in A1 notation, and the data you wish to write arranged in an appropriate request body object. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API.

Updates require a valid ValueInputOption parameter (for singular updates, this is a required query parameter; for batch updates, this parameter is required in the request body). The ValueInputOption controls whether input strings are parsed or not, as described in the following table:

ValueInputOption Description
RAW The input is not parsed and is simply inserted as a string, so the input "=1+2" places the string "=1+2" in the cell, not a formula. (Non-string values like booleans or numbers are always handled as RAW.)
USER_ENTERED The input is parsed exactly as if it were entered into the Google Sheets UI, so "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred, so "$100.15" becomes a number with currency formatting.

The singular and batch update methods are described below. For examples of basic write operations, see the Basic Writing recipes page.

Writing to a single range

To write data to a single range, use a spreadsheets.values.update request:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Updates the values in the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range the range of cells in spreadsheet
 * @param {} valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<string>} _values list of values to input
 * @returns {*} spreadsheet with updated values
 */
Snippets.prototype.updateValues = (spreadsheetId, range,
  valueInputOption, _values) => {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).setValues(values) is more appropriate.
  let values = [
    [
      // Cell values ...
    ]
    // Additional rows ...
  ];

  try {
    let valueRange = Sheets.newValueRange();
    valueRange.values = values;
    const result = Sheets.Spreadsheets.Values.update(valueRange,
      spreadsheetId, range, {valueInputOption: valueInputOption});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    Logger.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/SpreadsheetSnippets.java
List<List<Object>> values = Arrays.asList(
        Arrays.asList(
                // Cell values ...
        )
        // Additional rows ...
);
ValueRange body = new ValueRange()
        .setValues(values);
UpdateValuesResponse result =
        service.spreadsheets().values().update(spreadsheetId, range, body)
                .setValueInputOption(valueInputOption)
                .execute();
System.out.printf("%d cells updated.", result.getUpdatedCells());

JavaScript

sheets/snippets/snippets.js
var values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
var body = {
  values: values
};
gapi.client.sheets.spreadsheets.values.update({
   spreadsheetId: spreadsheetId,
   range: range,
   valueInputOption: valueInputOption,
   resource: body
}).then((response) => {
  var result = response.result;
  console.log(`${result.updatedCells} cells updated.`);
});

Node.js

sheets/snippets/snippets.js
let values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
const requestBody = {
  values,
};
this.sheetsService.spreadsheets.values.update({
  spreadsheetId,
  range,
  valueInputOption,
  requestBody,
}, (err, result) => {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    console.log('%d cells updated.', result.updatedCells);
  }
});

PHP

sheets/snippets/src/SpreadsheetSnippets.php
$values = [
    [
        // Cell values ...
    ],
    // Additional rows ...
];
$body = new Google_Service_Sheets_ValueRange([
    'values' => $values
]);
$params = [
    'valueInputOption' => $valueInputOption
];
$result = $service->spreadsheets_values->update($spreadsheetId, $range,
$body, $params);
printf("%d cells updated.", $result->getUpdatedCells());

Python

sheets/snippets/spreadsheet_snippets.py
values = [
    [
        # Cell values ...
    ],
    # Additional rows ...
]
body = {
    'values': values
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
values = [
  [
    # Cell values ...
  ]
  # Additional rows ...
]
data = [
  {
    range:  range_name,
    values: values
  },
  # Additional ranges to update ...
]
value_range_object = Google::Apis::SheetsV4::ValueRange.new(range:  range_name,
                                                            values: values)
result = service.update_spreadsheet_value(spreadsheet_id,
                                          range_name,
                                          value_range_object,
                                          value_input_option: value_input_option)
puts "#{result.updated_cells} cells updated."

The body of the update request must be a ValueRange object, though the only required field is values. If range is specified, it must match the range in the URL. In the ValueRange, you can optionally specify its majorDimension. By default, ROWS is used. If COLUMNS is specified, each inner array is written to a column instead of a row.

When updating, values with no data are skipped. To clear data, use an empty string ("").

Writing multiple ranges

If you want to write multiple discontinuous ranges, you can use a spreadsheets.values.batchUpdate request:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Updates the values in the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range range of cells of the spreadsheet
 * @param valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<string>} _values list of values to input
 * @returns {*} spreadsheet with updated values
 */
Snippets.prototype.batchUpdateValues =
  (spreadsheetId, range, valueInputOption,
    _values) => {
    // This code uses the Sheets Advanced Service, but for most use cases
    // the built-in method SpreadsheetApp.getActiveSpreadsheet()
    //     .getRange(range).setValues(values) is more appropriate.
    let values = [
      [
        // Cell values ...
      ]
      // Additional rows ...
    ];

    try {
      let valueRange = Sheets.newValueRange();
      valueRange.range = range;
      valueRange.values = values;

      let batchUpdateRequest = Sheets.newBatchUpdateValuesRequest();
      batchUpdateRequest.data = valueRange;
      batchUpdateRequest.valueInputOption = valueInputOption;

      const result = Sheets.Spreadsheets.Values.batchUpdate(batchUpdateRequest,
        spreadsheetId);
      return result;
    } catch (err) {
      // TODO (developer) - Handle exception
      Logger.log('Failed with error %s', err.message);
    }
  };

Java

sheets/snippets/src/main/java/SpreadsheetSnippets.java
List<List<Object>> values = Arrays.asList(
        Arrays.asList(
                // Cell values ...
        )
        // Additional rows ...
);
List<ValueRange> data = new ArrayList<>();
data.add(new ValueRange()
        .setRange(range)
        .setValues(values));
// Additional ranges to update ...

BatchUpdateValuesRequest body = new BatchUpdateValuesRequest()
        .setValueInputOption(valueInputOption)
        .setData(data);
BatchUpdateValuesResponse result =
        service.spreadsheets().values().batchUpdate(spreadsheetId, body).execute();
System.out.printf("%d cells updated.", result.getTotalUpdatedCells());

JavaScript

sheets/snippets/snippets.js
var values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
var data = [];
data.push({
  range: range,
  values: values
});
// Additional ranges to update.

var body = {
  data: data,
  valueInputOption: valueInputOption
};
gapi.client.sheets.spreadsheets.values.batchUpdate({
   spreadsheetId: spreadsheetId,
   resource: body
}).then((response) => {
  var result = response.result;
  console.log(`${result.totalUpdatedCells} cells updated.`);
});

Node.js

sheets/snippets/snippets.js
let values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
const data = [{
  range,
  values,
}];
// Additional ranges to update ...
const requestBody = {
  data,
  valueInputOption,
};
this.sheetsService.spreadsheets.values.batchUpdate({
  spreadsheetId,
  requestBody,
}, (err, result) => {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    console.log('%d cells updated.', result.totalUpdatedCells);
  }
});

PHP

sheets/snippets/src/SpreadsheetSnippets.php
$values = [
    [
        // Cell values ...
    ],
    // Additional rows ...
];
$data = [];
$data[] = new Google_Service_Sheets_ValueRange([
    'range' => $range,
    'values' => $values
]);
// Additional ranges to update ...
$body = new Google_Service_Sheets_BatchUpdateValuesRequest([
    'valueInputOption' => $valueInputOption,
    'data' => $data
]);
$result = $service->spreadsheets_values->batchUpdate($spreadsheetId, $body);
printf("%d cells updated.", $result->getTotalUpdatedCells());

Python

sheets/snippets/spreadsheet_snippets.py
values = [
    [
        # Cell values ...
    ],
    # Additional rows
]
data = [
    {
        'range': range_name,
        'values': values
    },
    # Additional ranges to update ...
]
body = {
    'valueInputOption': value_input_option,
    'data': data
}
result = service.spreadsheets().values().batchUpdate(
    spreadsheetId=spreadsheet_id, body=body).execute()
print('{0} cells updated.'.format(result.get('totalUpdatedCells')))

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
values = [
  [
    # Cell values ...
  ]
  # Additional rows ...
]
data = [
  {
    range:  range_name,
    values: values
  },
  # Additional ranges to update ...
]
batch_update_values = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new(
  data:               data,
  value_input_option: value_input_option
)
result = service.batch_update_values(spreadsheet_id, batch_update_values)
puts "#{result.total_updated_cells} cells updated."

The body of the batchUpdate request must be a BatchUpdateValuesRequest object, which contains a ValueInputOption and a list of ValueRange objects (one for each written range). Each ValueRange object specifies its own range, majorDimension, and the data to input.

Appending values

To append data after a table of data in a sheet, use a spreadsheets.values.append request:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Appends values to the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range range of cells in the spreadsheet
 * @param valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<string>} _values list of rows of values to input
 * @returns {*} spreadsheet with appended values
 */
Snippets.prototype.appendValues = (spreadsheetId, range,
  valueInputOption, _values) => {
  let values = [
    [
      // Cell values ...
    ]
    // Additional rows ...
  ];
  try {
    let valueRange = Sheets.newRowData();
    valueRange.values = values;

    let appendRequest = Sheets.newAppendCellsRequest();
    appendRequest.sheetId = spreadsheetId;
    appendRequest.rows = [valueRange];

    const result = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId,
      range, {valueInputOption: valueInputOption});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    Logger.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/SpreadsheetSnippets.java
List<List<Object>> values = Arrays.asList(
        Arrays.asList(
                // Cell values ...
        )
        // Additional rows ...
);
ValueRange body = new ValueRange()
        .setValues(values);
AppendValuesResponse result =
        service.spreadsheets().values().append(spreadsheetId, range, body)
                .setValueInputOption(valueInputOption)
                .execute();
System.out.printf("%d cells appended.", result.getUpdates().getUpdatedCells());

JavaScript

sheets/snippets/snippets.js
var values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
var body = {
  values: values
};
gapi.client.sheets.spreadsheets.values.append({
   spreadsheetId: spreadsheetId,
   range: range,
   valueInputOption: valueInputOption,
   resource: body
}).then((response) => {
  var result = response.result;
  console.log(`${result.updates.updatedCells} cells appended.`)
});

Node.js

sheets/snippets/snippets.js
let values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
const requestBody = {
  values,
};
this.sheetsService.spreadsheets.values.append({
  spreadsheetId,
  range,
  valueInputOption,
  requestBody,
}, (err, result) => {
  if (err) {
    // Handle error.
    console.log(err);
  } else {
    console.log(`${result.updates.updatedCells} cells appended.`);
  }
});

PHP

sheets/snippets/src/SpreadsheetSnippets.php
$values = [
    [
        // Cell values ...
    ],
    // Additional rows ...
];
$body = new Google_Service_Sheets_ValueRange([
    'values' => $values
]);
$params = [
    'valueInputOption' => $valueInputOption
];
$result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());

Python

sheets/snippets/spreadsheet_snippets.py
values = [
    [
        # Cell values ...
    ],
    # Additional rows ...
]
body = {
    'values': values
}
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells appended.'.format(result
                                   .get('updates')
                                   .get('updatedCells')))

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
values = [
  [
    # Cell values ...
  ],
  # Additional rows ...
]
value_range = Google::Apis::SheetsV4::ValueRange.new(values: values)
result = service.append_spreadsheet_value(spreadsheet_id,
                                          range_name,
                                          value_range,
                                          value_input_option: value_input_option)
puts "#{result.updates.updated_cells} cells appended."

The body of the update request must be a ValueRange object, though the only required field is values. If range is specified, it must match the range in the URL. In the ValueRange, you can optionally specify its majorDimension. By default, ROWS is used. If COLUMNS is specified, each inner array is written to a column instead of a row.

The input range is used to search for existing data and find a "table" within that range. Values are appended to the next row of the table, starting with the first column of the table. For example, consider a sheet Sheet1 that looks like:

A B C D E
1 x y z
2 x y z
3
4 x y
5 y z
6 x y z
7

There are two tables in the sheet: A1:C2, and B4:D6. Appended values would begin at B7 for all the following range inputs:

  • Sheet1, because it will examine all the data in the sheet, determine that the table at B4:D6 is the last table.
  • B4 or C5:D5, because they're both in the B4:D6 table.
  • B2:D4, because the last table in the range is the B4:D6 table (despite it also containing the A1:C2 table).
  • A3:G10, because the last table in the range is the B4:D6 table (despite starting before and ending after it).

The following range inputs would not start writing at B7:

  • A1 would start writing at A3, because that's in the A1:C2 table.
  • E4 would start writing at E4, because it's not in any table. (A4 would also start writing at A4 for the same reasons.)

Additionally, you can choose if you want to overwrite existing data after a table or insert new rows for the new data. By default, the input overwrites data after the table. To write the new data into new rows, specify insertDataOption=INSERT_ROWS.