'Select cell based on other values
I have a script where I am trying to send emails, and I have almost everything figured out. The last piece is actually selecting the email string from a list of rules.
I want to get the emails (col C) if curSheet.getSheetName() = value in colA AND newVal = value in colB. I am having trouble combining if statements & for loops.
Here is a link to a sample Notification Rules sheet: [https://docs.google.com/spreadsheets/d/1Cn9R3f85xF_QnOU_BNIvpqRq8FUvRGFaCUnTH3jyCTs/edit#gid=0]
function emailUpdate(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var curSheet = ss.getActiveSheet()
var notifSheets = ["Sheet1", "Sheet2"]
var newVal = e.value
var row = e.range.rowStart
var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy")
var hosp = curSheet.getRange(row,4).getValue()
var sheetID = curSheet.getSheetId()
var sheetURL = "linktodoc"+sheetID
var rules = ss.getSheetByName('Notification Rules')
var data = rules.getDataRange().getValues()
//Build Email
var subject = subjecttext
var intro = introtext
var closer = closingtext
if (e.range.columnStart == 2 && notifSheets.indexOf(curSheet.getSheetName()) != -1 && newVal !== undefined) {
console.log("It worked")
curSheet.getRange(row,3).setValue(curDate) //inserts edit date
var email = //some for / if statement to get colC if colA&colB match curSheet & newVal
GmailApp.sendEmail(email,subject,'', {htmlBody: intro+closer})
} else {
console.log("It didn't work")
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
