How to get Last Row of column E in google spreadsheet using Google App Script

Question

I m new at GS, i want to get last Row on Column E. Then use this last row to make loop to get all Data in Column E.

Spredsheet for input

Output i want.

Answer 1

To get an array that contains all non-blank values in column E:

  const ss = SpreadsheetApp.getActive();
  const values = ss.getRange('Sheet1!E1:E')
    .getValues()
    .flat()
    .filter(String);

To get the row number of the last row that has visible content in column E, and loop through the values in column E until that row, use this:

function iterateColumnE() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('E1:E' + getLastRow_(sheet, 5));
  range.getValues().forEach(row => {
    console.log(row[0]);
  });
}

/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.5, written by --Hyde, 4 April 2021
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}

Answer 2

This solution is an ARRAYFORMLA that can sit in a cell.

This will get you the row number of furthest data down column E:

=ARRAYFORMULA((LOOKUP(2,1/(E:E<>""),ROW(E:E))))

This will get the value:

=INDEX(E:E,ARRAYFORMULA((LOOKUP(2,1/(E:E<>""),ROW(E:E)))))

Either formula can sit in any cell in the sheet, apart from column E.

If you can show a sample sheet with data, I'll better understand what sort of loop you need.