Html - Updating appscript dropdown in sidebar after a successful operation

J_code
June 26, 2024
220 views
0 votes
2 Answers

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();
  ui.createMenu("Operations")
  .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"))
  .addToUi();
}

Along with the script that opens the sidebar:

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

The HTML script itself is here:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
      <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/4.6.1/css/bootstrap.min.css" /> 
      <style> 
        .narrow
        {
          margin-bottom: 0.5rem;
        }
      </style>
      <script>
        function SubmitRecord() 
        {
          document.getElementById("displayReturn").innerHTML = ""; 
          let category  = document.getElementById("category").value;
          google.script.run.withSuccessHandler(returnBack)
          .deleteCategory(category);
        }

        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, index) => 
            {
              let opt       = document.createElement("option");
              opt.value     = element;
              opt.innerHTML = element;
              category.appendChild(opt);
            });

          }).getAllCategories();
        }
      </script>
  </head>

  <body>
    <form>
      <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>
            </select>
          </div> 
        </div>

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

        <div id="displayReturn"></div>
      </div>
    </form>
    <script>GetCategories(); </script>
  </body>
</html>

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. I tried multiple approaches to no avail. The current HTML code posted above tried adding GetCategories() to the <input><input> tag which already has another function SubmitRecord(). I read that more than one function can be tied to an on click event although it is discouraged. That did not work. I also tried posting the following code in returnBack():

let cat       = document.getElementById("category");
let opt       = document.createElement("option");
opt.value     = category;
opt.innerHTML = category;
cat.removeChild(opt);

This also did not work. Any help is appreciated.

Answers

Try this in your gs code

function getAllCategories() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange("F12:F" + sheet.getLastRow()).getValues().flat();
}

even a single column is a two dimensional array

try this:

function GetCategories() {
  google.script.run.withSuccessHandler((cats) => {
    let select = document.getElementById("category");
    select.options.length = 0;
    for (var i = 1; i < cats.length; i++) {
      select.options[i] = new Option(cats[i], cats[i]);
    }
  }).getAllCategories();
}

Below is a simple example, including code for one .gs and one .html files.

Code.gs

/**
 * Adds a custom menu including an item to show the sidebar
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Demo')
    .addItem('Show sidebar', 'showSidebar')
    .addToUi();
}

/**
 * Shows the sidebar
 */
function showSidebar() {
  const htmlOutput = HtmlService.createHtmlOutputFromFile('index')
    .setTitle('Demo');
  SpreadsheetApp.getUi()
    .showSidebar(htmlOutput);
}

/**
 * Reads the column A from the sheet named Options.
 *
 * @returns {string[]} Dropdown options values
 */
function getOptions() {
  return SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Options')
    .getRange('A:A')
    .getValues()
    .flat()
    .filter(String);
}
/**
 * Appends a value to column A of the sheet named Options.
 *
 * @param {string} value
 */
function append(value){
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Options')
    .appendRow([value]);
}

index.html

It includes two divs with IDs, top and bottom. The div#top will hold the dropdown. The div#bottom holds an input element and a button. When the button is clicked, the input value is appended to column A of the sheet named Options; then the dropdown is replaced by a new dropdown including the most recent value appended.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div id="top">&nbsp;</div>
    <div id="bottom">
      <input type="text" />
      <button>Update</button>
    </div>
    <script>
      // Runs on sidebar loading
      (() => {
        // Adds the dropdown
        addDropdown();
        
        /**
         * Binds a function to click event of the button. 
         * This function appends the value entered 
         *  in the input element to the sheet named 
         *  Options, then remove the old select element 
         *  and add a new one, including the value  
         *  recently appended. 
         */
        document.querySelector('button')
          .addEventListener('click', () => {
            google.script.run
              .withSuccessHandler(() => {
                document.querySelector('#top').innerHTML = '&nbsp;';
                addDropdown();
              })
              .withFailureHandler((error) => alert(error.message))
              .append(document.querySelector('input').value)
          })
        
        }
      )();
      /**
       * Adds a select element to the div#top. The option values are taken from the column A of the sheet named Options.
       */
      function addDropdown(){
        const dropdown = document.createElement('select');
        google.script.run
          .withSuccessHandler( values => {
            values.forEach(value => {
              const option = document.createElement('option')
              option.setAttribute('value', value);
              option.innerText = value;
              dropdown.appendChild(option);
            });
            document.querySelector('#top').appendChild(dropdown);
          })
          .withFailureHandler((error) => alert(error.message))
          .getOptions();
      }
    
    </script>
  </body>
</html>

Please signup or login to give your own answer.

Click here to cancel reply.

Html – Updating appscript dropdown in sidebar after a successful operation

Answers