Faster way to find the first empty row in a Google Sheet column

Question

I've made a script that every few hours adds a new row to a Google Apps spreadsheet.

This is the function I've made to find the first empty row:

function getFirstEmptyRow() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var cell = spr.getRange('a1');
  var ct = 0;
  while ( cell.offset(ct, 0).getValue() != "" ) {
    ct++;
  }
  return (ct);
}

It works fine, but when reaching about 100 rows, it gets really slow, even ten seconds. I'm worried that when reaching thousands of rows, it will be too slow, maybe going in timeout or worse. Is there a better way?

Answer 1

The Google Apps Script blog had a post on optimizing spreadsheet operations that talked about batching reads and writes that could really speed things up. I tried your code on a spreadsheet with 100 rows, and it took about seven seconds. By using Range.getValues(), the batch version takes one second.

function getFirstEmptyRow() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
  }
  return (ct);
}

If the spreadsheet gets big enough, you might need to grab the data in chunks of 100 or 1000 rows instead of grabbing the entire column.