Apps Script Google Sheets POP up selecting yes or no always sends the email regardless-CodePudding

I have a google sheet with a dropdown menu. When the cell the drop down is in is equal to a certain string a pop up shows up on screen. You answer yes or no. When you answer yes it sends the email. If you answer no it does not send the email and resets the cell to its previous state. The issue I am having is that if you select no it does reset to the previous data, but it still sends the email regardless. Please help. I am fairly new and still learning.

UPDATE: Please see the update below. The only var I had to fix was for rData, other than that the script works as intended now. Thank you so much for your time and input. This was a headache for some time.

function sendMailEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  if (ss.getSheetName() == 'Project List' &
    ss.getActiveCell().getColumn() == 3 &
    ss.getActiveCell().getValue() == "_Complete") {

    var alertPromptText = 'Are you sure you want to select Complete? '  
      'This action will notify all parties in the next process that this job is ready for fabrication.';

    var promptResponse = ui.alert(alertPromptText, ui.ButtonSet.YES_NO);

    if (promptResponse == ui.Button.YES) {

      // CHECK START
      // variable email needs to be fixed. It gets the column of values. 
      // it needs to be converted to a comma separated list of recepients
      var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange(1, 1, 100).getValues();
      // CHECK END

      var rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,12).getValues();
      sendEmail(email,rData);

       

    } else { // For both 'No' and cancel response to pop-up
      fix(e);
    }
  }
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function sendEmail(email,rData) {
  var first = 0;
  var email = 1;

  var emailTemp = HtmlService.createTemplateFromFile("send");
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  var data = ws.getRange("A1:B"   ws.getLastRow()).getValues();
  var lj = rData[0][1];
  var j = rData[0][5];
  var d = rData[0][3];
  var p = rData[0][4];
  var m = rData[0][7];
  var desc = rData[0][11];
  var now = new Date().toLocaleString("en-US");
  var msg1 = "Laser Job Number: "   lj   " ("   now   ")"  
    "\nProject Job Number: "   j  
    "\nDesigner: "   d  
    "\nDate Project was Submitted to Programming: "   p  
    "\nMaterial used: "   m  
    "\nDescription: "   desc;
    var subject = "Project Ready for Fab";

  
  Logger.log(msg1);

  data.forEach(function(row){
    
    emailTemp.fn = row[first];
    emailTemp.msg = msg1;
    emailTemp.j = j;
    emailTemp.d = d;
    emailTemp.lj = lj;
    emailTemp.p = p;
    emailTemp.m = m;
    emailTemp.desc = desc;
    //emailTemp.cart = cart;

    const htmlMessage = emailTemp.evaluate().getContent();
    
GmailApp.sendEmail(row[email], subject, "Please open with an email client that supports HTML",
{htmlBody: htmlMessage});
  return;

  });
  
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function fix(e) {
  e.range.setNote(e.oldValue);
  e.range.setValue(e.range.getNote());
  e.range.clearNote();
}

CodePudding user response：

You need to set a variable to capture the response to the pop-up.

And then compare the value of the variable for the go-no-go part of the script.

For example:

var response = ui.prompt('Alert', 'Are you sure you want to select Complete?', ui.ButtonSet.YES_NO);

And then

if (response.getSelectedButton() == ui.Button.YES) { ... }

More here in the documentation.

Update

Try the following script. You'll need to set the onEdit trigger to run the function SendMailEdit

Also, check the code for getting the list of email recepients. Not sure if it will work.

function sendMailEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  if (ss.getSheetName() == 'Project List' &
    ss.getActiveCell().getColumn() == 3 &
    ss.getActiveCell().getValue() == "_Compvare") {

    var alertPromptText = 'Are you sure you want to select Compvare? '  
      'This action will notify all parties in the next process that this job is ready for fabrication.';

    var promptResponse = ui.alert(alertPromptText, ui.ButtonSet.YES_NO);

    if (promptResponse.getSelectedButton() == ui.Button.YES) {

      // CHECK START
      // variable email needs to be fixed. It gets the column of values. 
      // it needs to be converted to a comma separated list of recepients
      var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange(1, 1, 100).getValues();
      // CHECK END

      var rData = ss.getRange(ss.getActiveCell().getRow(), 1, 1, 12).getValues();
      sendEmail(email, rData);

    } else { // For both 'No' and cancel response to pop-up
      fix(e);
    }
  }
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function sendEmail(email, rData) {
  var lj = rData[0][1];
  var j = rData[0][5];
  var d = rData[0][3];
  var p = rData[0][4];
  var m = rData[0][7];
  var desc = rData[0][11];
  var now = new Date().toLocaleString("en-US");
  var msg = "Laser Job Number: "   lj   " ("   now   ")"  
    "\nProject Job Number: "   j  
    "\nDesigner: "   d  
    "\nDate Project was Submitted to Programming: "   p  
    "\nMaterial used: "   m  
    "\nDescription: "   desc;
  Logger.log(msg);
  GmailApp.sendEmail(email, "Project Ready for Fab", msg);
  return;
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function fix(e) {
  e.range.setNote(e.oldValue);
  e.range.setValue(e.range.getNote());
  e.range.clearNote();
}