2

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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