Get values from a range

This article explains how to get the values of a specific range from a spreadsheet.

Method to use

Sheet.getSheetValues([start row], [start column], [number of rows], [number of columns]);

Use getSheetValues(), which is provided by the Sheet class.

The Spreadsheet class also has a method with the same name, but here we use the Sheet class method, which lets you specify the target sheet explicitly.

It takes four arguments: the start row, the start column, the number of rows, and the number of columns.

For example, to get a range of 4 rows × 5 columns starting at cell C2 of the target sheet (C2:G5), specify getSheetValues(2, 3, 4, 5).

The return value is a two-dimensional array of values (Object[][]). Depending on the contents of each cell, each element is a number, a Boolean, a date (Date), or a string.

This gives the same result as sheet.getRange(2, 3, 4, 5).getValues().

Because the script accesses the spreadsheet, you may need to authorize it the first time you run it.

Sample code

/**
 * ID of the target spreadsheet
 * The xxx part of https://docs.google.com/spreadsheets/d/xxx/edit
 */
const SPREAD_SHEET_ID = '_______________';

/**
 * Gets the values of the specified range
 */
const getSheetValues = () => {
  /** Target spreadsheet */
  const ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);

  /** Target sheet */
  const sheet = ss.getSheetByName('Sheet1');

  if (!sheet) {
    console.log('The sheet was not found');
    return null;
  }

  /** Retrieved data */
  const arrays = sheet.getSheetValues(2, 3, 4, 5);

  console.log(arrays);
  return arrays;
};

When you run it, the values in the range of 4 rows × 5 columns starting at C2 (C2:G5) on “Sheet1” of the specified spreadsheet are returned.