Title: Automatically email when a cell in column has certain value
Tags: google-apps-script;google-sheets
Question: 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).


Here is the accepted answer: 
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 &gt; 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:


map()
filter()
join()


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.


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".
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 &gt; 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.


Comment for this answer: Very elegant solution, thank you!
Your code makes sense. Only problem is it's not producing a result.
At the moment the spreadsheet does have more than one "Go Short" and no "Go Long", but no email is sent...
Comment for this answer: thank you for your interest. Unfortunately it's not working for me. I now made a new spreadsheet just to re-test again:
Comment for this answer: This is now working perfectly well!
Thank you for this excellent solution.
I hope others will benefit by this also.
Comment for this answer: @Petrus Thank you for replying. I apologize for the inconvenience. About the situation of your reply comment, in my environment, the script works and email can be sent. So, unfortunately I cannot replicate your situation. Can you provide a sample Spreadsheet and script for replicating your situation? Of course, please remove your personal information. I would like to confirm it. If you can cooperate to resolve your new issue, I'm glad.
Comment for this answer: @Petrus If you cannot understand my ENglish, please tell me. I have to apologize for it and modify it. Because in order to resolve your issue, more information for replicating your situation is required. Can you cooperate to resolve your issue?
Comment for this answer: @Petrus Thank you for replying and providing the sample Spreadsheet. I modified the script by reflecting the differences between your initial question and your shared Spreadsheet. Could you please confirm it? If you use the modified script, please use your shared Spreadsheet. I confirmed that the script worked for your shared Spreadsheet.
Comment for this answer: @Petrus Thank you for replying. I'm glad your issue was resolved. Thank you, too.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2019:00:05/28/19', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<google-apps-script><google-sheets>', 'Title': 'Automatically email when a cell in column has certain value', 'ViewCount': 97, 'id': 56340483, 'user_AboutMe': None, 'user_AccountId': 15953450.0, 'user_DisplayName': 'Petrus', 'user_Reputation': 189.0, 'user_Views': 102.0, 'user_id': 11512178.0}}