thwippp
1/12/2017 - 4:10 PM

Get an array of cells and make a 2D Array (THE LONG WAY, one by one)-- Google Sheets

Get an array of cells and make a 2D Array (THE LONG WAY, one by one)-- Google Sheets

function array2d () {
/********** Gets values from a sheet and iterates them into a 2D Array **********/
  
  /********** Sets up sheet **********/
  var ss = SpreadsheetApp.getActiveSpreadsheet();  // Gets active spreadsheet
  var sheet = ss.getSheetByName("DataRange");  // Gets sheet by name-- RENAME
  
  /********** Sets up data range **********/
  var dataRange = ss.getRangeByName("dataRangeRange");  // The range of cells that you want to add to the 2D array
                                                        // ADD NAMED RANGE IN SHEET
  
  var dataRangelastRow = dataRange.getLastRow();  // Gets the last row of the dataRange
  var dataRangelastColumn = dataRange.getLastColumn();  // Gets the last column of the dataRange
  
  /********** Sets up array.  This is the guts of the function. **********/
  var mainArray = [];  // Sets a blank array
   
  for (var i = 0; i < dataRange.getNumRows(); i++) // For the number of rows in dataRange
    {
      var subArray = [];  // Creates a blank array inside of main array
      for (var j = 0; j < dataRange.getNumColumns(); j++) //number of columns in dataRange
      {
        subArray[j] = sheet.getRange(dataRange.getRow()+i,dataRange.getColumn()+j,
          dataRangelastRow,dataRangelastColumn).getValue();  // In subArray, iterates through each cell, through the lastRow and 
                                                            // lastColumn, gets the value, and pushes it to the subArray
      }  // End of subArray for loop
      mainArray[i] = subArray;  // Adds the subArray[0] to mainArray[0] and on and on...
    }  // End of mainArray for loop
    
  // Logger.log(mainArray);  // Logs mainArray in its entirety
  // Logger.log(mainArray[0][0]);  // Logs the very first entry (row 1, column 1) of the dataRange
}  // End of Function