Dynamically creating functions and passing them into custom menus for Google Docs products (Docs, Sheets, etc.)

Question

I want to create a custom menu in my Google Sheet that has a list of all of the patient account numbers in the sheet. Just to test if this is possible, I created an object and stored a basic function in the object, just an alert with the account name, with each function being assigned a key of the account number itself.

I have an array of account numbers, I loop through that array and create a key in an object for each account number and store a basic function in each key. obj[acctNum] = function () {SpreadsheetApp.getUi().alert(acctNum)}. Now that I have a function for every account number, I want to dynamically pass that account number and function into sub-menus for a custom menu in my Google Sheet.

I've gotten everything down besides actually calling the function. I can dynamically create the menu and sub-menus to contain every account number on the spreadsheet. I created the object with the functions. I can even explicitly call the function on it's own by typing out obj[acctNum]().

When I pass the name of the function as a string into the .addItem(string,string) method, I'm passing it exactly how it would need to be. You'll see in the code below.

My assumption is that I'm not understanding something about how the .getUi().createMenu() method invokes functions and/or something about the "server side" concept. I'm a self taught coder so I know practically nothing about how code compiling and all of that works. I've provided all of the code related to this issue below. Unfortunately, I cannot share my file because it has HIPPA protected information on it. If it comes down to it, I can create a dummy file with fake account numbers to demonstrate the the problem/process.

My question is: How can I dynamically create functions and pass into the Google custom menus with the ability to call those dynamically created functions?

This part of the code loops through two separate objects I've created that have one key (AcctNums) and this key is an array countaining a list of account numbers. There are closed accounts and open accounts on my sheet.

var functions = {};
openFiles.AcctNums.forEach(function (accNum) {
  functions[accNum] = function () {
    SpreadsheetApp.getUi().alert(accNum); 
  }
});
closedFiles.AcctNums.forEach(function (accNum) {
  functions[accNum] = function () {
    SpreadsheetApp.getUi().alert(accNum); 
  }
});

Below is the code where I create the custom menu and looping through the same two objects as above, I create an item for each account number and pass the name of the function based on it's key name in the object (functions[accNum]). All of this code compiles correctly, and I get a custom menu on the sheet with the options listed and all of the account numbers populate as items.

function onOpen(e) {
  var ui = SpreadsheetApp.getUi()
  var parentMenu = ui.createMenu("Claim Path Tools")
  var oACF = ui.createMenu("Open a closed file");
  openFiles.AcctNums.forEach(function (accNum) {
    oACF.addItem(accNum,"functions[\"" + accNum + "\"]");
  })
  var cAOF = ui.createMenu("Close an open file");
  closedFiles.AcctNums.forEach(function (accNum) {
    cAOF.addItem(accNum,"functions[\"" + accNum + "\"]");
  })
  parentMenu.addSubMenu(oACF).addSubMenu(cAOF).addToUi()
}

Expected result: Whenever I click one of the items in the custom menu, I get an alert with the relative account number.

Actual result: I get an error saying:

"Script function not found: functions["COOC2018-71"] For more information, see https://developers.google.com/apps-script/reference/base/menu#addItem(String,String)"

NOTE: The "COOC2018-71" is one of the many account numbers on the page. Also, if I type anywhere in the code functions["COOC2018-71"]() it properly calls the function and gives me an alert with the account number. This is what I expected the menu to do as well, but it can't find the function when I call it from the menu.

The more I think about it, I'm guessing now that this has something to do with the fact that after the code has compiled, the functions object itself does not persist. I'm guessing thats why I can call it at the time of compiling but I can't call it after the code has finished compiling. It's not stored in memory or something like that. — Matthew Wolman
I wonder if you could write them to another file in your project. I have no idea if this can be done and I've never gotten serious about learning the Google Apps Script API so this could be a wild goose chase. — Cooper
That's a really interesting approach. I like it, but as far as I understand, there is no way to dynamically write code into a file in the DE itself. I thought about that too, just not in a different file. Like if I could somehow tell the code to write new code dynamically. — Matthew Wolman
There are other volunteers here that know considerably more than I do so it's likely they'll leave a comment letting me know that it's a totally absurd idea. — Cooper
I didn't click on the link you provided but I should have. I never looked into the GAS API. I'm going to go over this now, as I see it does mention creating and modifying Apps Script projects. Fundamentally, that is what I need to do. Let's see if I can do this! I'll update ya :). — Matthew Wolman

Matthew Wolman Matthew Wolman · Accepted Answer · 2019-04-30T00:15:19

I figured it out! You can add functions to the server side (I'm not sure if I'm saying this correctly) with the 'this' keyword. 'this' is the keyword used to access an object of all of your functions. Because it is an object, you can also add functions to it dynamically! So I looped through all of my account numbers, created a function named after each one like this:

openFiles.AcctNums.forEach(function (accNum) {
  this[accNum] = function () {
    SpreadsheetApp.getUi().alert(accNum); 
  }
});

I did this globally, so the server-side instance of this now contained a new key for each account number that was a function that will re-run every time a code is called. This was the complete solution. I looped through the menu and did an .addItem(accNum,accNum) and it's able to call the function that is stored in the this property without even needing to preface the string with the 'this'. Success!

Dynamically creating functions and passing them into custom menus for Google Docs products (Docs, Sheets, etc.)

1 Answers