I am creating a project management file in google sheets.
I’m looking to move rows from a “Tasks” tab to a “Complete” tab once a check box complete is marked.
I currently have it working except it adds the row to the bottom of the complete tab instead of the top ideally like it to sort chronologically with the newest complete tasks are on the top.
This is the script I have so far. I tried changing the .getLastrow to .getFirstrow but did not work.
Any advice would be greatly appreciated!
function onEdit(e) {
const src = e.source.getActiveSheet();
const r = e.range;
if (src.getName() != "Tasks" || r.columnStart != 7 || r.rowStart == 1) return;
const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Complete");
src.getRange(r.rowStart,1,1,7).moveTo(dest.getRange(dest.getLastRow()+1,1,1,7));
src.deleteRow(r.rowStart);
}
Danny Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
How about first creating a new empty row at the top of the destination sheet, then moving the row there?
function onEdit(e) {
const src = e.source.getActiveSheet();
const r = e.range;
if (src.getName() != "Tasks" || r.columnStart != 7 || r.rowStart == 1) return;
const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Complete");
dest.insertRows(2);
src.getRange(r.rowStart,1,1,7).moveTo(dest.getRange(2,1,1,7));
src.deleteRow(r.rowStart);
}
user27425627 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
Transferring Data to another sheet using Checkbox
I modified your code and used appendRow and this is the output base on the Question and Comment you’ve given.
Now, is there a simple way to move the completed task on the “complete” tab back to the “tasks” tab to the top in a similar way? by unchecking the complete box
Sample Output:
Code:
function onEdit(e) {
const src = e.source.getActiveSheet();
const r = e.range;
if (src.getName() == "Tasks" && r.columnStart == 7 && r.rowStart != 1) {
if (r.getValue()) {
const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Complete");
dest.appendRow(src.getRange(r.rowStart, 1, 1, 6).getValues()[0]);
src.deleteRow(r.rowStart);
dest.getRange(dest.getLastRow(), 7).insertCheckboxes();
dest.getRange(dest.getLastRow(), 7).check();
}
} else if (src.getName() == "Complete" && r.columnStart == 7 && r.rowStart != 1) {
if (!r.getValue()) {
const ret = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks");
ret.appendRow(src.getRange(r.rowStart, 1, 1, 6).getValues()[0]);
src.deleteRow(r.rowStart);
ret.getRange(ret.getLastRow(), 7).insertCheckboxes();
ret.getRange(ret.getLastRow(), 7).uncheck();
}
}
}
Note: It also works with any Checkboxes within Column 7 Starts at Row 2.
References:
- AppendRow