Reading a matrix of data into a script function from Google Sheets

Question

I am new to Google Sheet script so I am trying to learn by looking at other professional code. I have noticed that most routines that read in arrays use .getDataRange().getValues() which reads in the whole worksheet.

The worksheets I work on have lots of different data info on any one sheet. Is there an easy way to read in a subset of the sheet and not the whole sheet?

As an example: If I want to read in range D6:J45 (note the ranges can change based on user request):

1. How can I load in only that matrix data into a 2 dimensional array in Script?

2. If I have to use nested for loops, .length seems to get me # of rows, how can get number of columns in range?

Answer 1

• Use getRange(a1Notation) to get the values of the specified range.
• getValues() returns a 2D array. The number of columns corresponds to the length of the inner arrays, so you have to retrieve the length of an element of the outer array, as shown below:

var values = SpreadsheetApp.getActiveSheet().getRange("D6:J45").getValues();
var numberOfColumns = values[0].length;