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()))
}