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
Try this in your gs code
even a single column is a two dimensional array
try this:
Below is a simple example, including code for one .gs and one .html files.
Code.gs
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.