How do you pass variables between functions in Google Apps Script when using menu items?

Viewed 2815

I've been able to successfully build a custom menu with the onOpen() code below.

My desired outcome is to use my function setSheet() to set a specific sheet once a user has clicked on a menu item to select the given sheet.

The problem is that the setSheet function doesn't actually do anything, because I can't figure out how to pass the menu item 'name' to the other function.

I'm not sure what I'm doing wrong in this case? I would appreciate any help you could provide. Thanks!

Here's the working code I have so far:

function onOpen(e) {
 subMenus = [];
 ss = SpreadsheetApp.getActiveSpreadsheet();
 allsheets = ss.getSheets();
 for ( i = 0; i < allsheets.length; ++i){
   sheet = allsheets[i]
   subMenus.push({name:sheet.getName(), functionName: 'setSheet'})
  }
  SpreadsheetApp
  .getActiveSpreadsheet()
  .addMenu("Pick A Spreadsheet", subMenus)
}

function setSheet() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  name = ss.getss.getSheetByName()
  ss.setActiveSheet(name)
}

Additionally, I have made a workaround by using the code below, but I would like to save the user the need to input values into a prompt directly because they might not know the values of the sheet name.

function onOpen(e) {
  subMenus = [{name:"Go To Sheet", functionName: 'setSheet'}];
  SpreadsheetApp
   .getActiveSpreadsheet()
   .addMenu("Pick A Spreadsheet", subMenus)
}

function setSheet() {
  ss = SpreadsheetApp
  active = ss.getActiveSpreadsheet()
  ui = ss.getUi();
  response = ui.prompt("What Sheet Do You Need?")
  active.setActiveSheet(active.getSheetByName(response.getResponseText()))
}
1 Answers
Related