As you've probably found, there appears to be no equivalent way to add the following Excel form and associated VBA code to Google Sheets or Scripts or Forms:
Is there some add-in that can be used to pop up this image and its controls? This has to be used many times in an accounting sheet to categorize expenditures at tax time.
It may not look exactly the same but I was able to construct a custom dialog in a short period of time to show how HTML service can be used to produce similar results.
First I construct an HTML template that contains the 2 combo boxes with multiple lines.
HTML_Test.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= include('CSS_Test'); ?>
</head>
<body>
<div id="left">
<label for="expenseCategory">Expense Category</label><br>
<select id="expenseCategory" size="10">
</select>
</div>
<div id="middle">
<label for="expenseSubCategory">Expense Sub Category</label><br>
<select id="expenseSubCategory" size="10">
</select>
</div>
<?!= include('JS_Test'); ?>
</body>
</html>
Then a CSS file to contain all my element formatting.
CSS_Test.html
<style>
#expenseCategory {
width: 90%;
}
#expenseSubCategory {
width: 90%;
}
#left {
width: 25%;
float: left;
}
#middle {
width: 50%;
float: left;
}
</style>
And a javascript file for client side javascript. I've simply hard coded some data to show how the select elements are filled in but this could just as easily be done using template scripting, or google.script.run
<script>
var expenses = [["A","1","2","3"],
["B","4","5"],
["C","6","7","8","9","10"]
];
function expenseCategoryOnClick() {
try {
let expenseCategory = document.getElementById('expenseSubCategory');
expenseCategory.options.length = 0;
expenses[this.selectedIndex].forEach( (expense,index) => {
if( index > 0 ) {
let option = document.createElement("option");
let text = document.createTextNode(expense);
option.appendChild(text);
expenseCategory.appendChild(option);
}
}
);
}
catch(err) {
alert("Error in expenseCategoryOnClick: "+err)
}
}
(function () {
// load first expense
let expenseCategory = document.getElementById('expenseCategory');
expenseCategory.addEventListener("click",expenseCategoryOnClick);
expenses.forEach( expense => {
let option = document.createElement("option");
let text = document.createTextNode(expense[0]);
option.appendChild(text);
expenseCategory.appendChild(option);
}
);
expenseCategory = document.getElementById('expenseSubCategory');
expenses[0].forEach( (expense,index) => {
if( index > 0 ) {
let option = document.createElement("option");
let text = document.createTextNode(expense);
option.appendChild(text);
expenseCategory.appendChild(option);
}
}
);
}
)();
</script>
Then there is the server side code bound to a spreadsheet.
Code.gs
function onOpen(e) {
var menu = SpreadsheetApp.getUi().createMenu("Test");
menu.addItem("Show Test","showTest");
menu.addToUi();
}
// include(filename) required to include html files in the template
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
function showTest() {
var html = HtmlService.createTemplateFromFile("HTML_Test");
html = html.evaluate();
html.setWidth(800);
SpreadsheetApp.getUi().showModalDialog(html,"Test");
}
The dialog looks like this. Many more html elements can be added as needed. This just shows the basics. This may be more difficult than an wysiwig html editor but I find I have better control of the appearance and function of my pages this way. Notice I clicked "C" and the sub category is filled in automatically.
