Why does HTML output not run in a loop the same as a Prompt Response or Alert?-CodePudding

I currently have column A in the spreadsheet filled with checkboxes and have getA1Notation() used to grab this action when the user checks a box. I would then like to run an HTML dialog that has buttons to create an action for that row (HTML script not created yet, just running this test).

The issue I am running into is that when I put the HTML dialog box into a loop, it does not appear when the action (clicking on the checkbox) is done. I can replace the HTML dialog box with a prompt response or alert with buttons and it works perfect, but I would like to use an html box to have multiple custom buttons.

I am very new to using Apps Script for spreadsheets, so thank you for any help.

CODE:

function onEdit(e) {
  for (var counter = 0; counter <= 500; counter = counter   1)
  {
    const a1 = SpreadsheetApp.getActiveSheet().getRange(2 counter, 1).getA1Notation();
    if (e.range.getA1Notation() == a1)
    {
      SpreadsheetApp.getActiveSheet().getRange(2 counter, 15).setValue('Hello'); //this is just to test the loop is working and doing this action anytime I click a checkbox
      var tester = HtmlService.createHtmlOutput("<p>test</p").setWidth(250).setHeight(300);
      SpreadsheetApp.getUi().showModelessDialog(tester, "teest");
    }
  }
}

CodePudding user response：

Just stop, don't use a simple trigger that use a loop to show a dialog / sidebar.

The above because simple triggers have limitations, i.e. they have a shorter execution time limit, and they can't do some things that a function called from button / custom menu can do. They might be able to do certain tasks when the spreadsheet owner activates a trigger that might not work when a editor do the same.

By the other hand, SpreadsheetApp.getUi().showModelessDialog(tester, "teest"); is asynchronous, meaning that after executing this statement the runtime will execute the next one immediately, it will not wait for the Google Sheets UI to load the dialog.

CodePudding user response：

Assign a function to each row

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "your sheet name" && e.range.rowStart < rowactions.length) {
    const rowactions = ["","funk1","funk2","funk3"];//assigned functions
    executeFunctionByName(rowactions[e.range.rowStart])
  }
} 

function executeFunctionByName(func) {
  this[func]();
}

Probably need an installalble onedit.