0

Is there a way to have multiple rows of tabs for the sheets at the bottom of the editor? Or failing that, is there a way I can lock one tab so it is always available and never "slid off the screen?"

I have a spread sheet with a lot of worksheets (16 and growing.)

As I switch back and forth between the sheets, it is a nuisance to either use the arrows on the right or the "drop down list" hamburger on the left, to switch.

I'd like to have multiple rows, but failing that, is there a way to lock one sheet into the first position such that is is always available?

Thanks, Mark.

1
  • Per SO/SE norms, please remove the tag from the question title. Remove the "Thanks, Mark". Jul 10, 2023 at 18:10

2 Answers 2

1

There is no built-in way to change the default behavior and the look and feel of tabs in the Google Sheets web app other than assigning a color to the tab, but there are a few options: All Sheets button, Name box, among others.

All Sheets button

All Sheets button

This option might be preferred by users that like to handle sheets opening by point and click and look for no setup, no maintenance option. Clicking the All Sheets button will show a list of all the sheets. It might not be the best option when having a very large list of sheets and on small screens.

Name box

Name box

This option might be preferred by users who like to use keyboard shortcuts and type over multiple clicks and scrolling. On the *Name box you can type the sheet name. The autocomplete option will show the matching sheets' names as you type; it's possible to select the sheet from the autocomplete options using the keyboard.

Google Apps Script

You might extend Google Sheets by using Google Apps Script. You could create a custom menu and Editor Add-On or Workspace Add-On (a side panel).

From the custom menu, you might open a dialog, a sidebar or open specific sheets.

Google Apps Script uses JavaScript as a programming language. It has built-in services that save a lot of programming compared to using a userscript or creating a web browser extension. It might require time for programming but doing it correctly could avoid requiring maintenance. Also, it's possible to make the script reusable by creating a library or add-on. Workspace account users might create add-ons for internal use.

Non built-in features

Web browser bookmarks

Each sheet has its URL. You could use your web browser bookmarks, especially if you have a few sheets that you need to open frequently.

Userscripts

I haven't used userscripts with Google Workspace editors (Google Docs, Drawings, Forms, Sheets, Slides), but it might be possible; just be aware that Google might change the elements ID and the class names anytime, causing the userscript to break and require more maintenance than other options. Because of this, you might prefer using stable features and the Google Sheets API to a userscript to play with the DOM.

1
  • 1
    I didn't think of the "Name box" IMO 'that' is the solution as it is easy quick and supports autocomplete so adding a unique first character to the sheet name makes it very quick.
    – Blindspots
    Jul 10, 2023 at 18:39
0

None of the things you are requesting are possible however depending on your workflow you can employ some workarounds.

Wicket has provided some good ones, in particular, the "Name box" seems like a good option, and doesn't require any setup for new spreadsheets.

Record a Macro (Script)

Should you want to create a macro with an associated keyboard shortcut, here are the steps:

  1. Navigate to Extensions > Macro and click Record macro

     

  2. Ignore the Recording new macro... dialog

  3. Type Alt+/ (search menus) and type go and click on Go to range (the first suggestion)

  4. In the Enter range dialog type the name of the sheet you want to jump to and click Enter

  5. Click on Save in the Recording new macro... dialog

     

  6. In the Save new macro dialog enter a name and give a number for the shortcut

     

  7. If you review the code by clicking Apps Script in the Extensions menu, the macro will look something like the following where Sheet1 will be replaced by your own sheet name:

    function Jump() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('L20').activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
    };  
    


     

Notes:

  1. The code line similar to spreadsheet.getRange('L20').activate(); is unnecessary and can be removed (L20 will be different). Any other lines of code related to unnecessary actions performed while the macro was recording can likewise be removed. So the required code can be simplified to:

    function Jump() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
    };  
    
  2. My instructions #3 and #4 can optionally be replaced by Wicket's "Name Box" instructions. Recording either to a macro will generate the same Apps Script code, albeit my approach is less efficient (requires more steps).

2
  • 1
    I accidentally forgot to mention the use of macros in Google Sheets in my answer. It's great that you have mentioned it. P.S. While it might be possible to create good macros, I find using Google Apps Script custom menus more friendly than the macros menu / keyboard shortcuts. Jul 10, 2023 at 19:24
  • 1
    Again credit to Wicket for another great point (Apps Script custom menu). Here is a link to a tutorial to build a tab Navigation menu in Google Sheets
    – Blindspots
    Jul 10, 2023 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.