Automatically email when a cell in column has certain value

1
votes

I have a column with certain text that I use for signals. When a cell value in the column has the text "Signal1" or "Singal2" then send email with title that "Signals were found". When scanning the column any other cell expect for "Signal1" or "Signal2" can be ignored.

This is what I have so far but it's only for one cell one signal:

 function CheckSignals() {

   // Fetch data
   var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Signal").getRange("H2:H29"); 
   var data = dataRange.getValue();

   // Check for signals
   if (data = "Go Short" || "Go Long"){

     // Fetch the email address and send
     var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("C2");
     var emailAddress = emailRange.getValues();

     // Send Alert Email.
     var message = 'Signal1 ' + data; // Second column
     var subject = 'Signals were found';
     MailApp.sendEmail(emailAddress, subject, message);
     }
 }

This is working but it's too simple... There are two signals I have to scan for in the column: "Singal1" and "Signal2".

For example, if column H was scanned three "Signal1" and/or "Signal2" was found. The email content should contain information from the whole row for each cell where it was found.

Example email:

Subject: Signals was found

Message:

Signal 1 was found in the following rows with the following data: Row6: data from row 6 column A, data from row 6 column B,data from row 6 column C,data from row 6 column D, ...up to column H

Row11: Brown, Denver, 23, 1967, 11:00, 34, etc...

The spreadsheet always have 29 rows, where the first row is headers. The spreadsheet has 9 columns (A - H).

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes
  • You want to retrieve rows, when the values of the column "H2:H29" is Go Short or Go Long.
    • Values are always in "A2:H29".
  • You want to send the retrieved rows as one email.

If my understanding is correct, how about this modification?

Modification points:

  • At first, the values of "A2:H29" are retrieved. Then, the rows including Go Short or Go Long in the column "H" are retrieved.
  • In this modified script, when the rows including Go Short or Go Long in the column "H" are retrieved, the base of message is created.

Modified script:

function CheckSignals() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Fetch data
  var data = ss.getSheetByName("Signal").getRange("A2:H29").getValues();

  // Check for signals
  var contents = data.map(function(e, i) {return e[7] == "Go Short" || e[7] == "Go Long" ? e[7] + " Row " + (i + 2) + ": " + e.join(", ") : ""}).filter(String);

  if (contents.length > 0) {

    // Fetch the email address and send
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("C2");
    var emailAddress = emailRange.getValues();

    // Send Alert Email.
    var message = contents.join("\n");
    var subject = 'Signals were found';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Note:

  • The format of message is different between your script and your question. So in this modification, the required values are used, because I couldn't understand about the correct one you want. Please modify this for your situation.
  • I could't understand about Second column of var message = 'Signal1 ' + data; // Second column.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

From your shared Spreadsheet, I modified your script again. The differences between your initial question and shared Spreadsheet are as follows.

  1. Values of Signal1, Signal2 and Go Short and Go Long cannot be found at the column "H". Those values can be seen at the columns of "D", "E", "F" and "G".
  2. In your script in your question, Go Short and Go Long are used. But in your shared Spreadsheet, Go short and Go long are used.

By above differences, my modified script didn't work. This is due to my poor skill. I apologize for this situation. I reflected above differences and your shared Spreadsheet to my modified script. Please confirm the following modified script.

Modified script:

function CheckSignals() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Fetch data
  var data = ss.getSheetByName("Signal").getRange("A2:H29").getValues();

  // Check for signals
  var searchValues = ["Go short", "Go long"];
  var contents = data.map(function(row, i) {return searchValues.some(function(e) {return ~row.indexOf(e)}) ? row[7] + " Row " + (i + 2) + ": " + row.join(", ") : ""}).filter(String);

  if (contents.length > 0) {

    // Fetch the email address and send
    var emailRange = ss.getSheetByName("Email").getRange("C2");
    var emailAddress = emailRange.getValue();

    // Send Alert Email.
    var message = contents.join("\n");
    var subject = 'Signals were found';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Note:

  • About the format of the output values, I couldn't understand about it from your question and reply comment. So I prepared the values as a sample. So about this, please modify for your situation.