Scripting for Dynamic Ranges in Google Spreadsheets

In my previous post, I took the data from my google spreadsheet and passed into an array, so that I could then perform an operation (print out the third row of data). However, I was selecting a static range from my spreadsheet - fine for that example, but not suitable for any application in real life, where your database will change in size. This example below uses the same spreadsheet as yesterday's example.

So what I want to do is change the code to work whether my spreadsheet has 10 rows, 100 rows or 10,000 rows. I also want to simplify yesterday's code, which was largely based on this excellent google tutorial.
This is the code snippet that I want to change:

//select the data range - static for now
var dataRange = sheet.getRange("A1:D8");

Instead of referencing cells, I use the "getLastRow()" and "getLastColumn()" methods to find out the size of my data range and use that in my "getRange" method. The code is as follows, telling the computer to grab everything from cell (1,1) to (last row, last column):

// grab the range from cell (1,1) to last row and column
// and put into a variable called "dataRange"
var dataRange = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());

We can make the code cleaner by defining two variables, N and P, that hold the values of the last row and last column - we'll find these are useful elsewhere. The code to do this is as follows:

// get the size of the data range
var N = sheet.getLastRow();
var P = sheet.getLastColumn();

And then the code to grab the range becomes:

var dataRange = sheet.getRange(1,1,N,P);

So, this is good, I now have the data range selected. Time to pass into an array. For my own learning, I'm going to do this step by step, as follows: i) create a variable that holds the header labels (these are used as labels in the row arrays); ii) create a row array for one example row in my data range (I chose row 2); and finally iii) create a new array that holds each row array.

Step 1: create a variable of header labels by using the "getValues()" method on the data range. Notice the "[0]" which ensures I'm selecting the first row i.e. the header row. The code is:

// define headers variable as first row of data range 
// use as labels in my array
var headers = dataRange.getValues()[0];

Step 2: create an object array called "object" and pass in the data from a single row. The idea is to use a for loop that starts with the data in the first column, puts into the array with the label "header[0]", then repeat for the second column, which has label "header[1]" through to the last column. The code is as follows:

//get values from data range
var dataRangeValues = dataRange.getValues();

// create the empty array object
var object = {};

for (var j = 0; j < P; j++) {
   var cellData = dataRangeValues[4][j]; // select data from row 4
   object[headers[j]] = cellData; // pass into object array
}

To test, let's try printing out a value stored in the array. I chose something from the 4th column with the following snippet of code:

var stringToDisplay = object["web"];
Browser.msgBox(stringToDisplay);

The output should return a message box with "www.example4.com".

Step 3: finally, I want to grab every row array and pass these into a new array called "mainObjects". This is achieved by another for loop that cycles through from the first to the last row. The code is as follows:

// define empty array to hold all row arrays
var mainObject = []; 
  
for (var i = 0; i < N; i++) {
    
    // define empty array for each row
    var object = {}; 
    
    // for each row, loop to add data from each column
    for (j = 0; j < P; j++) {
      var cellData = dataRangeValues[i][j];
      object[headers[j]] = cellData;
    }
    
    // push each row array into the main array
    mainObject.push(object);
}

Lastly for tonight, let's test that the data is correctly placed into the "mainObjects" array by printing out an example value (the first name of the third row array), as follows:

var thirdEntry = mainObject[3];
Browser.msgBox(thirdEntry.name1);

The output should be a message box showing "Bob" as follows:

So, the full code for this example is:

function array2() {
  // select spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];
  
  // get the size of the data range
  var N = sheet.getLastRow();
  var P = sheet.getLastColumn();
  
  // grab the range from cell (1,1) to the cell in the last row and last column
  // and put into a variable called "dataRange"
  var dataRange = sheet.getRange(1,1,N,P);
  
  // pass the header row into a headers variable for use as labels in my array
  var headers = dataRange.getValues()[0];
  
  var dataRangeValues = dataRange.getValues();
  
  // define empty array to hold all row arrays
  var mainObject = []; 
  
  for (var i = 0; i < N; i++) {
    
    // define empty array for each row
    var object = {}; 
    
    // for each row, loop to add data from each column
    for (j = 0; j < P; j++) {
      var cellData = dataRangeValues[i][j];
      object[headers[j]] = cellData;
    }
    
    // push each row array into the main array
    mainObject.push(object);
  }

  var thirdEntry = mainObject[3];
  Browser.msgBox(thirdEntry.name1);
  
}

Any thoughts or corrections welcome! Tomorrow I will show some of the work I've been doing to create a GUI to add rows of data to my spreadsheet. My goal is to build a contacts database in a google doc that I can add data to, edit existing data, delete data and display data. Each of these posts will be a building block for my final application.