Sometimes your Google Sheet tabs can get out of hand. They can be mixed up and confusing to users. It’s often necessary to simply sort them in ascending or descending order.
In this tutorial, we will use Google Apps Script to sort tabs in Google Sheets. We’ll also use a handy menu bar to quickly run the sort.
The approach below relies on a natural sort. In a normal sort where you also have numbers say:
"2. Cheese", "10. Pizza", "1. Bananas"
Then you ran your sort, you would not get what your expected but rather:
"1. Bananas", "10. Pizza", "2. Cheese"
With a natural sort, we consider the number numerically, rather than as characters. This means our sort would come out as expected:
"1. Bananas", "2. Cheese", "10. Pizza"
You can learn more about creating the Apps Script by following the video below or you can jump down and grab a copy of your code and run it in your own project.
// Iterates through each sheet sets it to activve and updates it order.
sheetNames.forEach((sheet,idx)=>{
ss.setActiveSheet(sheet)
ss.moveActiveSheet(idx+1)// Sheet tab indexes start from one so we must add one.
});
}
};
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Line 8: To create a menu we use the Create Menu method and add the menu label “Extras” as an argument.
Lines 9-10: Next, we can add menus to the main menu header with the Add Item method. These menus take a menu label and then a function or an object method that will run when the menu is selected. Ensure that you don’t include parentheses in these arguments and contain them in a string.
Line 11: Finally, we need to build the menu by adding all the menu items to the UI.
You can learn more about building menus in Google Sheets, Docs and Slides here:
The sortTabs property does all the heavy lifting in this object.
It takes a boolean as an argument that will determine if the sort is ascending or descending.
Get a list of sheets
Get a list of sheets
1
2
3
constss=SpreadsheetApp.getActiveSpreadsheet();
constsheets=ss.getSheets();ss.sheet
Our first task is to get an array containing all the Google Sheets in the currently active sheet. We can do this with the SpreadSheet App Get Sheets method. This will contain an array with all the methods available for each found sheet tab.
Using Intl Collator for natural sorting
We want to sort our sheet tabs naturally just in case we have numbers in the name of the tab (e.g. “1”, “2”, “4. Cake”). We can do this with the JavaScript Intl.Collator object to sort the sheet tabs in a natural order, meaning that numeric values are sorted in numerical order and text values are sorted alphabetically. You can modify this function to use a different sorting algorithm or change the sorting order.
new Intl.Collator JS
1
2
3
4
constcollator=newIntl.Collator(undefined,{
numeric:true,// Sets numeric collation so 1 < 2 < 10
sensitivity:'base'// Ignores accents and other diacritic marks in lettering.
});
Line 2: Here, we create a new Intl.Collator constructor. The first optional parameter of the constructor defines the locale or language tab. We don’t need this option, so we set it to ‘undefined’.
The second parameter sets the options for the collator.
Line 3: First, we use the numeric property and set it to true. This will allow us to order the numbers properly so that 1 < 2 <10.
Line 4: Secondly, we set the sensitivity to base. This means that we do not take into account any accents or other markings on letter characters. If you are using this Google Sheets sort tab code in other languages, then you might want to modify this.
Sort Google Sheet tabs Apps Script-side
Now that we have our collator set up we use the JavaScript sort method to sort through the array of sheet names.
Sort apps script-side
1
2
3
4
5
// Sorts the sheet.
let sheetNames=sheets.sort((a,b)=>{
// Compares first sorted sheet name value against last sorted sheet name value.
returncollator.compare(a.getName(),b.getName())
})
Line 2: Here, we use an arrow function with a standard a, b parameter set. These parameters refer to the first and second elements of comparison respectively.
Line 3: Use our collator and apply the compare method to compare the sheet names against each other.
Reversing the sort
Reversing a sort
1
2
// If set to descending the sheetNames lists is ignored.
Line 3: Inside each loop, we need to set the active sheet to the currently iterated sheet.
Line 4: Finally, we move the active sheet to the next position in the sheet tabs. Note that sheet indexes start from one (1) however our array index starts from zero (0). This is why we need to add one to the index.
Starting the sort after a set number of tabs
Perhaps you have an instruction tab or dashboards that you want to leave to the left of your sort. Here you can update the moveActiveSheet() by increasing the value to add to the index by the desired number plus 1.
Let’s say I have an ‘Instructions’ tab and a ‘Dashboard Tab’ that I want to keep to the far left and is not included in my sort list. my moveActievSheet() function would look like this:
Sorting after the desired number of tabs from the left.
1
ss.moveActiveSheet(idx+3)
Here I add 1 to the zero-based index and another 2 for the two tabs I want to keep in position.
Google Sheet Tabs by Date
Check out my bonus video on sorting tabs by leading date.
In this tutorial, we will cover creating a custom Table of Contents that lists your Google Sheets tabs on its own tab using Google Apps Script. This Table of Contents (TOC) will update whenever you open your sheet or choose to update it with a button.
But Yagi! Can’t I just check the list of tabs from the All Sheets button in the bottom right or scroll across bottom list of tabs until I find what I need?
Sure you can, but sometimes the sheet tab name just doesn’t properly explain what is in your sheet. There is a word limit to the tags and that bottom tab bar will get awfully cluttered if you start creating verbose tags. 🐐
On most of your sheets, you will probably have a title or description perhaps on the first row. This will probably more accurately detail what is occurring. You might also have some universal details that you have on all your sheets that you want to display on your table of contents tab.
Finally, you might only want certain tabs to be in your Table of Contents.
Note! As always, read as much as you need or settle in to read the whole thing.
Features
Our code contains the following features:
Generate a table of contents on a separate sheet tab. Any time we create a new sheet tab it will be added to our table of contents either on the next load of the Google Sheet or manually when editors of the sheet click a button.
Sort the sheet tabs alphabetically. So that your users have an easily indexable list. The can be removed.
Dedicated ‘Notes’ Sheet Tab for you to easily edit to change how you want your Table of Contents to be displayed. Make changes to how you want your Table of Contents to look right inside your Google Sheet.
Choose the location cell of your tab titles. Assign what cell your titles are going to be in.
Identify what Sheet Tabs you don’t want to be included in your Table of Contents. You might not want to share all of your tabs, right? For example, it seems a little silly to share your Table of Contents tab.
Optional addition of your Sheet Tab name included in the TOC.
There will be a bunch of example Sheet Tabs already there for you. Just go to File > Make a copy. Then open the Google Apps Script Editor (Extensions > App Script).
The Code
Code.gs
Code.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
//############# GLOBALS ##################
constNOTES_SHEET="Notes";
/**
* Google Apps Script trigger that runs each time the sheet is opened.
*/
functiononOpen(){
updateTOC();
};
/**
* Updates the Table of Contents (TOC)
*
* Searches the sheet tabs and if the shet is not on the excluded list,
* it will add it to the TOC.
* It will then publish it on the assigned page.
*
* Requires a dedicated "Notes" google sheet with a set of input boxes.
*
*/
functionupdateTOC(){
constSS=SpreadsheetApp.getActiveSpreadsheet();
constSS_ID=SS.getId();
constTOC_vars=getVariables(SS);// Get the object of values drawn from the 'Notes' sheet tab.
//Set the Table of Contents to active sheet (focus to this sheet)
Enter in all of your extra Sheet Tabs. Or as many as you have. You can always add more and your sheet will update your Table of Contents (TOC) next time the sheet opens.
Enter all of your parameters for your TOC (more on this in a bit) in the Notes sheet tab and click the button to run the code for the first time and go through the process of accepting permissions to run the code if you are happy with it.
Make a copy of the Notes tab data. Create a Notes tab and paste it into the exact same location.
Right-click on the Notestab of the Template Google Sheet. Select Copy to > Existing spreadsheet. Then search for the current Google Sheet you are working in.
Click to expand!
Then copy the Google Apps Script code above and paste it into your code editor.
What if I want to put the Notes setup in another place?
If you want to put the setup data in another Google Sheets tap, you will need to update the NOTES_SHEET variable on line 2 of the Code.gs file.
1
2
//############# GLOBALS ##################
constNOTES_SHEET="Notes";
If you want to move the setup data to start at a different cell you will need to scroll down to the getVariables() function and update the following line:
1
2
3
...
.getRange("A1:B31")
...
Ensure that the range is 30 rows deep and 2 rows wide and you will be good.
Completing the Setup Data in the Notes Sheet Tab
All grey areas indicate the places you need to fill out. There are instructions for each part. If you need an example, hove over the input fields and a note will popup with an example.
1. Select the location of your Title
All of your sheets will probably have the exact same location of their Title. Here you will provide the cell. If the title is merged over multiple cells, select the first cell in the top-left.
An example of a valid input would be, A2 or B4.
2. Do you want to add the sheet tab name to your Table of Contents?
You can essentially choose to display your table of contents with a counter and the title:
Or include the Sheet Tab name as a third row.
Having the sheet tab name can be really handy if you want to create other columns of data for your Table of Contents using the INDIRECT Google Sheets function. Take a look at this example:
Click to Expand!
Here is the formula, have a try yourself if you are playing along:
=IF(C3="","",INDIRECT(C3&"!A2"))
Check out this example sheet where we have added the name and students who have grades remaining to the TOC.
3. When a TOC link is clicked where should we navigate to?
You can choose what cell you want your uses to be navigated to when they click the link in the TOC.
You might not always want your users to go straight to cell A1. Perhaps you want to get them to work straight away and navigate them to the first cell of the data they need to enter say, cell B6 for example.
4. Name the Sheet Tab Where you are storing your TOC.
This will automatically be set to Contents, but you might want to call it TOC or list, or something.
Note that this will automatically update cell A20 so that it is excluded from the contents. If you are feeling a little eccentric then you can delete this.
5. The start row of the TOC
Choose the row that your Table of Contents, including the headers, will go. You might want to give your contents sheet tab a title so you may wish to indicate row 2 here.
6. Excluding sheets
You can list all the sheet tabs you want to be excluded here. the TOC sheet and the Notes tab is in by default but you can add up to 12 sheets you want to be excluded.
This might be useful for hidden sheets or sheet that don’t follow the Title pattern.
7. Run the code
To generate the TOC for the first time, run the code and got through the permission process. you will only have to do this once.
If you add more sheet tabs you can either click the button again or reload the page.
That’s all there is to set up your own Table of Contents for your Google Sheet. If you want to dive into the code with me, head down to the next chapter. If you are happy with this free tool, hit the like button and subscribe. Finally, donations help keep this site alive and reduce the ads I need to put on here. If you want to donate and support me there is a button up in the top-right of the sidebar.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Not much going on in the Global Variables. If you have your Table of Contents (TOC) setting in another sheet then you will need to update this.
onOpen()
onoOpen
1
2
3
4
5
6
/**
* Google Apps Script trigger that runs each time the sheet is opened.
*/
functiononOpen(){
updateTOC();
};
The onOpen() function is a reserved custom trigger in Google Apps Script. It can take one argument commonly notated as efor the event. For us, we do not need the event argument so we have left it out.
As its name suggests the onOpen() trigger runs when the document is first loaded. The function’s only task is to run the updateTOC() trigger.
It is generally a good practice to not bloat these custom trigger. Instead, use them to refer to functions that complete specific tasks.
updateTOC()
This is the main driving function. It will review the setup details for the TOC and then collect all the Google Sheet tab excluding the ones indicated. Then it will add the table of contents to the assigned sheet and then sort it.
Acquiring the main variables
1
2
3
4
5
...
constSS=SpreadsheetApp.getActiveSpreadsheet();
constSS_ID=SS.getId();
constTOC_vars=getVariables(SS);// Get the object of values drawn from the 'Notes' sheet tab.
We will need the unique ID of the spreadsheet to create our URL to link to other parts of the Google Sheet. Fortunately, we can do that easily with the getId() method. (Line 3)
Finally, we need to collect all the values that have been submitted in the TOC settings block found in the Notes. This is done with the getVariables(SS) function. This function takes the current spreadsheet object as an argument and returns an object containing something like the following example: (Line 4)
1
2
3
4
5
6
7
8
9
10
11
12
{
cellLoc:'A1',
sheetTabName:true,
navTo:'A1',
tocName:'Contents',
tocStart:'2',
exclude:[
'Contents',
'Notes',
'Example'
]
}
Loading the sheet on the Table of contents tab
1
2
//Set the Table of Contents to active sheet (focus to this sheet)
You’ll probably want your Google Sheet to open onto your Table of Contents each time. You can do this with the setActiveSheet() method that takes the sheet identifier.
Inside the brackets, you can see that we are using the getSheetByName() method to grab our selected sheet by calling on the TOC_vars object’s tocName key. In our example, we are referencing the Contents sheet tab.
If you don’t want the spreadsheet to open on your TOC you can comment this out or change the name of the sheet to your desired sheet tab name.
Set up the container variable that will store the TOC
1
2
3
4
5
6
...
//Set up headers depending on if user selects to add the Sheet tab or not.
let TOC_list=(TOC_vars.sheetTabName)?[["#","Title","Sheet Name"]]:[["#","Title"]];
let count=0;// This is to add a serial number to the list.
...
In our TOC setting, we give you the option to include the Sheet Tab Name as well as the title and reference number.
We use a Javascript ternary operator to first check if the tick box has been selected. If it has, we add the reference number, title and sheet name headers and store it in our TOC_list variable. If it hasn’t we only store the reference number and title headers. (Line 3)
To create our reference number, we will add a count variable and set it to zero. (Line 5)
Looping through all the Google Sheets
1
2
3
4
5
6
7
8
9
10
11
...
//Loop through all sheet tabs and select the title from each.
SS.getSheets().forEach(sheet=>{
let sheetName=sheet.getName();
if(!TOC_vars.exclude.includes(sheetName)){
let title=sheet.getRange(TOC_vars.cellLoc).getValue();
let sheetID=sheet.getSheetId();
...
Our first task is to iterate through all the sheet tabs. We can get a list of sheets using the getSheets() method. From there, we can apply the forEach JavaScript method to iterate through each sheet. (Line 3)
The forEach() method runs a function for each element in the array. We set sheet as our iterator variable.
The first task is to grab the sheet name from each sheet and store it in the sheetName variable. (Line 5)
As we look at each sheet name, we need to check it against our list of sheet tabs we want to exclude from our TOC. This is done on line 7 with an if statement that says that if the current sheet name is not included, or present, in our list of excluded sheet tabs, then continue with adding it to our table of contents.
We use the very fancy includes JavaScript method here to check if our current sheet exists in the list of excluded tab. Note the ! at the start which can be described as ‘not’ but more formally it means that we are looking for a false report on our if statement.
Next, we grab the title by using the getRange() Google Apps Script method to find the cell with the title in the currently iterated sheet. The location of the title is drawn from the TOC_vars.cellLoc value. The getRange() method can take, among other arguments A1notation to find a range. In our example, this is cell A1.
Lastly, we grab the sheet id. We will use this in a moment to create our sheet tab link.
Creating the link URL to each sheet tab
1
2
3
4
5
...
//Get the link to the sheet.
let sheetCellURL=`https://docs.google.com/spreadsheets/d/${SS_ID}/edit#gid=${sheetID}&range=${TOC_vars.navTo}`;
let hyperlink=`=HYPERLINK("${sheetCellURL}","${title}")`;
...
We’ll be making use of the Google Sheet HYPERLINK function to create a link for the title for each sheet. This function takes two arguments. The URL and the label for the URL. (Line 4)
Above this line, we will build the URL. There are three key points that we make modifications to the URL that you can see in the curly braces (${}).
The SS_IDis the unique spreadsheet ID for the current document.
The sheetID is the unique ID number for the sheet tab.
The TOC_vars.navTo is the cell where we want to direct the user to in the sheet.
Adding the count, title/link (and sheet name)
1
2
3
4
5
6
7
8
9
10
...
count++
//Add sheet tab data and count depending on whether user chose to add the Sheet Tab name
if(TOC_vars.sheetTabName){
TOC_list.push([count,hyperlink,sheetName]);
}else{
TOC_list.push([count,hyperlink]);
};
...
After we first increase our count by one (Line 2) we then need to add the count, the title connected to our link and if we chose to add the sheet name, well… we add the sheet name. 🙄
Line 5sif statement checks if the user selected the sheet tab name. If they did we push the count, hyperlink and sheet name to the TOC_list. Otherwise, we just push the count and the hyperlink. (Lines 5-9)
We will soon be pasting in our table of contents, but first, we will need to determine how deep our data is in rows and how wide it is. (Lines 6 & 7)
Just in case you delete out some Sheet tabs we want to make sure that you have a clean page, so we initially clear out the content. First, we grab the range with getRange() this time using 4 number parameters: (Line 8)
Row Start
Column Start
Row height
Col width
We have made the row height 100. It would be rare that you had more than 100 sheet tabs worth of rows in your TOC but you can always update this. Google is vague about the limit of sheet tabs.
Then we append the clearContent() method that clears the data from the range but not the formatting.
Finally, grab the range of the Table of Contents sheet again this time using our row height gathered from the length of the array. We then use setValues() to input our array of TOC into our sheet.
Our last task is to sort our table of contents. This is an optional step and you can comment out these two lines if you don’t want to use it.
We want to make sure that our data is loaded into our Google Sheet before we sort it or we might have an error or the sort might be skipped entirely. This is called accounting for Race Conditions. This is done by applying the flush() method straight from the SpreadsheetApp class. (Line 2)
Next, we want to grab the row below our newly added header and then all the listed sheet tabs. We add the Google Apps Script sort() method to this which for us takes a single argument, sort ascending by the 2nd across. (Line 3)
getVariables()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
* Acquires the user preferences for the TOC from the 'Notes' Google Sheet.
*
* @param {object} SS - the SpreadSheet App object.
* @return {object} = an object containing.
*/
functiongetVariables(SS){
constvals=SS.getSheetByName(NOTES_SHEET)
.getRange("A1:B31")
.getValues();
//Assign input from 'Notes' tab to keys.
constdataReferences={
cellLoc:vals[3][1],
sheetTabName:vals[6][1],
navTo:vals[9][1],
tocName:vals[12][1],
tocStart:vals[15][1],
exclude:(()=>{
return(vals.slice(19)
.map((row)=>row[0])
.filter((row)=>row!==""))
})()
}
returndataReferences;
};
The getVariables() function takes the spreadsheet as an argument and returns an object, for example:
1
2
3
4
5
6
7
8
9
10
11
{
cellLoc:'A1',
sheetTabName:true,
navTo:'A1',
tocName:'Contents',
tocStart:'2',
exclude:[
'Contents',
'Notes',
'Example'
]}
The functions first task is to grab the range of Table of Contents settings data. First, it grabs the sheet by its name (Line 8).
Then it grabs the range. You can change this range value if you put the settings range in a different spot. Just make sure it is 2 columns wide and 30 rows deep. (Line 9)
Next, we grab the values of the settings range with the getValues method. (Line 10)
We then create the dataReference object and assign our setting values to our sheet. Each location is in a 2d array and we draw them out of our vals array by first referencing the row and then the column: (Lines 13-23)
vals[row][column]
To get our list of excluded sheet tabs we run an Immediately Invoked Function Expression (IIFE)(Line 19). First, we slice our vals array from row 19 onwards (Line 20). We then use the map method to iterate through the remaining rows selecting only the first column (Line 21). Finally, we filter out all the empty rows ( Line 22)
The dataReference object is then returned to updateTOC() function. Line 25
Conclusion
Creating a table of contents in a tab of your Google Sheet is pretty useful for your users to be able to quickly navigate to what sheet tab they need. I hope that after reviewing the code you can make some changes for your own project.
If you have been playing along, you might have noticed that there is no data validation to ensure the received TOC settings are correct. I kinda thought adding this extra level of complexity would detract from what I was trying to achieve in the tutorial portion of this post.
However, running some validation either Google Sheets-side with Data Validation or inside your Google Apps Script will help reduce errors, but to be honest, not many folks are going to have access to the settings and those that do will probably figure out the error.
I was compelled to write this post based on interest in my Table of Contents from my previous post on using Google Sheets as a recipe folder. Check it out:
I would love to hear how you applied this Table of Contents creator in your own project. Feel free to comment below.
If you like this tutorial, give it a like so I know to keep em coming. If you want a regular dose you can subscribe down below. And if you want to support me, feel free to donate (top right-sidebar).
