Google Sheets Automation
Automated Email Based On Cell Value
Copy below Code and paste under apps script (Make necessary changes as required)
function sendEmail() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with your sheet name
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
var lastRow = dataRange.getLastRow();
for (var i = 1; i < lastRow; i++) { // Start from 1 to skip header row
var stockCount = values[i][2]; // Column C (index 2) for stock count
var emailAddress = values[i][1]; // Column B (index 1) for email address
var emailSent = values[i][4]; // Column E (index 4) for email status
if (stockCount > 0 && emailSent !== "SENT") { // Code by Excelmind
var subject = "Stock count is greater than zero"; // Change the subject as per your choice
var message = "Stock count for your item is: " + stockCount; //Change the message as per your choice
MailApp.sendEmail(emailAddress, subject, message);
// Mark the email as sent in column E
sheet.getRange(i + 1, 5).setValue("SENT"); // i + 1 because getRange and setValue use 1-based indexing
}
}
}
No comments:
Post a Comment