How to trigger email alert from a certain value on Google Sheet updated by Zapier

[Sue]

I am a beginner, so sorry if this is a dumb question.

I am trying to get an email alert triggered if there is a row that was updated by Zapier with a low NPS value. This is on column G.

I found a relevant tutorial, edited the script slightly and linked it to the Google sheet linked to the form. This is the function that is supposed to get the NPS value upon edit. The trigger was set to "on form submission". 

I think the script doesn't work for my purpose because the sheet updates by a form and therefore is not "active". 

function checkLowNPS(e)

{

  var range = e.range;

  

  if(range.getColumn() <= 7 && 

     range.getLastColumn() >=7 )

  {

    var edited_row = range.getRow();

    

    var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,7).getValue();

    if(status < '9')

    {

      return edited_row;

    }

  }

  return 0;

}

I tried reading the class scriptApp page, but I couldn't figure it out. How can I update this script to do what I want?

Thank you in advance!

Edited to correct Google Forms to Zapier - I thought the sheet was linked to Google forms but the input is on Typeform and the data is sent to Gsheets via Zapier.

[Andrew Roberts]

If status is a number you need to remove the speech marks:

if (status < 9) {

On Thursday, 9 January 2020 08:37:33 UTC, Sue wrote:

I am a beginner, so sorry if this is a dumb question.

I am trying to get an email alert triggered if there is a Google form that was submitted with a low NPS value. This is on column G.

[Clark Lind]

As Andrew pointed out, the "status < '9' "  should be: "status < 9 " (no quotes around the 9). Also, I'm assuming this is being called from an  onEdit(e) function? Something like the following could work:

onEdit(e) {

  var row = checkLowNPS(e);

  if (row < 9 && > 0) {    //I added the > 0 since a zero could be returned if status is > 9

    MailApp.sendEmail(
        "desired email",    //email address
       "Low NPS Alert",  //subject
       "Someone edited row " + row   //email body

        );