I have a short function that I made using the answers to other questions here but it is not working perfectly and I don't understand why!
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = e.source.getActiveSheet();
var r = e.source.getActiveRange();
if(s.getName() == "Input Expenses" && r.getColumn() == 14 && r.getValue() == "Verified") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Verified Expenses");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
The idea is to check for "Verified" and if it is, then move it to a "Verified Expenses" sheet. I prefer this to be a totally separate spreadsheet so my data entry sheet is quick to load. I also tried openByID
in case you are wondering.
This is the change I made to try and get it to work:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = e.source.getActiveSheet();
var r = e.source.getActiveRange();
var t = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1R6YQAjV5G8JVzr2ecw6IZQFzC8J5KbzLImXOb6KGDIw/edit#gid=0");
if(s.getName() == "Input Expenses" && r.getColumn() == 14 && r.getValue() == "Verified") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = t.getSheetByName("Verified Expenses");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
I feel like it is something very simple that I overlooked because I'm staring at it too long.
--edit--
This is the execution transcript when I run it as a manual function rather than onEdit()
[15-05-27 12:28:50:192 EDT] Starting execution
[15-05-27 12:28:50:209 EDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[15-05-27 12:28:50:244 EDT] Spreadsheet.getActiveSheet() [0.034 seconds]
[15-05-27 12:28:50:245 EDT] Spreadsheet.getActiveRange() [0 seconds]
[15-05-27 12:28:50:308 EDT] SpreadsheetApp.openByUrl([https://docs.google.com/spreadsheets/d/1R6YQAjV5G8JVzr2ecw6IZQFzC8J5KbzLImXOb6KGDIw/edit#gid=0]) [0.062 seconds]
[15-05-27 12:28:50:308 EDT] Sheet.getName() [0 seconds]
[15-05-27 12:28:50:308 EDT] Range.getColumn() [0 seconds]
[15-05-27 12:28:50:309 EDT] Sheet.getName() [0 seconds]
[15-05-27 12:28:50:309 EDT] Execution succeeded [0.1 seconds total runtime]