1

I am trying to loop through a large range of cells testing a value in column A, if that returns true then I want to store the adjacent 3 cells (columns B-D) in an array and ultimately paste the array on a separate sheet. The current code finds all the correct values but it writes them all in one row instead of multiple rows and 3 columns like in the original data source. 

var dataset = [],
    month,
    i,
    j,
    x = 0,
    targetmonth = ss.getSheetByName("BOH").getRange("B2").getValue(),
    location = ss.getSheetByName(output).getRange("D3").getValue();

for ( i = 7; i < 3000; i++){
  month = ss.getSheetByName(location).getRange(i,1).getValue();
  if (month == targetmonth){
    for (j = 2; j<5; j++){
      dataset [x] = [ss.getSheetByName(location).getRange(i,j).getValues()];
      x = x + 1;
    } 
  }
}

//I've changed the range size in line below to 360x3 which is what it should be 
//but this line currently only runs when set to 1x360

ss.getSheetByName(output).getRange(8,3,360,3).setValues([dataset]);
Rubén
  • 34,714
  • 9
  • 70
  • 166
user3609522
  • 13
  • 3
  • No love for working solely in JavaScript Arrays? Your script will be immeasurably faster. – tehhowch Jun 04 '18 at 21:11
  • Could you direct me to some resources on that? Relatively new to JavaScript. – user3609522 Jun 04 '18 at 21:30
  • Your JavaScript reference of choice and innumerable web tutorials will all have plenty of information on the `Array` object. I prefer Mozilla Developer Network, but to each their own. As far as using them in Apps Script, read Apps Script documentation for best practices in the `Spreadsheet` class. – tehhowch Jun 04 '18 at 22:16

1 Answers1

0

Abiding by best practices, you should minimize repeated calls to the Spreadsheet Service through the use of getValues(). Your current script loops accesses the values of A7:A2999 one-by-one, when it could do it in a single read:

// const x -> no reassignment / redeclaring x allowed. (You can still manipulate the object).
const startRow = 7, endRow = 2999,
    numRows = endRow - startRow + 1;
const months = ss.getSheetByName(location)
  .getRange(startRow, 1, numRows, 1).getValues();

Then you conditionally access columns B:D of that same range. You will find that it is still faster to simply read this all into memory at the start, and only access your desired rows & columns when needed:

const startCol = 2, endCol = 4,
    numCols = endCol - startCol + 1;
const targetValues = ss.getSheetByName(location)
  .getRange(startRow, startCol, numRows, numCols).getValues();

You should also use more meaningful iteration index names than i and j, and you don't have declare everything at the start of your function (read about JavaScript and 'hoisting'), when point-of-use is more meaningful.

The rest of your function then looks like this:

const output = [],
    targetMonth = /** set this */,
    destinationName = /** set this */;
for (var monthIndex = 0; monthIndex < months.length; ++monthIndex) {
  // Add the array of target column values to the output (by reference)
  if (months[monthIndex][0] === targetMonth) {
    output.push(targetValues[monthIndex]);
  }
}
// Write the output array.
ss.getSheetByName(destinationName).getRange(8, 3, output.length, output[0].length).setValues(output);

We just went from ~numRows x numColumns Spreadsheet accesses to just ~4! Given that individual cell access calls takes around 0.1 seconds this will be boatloads faster. Yes, if you read a lot of cells it can take a while (getting/setting values from 400k cells is ~30 seconds), but it takes nowhere near as long as 1-by-1 access.

References:

PS: if you change the values in targetValues before you serialize the data, those referenced in output will update as well, because they are the same object. (Read about "by value" / deep copy and "by reference" / shallow copy to understand why. For this script as written, the distinction does not matter because they are not modified.)

tehhowch
  • 9,645
  • 4
  • 24
  • 42