1

I'm trying to find a way to find the last row with data in Column D. I also want the search to start at Row 4.

I'm really struggling and would appericate any help please.

Simon Davies
  • 11
  • 1
  • 3
  • 1
    Have you tried [this](https://stackoverflow.com/questions/8116043/get-the-last-non-empty-cell-in-a-column-in-google-sheets)? – Broly Jun 14 '21 at 16:17
  • 1
    Try getColumnHeight() here: https://stackoverflow.com/a/55425107/7215091 – Cooper Jun 14 '21 at 16:20
  • I thought that this thread might be useful. https://stackoverflow.com/a/44563639/7108653 – Tanaike Jun 15 '21 at 01:28

2 Answers2

2

You can refer to this code:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var rowOffset = 3;
  var count = sheet.getRange('D4:D').getDisplayValues().flat().filter(String).length;
  var lastRow = count+rowOffset;
  Logger.log(lastRow);

What it does?

  1. Select Range D4:D and get its value, since you want to get the last row with data in column D starting at row 4.
  2. Use array.flat() to change 2-d array into 1-d array.
  3. Use array.filter() to remove empty values. Then get it's array length
  4. To get the last row index, Use the cell count that has data which we obtained in step 3 and add it to 3 (start offset since we start our search at row 4)

Note:

  • This solution will only work assuming you don't have empty rows in between.

Output:

enter image description here

Execution log
2:01:53 AM  Notice  Execution started
2:01:54 AM  Info    13.0
2:01:55 AM  Notice  Execution completed
Ron M
  • 5,791
  • 1
  • 4
  • 16
0

There are many ways to find the last value in a column, but here is one. See if this helps!

function myFunction() {
  const spreadsheet = SpreadsheetApp.openByUrl('Insert Sheet URL Here');
  const sheet = spreadsheet.getSheetByName('Insert Sheet Name Here - e.g. Sheet1');
  const lastSheetRow = sheet.getLastRow();
  let lastColumnRow = 'This column is empty';
  let reversedColumnValues = sheet.getRange(1, 4, lastSheetRow).getValues().reverse();
  for (index in reversedColumnValues) {
    if (reversedColumnValues[index][0] != '') {
      lastColumnRow = lastSheetRow - index;
      break;
    }
  }
  Logger.log(lastColumnRow);
}

This Apps Script Video may help you out too: https://www.youtube.com/watch?v=1Po1QElOFPk

David Weiss
  • 93
  • 1
  • 5