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
CodePudding user response：
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.