0
101
Viewscompare two values of cell then send email
function myFunction() {
const minimumLevel = [0,5,10,15,20,25,30];
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
var data = sheet.getRange(2,1,data.getLastRow() - 1, data.getLastColumn()).getValues();
data.forEach((r,i) => {
if(r[9] < minimumLevel[1]){
console.log("true");
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2");
var emailAddress = emailRange.getValues();
// Send Alert Email.
var message = 'This is your Alert email!'; // Second column
var subject = 'Your Google Spreadsheet Alert';
MailApp.sendEmail(emailAddress, subject, message);
}
})
}
I am trying to compare to values of cell and then if the value of that cell goes below minimum level it will send an email to someone, however I think i am stuck? please can someone help?
2 answers
Answer question0
Try it this way:
function myFunction() {
const minimumLevel = [0,5,10,15,20,25,30];
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
var data = sheet.getRange(2,1,sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
data.forEach((r,i) => {
if(r[9] < minimumLevel[1]){
console.log("true");
//send email code here
}
});
}
0
Based on your new comments I understand that you have two columns, one for the actual stock price and another for the desired minimum level. I also understand that your goal is to send an alert email when the stock price descends below the alert price. If my understanding is accurate, then you can reach your goals easily by making small modifications on your script.
The modified script below will first extract the data from the stock price and alert level columns. I am assuming that the stock price is on Column A and the alerts at Column B, but you can modify that easily. Then the script will run a for loop and if the stock price is lower than the alert level, then an email will be sent.
function myFunction() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
var stockPrice = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
.flat(2);
var minimumLevel = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues()
.flat(2);
var emailAddress = sheet.getRange("B2").getValue();
var emailMessage = "{ ALERT MESSAGE }";
var emailsubject = "{ EMAIL SUBJECT }";
for (let i = 0; i < stockPrice.length; i++) {
if (stockPrice[i] < minimumLevel[i]) {
MailApp.sendEmail(emailAddress, emailsubject, emailMessage);
}
}
}
Please test this approach and let me know if you find any difficulties.