0

I have a Google Form for students to input data and I would like an email sent to myself or another person if a specific question has a certain answer. In the email I would like the name of the student that submitted the form to be included in the email. The problem I'm having is the email is only sending the information from Line 2 of my Sheet. (I have line 1 Frozen as headers). I need it to pull from the last line of data entered from the Google Form.

Google Form - https://docs.google.com/forms/d/1r1Tsyfl71zfzgzhs3-_qFB57FcSSEX1zS5BkTgz-ESA/prefill

Google Sheet Data - https://docs.google.com/spreadsheets/d/1P1RD7My91jLSHS9hgEOBPv5oYnfCJQviN6Nzy_cH7Cc/edit?usp=sharing

Here is my script code:

 function CheckStudent(e) {

  
       // Fetch the email address
      var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("B2");
      var emailAddress = emailRange.getValue();
  
  
      // Send Alert Email.
    {
      var studentData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").getRange("C2");
    var studentData = studentData.getValue();
    var message = studentData + ' is not doing well today!' // Second column
    var subject = 'Student Daily Check Alert';
    MailApp.sendEmail(emailAddress, subject, message);
    }
    }
Steph Barnes
  • 1
  • 1
    Welcome to [so]. Show what you tried to solve the problem as well a brief description of your search/research efforts as is suggested in [ask]. HINT: Instead reading the values from the spreadsheet, take them from the on form submit event object. – Rubén Nov 10 '20 at 16:05
  • You can try this solution: https://talentnett.com/how-to-send-email-notifications-on-google-spreadsheets-if-any-cell-value-changes/ – Rocky Nov 10 '20 at 17:12
  • I have a doubt about your question. Do you want to execute this piece of code every time an user fills the form? Or do you want to store all the data an execute this only once? If it's the first one you can just use [triggers](https://developers.google.com/apps-script/guides/triggers/events#form-submit). – Raserhin Nov 11 '20 at 16:04
  • @Raserhin We send the form out to students every day, so we would want it to send an email to the teacher and guidance counselor if a student picks a certain answer. So yes every time someone fills it out we would want it to execute, do you have suggestions to set that up? – Steph Barnes Nov 11 '20 at 17:34

1 Answers1

0

If you are already registering the email in the sheet submitted by the form, then is as easy as creating a installable trigger.

You can do that using the event object.

If you are unfamiliar with triggers then I recommend you to have a read at the official documentation in there you can see a few examples of how to create them:

var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger("CheckStudent") // You will need to adapt your function 
                                     // to recieve the event parameter
  .forSpreadsheet(sheet)
  .onFormSubmit()
  .create();

Once you have executed the previous code then your function will trigger every time someone submit the form.

EDIT

Now taht I think about you can just do the trigger directly in the form object not going through the sheets.

Changing the event object code slighlty:

var form = FormApp.getActiveForm();
ScriptApp.newTrigger('CheckStudent')
    .forForm(form)
    .onOpen()
    .create();
Raserhin
  • 2,516
  • 1
  • 10
  • 14