lundi 9 novembre 2015

How to perform Sendnotification on criteria in array

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