Google Sheets – Email Specific Spreadsheet Row Data on Event

google sheetsgoogle-apps-scriptgoogle-forms

Scenario

Basic support ticket clone:

Users submit a form (e.g. answering fault reporting questions)
Data is obviously stored as rows in the corresponding spreadsheet
I've added an onEvent trigger to send email onFormSubmit
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

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

Best Answer

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

Look for rows that have a "Closed" status and an empty flag field. (Search the matrix generated by SpreadsheetApp.getActiveSheet().getDataRange().getValues())
Now, for each row, do the following:
Find the email cell in that row (you know the column number), send the emails
Set the flag cell in that row to 1 or something

That's it :)

Related Solutions

Google-sheets – Change a working function from onOpen() to onEdit()

Yes, if you want a script to be triggered by updates to spreadsheet A, then it should be bound to that spreadsheet. It can still access the other spreadsheets by their URL or Id. For example, this function copies the cells F2:H6 of the present sheet to the cells A1:C5 of another one.

function copyStuff() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('MasterSheet1')
  var ss2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/...');
  var targetSheet = ss2.getSheetByName('Sheet1');
  targetSheet.getRange("A1:C5").setValues(sourceSheet.getRange("F2:H6").getValues());
}

Google-forms – How to we NOT record, but on the fly disregard responses not matching an already existing roster

Short answer

Once the response is submitted it's stored and there isn't a way to avoid this. By the other side, it's not possible to delete individual responses on the responses container bounded to the Google Form.

Explanation

At this time Google Forms responses are stored in the Google form file and optionally could be set to automatically send them to a Google spreadsheet by using a built-in feature.

It's worth to say that Google Forms also could be used as a script container and that it have the event on form submit that could be used to do some things with the submitted responses like to discriminate which responses should be sent to a Google spreadsheet, but not for delete individual responses from the responses container bounded to the Google form file.

Remarks

As was already mentioned in a comment to the question by Canadian Luke, relying on students typing their ID in a Google Form and automatically discriminating their responses based on it could cause "problems". One way to minimize this is to send to each student the URL to a pre-filled Google Form with their ID already filled up.