Code correction

[Jossey D]

Hi, 

I would highly appreciate any help on the following code.

Currently, the range ("H2:H16");  has 3 conditions: "Buy", "Near Buy" and "Wait". I would like the email to return all the values in ("c2:d16"); where the value is "Buy" or  "Near Buy " for the ("H2:H16").

In my view, the change needs to be made to the section of the code with yellow highlights, but I do not know-how. Many thanks in advance for your help...

==================

CODE:

function sendMailWatchlist() {

   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WATCH"); //This is getting the sheet you want.

  var sellorhold = sheet.getRange("H2:H16"); //This is the range which you are checking for a TRUE out of your companies. It is currently looking through rows 2 to 30 in column F. Can modify to suit needs.

  var names = sheet.getRange("c2:d16");//Gets the names of all companies

  var sellorholdValues = sellorhold.getValues(); // Gets the values from range ("TRUE" or "FALSE")

  var nameValues = names.getValues(); // Gets the values of the company names.

  var message = "";

  var goodBuyList = [];

  var yourName = "Vrock"

    var yourEmailAddress = "vroc...@Gmail.com"  

  

  //Looping through range of companies and storing ones with TRUE signal in a array called goodBuyList 

  for (var i=0; i<sellorholdValues.length; i++){

    if(sellorholdValues[i] == "Buy"){

    goodBuyList.push(nameValues[i]); }

      }

      //Send Email

  message = "Hi " +yourName  + ",\nThe following stock(s) from your WATCHLIST are in the BUY & Near-Buy range:\n"; //Trying to make email look nice.

  for(var i=0; i<goodBuyList.length; i++){message+="\n" + goodBuyList[i]}

  message +="\n";

  message += "\nGood luck" +yourName;

  var subject = 'Watchlist Update!';

  GmailApp.sendEmail("vroc...@Gmail.com", subject, message); //how Gmail App works: put email, subject then message. This end the email.

 

}

 

[CBMServices Web]

Sounds like you just need a small change to the if statement.

Change it from:

if(sellorholdValues[i] == "Buy"){

To:

if((sellorholdValues[i] == "Buy") || (sellorholdValues[i] == "Near Buy")){

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/057150f7-4f16-48ab-b535-f0067eb40aden%40googlegroups.com.

[M Muzamil Naqshbandi]

Thanks much George. Your suggestion worked! Can I ask 2 related questions please: 

1. How can I get the word "WATCHLIST" to appear bold and underlined in the email?

2. in this statement var names = sheet.getRange("c2:d16"); how can I get names of companies from C2:C16 and E2:E16. Currently, it gets the matches from columns C and D while I would like the match from C & E (usually separated by a comma)

TIA again

Best wishes

[CBMServices Web]

The email you are sending is a plain text email so there is no way to format any of the text. You can use html as the format of the email and with that, it opens up much more formatting capabilities.

Do you have any html experience? If not check out this site for some examples:

https://www.w3schools.com/html/

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bddd60cb-0361-4188-a30f-a2d5fc773a5cn%40googlegroups.com.

[Jossey D]

Many thanks, George. I will learn HTML from the website you mentioned. Hopefully, the code is not difficult since all I want is the above email in HTML format. Any help on the 2nd question (  in this statement var names = sheet.getRange("c2:d16"); how can I get names of companies from C2:C16 and E2:E16. Currently, it gets the matches from columns C and D while I would like the match from C & E (usually separated by a comma)  ) is appreciated. Thanks again.

[CBMServices Web]

Yes, you can display names in the email also. I see that your script retrieves the names and stores them in this variable:

var nameValues = names.getValues(); // Gets the values of the company names.

To get the names, you just need to grab the appropriate name from that list and add it to your email message.

How would you like the email to look?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/258e1660-5f8e-439d-95f9-519aa2e57a3bn%40googlegroups.com.

[Jossey D]

Hi George,

I would like the email to look like the following:

=======================================================

Dear investor,

Please find below the update on your portfolio:

LIST #1:    SELL LIST             

1. MICROSOFT, CURRENT PRICE 130
2. SEAGEN, CURRENT PRICE 290

LIST #2:    APPROACHING SELL TARGET

1. APPLE, CURRENT PRICE 140
2. AMGEN, CURRENT PRICE 205

LIST #3:    APPROACHING STOPLOSS 

1. AMAZON, CURRENT PRICE 125
2. FACEBOOK, CURRENT PRICE 220
3. NETFLIX, CURRENT PRICE 225

LIST #4:    HOLD LIST             

1. AUTODESK, CURRENT PRICE 325
2. CADENCE DESIGN, CURRENT PRICE 525
3. COSTCO, CURRENT PRICE 425
4. VERISIGN, CURRENT PRICE 223

 All the best.

=======================================================

The name of the stock and the current price is currently in Column C & E respectively in Google Spreadsheet. Thanks again for your help.

Best wishes,

[CBMServices Web]

I see. Sounds like you need additional values on the check which currently only looks at Buy and Near Buy as well.

 I can help you get this done. Let me pull together a quick script for you to do this rather than updating the script you currently have.

 Will send it back to you shortly.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0e8011a0-acc9-47ae-aa14-14a11d0ac93en%40googlegroups.com.