Efficient way of getting last value in a specific column

Question

I wrote a function to get the last value in a single column in a Google Sheet.

// Returns the row number of the first empty cell
// Offset should be equal to the first row that has data
function getLastRowInColumn(sheet, column, rowOffset) {
  var lastRow = sheet.getMaxRows();
  for (var row = rowOffset; row < lastRow; row++) {
    var range = sheet.getRange(row, column);
    if (range.isBlank()) {
      return row;
    };
  }
}

I can assume that all values are filled out, therefore the function is actually returning the first empty cell it finds (in this case, that is equivalent to the first empty row).

The function is taking a long time to run for a sheet with ~1000 rows. How can I make it more efficient?

Thanks in advance.

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

See the answer here to a similar question. It is about getting the first empty row, you are just one higher. Faster way to find the first empty row

edited May 23 '17 at 10:27

Community

1
1

answered Oct 14 '16 at 15:59

Karl_S

3,364
2
19
33

score 0 · Accepted Answer · answered Oct 14 '16 at 21:55

Read all of the data into a local array and read through that, just remember the row will be a zero-indexed (start from 0) rather than the row number which starts from 1:

// Returns the row number of the first empty cell
// Offset should be equal to the first row that has data
function getLastRowInColumn(sheet, column, rowOffset) {
  var lastRow = sheet.getMaxRows();
  var data = sheet.getDataRange().getValues()
  for (var row = rowOffset; row < lastRow; row++) {
    if (data[row][0] === '') {
      return row;
    };
  }
}

Efficient way of getting last value in a specific column

2 Answers2