Walden Systems Geeks Corner News Google Apps Script: Setting Range Cell Values Using a Two Dimensional Array Rutherford NJ New Jersey NYC New York City North Bergen County

Google Apps Script: Setting Range Cell Values Using a Two Dimensional Array

There is always more than one way to accomplish a programming task. In this example I will explain two different ways to write data to the spreadsheet range. In order to work with a range we want to create a two dimensional array of data. The two dimensional array could be populated from a database or a different spreadsheet range. Populating a two dimensional array is out of the scope of this article.

Let's assume we have a two dimensional array of data that we want to work with and use it to populate a range in a spreadsheet. The first method will calculate the number of rows and columns that a two dimensional array contains. It will then create a range based on the size of the array to write into. This function will take a starting row and starting column that we will use as an anchor to write into. Function dispValuesInSheet() will take four parameters. ‘valueList’, which is a two dimensional array of data. ‘strRow’ and ‘strCol, which we will use as a starting position when writing into the sheet range. ‘sheet’ is where we will calculate and create the range to write into.

To calculate the number of rows in an array we use the array property ‘length’. To retrieve number of columns in a row, we use the first row index zero and then use the array property ‘length’


  var numOfRows = valueList.length;
  var numOfCols = valueList[0].length;

With these two values we can now calculate the size of the range. And then we will call the ‘setValues()’ function to write the two dimensional array into the range that we just created.


  var rangeToSet = sheet.getRange(strRow, strCol, numOfRows, numOfCols);
  rangeToSet.setValues(valueList);

It is important to note that the range we created must be identical in size to the two dimensional array being used to write into the range.


function dispValuesInSheet(valueList, strRow, strCol, sheet ) {
  var numOfRows = valueList.length;
  var numOfCols = valueList[0].length;
  var rangeToSet = sheet.getRange(strRow, strCol, numOfRows, numOfCols);
  rangeToSet.setValues(valueList);
};

The second method we will use is to iterate the two dimensional array one row and column at a time. At the same time we will incrementally adjust the range one cell at a time. This will allow us to write each value in the two dimensional array to each range cell. The function dispValuesInSheet2() will take exactly the same four parameters as the first method. First we will get the top left most range cell that we will use to write the first value into.


  var rangeToSet = sheet.getRange(strRow, strCol );

After that we will set up two simple ‘for’ loops to iterate each row and column in a two dimensional array. You can use a ‘Logger.log()’ to print out each value in the array.


  for (var currRow=0; currRow < valueList.length; currRow++) {
    var rowValues = valueList[currRow];
    for (var currCol=0; currCol < rowValues.length; currCol++) {
    	Logger.log(rowValues[currRow][currCol]);
    };
  };

Next it is just a matter of calculating the correct range cell to iterate into and set its values.


  // get the next column to set
  rangeToSet = sheet.getRange(strRow, strCol+currCol+1 );
  // get the next row to set
  rangeToSet = sheet.getRange(++strRow, strCol );

The full function code is as follows:


function dispValuesInSheet2(valueList, strRow, strCol, sheet ) {
  var rangeToSet = sheet.getRange(strRow, strCol );
  for (var currRow=0; currRow < valueList.length; currRow++) {
    var rowValues = valueList[currRow];
    for (var currCol=0; currCol < rowValues.length; currCol++) {
      rangeToSet.setValue(rowValues[currCol]);
      rangeToSet = sheet.getRange(strRow, strCol+currCol+1 );
    };
    rangeToSet = sheet.getRange(++strRow, strCol );
  };
};