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
J

J_code

Guest
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:

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

The HTML script itself is here:

Code:
<!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():

Code:
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 tips/suggestions are welcome.

<p>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.</p>
<p>Essentially I have an HTML script that opens a sidebar and loads a list from a range in my sheet.</p>
<p>This is the script that returns the items from the column:</p>
<pre><code>function getAllCategories() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
return sheet.getRange("F12:F").getValues();
}

</code></pre>
<p>The HTML script itself is here:</p>
<pre><code><!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>


</code></pre>
<p>The HTML script is called from an <code>onOpen(e)</code> trigger. The script that performs the operation, in this case a deletion <code>deleteCategory(category)</code> 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 <code>GetCategories()</code> to the <code><input><\input></code> tag which already has another function <code>SubmitRecord()</code>. 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 <code>returnBack()</code>:</p>
<pre><code>let cat = document.getElementById("category");
let opt = document.createElement("option");
opt.value = category;
opt.innerHTML = category;
cat.removeChild(opt);
</code></pre>
<p>This also did not work. Any tips/suggestions are welcome.</p>
 
Top