google-sheetsgoogle-apps-scripttriggersmodal-dialogpopup

Popping a Modal Dialog when changing the drop down entry in Google Sheets


I am trying to create a script where, when a user selects a specific value in a dropdown, say 'a', and a specific modal dialog is displayed.

I am trying this, but get this error:

Cannot read properties of undefined (Reading 'range')

function onEdit(e) {
  if (e.range.getSheet().getName() != 'Sheet1') return;
  if (e.range.getA1Notation() != 'A1') return;  
  if (e.value != 'a') return;
SpreadsheetApp.getUi().showModalDialog(widget, "Practice Criteria");
 var widget = HtmlService.createHtmlOutput("<p><h1>Co-creating or Co-designing Core Elements of Activity Design</h1></p><p>1)Activity objectives, results, or interventions determined using a co-creation or co-design process.</p><p>2)Local or regional partners, partner government implementing entities, Innovation Incentive Authority  awardees, and/or stakeholders, including communities, participate in the co-design or co-creation process.</p><p>3)Co-design or Co-creation Process is Documented.</p><p>4)Co-creation or co-design process generated substantiv contributions to Activity Design.</p>");
}

Solution

  • Cannot red properties of undefined (Reading 'range')

    The onEdit(e) function is a simple trigger that is designed to run automatically when you manually edit the spreadsheet. In that context, the event object e is properly populated. Do not run the code through the ▷ Run button in the script editor. If you do, the event parameter e is not populated, causing the error you mention.

    One issue with the code is that it attempts to refer to widget before that variable is declared.

    Another issue is that simple triggers run in a restricted context where they cannot use UI methods such as .showModalDialog(). Use an installable trigger instead. You'll need to authorize the code when creating the trigger.

    Here's an improved (but untested) version of your code.

    function installableOnEdit(e) {
      if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window. It runs automatically when you hand edit the spreadsheet. See https://stackoverflow.com/a/63851123/13045193.');
      if (e.value !== 'a'
        || e.range.getA1Notation() !== 'A1'
        || e.range.getSheet().getName() !== 'Sheet1') {
        return;
      }
      const widget = HtmlService.createHtmlOutput('<p><h1>Co-creating or Co-designing Core Elements of Activity Design</h1></p><p>1)Activity objectives, results, or interventions determined using a co-creation or co-design process.</p><p>2)Local or regional partners, partner government implementing entities, Innovation Incentive Authority  awardees, and/or stakeholders, including communities, participate in the co-design or co-creation process.</p><p>3)Co-design or Co-creation Process is Documented.</p><p>4)Co-creation or co-design process generated substantive contributions to Activity Design.</p>');
      SpreadsheetApp.getUi().showModalDialog(widget, 'Practice Criteria');
    }