I am trying to perform a sendnotification function in a google sheet that is set to trigger OnEdit. So far, I have the following script but it is not working properly. Basically, I have two arrays of values that I want a notification to be sent if the value in the specified cell matches a value in the said array. Any help would be much appreciated. Thanks!
`function sendNotification(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var mycell = ss.getActiveSelection();
var cellcol = mycell.getColumn();
var cellrow = mycell.getRow();
var rowData = sheet.getSheetValues(cellrow,10,1,1);
var recipient1 = "johnsmith@email.com";
var recipient2 = "george.frank@email.com";
var Colors1 =["Red","Blue","Yellow","Green"];
var Colors2 = ["Black","Silver","White","Grey"];
var body = "A new submission has been added to " + ss.getName() + ".Visit the following link to view the new submission:"
+ '\n' + ss.getUrl()
+ '\n'
+ '\nThis request has been submitted by ' + rowData;
if (cellcol == 4 && sheet.getName() == "Sheet1" && mycell.getValues() == isInArray(Colors1)
{
return array.indexOf(Colors1) >= 0;
}
{
MailApp.sendEmail(recipient1, subject, body);
} else if (cellcol == 4 && sheet.getName() == "Sheet1" && mycell.getValues() == isInArray(Colors2)
{
return array.indexOf(Colors2) >= 0;
}
{
MailApp.sendEmail(recipient2, subject, body);
}
}`
Aucun commentaire:
Enregistrer un commentaire