Writing to Sheets when you don't know the number of rows

Question

I'm attempting to write an array to Google Sheets. I'm getting my data from a datasheet and building the array based on if the item matches criteria. The problem is that I don't know how many items will be in the array.

function sortItems(){
  var ss = SpreadsheetApp.getActive();
  var consume = ss.getRange('Consumables').getValues();
  var count = (consume.length);
  var originalrow = 0;
  var data = [];


  do {
    if(consume[originalrow][18] == "y"){
      data.push(consume[originalrow]);
      Logger.log(data);
    }
    originalrow++;
  } while (originalrow < count);
  ss.getRange('NewSheet!B3:Y100').setValues(data);
}

When I run this it tells me "Exception: The number of rows in the data does not match the number of rows in the range. The data has 58 but the range has 98." How do I dynamically set the number of rows it will write to?

Answer 1

One way is to use Range.offset(), like this:

  ss.getRange('NewSheet!B3')
    .offset(0, 0, data.length, data[0].length)
    .setValues(data);

See Range.offset().

An easier way to filter the values is to use Array.filter(), like this:

function sortItems() {
  const ss = SpreadsheetApp.getActive();
  const data = ss.getRange('Consumables')
    .getValues()
    .filter(row => 'y' === row[18]);
  ss.getRange('NewSheet!B3')
    .offset(0, 0, data.length, data[0].length)
    .setValues(data);
}