jquery google-apps-script google-sheets semantic-ui

Google sheets multiple choice selection menu with persistent checked boxes display?

I am trying to modify the code given in another SO answer here (https://stackoverflow.com/a/72411940/8236733) which allows for google sheets to do multiple choice menu selection. Reproduced below for easy reference:

Code.js:

/**
 * Changes the variable validation if needed
 */

var validation = {
    sheet: 'VALIDATION',
    range: 'A2:A'
}

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
    SpreadsheetApp.getUi().createMenu('Sidebar')
        .addItem('Show Sidebar', 'showSidebar')
        .addToUi();
        showSidebar();
}


/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */

function showSidebar() {
    SpreadsheetApp.getUi()
        .showSidebar(HtmlService.createTemplateFromFile('SIDEBAR')
            .evaluate()
            .setSandboxMode(HtmlService.SandboxMode.IFRAME)
            .setTitle('Multiple selector'));
}

function getOptions() {
    return SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues()
        .filter(String)
        .reduce(function(a, b) {
            return a.concat(b)
        })
}

function process(arr) {
    arr.length > 0 ? SpreadsheetApp.getActiveRange().clearContent().setValue(arr.join(", ")) :
        SpreadsheetApp.getUi().alert('No options selected')
}

SIDEBAR.html:

<!DOCTYPE html>
<html>
<style>
    .container,
    .buttons {
        margin: 5px;
        width: 95%;
        padding: 2px;
        font-size: 13px;
    }
</style>

<head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>

<body>
    <div class="container"></div>
    <div class="buttons">
        <p>
            <button class="action" id="action">Fill active cell</button>
            <button id="btn">Rebuild options</button>
        </p>
    </div>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <!-- script src="https://cdn.rawgit.com/mdehoog/Semantic-UI/6e6d051d47b598ebab05857545f242caf2b4b48c/dist/semantic.min.js" --></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/semantic-ui/2.1.8/semantic.min.js"></script>
    <script>
        $(document).ready(function() {
            createList();
            var selected = [];
            $('.ui.checkbox').checkbox();
            $("#action").click(function() {
                $("input:checkbox[name=sel]:checked").each(function() {
                    selected.push($(this).val());
                    $(this).prop( "checked", false ); 
                });
                google.script.run.process(selected)
                selected.length = 0;
            });
            $("#btn").click(function() {
                createList();
            });
        });

        function options(arr) {
            $(".container").empty();
            $(arr).each(function(i, el) {
                $(".container").append('<div class="field"><div class="ui checkbox"><input type="checkbox" name="sel" value="' + el + '"><label>' + el + '</label></div></div>')
            });
        }

        function createList() {
            google.script.run.withSuccessHandler(options).getOptions()
        }
    </script>
</body>

</html>

I am trying to add code to the script portion of the HTML so that it checks the text value of the active cell in the sheet and parses it as a CSV list. It then should go through the created options checklist sidebar and, if the value already exists in the parsed active cell, automatically check off the corresponding option in the sidebar.

Currently, when I click a cell, fill it with values from the options sidebar, select another cell, then re-select that cell I filled, I see: ... What I want to see when re-selecting that cell is: (Where the "a" and "b" options would be automatically checked off since they exist in the cell already). The options come from the column of another sheet (named "VALIDATION" as referenced in the Code.js apps script) like this...

How can I do this? (Very little experience with HTML, apps script, jQuery, and semantic)

That is, if the value of the active cell in the sheet --when decomposed into a CSV list-- contains any values in the options menu sidebar generated by the existing code, then those values automatically display themselves with the corresponding checkbox of the sidebar as already being checked off. I'm doing this so that I can easily see at a glance what options are checked, rather then having to read a long CSV string in the upper formula/display bar of the spreadsheet.

Thanks.

Solution

From your following reply,

When clicking a cell, I want the multiple choice sidebar options to automatically display as being checked off if they exist / are already present in the active cell (which we can assume will have a comma separated list of text values).

How about the following answer?

Issue and workaround:

I understood that by clicking a cell on Google Spreadsheet, you want to update the checkboxes on the sidebar. There is a simple trigger (onSelectionChange(e)) for executing the script by clicking a cell. Ref But, I think that in the current stage, the sidebar cannot be used by the simple trigger. From this situation, I'm worried that your goal cannot be directly run.

So, as a workaround, how about updating the checkboxes by clicking a button on the sidebar? In this case, I think that this can be achieved. When this is reflected in your showing script, how about the following modification?

Modified script:

Google Apps Script:

In this modification, the function getValues was added.

/**
 * Changes the variable validation if needed
 */

var validation = {
  sheet: 'VALIDATION',
  range: 'A2:A'
}

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Sidebar')
    .addItem('Show Sidebar', 'showSidebar')
    .addToUi();
  showSidebar();
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  SpreadsheetApp.getUi()
    .showSidebar(HtmlService.createTemplateFromFile('SIDEBAR')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Multiple selector'));
}

function getOptions() {
  return SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues()
    .filter(String)
    .reduce(function (a, b) {
      return a.concat(b)
    })
}

function process(arr) {
  arr.length > 0 ? SpreadsheetApp.getActiveRange().clearContent().setValue(arr.join(", ")) :
    SpreadsheetApp.getUi().alert('No options selected')
}

// I added the below script.
function getValues() {
  return SpreadsheetApp.getActiveRange().getValue().split(",").map(e => e.trim()).filter(String);
}

HTML & Javascript:

Here, I added a button of "sample". When this button is clicked, the current cell value is reflected in the checkboxes.

<!DOCTYPE html>
<html>
<style>
  .container,
  .buttons {
    margin: 5px;
    width: 95%;
    padding: 2px;
    font-size: 13px;
  }
</style>

<head>
  <base target="_top">
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>

<body>
  <div class="container"></div>
  <div class="buttons">
    <p>
      <button id="sample">sample</button>
      <button class="action" id="action">Fill active cell</button>
      <button id="btn">Rebuild options</button>
    </p>
  </div>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <script src="https://cdn.rawgit.com/mdehoog/Semantic-UI/6e6d051d47b598ebab05857545f242caf2b4b48c/dist/semantic.min.js">
  </script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/semantic-ui/2.1.8/semantic.min.js"></script>
  <script>
  $(document).ready(function () {
    createList();
    var selected = [];
    $('.ui.checkbox').checkbox();
    $("#action").click(function () {
      $("input:checkbox[name=sel]:checked").each(function () {
        selected.push($(this).val());
        $(this).prop("checked", false);
      });
      google.script.run.process(selected)
      selected.length = 0;
    });
    $("#btn").click(function () {
      createList();
    });

    // I added the below script.
    $("#sample").click(function () {
      google.script.run.withSuccessHandler(ar => {
        $("input:checkbox[name=sel]").each(function () {
          if (ar.includes($(this).val())) {
            $(this).prop("checked", true);
          }
        });
      }).getValues();
    });

  });

  function options(arr) {
    $(".container").empty();
    $(arr).each(function (i, el) {
      $(".container").append('<div class="field"><div class="ui checkbox"><input type="checkbox" name="sel" value="' + el + '"><label>' + el + '</label></div></div>')
    });
  }

  function createList() {
    google.script.run.withSuccessHandler(options).getOptions()
  }
  </script>
</body>

</html>

Testing:

When this script is used, the following result is obtained.

Note:

For example, when the active cell is checked by the timer on Javascript, your goal might be able to be achieved. Ref (Author: me) But, in that case, I'm worried that it might not be your expected direction. So, I proposed the above workaround.
By the way, when you click "Fill active cell" button, you want to keep the checked checkboxes, please remove $(this).prop("checked", false);.