google-sheets google-apps-script modal-dialog

I want to launch a modal using checkbox

I want to launch a modal once the value of checkbox is change to True, I already tried to create function for that but once I checked the checkbox it doesn't show anything. I keep on changing and adjusting my codes but I really can't find where I go wrong.

Here are all my Function Codes that created:

    let folderId = '1uQjcvYKOdbpnD3AybwN4KTKQeFfun_wB';

    function onOpen() {
        let ui = SpreadsheetApp.getUi();
        ui.createMenu('Script Menu')
        .addItem('Upload Files','uploadFile')
        .addToUi();
    }


    function uploadFile() {
        let window = HtmlService.createHtmlOutputFromFile('Upload.html');
        window.setWidth(600);
        window.setHeight(400);
        SpreadsheetApp.getUi().showModalDialog(window,"Upload File");
    }


    function saveFile(e) {
        let ss = SpreadsheetApp.getActiveSpreadsheet();
        let blob = Utilities.newBlob(e.bytes,e.mimeType,e.filename);
        let folder = DriveApp.getFolderById(folderId);
        let file = folder.createFile(blob);

        let fileName = file.getName();
        let fileUrl = file.getUrl();

        let sheet = ss.getSheetByName("Request Input").getRange('B10');

        sheet.setValue(fileUrl);

        return [fileName,fileUrl];

    }

    function checkBox() {

      const activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell()
      const reference = activeCell.getA1Notation()
      const sheetName = activeCell.getSheet().getName()
      const activeValue = activeCell.getValue()

      if (reference == "B10" && sheetName == "Request Input" && activeValue == true)
      {

        uploadFile();
        activeCell.setValue(false)
    
      }

    }

And here is my HTML Code for the function code:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
</head>

<body>

  <div class="container">
    <div id="form">
      <div id="fileUpload" class="mb-3">
        <input id="file" type="file" onchange="saveFile(this)" />
      </div>
      <div id="progressSpinner" class="spinner-border" role="status" style="display: none;"></div>
    </div>
  </div>


  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"
    integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous">
  </script>

  <script>
    function saveFile(f) {
      document.getElementById('progressSpinner').style.display = 'block';
      const file = f.files[0];
      const fr = new FileReader();
      fr.onload = function (e) {
        const obj = {
          filename: file.name,
          mimeType: file.type,
          bytes: [...new Int8Array(e.target.result)]
        };
      //google.script.run.withSuccessHandler(data => success(data)).saveFile(obj);
      google.script.run.withSuccessHandler(google.script.host.close).saveFile(obj);
      }
    fr.readAsArrayBuffer(file);

    }

    function success(data) {
      document.getElementById('progressSpinner').style.display = 'none';
      document.getElementById('fileUpload').innerHTML = `<strong>File Uploaded Successfully</strong><br /><a target="_blank" href="${data[1]}">${data[0]}</a>`
    }
  </script>
</body>

</html>

Solution

The code in question doesn't include anything to open the modal dialog when a checkbox is checked. For this, you should use an installable trigger. On edit is the most convenient option as the event option includes the range property holding the SpreadsheetApp.Range what was edited.

Below, I have for you an elementary example. It includes two functions: the main function is called editHandler, and the other function, init, is optional as an installable trigger can be created manually.

editHandler depicts the use of SpreadsheetApp.Range.isChecked() that returns true if the edited range contains a Google Sheets checkbox (Insert > Checkbox) and it was checked.

/**
 * To be called by on edit installable trigger
 * 
 * @param {Objec} e Edit event object
 * @property {SpreadsheetApp.Range} range
 */
function editHandler(e = {range: SpreadsheetApp.getActiveSpreadsheet().getActiveRange()}) {
  if(e.range.isChecked()){
    const modal = HtmlService.createHtmlOutput('<p>Hello world!');
    SpreadsheetApp.getUi().showModalDialog(modal, 'Modal Demo');
  }
}

/**
 * Run once to create an on edit installable trigger for
 * the active spreasheet
 */
function init(){
  const handlerFunction = 'editHandler';
  // Prevent having multiple triggers calling the same function
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if(trigger.getHandlerFunction() === handlerFunction){
      ScriptApp.deleteTrigger(trigger);
    }
  })
  ScriptApp.newTrigger(handlerFunction)
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create()
}

Disclaimer: For simplicity, the above example doesn't validate that a specific range contains the checked checkbox, so the modal dialog will be shown when any checkbox in the spreadsheet is checked.

Demo Spreadsheet

For your convenience, I created a demo spreadsheet with a few changes,

Make a copy.
Clicking on this link will open the "Make a Copy" page for the demo spreadsheet. Click the Make Copy button. Please be patient, as creating the copy might take a few seconds. If you prefer, you might look first at the contained script by clicking View Apps Script file.
On the copy of the demo spreadsheet, click on Extensions > Apps Script.
Click the Run button. By default, the init function will be selected in the function dropdown. This function creates an on-edit installable trigger to call editHandler function. The first time that any of this project's functions is executed, a prompt telling the user that authorization is required will be shown. Click review permissions. When using a Gmail account a warning will be shown

Google hasn’t verified this app The app is requesting access to sensitive info in your Google Account. Until the developer (your_email_address) verifies this app with Google, you shouldn't use it.

Click the Advanced link. This will show

Continue only if you understand the risks and trust the developer (your_email_address).

Go to Open Modal Dialog when a Checkbox is checked (unsafe)

Click the "Go to Open Modal Dialog when a Checkbox is checked (unsafe)" link. This will show the required permissions.

Once you have read and understood the required permissions, click the Allow button. At the bottom of the Apps Script Editor, the Execution Logs will be opened and shown the following (timestamps are not included):
```
Notice  Execution started
Notice  Execution completed
```
Go back to the spreadsheet.
Check the checkbox.
Please be patient as it might take a bit for the editHandler function to start its execution and open the modal. Note: The checkbox will be automatically unchecked to require a single click to show the modal dialog again.
To close the modal, click the X button.