Parsing .getRange() values in Googles Sheets function

Question

Due to overhead issues, instead of reading a large matrix from a sheet cell by cell, I would like to load the total matrix into a function's multi-dimensional array and then be able to access any value in the matrix. I tried code below. Results commented below function.

function Analyze() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var BondInputSS = ss.getSheetByName("BOND INPUT");

BondInputSS.activate();

var values = SpreadsheetApp.getActiveSheet().getRange("F9:BD57").getValues();

var data1 = values [4];
var data2 = values [4,3];

}

In the debugger, values seems to be array(51) where each total row is a separate row. data1 is also an array = 4th row of values array. data2 is also an array = 3th row of values array.

Is there anyway to get the spreadsheet range directly into an array that I could array type controls to get to the data (e.g.; data3 = values[2,3] is equal to value in cell offset by (2,4) or value in J11)

Answer 1

.getValues() returns a 2D array. Once you have it, you access the values like you do a normal JavaScript 2D array.

var values = SpreadsheetApp.getActiveSheet().getRange("F9:BD57").getValues();

// values[0][0] = row 1, column 1
// values[0][1] = row 1, column 2
// values[1][0] = row 2, column 2

Answer 2

data1=values[4][0]; data2=values[4][3];

data1 will give 5 th row first column

data2 will give fifth row 4 the column