Want to email specific spreadsheet row data on event

Question

Scenario

Basic support ticket clone:

1. Users submit a form (e.g. answering fault reporting questions)
2. Data is obviously stored as rows in the corresponding spreadsheet
3. I've added an onEvent trigger to send email onFormSubmit
4. Admin checks spreadsheet and updates additional Status column to "Closed" when the job is done

Question

I want to add a step 5 to the scenario where the user who submitted the fault and the department responsible (both emails addresses stored in the row) get a reply when the Status column is updated to "Closed".

I can add the onEdit event which will send an email, but I can't

1. Get the active row
2. Get the emails from that row (so I send an email to only relevant addresses)
3. Trigger onEdit ONLY when the Status column is changed

Answer 1

Have an additional column, "flag" (leave it blank). Now, in your onEdit event:

1. Look for rows that have a "Closed" status and an empty flag field. (Search the matrix generated by SpreadsheetApp.getActiveSheet().getDataRange().getValues())

2. Now, for each row, do the following:

3. Find the email cell in that row (you know the column number), send the emails

4. Set the flag cell in that row to 1 or something

That's it :)