Clear Drop Down Based on UI response

user_17150795 0
Viewed 30

I have a drop down that is the word submitted is selected a prompt pops up asking a question. If the user clicks no or cancel I need that active cell to revert back to an empty drop down (like when you click delete and it resets the drop down)

Here is the code I do have:

    function sendMailEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var rData1 = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,15).getValues();
  var lj1 = rData1[0][0];
  

  if (ss.getSheetName() == 'Design Doc Queue' &
    ss.getActiveCell().getColumn() == 6 &
    ss.getActiveCell().getValue() == "Submitted") {    
    var alertPromptText = '⚠️ Are you sure you want to submitt these drawings '+ lj1 +' for review? ';

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

    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,15).getValues();
      sendEmail(email,rData);
       }
       else if(promptResponse == ui.Button.Close){
         var clear = ss.getActiveCell().getColumn() == 6;
         clear.clearNote();
         return;
       }
      }

I have tried multiple variations on this but no luck. Any advice would be grwatful.

D

google-apps-script google-sheets
2 Answers

Try this:

function sendMailEdit(e) {
  var ui = SpreadsheetApp.getUi();
  const sh = e.range.getSheet();
  var rData1 = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 15).getValues();
  var lj1 = rData1[0][0];
  if (sh.getName() == 'Design Doc Queue' && e.range.columnStart == 6 && e.value == "Submitted") {
    var alertPromptText = '⚠️ Are you sure you want to submitt these drawings ' + lj1 + ' for review? ';
    var promptResponse = ui.alert(alertPromptText, ui.ButtonSet.YES_NO_CANCEL);
    if (promptResponse == ui.Button.YES) {
      var email = e.source.getSheetByName("Email").getRange(1, 1, 100).getValues().flat().join(",");
      var rData = sh.getRange(e.range.rowStart, 1, 1, 15).getValues();
      sendEmail(email, rData);
    } else if (promptResponse == ui.Button.Close) {
      sh.getRange(e.range.rowStart, 6).setValue('');
    }
  }
}
user_72150910

Ok so I finally found the solution. It was so simple, just didn't see the forest through the trees. Basically in the ui.Button.CLOSE I has the Close with a capital and lowercase letters. Once I changed it to CLOSE my script worked great.

Final script:

    function sendMailEdit(e) {
    var ui = SpreadsheetApp.getUi();
  const sh = e.range.getSheet();
  var rData1 = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 15).getValues();
  var lj1 = rData1[0][0];
  
  if (sh.getName() == 'Design Doc Queue' && e.range.columnStart == 6 && e.value == "Submitted") {
    var alertPromptText = '⚠️ Are you sure you want to submitt these drawings ' + lj1 + ' for review? ';
    var promptResponse = ui.alert(alertPromptText, ui.ButtonSet.YES_NO_CANCEL);
    if (promptResponse == ui.Button.YES) {
      var email = e.source.getSheetByName("Email").getRange(1, 1, 1).getValues().flat().join(",");
      var rData = sh.getRange(e.range.rowStart, 1, 1, 15).getValues();
      sendEmail(email, rData);
    } else if (promptResponse == ui.Button.CLOSE) {
      clear();
    } else if (promptResponse == ui.Button.NO) {
      clear();
    } else if (promptResponse == ui.Button.CANCEL) {
      clear();
    }
  }
}

and

    function clear() {
  var sheet, dateSheet, activeCell, activeRow;
  sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  dateSheet = "Design Doc Queue";
  activeCell = sheet.getActiveCell();
  activeRow = activeCell.getRow();
  
  if(sheet.getName() === dateSheet && activeCell.getColumn() === 6 && activeCell.getDisplayValue() === "Submitted"){
    sheet.getRange(activeRow,6).clear({contentsOnly: true}).clearNote();
  } 
  
}

Thank you so much for your time.

D

user_171507950
Related