Get last row ignoring formula in google script

user_17684392 0
Viewed 214

I used a google script that move row from one sheet to another from column A to column D. On column E i have a formula. How can i put data on last row from Column A to D from example, ignoring formula from column E. Can someone help me with this?

function onEdit(e){
  let r = e.range;
  if (r.columnStart != 4 || r.rowStart == 1 || e.value == null) return;
  const sh = SpreadsheetApp.getActive();
  const valArray = ["Waiting","In progress","Complete"];
  const destArray = ["Order","Open order","Complete order"];
  let dest = sh.getSheetByName(destArray[valArray.indexOf(e.value)]);
  let src = sh.getActiveSheet();
  if (dest.getName() == src.getName()) return;
  src.getRange(r.rowStart,1,1,4).moveTo(dest.getRange(dest.getLastRow()+1,1,1,4));
  src.deleteRow(r.rowStart);
}

Here an example of my problem, maybe this will explain better:https://docs.google.com/spreadsheets/d/1qowADCPYiyej25ezXtjVLO5fvg9Gr9rolX3bh2-ZAG4/edit#gid=0

formula move row
1 Answers

Replace dest.getLastRow() by dest.getLastDataRow('A') and add this function:

Object.prototype.getLastDataRow = function(col){
  var lastRow = this.getLastRow();
  var range = this.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }  
};

I still sugest you to erase all formulas in column E and put in E1

={"price * 2";ARRAYFORMULA(if(C2:C>0;C2:C*2;""))}

so that the formula will populate / will be active the new row

user_150193800
Related