I am working on a project that creates new Google Sheets from a template based on names from a list in a backend Google sheet.
My struggle is I am trying to create a custom menu from a range of cells. Each menu item will open up a GUI, but I need to be able to pass data to that sheet (which you can't call functions with parameters from an app script menu item). My solution was to dynamically create functions from the list of sheet names. The only problem is I can't seem to make functions that the menu can call.
function updateMenu(){
//gets the names for the spreadsheets I want to create
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Checklist");
names = sheet.getRange('B:B').getValues();
//creates the functions for the list of names
//the function removeInvalidCharacters removes characters that are invalid for function naming
for(let i = 0; i<names.length; i++){
var tempName = '\"' + names[i] + '\"';
eval('var ' + removeInvalidCharacters(names[i]) + '= function(){openGui('+tempName+')};');
}
//Create the menu and the sub menu that will contain the sheet names you can create
var menu1 = SpreadsheetApp.getUi().createMenu('Create Sheets');
var subMenu = SpreadsheetApp.getUi().createMenu('Create from checklist');
//adding the names to the sub menu
//removeInvalidCharacters(names[i]) is what should call function created earlier
for(var i = 0; i < names.length; i++){
subMenu.addItem(names[i], removeInvalidCharacters(names[i]));
}
//adds the sub menu to the main menu and creates it
//also adds an option to update the names by calling this function again
menu1.addSubMenu(subMenu).addSeparator().addItem('Update Names', "updateMenu").addToUi();
}
When I run the above code it creates the menu with the proper names in the menu, but when I select one of the options in the menu it claims the script for that name doesn't exist
The other functions:
//removes whitespace and commas
function removeInvalidCharacters(word){
var newWord = word.toString();
newWord = newWord.replace(/\s+/g, '').replace(/,/g,'');
return newWord;
}
function openGui(fileName) {
var html = HtmlService.createHtmlOutputFromFile('Index.html')
.setWidth(640)
.setHeight(360)
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi().showModalDialog(html, 'Select Folder For: '+ fileName);
}
I assume my problem has something to do with the scope of the functions I created from the names because I can get a simpler test to work (seen at the bottom of the page) when creating the functions outside of an update function. The problem with that is I can't get it to update when I make changes to the list.
function onOpen() {
createMenu();
}
function createMenu(){
var menu1 = SpreadsheetApp.getUi().createMenu("Test")
for( var i = 0; i<names.length;i++){
menu1.addItem(names[i],names[i]);
}
menu1.addToUi();
}
function common(name) {
SpreadsheetApp.getActive().toast(name);
}
var names = ["A","B","C"]
for(let i = 0; i < names.length; i++){
var tempName = '\"' + names[i] + '\"';
eval('var ' + names[i] + ' = function() {common('+tempName+');}');
}