How to send a conditional email depending on a google form answer on google sheets?

user_19793046 0
Viewed 106

I have just set up a google form that automatically populates a Google Sheet. I'm trying to set up an automatic email to notify a specific contact everytime someone submits a form and marks their test as positive. If they submit a negative test, I don't want it to send anything.

I've written the following with the following trigger:
Select event source - From Spreadsheet
Select event type - On from Submission

I've linked the formula to another Sheet for the script contacts. I managed to get it to send an email, however, it was previously sending an email even if a negative test was submitted. Because of this, I added another 'If' for a negative display but now it wont send anything at all.

Can anyone help? This is my first time coding any feel like I have hit a roadblock.

Formula:

function positiveemailsubmission()
{
    var positiverange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LFT Submissions").getRange("G2:G50000")
    var positivevalue = positiverange.getDisplayValue("Positive");
    {
      if (positivevalue.getDisplayValue() === "Positive")
      {
        var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Script info").getRange("B2");
        var emailAddress = emailRange.getDisplayValue();
        var message = 'A positive LFT test has been submitted. Please see the form for more information (WWW.DOCUMENTLINK.CO.UK)';
        var subject = 'Positive LFT Submission ';
        GmailApp.sendEmail(emailAddress, subject, message);
      }
      if (positivevalue.getDisplayValue() === "Negative") 
      {

      }
    }
}

google-forms google-apps-script google-sheets
1 Answers

In order to provide a proper response to the question, I'm writing this answer as a community wiki, since the solution is in the comments section.

Like @Yuri Khristich said, positiverange was a range of a lot of rows "G2:G50000", in this case what you need is the value of the last row (last submit from Form) to determine if that specific answer was "Positive" or "Negative".

I tested your script with the changes mentioned in the comments and it worked.

function positiveemailsubmission()
{
    var positiverange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LFT Submissions");
    var  positivevalue = positiverange.getRange("G" + positiverange.getLastRow());
    {
      if (positivevalue.getDisplayValue() === "Positive")
      {
        var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Script info").getRange("B2");
        var emailAddress = emailRange.getDisplayValue();
        var message = 'A positive LFT test has been submitted. Please see the form for more information (WWW.DOCUMENTLINK.CO.UK)';
        var subject = 'Positive LFT Submission ';
        GmailApp.sendEmail(emailAddress, subject, message);
      }
      if (positivevalue.getDisplayValue() === "Negative") 
      {

      }
    }
}
user_179264780
Related