0

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+');}');
}
  • In your situation, is this thread useful? https://stackoverflow.com/a/59417714 – Tanaike Jul 07 '22 at 01:32
  • @Tanaike The thread fixed the problem, I removed my loop that created functions and implemented what was explained in the thread. – Ki McAllister Jul 07 '22 at 03:15
  • Thank you for replying. I'm glad your issue was resolved. From your reply, I flagged your question as a duplicated question. – Tanaike Jul 07 '22 at 04:35

0 Answers0