OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Updating appscript dropdown in sidebar after a successful operation

  • Thread starter Thread starter J_code
  • Start date Start date


I'm progressively working through learning enough of HTML and javascript to apply to a google sheets package I'm developing for personal use. I developed a script from assistance on a previous post I made but am having some issues.

Essentially I have an HTML script that opens a sidebar and loads a list from a range in my sheet.

This is the script that returns the items from the column:

function getAllCategories() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   return sheet.getRange("F12:F").getValues().filter(category => category != "").flat();

The onOpen(e) trigger:

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  .addSubMenu(ui.createMenu("Add").addItem(TypeCategory, addCategoryMenu.name).addItem(TypeBill, addBill.name).addItem(TypeTarget, addTarget.name))
  .addSubMenu(ui.createMenu("Delete").addItem(TypeCategory, deleteCategoryMenu.name).addItem(TypeBill, deleteBill.name).addItem(TypeTarget, deleteTarget.name))
  .addSubMenu(ui.createMenu("Transfer").addItem(TypeCategory, transferCategories.name).addItem(TypeBill, transferBills.name).addItem(TypeTarget, transferTargets.name))
  .addSubMenu(ui.createMenu("Modify").addItem(TypeCategory, "test").addItem(TypeBill, "test").addItem(TypeTarget, "test"))

Along with the script that opens the sidebar:

function deleteCategoryMenu() {
  let form = HtmlService.createHtmlOutputFromFile('DeleteCategoryForm').setTitle('Delete Category');

The HTML script itself is here:

<!DOCTYPE html>
    <base target="_top">
      <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/4.6.1/css/bootstrap.min.css" /> 
      <style> /*Investigate what this does*/
          margin-bottom: 0.5rem;
        function submitRecord() 
          document.getElementById("displayReturn").innerHTML = ""; //Not sure if this is needed
          let category  = document.getElementById("category").value;

        function returnBack(stringBack)
          document.getElementById("displayReturn").innerHTML = stringBack;
          document.getElementById("category").value = '';

        function getCategories()
          google.script.run.withSuccessHandler((categories) => 
            let category = document.getElementById("category");
            categories.forEach((element) => 
              let opt       = document.createElement("option");
              opt.value     = element;
              opt.innerHTML = element;

        function resetDropdown()
          .withSuccessHandler((categories) => 
            const parent  = document.getElementById("category");
            let opt       = document.createElement("option");
            opt.value     = '';
            opt.innerHTML = "---Select Category to Delete---";
            opt.disabled  = true;

            categories.forEach((element) => 
              let newOpt       = document.createElement("option");
              newOpt.value     = element;
              newOpt.innerHTML = element;

      <div style="padding: 10px" >
        <div class="form-row" >
          <div class="form-group col-md-6 narrow">
            <label for="category" style="margin-bottom: 0rem" >Category</label>
            <select id="category" class="form-control" >
              <option selected disabled value="">---Select Category to Delete---</option> 

        <div class="form-row">
          <div class="form-group col-md-6 narrow">
            <input type="button" value="Delete" onclick="submitRecord();" class="btn btn-primary"/>

        <div id="displayReturn"></div>
    <script>getCategories(); </script>

The HTML script is called from an onOpen(e) trigger. The script that performs the operation, in this case a deletion deleteCategory(category) works by deleting a category from a range, in this case F12:F. That category is selected from the dropdown list in the sidebar. What I want to happen is that once the category is deleted, I want the dropdown list to be immediately updated without having to close the sidebar and reopen it. What I have now surprisingly does not work and I'm not sure why. The dropdown list ends up not changing at all, specifically in resetDropdown(). Any help is appreciated.

Continue reading...