GAS – Google Sheets – Dynamic Menu and Functions for Sheet Selection

May seem like a sledgehammer to crack a nut, but I was looking for a way to satisfy two things:

  1. Change sheet tabs and know (script-wise) which tab I had come from and was going to
  2. Create a dynamic menu for sheet tab selection, so that I could get the user focused on a single tab, as opposed to having them all there along the bottom

I first tackled this the hard coded long winded way, capturing the names of all sheets and writing an individual function for each, then moved on the a pair forwards / backwards functions, using “getSheets()” to provide the sheet array. This then lead me to going all dynamic!

Started searching, and there were a couple of posts about placing parameters into custom menu items, but nothing that really cracked it, until I came upon this post:

http://clav.cz/google-apps-script-menu-functions-with-parameters/

where Václav Novotný provides a solution to create a dynamic custom menu, and dynamic scripting for functions to go with it. This was setup for a Google Doc, so I need to rework it a bit for my needs. All credit to Václav Novotný though for the meat on the bones.

  • Opened up a new Google Sheet and added four more tabs, named these up, and added colours for fun
  • Opened up the script editor and started the code. Initally we need to create a couple of arrays, one with the sheet numbers (0 – n) and one with the sheet names in the same order, along with some variables needed as prefixes for the menu entries:

  • I then needed a couple of simple scripts to hide all the tabs and show all the tabs, as I needed to hide all but one tab on open:

  • With this set I then had to create the menu

For now, we are only interested in the loop in the middle, which builds the menu items and function names for each. I’ll cover the other menu entries further on.

  • Now for the magic, and I have to confess I do not completely understand this, but from Václav’s comments this piece of code “sets” the dynamic functions, which seem to exist in the javascript ether, and ensures they do not get lost on sheet usage, and also calls the dynamic function “alertNum”:

  • As mentioned the last part is the function that can use the parameter generated by the custom menu, arrays, and previous code. It comes in two parts, first function picks up the parameter, then calls the second function which selects the sheet tab the user asked for, then displays this and hides the previously selected tab:
  • The last piece of the dynamic puzzle is to have the onOpen trigger script. This runs the hide tabs function and the create menu function

  • OK, with all that done, I also wrote the Go Forward and Go Backward function to simply do that. These were added to the custom menu along with the hide all / show all functions

    And it just works :) The functions should work with any number of tabs, you can name each tab/sheet what you like. It does however rely on the first sheet staying where it is being a sort of DASHBOARD or INSTRUCTIONS sheet.

    Here is the full gs file:

    And you can access a sample spreadsheet here:
    https://docs.google.com/spreadsheets/d/1ysfmIxeOeU3GVhAg96KDoiDFO5Zs-xjRSnUTVqpEN2o/edit?usp=sharing

    Make a Copy if you wish

Leave a Reply

Your email address will not be published. Required fields are marked *