mercredi 26 mai 2021

Google Script to auto send emails when field in a column changes to specific text [duplicate]

I'm needing to write a Google Script that automatically sends an email to an employee - let's call them xyz@gmail.com for now - when any field in Column A changes to "Ready for Review". The email should contain the text "A data source in Row N from Publisher Name is ready for review."

The Publisher Name is in Column C, Row N. Row N is the row of the value that changed to "Ready for Review".

I also need the script to send an email when the field changes to "Reviewed - OK", but I can fiddle with the script to just duplicate the function above once it's right.

So far I have the following, and I set the trigger to go on Edit, but I'm getting emails every time the sheet is edited, and not just when a value in Column A is set to Ready for Review'

function checkValue() 
{
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("New Template");

  var valueToCheck = sheet.getRange("A4:A1000").getValue();

  if(valueToCheck = 'Ready for Review')
    {
      MailApp.sendEmail("xyz@gmail.com", "A data source is ready for review!", "The spreadsheet is here: google sheet link");
    }
}

Aucun commentaire:

Enregistrer un commentaire