The Pop up does not disappear after submission-CodePudding

function onEdit(e) {
  var columnToWatch = 116;
  var sheet = SpreadsheetApp.getActiveSheet();
  var clickedCell = sheet.getActiveCell();

  if (clickedCell.getColumn() == columnToWatch) {
    showOptionWindow(clickedCell);
  }
}

function showOptionWindow(clickedCell) {
  var html = '<html><body><form><p>Options :</p>';
  html  = '<input type="checkbox" name="option1" value="APIs/Storefront">APIs/Storefront<br>';
  html  = '<input type="checkbox" name="option2" value="Employee Rewards">Employee Rewards<br>';
  html  = '<input type="checkbox" name="option3" value="Channel Incentives">Channel Incentives<br>';
  html  = '<input type="checkbox" name="option4" value="Customer Rewards">Customer Rewards<br>';
  html  = '<input type="checkbox" name="option5" value="Payouts">Payouts<br>';
  html  = '<br>'
  html  = '<input type="submit" value="Submit" style="font-weight:bold; background-color:black; color:white;" onclick="google.script.run.processForm(this.form)">';
  html  = '</form></body></html>';
  var ui = HtmlService.createHtmlOutput(html)
    .setWidth(250)
    .setHeight(275);
  SpreadsheetApp.getUi().showModalDialog(ui, 'Pillar Selection');
}

function processForm(form) {
  var selectedOptions = [];
  if (form.option1.checked) {
    selectedOptions.push(form.option1.value);
  }
  if (form.option2.checked) {
    selectedOptions.push(form.option2.value);
  }
  if (form.option3.checked) {
    selectedOptions.push(form.option3.value);
  }
  if (form.option4.checked) {
    selectedOptions.push(form.option4.value);
  }
  if (form.option5.checked) {
    selectedOptions.push(form.option5.value);
  }
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var clickedCell = sheet.getActiveCell();
  
  clickedCell.setValue(selectedOptions.join(', '));
  SpreadsheetApp.getUi().alert('You selected: '   selectedOptions.join(', '));
  SpreadsheetApp.getUi().close();
}

When the user submits the form by clicking the submit button, the script runs the processForm function to process the form data. The selected options are stored in an array, selectedOptions, and joined into a string with a comma-space separator. The value of the clicked cell is then set to the joined string of selected options and a confirmation alert is displayed to the user.

CodePudding user response：

I think it would be simpler to just allow the user to close the dialog than automate it because no solutions are working. I did find an article about this topic on Spreadsheet Dev regarding your issue.

Hope it helps!

CodePudding user response：

Try this:

Use an installable onEdit trigger:

function showOptionWindow() {
  var html = '<html><body><form><p>Options :</p>';
  html  = '<input type="checkbox" name="option1" value="APIs/Storefront">APIs/Storefront<br>';
  html  = '<input type="checkbox" name="option2" value="Employee Rewards">Employee Rewards<br>';
  html  = '<input type="checkbox" name="option3" value="Channel Incentives">Channel Incentives<br>';
  html  = '<input type="checkbox" name="option4" value="Customer Rewards">Customer Rewards<br>';
  html  = '<input type="checkbox" name="option5" value="Payouts">Payouts<br>';
  html  = '<br>'
  html  = '<input type="button" value="Submit" style="font-weight:bold; background-color:black; color:white;" onclick="google.script.run.withSuccessHandler((v) => {google.script.host.close();}).processForm(this.parentNode)"/>';
  html  = '</form></body></html>';
  var ui = HtmlService.createHtmlOutput(html)
    .setWidth(250)
    .setHeight(275);
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Pillar Selection');
}

function onMyEdit(e) {
  //e.source.toast("Entry")
  const sh = e.range.getSheet();
  if (e.range.columnStart == 1 && sh.getName() == "Sheet0") {
    //e.source.toast("Gate1")
    showOptionWindow(e.range);
  }
}

function processForm(obj) {
  Logger.log(JSON.stringify(obj))
  var selectedOptions = [];
  if (obj.hasOwnProperty("option1")) {
    selectedOptions.push(obj.option1);
  }
  if (obj.hasOwnProperty("option2")) {
    selectedOptions.push(obj.option2);
  }
  if (obj.hasOwnProperty("option3")) {
    selectedOptions.push(obj.option3);
  }
  if (obj.hasOwnProperty("option4")) {
    selectedOptions.push(obj.option4);
  }
  if (obj.hasOwnProperty("option5")) {
    selectedOptions.push(obj.option5);
  }
  Logger.log(JSON.stringify(selectedOptions));
  var sheet = SpreadsheetApp.getActiveSheet();
  var clickedCell = sheet.getActiveCell();
  
  clickedCell.setValue(selectedOptions.join(', '));
  SpreadsheetApp.getUi().alert('You selected: '   selectedOptions.join(', '));
  return true;
}