Needs Help With Javascript For Google Spreadsheet

Noah
Noah used Ask the Experts™
on
Greetings experts,

Here is a screenshot of the "Key-In Data" sheet I am trying to work with:
Capture.PNG
And here is my code:
function myFunction(){

var app = SpreadsheetApp;
 var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
{var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B26");
var emailAddress=emailRange.getValues();
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("E2").setFormula("=B2+C2+D2");

 for (var i=2;i <= 2;i++) {
 var currentCell = activeSheet.getRange (i,5).getValue();
   if(currentCell < 4){
   var message="Product has reached a critical value "
   var subject = "Update on transfusion product (EMERGENCY!!)";}
   else if (currentCell > 6) { 
   var message="Product has reached a normal value"
   var subject = "Update on transfusion product";}
   else 
  {var message="Product has reached a minimum value"
   var subject = "Update on transfusion product";}
  
  MailApp.sendEmail(emailAddress,subject,message);
  }

}
}

}

Open in new window


What this code does is that it will send an email based on cell E2. The logic is completely fine here and it does not require any changes.

What I do need help with are:
1. Right now, the code is pointing to cell B26 for the email. I need the email in the code instead.
2. I also want to add cell E2's value at the back of the Email message. For example, "Product has reached a critical value of 3".
3. Every time the code is run, a time stamp should be generated at cell A2.
4. After the timestamp is added, I need row 2 of "Key-In Data" sheet to be added to the next available row in the "Summary Data" sheet.

Here is sample test environment:
https://docs.google.com/spreadsheets/d/1kA1CH2I0Rho_GcvO_PqsWIAuZMZlK4BFukP-TsUlmyY/edit?usp=sharing

Any help is much appreciated! :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
NorieAnalyst Assistant

Commented:
Noah

1 Do you mean you want to hard-code the email?
NoahHardware Tester and Debugger

Author

Commented:
@Norie Yes I would like to include the email within the code. In other words, hard-code it.
Hardware Tester and Debugger
Commented:
I have finalized my solution on my own.

function AddRowAndSendEmail(){
  try{
    var ss=SpreadsheetApp.getActive();
    var ash=ss.getActiveSheet();
    //var emailAddress=ss.getSheetByName("Sheet1").getRange("B26").getValue();
    var emailAddress=" recipient's email here";
    ash.getRange('A2').setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm:ss"));
    ss.getSheetByName('Summary Data').appendRow(ss.getSheetByName('Key-In Data').getRange(2,1,1,ss.getSheetByName('Key-In Data').getLastColumn()).getValues()[0]);
    var vE2=ash.getRange("E2").setFormula("=B2+C2+D2");
    var currentCell = ash.getRange(2,5).getValue();
    if(currentCell < 4){
      var message="Product has reached a critical value of " + currentCell;
      var subject = "Update on transfusion product (EMERGENCY!!)";
    }else if (currentCell > 6) { 
      var message="Product has reached a normal value of " + currentCell;
      var subject = "Update on transfusion product";
    }else {
      var message="Product has reached a minimum value of " + currentCell;
      var subject = "Update on transfusion product";
    }
  }
  catch(e) {
    SpreadsheetApp.getUi().alert(e);
    return;
  }
  MailApp.sendEmail(emailAddress,subject,message); 
  ss.toast("Process Complete")
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial