1

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

Spredsheet for input

Output i want.

Output i want.

Mehdi Abbas
  • 105
  • 1
  • 10
  • I think that these threads might be the answers for your question. https://stackoverflow.com/q/17632165 https://stackoverflow.com/q/26056370 – Tanaike May 04 '21 at 08:24

2 Answers2

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;
}
doubleunary
  • 13,842
  • 3
  • 18
  • 51
1

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.

Aresvik
  • 4,484
  • 1
  • 5
  • 18
  • 1
    SCREENSHOT ATTACHED – Mehdi Abbas May 06 '21 at 06:59
  • Can you share a sample Sheet with what you currently have, and what you want the outcome to look like? – Aresvik May 06 '21 at 07:48
  • YES, SCREENSHOT ATTACHED IN QUESTION. – Mehdi Abbas May 06 '21 at 08:18
  • I don't understand the screenshot. It doesn't have any context since we can't see the column letters or rows, or sheet name. What are you trying to achieve? 'Dummy129' is presumably your last record in what I guess is col E, but how do you want to use this value relative to the rest of the values in col E? – Aresvik May 06 '21 at 09:09