skip to Main Content

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.

2

Answers


  1. 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();
    }
    
    Login or Signup to reply.
  2. 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>
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search