Javascript extract subarray from 2D array using spreadsheet range A1 notation

Question

Among many Google Apps Script best practices, as for script performance improvement, it is recommended to minimise calls to other services:

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

As a result, when dealing with GAS on a spreadsheet, a common practice is to copy values from a sheet as a whole into a 2D array, do all manipulations on the array, then flush all data from array back to sheet.

Once you have copied all data from sheet into the 2D array, dealing with columns may be tricky, particularly with large column sets, therefore it could be handy to use a function that will extract/set data from the 2D array using A1 notation, as this allows to visually determine on the sheet what is the correct range, while using a Javascript transcoding function to identify columns and rows accordingly.

Without reinventing the wheel, I was wondering if someone wrote some code to extract data from a 2D array as a subarray, using a spreadsheet range A1 notation to reference the boundaries of the subarray.

For instance, assuming a custom function getRange:

var SS = SpreadsheetApp.openById(myID); // open spreadsheet
var sheet = SS.getSheetByName("Test"); // get sheet
var sheetValues = sheet.getSheetValues(1,1,-1,-1); // copy all values from sheet to 2D array

// samples: how the function could be invoked to extract subarray using A1 notation 
var subArray = getRange(sheetValues, "A2:A"); // returns a "column" from the array begin at row 2
subArray = getRange(sheetValues, "A2"); // returns a "cell" from the array (1,0)
subArray = getRange(sheetValues, "B2:D3"); // returns a 2D subarray

Similar to this

Edit:

I have copied the following code from another similar post and this works now that I have correctly set parameters:

var matrix = [
          ["a1", "b1", "c1", "d1"],
          ["a2", "b2", "c2", "d2"],
          ["a3", "b3", "c3", "d3"],
          ["a4", "b4", "c4", "d4"]
        ]
var startRow = 1
var startCol = 0
var endRow = 2
var endCol = 0

var section = matrix.slice(startRow, endRow + 1).map(i => i.slice(startCol, endCol + 1))
console.log(JSON.stringify(section))

I will further investigate on A1 transcoding!

Answer 1

I've got it to work, combining a snippet from here.

Here's the final code, it may be further optimized (and requires input data validation).

var matrix = [
   ["a1", "b1", "c1", "d1"],
   ["a2", "b2", "c2", "d2"],
   ["a3", "b3", "c3", "d3"],
   ["a4", "b4", "c4", "d4"]
]

console.log("getRange('a2:2') returns:\n" + JSON.stringify(getRange("a2:2")));

function getRange(textRange) {
   var startRow, startCol, endRow, endCol;
   var range = textRange.split(":");
   var ret = cellToRoWCol(range[0]);
   startRow = ret[0]
   startCol = ret[1]
   if (startRow == -1) {
      startRow = 0;
   }
   if (startCol == -1) {
      startCol = 0;
   }

   if (range[1]) {
      ret = cellToRoWCol(range[1]);
      endRow = ret[0]
      endCol = ret[1]
      if (endRow == -1) {
         endRow = matrix.length;
      }
      if (endCol == -1) {
         endCol = matrix.length;
      }
   } else { // only one cell
      endRow = startRow
      endCol = startCol
   }

   return matrix.slice(startRow, endRow + 1).map(function(i) {
      return i.slice(startCol, endCol + 1);
   });
}

function cellToRoWCol(cell) {
   // returns row & col from A1 notation
   var row = cell.replace(/[^0-9]+/g, '');
   var letter = cell.replace(/[^a-zA-Z]+/g, '').toUpperCase();

   var column = 0,
      length = letter.length;
   for (var i = 0; i < length; i++) {
      column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
   }

   row = Number(row) - 1;
   column--;

   return [row, column];
}

Answer 2

For what I could understand from your question, an easy way to extract the subarrays from the range:

Example 1:

var subarray = sheet.getRange('B1:E1').getValues()[0];

Now, imagine you have values in that row and because it 's only one row you don't actually need it as an array inside another one, so putting the [0] you could get the subarray as a 1D array.

For returning a column you can use the same example from above, but changing the range for something like this

Example 2:

var subarray = sheet.getRange('A2:A5').getValue();

If you want to specify just one cell you can use

Example 3:

var subarray = sheet.getRange('A5').getValue();

Edit:

I can see in your question you found a way for getting the rows. I will give you a function to get the column you want at a certain position

function getRangeCol(two_d, colPosition, rowToStart) {
    var col = two_d.map(function(value, index) { 
      if(rowToStart <= index)
      return value[colPosition]; 
    }).filter(function (el) {
      return el != null;
    });
    Logger.log(col);
}

Let me know if this helps you