Script needs to find last in row, not sheet

Question

I saw another thread Here with a solution to my start stop issue except it is written to find the last cell in the whole book. I need this for just rows A (for start), and row B (for stop) as I will have other data on the sheet.

I also need to be able to do the same function for multiple sheets... so start and stops just for that sheet.

Here is the current code I am using...

Answer 1

I believe your goal is as follows.

• When you run the script of startTime(), you want to put new Date() to the cell of the next row of the last row of the column "A".
• When you run the script of stopTime(), you want to put new Date() to the cell of the next row of the last row of the column "B".
• You want to run the script for the specific sheets you expect.

getLastRow returns the last row of all columns. When you want to retrieve the last row of the specific column, it is required to check it using a script instead of the method of getLastRow. So, from your sample Spreadsheet shown in your image, how about the following sample script?

Sample script:

const sheetNames = ["Line1",,,]; // Please set the sheet names you want to run the script.

// Ref: https://stackoverflow.com/a/44563639
Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
  const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
  return search ? search.getRow() : offsetRow;
};

function startTime() {
  const sheet = SpreadsheetApp.getActiveSheet();
  if (!sheetNames.includes(sheet.getName())) return;
  const row = sheet.get1stNonEmptyRowFromBottom(1) + 1;
  sheet.getRange(row, 1).setValue(new Date());
}

function stopTime() {
  const sheet = SpreadsheetApp.getActiveSheet();
  if (!sheetNames.includes(sheet.getName())) return;
  const row = sheet.get1stNonEmptyRowFromBottom(2) + 1;
  sheet.getRange(row, 2).setValue(new Date());
}

Note:

• If you don't want to put the value to the column "B" when the column "A" is blank, please modify sheet.getRange(row, 2).setValue(new Date()); in stopTime() as follows.

    const range = sheet.getRange(row, 2);
    if (range.offset(0, -1).isBlank()) return;
    range.setValue(new Date());
  

Reference:

• Related thread
  • Determining the last row in a single column