I am working on a tracking document for work for software errors: https://docs.google.com/spreadsheets/d/1UNAUm23iAOlc4VqgW591gGBU758RddcGNdW2SDYCvk8/edit#gid=258517814. When a developer has completed a bug fix, they select "Resolved" from a drop-down in Column A. I'm trying to accomplish 3 things with a script:
1) When "Resolved" is selected in Column A on any row, a date/time stamp is entered in Column K, and a duration is calculated in Column L. I've accomplished this much with my script "ResolvedTimestamp".
2) Right after adding the timestamp and duration in the first step, I'd like a box to pop up that has a large text field for a developer to add notes to the user. Once finished, they click a button that says "OK". The notes entered into this box will populate the cell in Column C for the resolved row.
3) After the timestamp, duration, and developer's notes are added, the resolved row will move from the sheet "Form Responses" to the sheet "Resolved. Preferably, they would be added on top of everything else, on Row 2 (since Row 1 is for headers). That way, newest resolved rows are on top. The row would disappear from "Form Responses".
Can someone help me add #2 and #3 to my existing script? Thank you for the help! Here is the script I have that does #1, for reference:
function onEdit(e) {
//Add Resolved timestamp and duration
var value = (typeof e.value == "object" ? e.range.getValue() : e.value);
if (e.range.columnStart == 1 && value == "Resolved") {
e.range.offset(0, 10).setValue(new Date());
}
if (e.range.columnStart == 1 && value == "Resolved") {
e.range.offset(0, 11).setFormulaR1C1('=R[0]C[-1]-R[0]C[-8]-((weeknum(R[0]C[-1])-weeknum(R[0]C[-8]))*2)');
}
}