function with parameter for addItem in spreadsheet menu using app script

Question

I need at each open of the spreadsheets, to check for the available sheets in the spreadsheet and make a list add-on that Go to Specific sheet through click in the item list

The number of the sheets will be large and added dynamically through working

i'm blocking currently on making this "dummy" function take parameters so, when called it should open the sheet by its index

function onOpen() {
  var monthPlanShortcuts = SpreadsheetApp.getUi().createMenu("Month Plan Shortcuts");
  var gotoSheet = SpreadsheetApp.getUi().createMenu("Go to sheet");
  // get properties to know who is the editor
  var userProperties = PropertiesService.getUserProperties();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  if (sheets.length > 1) {
    for (var index = 0; index< sheets.length;index++)
    {
      var currentIndex = userProperties.setProperty("index",index);
      gotoSheet.addItem(sheets[index].getName(),"dummy");
    }
  }
  monthPlanShortcuts
    .addItem('View Current Week Plan', 'gotoCurrentWeek')
    .addItem('Go to Specific Week', 'gotoSpecificWeek')
  .addSeparator()
  .addSubMenu(gotoSheet)
  .addToUi();
}

any suggestions??

Unfortunately, you can not pass a parameter value from the function in .addItem('Title of Menu Item', 'function name') to the function being called. So, there is no way to do what you want in one step from a menu. You would need to open a dialog box, or sidebar from the menu. Or open a sidebar automatically when the spreadsheet opens, and with HTML and JavaScript you can create something that will associate the sheet name with whatever code you want to run. — Alan Wells
– Alan Wells, Commented Feb 7, 2017 at 17:04
@SandyGood thank you but isn't there any workaround like, 1- generating functions with the a specific name and mark its functionality to active the sheet or 2- getting the index of the list an the index will refer to the sheet index as well or 3- unique function that take the function name and transfer to function with parameters — Mo SAEED
– Mo SAEED, Commented Feb 8, 2017 at 9:28

Florian Loitsch · Accepted Answer · 2020-12-31 16:49:54Z

5

There is a hackish workaround:

Dynamically create top-level functions, and then refer to them when adding the menu items.

Example:

/**
 * @OnlyCurrentDoc
 */
function onOpen() {
  let menu = SpreadsheetApp.getUi().createMenu("Dynamic entries");
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  for (var sheet of ss.getSheets()) {
    let id = sheet.getSheetId();
    let funName = "dynamicFun" + id;
    menu.addItem(sheet.getName() + "(" + id + ")", funName)
  }
  menu.addToUi();
}

for (var sheet of SpreadsheetApp.getActiveSpreadsheet().getSheets()) {
  let id = sheet.getSheetId();
  let funName = "dynamicFun" + id;
  this[funName] = function() { myRealFun(id); };
}

function myRealFun(id) {
  SpreadsheetApp.getUi().alert("" + id + " was pressed");
}

answered Dec 31, 2020 at 16:49

Florian Loitsch

8,1421 gold badge29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

function with parameter for addItem in spreadsheet menu using app script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related