Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
share|improve this question
    
Can you share a doc with us? –  Jacob Jan Tuinstra Jan 25 '13 at 20:48

1 Answer 1

up vote 0 down vote accepted

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 :)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.