I’m developing a spreadsheet to track my expenses and along the way have been using appscript to add more functionality. There are a few things I’d like to do which seem not to be possible in appscripts and require HTML to work, but I have no real idea where to begin, nor do I have any experience coding HTML.

I have a script that’s bound to an onOpen(e) simple trigger

function addMenu() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Operations")
  .addSubMenu(ui.createMenu("Add").addItem(TypeCategory, addCategory.name).addItem(TypeBill, addBill.name).addItem(TypeTarget, addTarget.name))
  .addSubMenu(ui.createMenu("Delete").addItem(TypeCategory, deleteCategory.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();
}

Just focusing on the transferCategory script in the Transfer SubMenu:

function transferCategories() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let transferRange = sheet.getRange("G7:G9");
  let categoryRange = sheet.getRange("F12:F");
  let amount2Add    = sheet.getRange("G9");
  let originCategory = sheet.getRange("G7");
  let destinationCategory = sheet.getRange("G8");

  if (originCategory.isBlank() && destinationCategory.isBlank() && amount2Add.isBlank()) { Browser.msgBox("Please specify all fields!"); return }
  if (!originCategory.isBlank() && destinationCategory.isBlank() && amount2Add.isBlank()) { Browser.msgBox("Please specify all fields!"); return }
  if (originCategory.isBlank() && !destinationCategory.isBlank() && amount2Add.isBlank()) { Browser.msgBox("Please specify all fields!"); return }
  if (!originCategory.isBlank() && !destinationCategory.isBlank() && amount2Add.isBlank()) { Browser.msgBox("Please specify an amount to transfer!"); return }
  if (originCategory.isBlank() && !destinationCategory.isBlank() && !amount2Add.isBlank()) { Browser.msgBox("Please specify an origin category!"); return }
  if (!originCategory.isBlank() && destinationCategory.isBlank() && !amount2Add.isBlank()) { Browser.msgBox("Please specify a destination category!"); return }

  if ((originCategory.getValue() === destinationCategory.getValue()) && amount2Add.getValue() < 0) { 
  Browser.msgBox("Cannot transfer between categories of the same name!\nAmount to transfer must be greater than zero!"); 
  return; 
  }
  if ((originCategory.getValue() === destinationCategory.getValue()) && isNaN(amount2Add.getValue())) { 
  Browser.msgBox("Cannot transfer between categories of the same name!\nAmount to transfer must be a number!"); 
  return; 
  }
  if ((originCategory.getValue() === destinationCategory.getValue()) && !isNaN(amount2Add.getValue()) && amount2Add.getValue() >= 0) { 
  Browser.msgBox("Cannot transfer between categories of the same name!"); 
  return; 
  }
  if ((originCategory.getValue() != destinationCategory.getValue()) && amount2Add.getValue() < 0) { 
  Browser.msgBox("Amount to transfer must be greater than zero!"); 
  return; 
  }
  if ((originCategory.getValue() != destinationCategory.getValue()) && isNaN(amount2Add.getValue())) { 
  Browser.msgBox("Amount to transfer must be a number!"); 
  return; 
  }

  let overflowColumn                  = 10;
  let allocatedAmountRow              = 12; //Row number of first value
  let allocationAmountColumn          = 8;
  let previousAllocationAmountColumn  = 7; 
  let rowOrigin                       = categoryRange.getValues().filter(r => r!= "").flat().indexOf(originCategory.getValue());
  let rowDestination                  = categoryRange.getValues().filter(r => r!= "").flat().indexOf(destinationCategory.getValue());

  //Confirm that amount to deduct from origin category is enough
  let originCell    = sheet.getRange(allocatedAmountRow + rowOrigin, overflowColumn);
  let originAmount  = originCell.getValue();

  if (originAmount < amount2Add.getValue()) { Browser.msgBox("Not enough funds to transfer!"); return; }

  //Move values
  let originAllocationCell        = sheet.getRange(allocatedAmountRow + rowOrigin, allocationAmountColumn);
  let remainingAllocationAmount   = originAllocationCell.getValue() - amount2Add.getValue();

  if (remainingAllocationAmount < 0) {
    let prevAllocationCell    = sheet.getRange(allocatedAmountRow + rowOrigin, previousAllocationAmountColumn);
    let destinationAllocation = sheet.getRange(allocatedAmountRow + rowDestination, allocationAmountColumn);
    prevAllocationCell.setValue(parseFloat(prevAllocationCell.getValue()) - parseFloat(Math.abs(remainingAllocationAmount)));
    originAllocationCell.setValue(0);
    destinationAllocation.setValue(destinationAllocation.getValue() + parseFloat(amount2Add.getValue()));
    transferRange.clearContent();
    return;
    }
    originAllocationCell.setValue(originAllocationCell.getValue() - amount2Add.getValue());
    let destinationAllocation = sheet.getRange(allocatedAmountRow + rowDestination, allocationAmountColumn);
    destinationAllocation.setValue(parseInt(destinationAllocation.getValue()) + parseFloat(amount2Add.getValue()));
    transferRange.clearContent();
    return;
}

What this script is essentially doing is allowing the user to transfer allocation amounts between two spending categories.

Note that this script was originally written with the intention that the user would input everything in the sheet itself in two cells with dropdown lists that link to the spending categories, with an origin and destination category specified. In a separate cell, the amount to transfer would also be entered. The script itself was originally bound to a clickable drawing. The method is kind of long, but what is important is that I want to move everything to a Browser input box. After doing some research, I realized that the Browser class does not allow for the functionality of inputting multiple elements that you select from dropdown windows. Apparently what I need is the HtmlService class. Unfortunately the documentation is not very helpful.

In short, what I need is a window, similar in style to the Browser.msgBox() window:

In the window will be the relevant titled prompt and preferably three windows or fields (Option pane?), two with named dropdowns that are linked to the columns in the sheet from which to select, and one in which to enter the amount. Preferably there would be an OK and Cancel button (With position adjustable). When the OK button is selected, transferCategory is called and any associated error messages are pushed to a new window that display the appropriate message, from which the user can choose to try again, or cancel the operation. A successful transfer also displays a confirmation message. I noticed that the Browser class pauses script execution, whereas in the examples I’ve seen for HTMLService, this doesn’t seem to be the case. I don’t think pausing is needed here.

A template on how to get started on this would be appreciated.