Obtaining the last row of a sheet when columns contain hidden content

Question

I have been working on a personal project and encountered a problem where I cannot figure out 'how to obtain the last row of a particular table when there is hidden data such as checkboxes.' I have been researching on the topic and came across a possible solution but I am not sure how to fit it into my code or whether I could incorporate it into mine at all because frankly, I am not very fluent yet in the coding language.

Credit goes to the original writer (https://yagisanatode.com/) for the following code:

function getLastRowSpecial(range){
   var rowNum = 0;
   var blank = false;
 for(var row = 0; row < range.length; row++){
 if(range[row][0] === "" && !blank){
  rowNum = row;
  blank = true;
} 
else if (range[row][0] !== ""){
    blank = false;
   };
};
 return rowNum;
};

and here is my current code:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSh = event.source.getActiveSheet();
  var currentCell = event.source.getActiveRange();

if (currentSh.getName() == 'invoice_approval' && currentCell.getColumn() == 5 && currentCell.getValue()== 'Approved') {

 var row = currentCell.getRow();
 var numColumns = currentSh.getLastColumn();

  var targetSh = ss.getSheetByName('open_invoices');
  var target = targetSh.getRange(targetSh.getLastRow()+1, 1);
  currentSh.getRange(row,1,1,numColumns).moveTo(target);
  currentSh.deleteRow(row);
}
// (Moving back rows <open_invoices to invoice_approval> when a cell's value is changed from 'Approved')

else if (currentSh.getName() == 'open_invoices' && currentCell.getColumn() == 5 && currentCell.getValue()!= 'Approved' ) {

   var row = currentCell.getRow();
   
  var targetSh = ss.getSheetByName('invoice_approval');
  var target = targetSh.getRange(targetSh.getLastRow()+1, 1);
  currentSh.getRange(row,1,1,5).moveTo(target);
  currentSh.deleteRow(row);

}
}

I made a sample sheet and listed the problem I am facing:

https://docs.google.com/spreadsheets/d/1Yol5FAa3Sw84zRDanOCILUhD1IWMKjrHAJewT-P18to/edit?usp=sharing

Answer 1

I believe your goal is as follows.

• When the column "E" of "invoice_approval" sheet is Approved, you want to move the row to the 1st empty row of "open_invoices" sheet.
• When the column "E" of "open_invoices" sheet is NOT Approved, you want to move the row to the 1st empty row of "invoice_approval" sheet.

In this case, how about the following modification?

Modified script:

function onEdit(event) {
  // This sample script is from https://stackoverflow.com/a/44563639
  Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
    const range = this.getRange(offsetRow, columnNumber, 2);
    const values = range.getDisplayValues();
    if (values[0][0] && values[1][0]) {
      return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
    } else if (values[0][0] && !values[1][0]) {
      return offsetRow + 1;
    }
    return offsetRow;
  };

  var {source, range} = event;
  var currentSh = source.getActiveSheet();
  var sheetName = currentSh.getSheetName();
  if (sheetName == 'invoice_approval' && range.columnStart == 5 && range.getValue() == 'Approved') {
    var row = range.rowStart;
    var numColumns = currentSh.getLastColumn();
    var targetSh = source.getSheetByName('open_invoices');
    var target = targetSh.getRange(targetSh.get1stEmptyRowFromTop(1), 1);
    currentSh.getRange(row, 1, 1, numColumns).moveTo(target);
    currentSh.deleteRow(row);
  } else if (sheetName == 'open_invoices' && range.columnStart == 5 && range.getValue() != 'Approved') {
    var row = range.rowStart;
    var targetSh = source.getSheetByName('invoice_approval');
    var target = targetSh.getRange(targetSh.get1stEmptyRowFromTop(1), 1);
    currentSh.getRange(row, 1, 1, 5).moveTo(target);
    currentSh.deleteRow(row);
  }
}

• In this script, the event object is used. Ref When the event object is used, the process cost can be reduced a little. Ref
• In order to retrieve the 1st empty row from the top of the sheet, I used a sample script from this thread.

References:

• Event Objects
• Benchmark: Event Objects for Google Apps Script
• Related thread.
  • Google Script - get last row of specific column