Send email whenever a specific cell contains a specific value

Question

I have a Google Spreadhsheet ("Trigger") in a Google Workbook with three columns:

Column A: Email address
Column B: Email text
Column C: cell which either contains "yes" or "no"

The values for these columns start in row 2 and so far I need a solution for only this single row.

Whenever the value in cell C2 is "yes" (the calculation here is based on a current stock price) I want to send an email to the email account in A2 with the text in B2 and the subject to be "Price has been reached? ==" & C2

What I have so far (I am very new to this but have some experience in VBA) is this:

 function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Price has been reached? ==";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

This works in sending an email but does not take the value in C2 into account. How can I achieve this?

Also I am wondering how to code that this script should only be linked to spreadsheet "Trigger" and not to other sheets in this workbook?

Once this works I can define a proper trigger within the Script Editor and then think about how to make this work for more than one row.

So just to be clear the main question is about setting up the trigger? What makes c2 become yes? Is it a formula based on the price? How do you get the price? — Robin Gertenbach
The main question is how to code that only if c2 becomes "yes" an email is sent. Yes it is a formula based on a price using +Googlefinance("PG","price"), but I omitted these columns in the description as only the calculated value in c2 is important — stefan

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-10-06T19:53:09

Some functions such as GOOGLEFINANCE do not cause onEdit or onChange to trigger.

You'll need to create a time based trigger that checks frequently (up to once a minute)

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trigger");  // To only handle the trigger sheet
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    if (row[2] === "Yes") {       // Trigger only if Column C is "Yes"
      var emailAddress = row[0];  // First column
      var message = row[1];       // Second column
      var subject = "Price has been reached? ==" + row[2]; // Add "Yes" although by your trigger logic it will always say yes in the email
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

And then set the trigger in the script editor.

Send email whenever a specific cell contains a specific value

1 Answers