Mailing system in GoogleSheets, resume of crypto-transactions. I'm notified only about 1 of 7 operations, and more than once. How to edit the script?

Question

Actually the code is as follows, there are 7 active operations plus one of the required two sendEmail functions.

Most of this code has been copied from another discussion so not mine, tried to adjust it up to my purpose.

The goal is: 1. get the script to check if a cell on the sheet called AutoMail is empty or filled with either "gain position" or "purchase position" (it's not one cell, there are two, the one on the left is eventually filled with the first expression and on the right with the latter); 2. in case of gain/purchase position, get the relative coin abbreviation and amount from two cells located on another sheet (YourData) and to paste them in a personalized text used as subject and then in the body of my message, and of course let this email reach my inbox, possibly ONCE the cell is filled up with the above mentioned expressions, not every minute.

I'd like the script to check the sheets once every minute, I think I mistake in the trigger setting as well. The repetition of var ss1 and var sheet1 might be useless here.

    function readCell() {
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain1 = sheet1.getRange("C2:C46").getValue();
      var operationpurchase1 = sheet1.getRange("D2:D46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin1 = sheet2.getRange("B3");
      var amount1 = sheet2.getRange("B4");
      if(operationgain1="gain position") sendEmail(operationgain1);
        function sendEmail(operationgain1) {
          var email = "[email protected]";
          var subject = "Your "+amount1+" "+coin1+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin1+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain2 = sheet1.getRange("E2:E46").getValue();
      var operationpurchase2 = sheet1.getRange("F2:F46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin2 = sheet2.getRange("C3");
      var amount2 = sheet2.getRange("C4");
      if(operationgain2="gain position") sendEmail(operationgain2);
        function sendEmail(operationgain2) {
          var email = "[email protected]";
          var subject = "Your "+amount2+" "+coin2+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin2+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain3 = sheet1.getRange("G2:G46").getValue();
      var operationpurchase3 = sheet1.getRange("H2:H46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin3 = sheet2.getRange("D3");
      var amount3 = sheet2.getRange("D4");
      if(operationgain3="gain position") sendEmail(operationgain3);
        function sendEmail(operationgain3) {
          var email = "[email protected]";
          var subject = "Your "+amount3+" "+coin3+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin3+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain4 = sheet1.getRange("I2:I46").getValue();
      var operationpurchase4 = sheet1.getRange("J2:J46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin4 = sheet2.getRange("E3");
      var amount4 = sheet2.getRange("E4");
      if(operationgain4="gain position") sendEmail(operationgain4);
        function sendEmail(operationgain4) {
          var email = "[email protected]";
          var subject = "Your "+amount4+" "+coin4+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin4+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain5 = sheet1.getRange("K2:K46").getValue();
      var operationpurchase5 = sheet1.getRange("L2:L46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin5 = sheet2.getRange("F3");
      var amount5 = sheet2.getRange("F4");
      if(operationgain5="gain position") sendEmail(operationgain5);
        function sendEmail(operationgain5) {
          var email = "[email protected]";
          var subject = "Your "+amount5+" "+coin5+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin5+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain6 = sheet1.getRange("M2:M46").getValue();
      var operationpurchase6 = sheet1.getRange("N2:N46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin6 = sheet2.getRange("G3");
      var amount6 = sheet2.getRange("G4");
      if(operationgain6="gain position") sendEmail(operationgain6);
        function sendEmail(operationgain6) {
          var email = "[email protected]";
          var subject = "Your "+amount6+" "+coin6+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin6+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
      var ss1 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet1 = ss1.getSheetByName("AutoMail"); 
      var operationgain7 = sheet1.getRange("O2:O46").getValue();
      var operationpurchase7 = sheet1.getRange("P2:P46").getValue();
      var ss2 = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss2.getSheetByName("YourData");
      var coin7 = sheet2.getRange("H3").getValue();
      var amount7 = sheet2.getRange("H4").getValue();
      if(operationgain7="gain position") sendEmail(operationgain7);
        function sendEmail(operationgain7) {
          var email = "[email protected]";
          var subject = "Your "+amount7+" "+coin7+" are somewhere in gain position.";
          var body = "Keep an eye on your "+coin7+"s, they're goin' up.";
          MailApp.sendEmail(email,subject,body);}
    };

Tanaike Tanaike · Accepted Answer · 2017-11-17T00:01:12

How about this modification? But I worry about whether I could completely understand your question. If I misunderstand your question, please tell me. My understanding from your question is as follows.

When the word of "gain position" is included in the cells of "O2:O46", you want to send an email with var subject = "Your "+amount+" "+coin+" are somewhere in gain position.".
When the word of "purchase position" is included in the cells of "P2:P46", you want to send an email with var subject = "Purchase position for your "+amount+" "+coin+" somewhere there!";.
coin and amount are used at sendEmail().
Is sheet used at var operationpurchase = sheet.getRange("P2:P46").getValue(); sheet1? In this sample, I used sheet1.
About var coin = sheet2.getActiveCell("H3"); and var amount = sheet2.getActiveCell("H4");, I didn't know what you want to do. I'm sorry. So I estimated that you might want the values of "H3" and "H4".

The modified script which is reflected above is as follows.

Modified script :

function readCell() {
  var ss1 = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss1.getSheetByName("AutoMail"); 
  var operationgain = sheet1.getRange("O2:O46").getValues(); // Modified
  var operationpurchase = sheet1.getRange("P2:P46").getValues(); // Modified
  var ss2 = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2 = ss2.getSheetByName("YourData");
  var coin = sheet2.getRange("H3").getValue(); // Modified
  var amount = sheet2.getRange("H4").getValue(); // Modified

  // Below script was added.
  var email, subject, body
  if (~Array.prototype.concat.apply([], operationgain).indexOf("gain position")) {
    email = "//my email here//";
    subject = "Your " + amount + " " + coin + " are somewhere in gain position.";
    body = "Keep an eye on your " + coin + "s, they're goin' up.";
  }
  if (~Array.prototype.concat.apply([], operationpurchase).indexOf("purchase position")) {
    email = "//my email here//";
    subject = "Purchase position for your " + amount + " " + coin + " somewhere there!";
    body = "Your " + coin + "s might be about to be sold, give them a look.";
  }
  if (email && subject && body) MailApp.sendEmail(email,subject,body);
};

Note :

~Array.prototype.concat.apply([], operationpurchase).indexOf("purchase position") means that the 2 dimensional array retrieved by getValues() is flattened, and the strings of purchase position is searched.

If this was not useful for you, I'm sorry.

Mailing system in GoogleSheets, resume of crypto-transactions. I'm notified only about 1 of 7 operations, and more than once. How to edit the script?

2 Answers

Modified script :

Note :