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> /*Investigate what this does*/
.narrow
{
margin-bottom: 0.5rem;
}
</style>
<script>
function submitRecord()
{
document.getElementById("displayReturn").innerHTML = ""; //Not sure if this is needed
let category = document.getElementById("category").value;
google.script.run.withSuccessHandler(returnBack)
.deleteCategory(category);
resetDropdown();
}
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;
category.appendChild(opt);
});
}).getAllCategories();
}
function resetDropdown()
{
google.script.run
.withSuccessHandler((categories) =>
{
const parent = document.getElementById("category");
parent.replaceChildren();
let opt = document.createElement("option");
opt.value = '';
opt.innerHTML = "---Select Category to Delete---";
opt.disabled = true;
parent.appendChild(opt);
categories.forEach((element) =>
{
let newOpt = document.createElement("option");
newOpt.value = element;
newOpt.innerHTML = element;
parent.appendChild(newOpt);
});
})
.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();" 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. 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.
resetDropdown() is misplaced. Try the following:
function submitRecord() {
document.getElementById("displayReturn").innerHTML = ""; //Not sure if this is needed
let category = document.getElementById("category").value;
google.script.run.withSuccessHandler(returnBack)
.deleteCategory(category);
resetDropdown();
}
Because google.script.run is asynchronous resetDropdown(), it might be executed before the server-side function deleteCategory finishes.
To ensure that resetDropdow is executed after deleteCategory has finished, relocate it.
function submitRecord() {
document.getElementById("displayReturn").innerHTML = ""; //Not sure if this is needed
let category = document.getElementById("category").value;
google.script.run.withSuccessHandler((stringBack) => {
returnBack(stringBack);
resetDropdown();
})
.deleteCategory(category);
}
Another option might be to move resetDropdown(); to the the returnBack function declaration.