Google Workspace and Software Development
Sometimes we want to compare the difference between two arrays in JavaScript and return the remaining values in a new array.
This can be useful for inventory or purchase management where we could compare the selected items against the available stock, for example.
In this tutorial, we will cover two approaches to this:
Let’s get cracking!
Continue reading “Get the Difference Between Two Arrays in JavaScript”
A mate reached out to me last week asking if there was a way to monitor the times any article in a Google Sheet list goes through an editing stage before being published. From there, he wanted to report the number of edits on the article in an adjacent cell.
Take a look at the example:
As you can see in the example, whenever “NEEDS EDIT” is selected from the dropdown menu, then the counter column for the selected row item is updated automatically.
In this tutorial, we will cover how to create this for a single Google Sheet tab or for multiple selected tabs. I’ll also wrap up with a script to calculate just the aggregate count of changes to all the rows in the selected range and report it in a single cell.
Check out the video tutorial that covers the basics and grab the Starter Sheet to play along:
Scheduled release 6 Dec 2022 9am GMT+10
Generally better to view it from YouTube.
Here is a link to the starter sheet copy the sheet and head to Extensions > Apps Script in the menu.
Everything starts with the onEdit(e) simple trigger. This is a special trigger in Google Apps Script called a Simple Trigger that will run every time you edit your Google Sheet. If Apps Script sees the onEdit() function in your project it runs automatically.
Every time onEdit() is executed, it will retrieve an event parameter that is commonly referenced as "e" (onEdit(e)). The event object can return things like:
We will be using a few of these in our code below.
Unless you have a very short coding task, it is generally good practice to reference another function that will be executed when onEdit() is run rather than coding directing within the onEdit() function.
Take a look at the three examples I added for my test project for this tutorial:
|
1 2 3 4 5 6 7 8 |
function onEdit(e) { countTimesSpecificCellValSelected_v1_(e); // Count each row in a single col countTimesSpecificCellValSelected_v2_(e); // Count for multiple Sheet Tabs countTimesSpecificCellValSelected_v3_(e); // Count Aggregate }; |
You can see that each one of these functions will be run one after the other when a cell is edited and calls onEdit().
In this example, we add a count to our adjacent counter cell each time the cell is edited.
Note that we take the event (e) as our parameter for our countEveryTimeCellIsChanged(e) function.
|
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 |
/** * @OnlyCurrentDoc */ /** * Simple trigger function that runs after each change in the sheet. */ function onEdit(e) { countEveryTimeCellIsChanged(e) }; /** * Counts the times a cell is edited in a specified column and displays * the count in the selected adjacent column. * @param {Object} e - The event object from the onEdit trigger event. */ function countEveryTimeCellIsChanged(e){ // ### VARIABLES ### // Update these const tgtSheet = "Single List"; // The sheet name the data is on. const tgtCol = 3; // The column to search for a change. const counterCol = 4; // The column to update the counter. // ### --- ### const range = e.range const sheet = range.getSheet(); const column = range.getColumn(); // check if on the correct sheet and in the correct column. if(sheet.getName() === tgtSheet && column === tgtCol){ const row = range.getRow() // Get the counter cell; const counterCellRange = sheet.getRange(row, counterCol); const counterCellVal = counterCellRange.getValue(); const updateCounterCell = (counterCellVal < 0)? 1 : counterCellVal + 1; counterCellRange.setValue(updateCounterCell) }; }; |
First, let’s assign all our variables at the top of our function to make it easier for us to change should we need to down the track.
We have 3 variables that we need to include here. They won’t change so we will make them a constant variable:
tgtSheet – This is the target sheet tab name. For our example, this is assigned to “Single List”.tgtCol – This is the target column or the column that has the values that will need to be monitored for changes. In our example, this is the status column, column 3.counterCol – This is the column where we display our change count. For us, this is column 4, but it could be any selected column in the sheet.
You can stop here if you want to copy and paste the script into your own project and update the variables for your own purposes. Or continue to learn how it all works.
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.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
Lines 33-38
We don’t want to end up counting the wrong column or updating a completely different sheet tab by mistake. This means we need a way to check if we are in the right spot.
The first thing we need to do is check the range we are currently editing. This can be done by grabbing the range object in our event parameter. Line 33
We now have access to the Google Apps Script range class. From this class, we can get the sheet class and also the range column number. Line 34-35
Using the JavaScript ‘if’ statement we first check that the sheets name (sheet.getName()) is equal to our assigned target sheet name and if it is, then check that the selected column is equal to the target column. Line 38
Lines 40 – 48
We need to update the adjacent counter cell in the same row that was changed when edited.
We can use the range class again with the getRow() method. This will give us a row number. Line 40
const row = range.getRow();
Next, using the sheet variable we created earlier we can call the getRange() method to get the counter cell. For a single cell, this method can take two variables the row and the column number. Here, we add in these two variables we collected earlier.
const counterCellRange = sheet.getRange(row, counterCol);
Now we need to add one to the current count in our counter cell. This means that we first need to collect the current value in the cell, which we do with the getValue() method.
We need a way to check if the value in the counter cell is empty. If it is then we want to give it a value of 1 otherwise we want to add 1 to the current value.
const counterCellVal = counterCellRange.getValue();
This can be achieved with a ternary operation. Think of a JavaScript ternary operator as a single line if statement in a way.
The ternary operator below states that if the value is less than zero (empty) then add one otherwise add one to the counter cell value.
const updateCounterCell = (counterCellVal < 0)? 1 : counterCellVal + 1;
Finally, we update the counter cell:
counterCellRange.setValue(updateCounterCell);
Done!
The following scripts will modify this code. We will touch on the changes only in the code discussion of each script.
In this scenario, we only want to count when a cell changes to a specific value. For our example, this value is “NEEDS EDIT”.
|
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 |
/** * @OnlyCurrentDoc */ /** * Simple trigger function that runs after each change in the sheet. */ function onEdit(e) { countTimesSpecificCellValSelected_v1_(e); // Count each row in a single col }; /** * Counts the number of times a cell value is change to a specfic item * from a dropdown in a specified column and displays the count in the * selected adjacent column. * @param {Object} e - The event object from the onEdit trigger event. */ function countTimesSpecificCellValSelected_v1_(e){ // ### VARIABLES ### // Update these const tgtSheet = "Single List"; // The sheet name the data is on. const tgtCol = 3; // The column to search for a change. const counterCol = 4; // The column to update the counter. const tgtVal = "NEEDS EDIT" // ### --- ### const range = e.range const sheet = range.getSheet(); const column = range.getColumn(); // check if on the correct sheet and in the correct column. if(sheet.getName() === tgtSheet && column === tgtCol){ // Check if the cell value matches the target value we need to count. const cellVal = e.value; if(cellVal === tgtVal){ const row = range.getRow() // Get the counter cell; const counterCellRange = sheet.getRange(row, counterCol); const counterCellVal = counterCellRange.getValue(); const updateCounterCell = (counterCellVal < 0)? 1 : counterCellVal + 1; counterCellRange.setValue(updateCounterCell) }; }; }; |
First off, we need to store a target value that we want to monitor for and if that value appears add one to our counter cell. Here on line 29, we set our target value to “NEEDS EDIT”.
const tgtVal = "NEEDS EDIT"
You can change this to the value that you need for your project.
Next, we need to check if the cell that was edited was edited to the new target value before we update the counter cell.
To do this we first get the value of the selected range (Line 42):
const cellVal = range.getValue();
Then we create an if statement checking that the cell value matches the target value on line 43 and closes on line 54.
if(cellVal === tgtVal){
What if we have different editorial teams or genres handled by different editors? We might want to put the data for each team in a different sheet tab to make it easy for monitoring. Here we will need to check all the selected sheets for any changes.
In our example now we want the “Team 1” and “Team 2” sheet tabs. Both tabs are identical in format.
Let’s see how our code works now:
|
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 |
/** * @OnlyCurrentDoc */ /** * Simple trigger function that runs after each change in the sheet. */ function onEdit(e) { countTimesSpecificCellValSelected_v2_(e); // Count for multiple Sheet Tabs }; /** * Counts the number of times a cell value is change to a specfic item * from a dropdown in a specified column for selected sheets and displays * the count in the selected adjacent column. * @param {Object} e - The event object from the onEdit trigger event. */ function countTimesSpecificCellValSelected_v2_(e){ // ### VARIABLES ### // Update these const tgtSheets = ["Team 1", "Team 2"]; // The sheet names the data is on. const tgtCol = 3; // The column to search for a change. const counterCol = 4; // The column to update the counter. const tgtVal = "NEEDS EDIT" // ### --- ### const range = e.range const sheet = range.getSheet(); const column = range.getColumn(); // check if on the correct sheet and in the correct column. if(tgtSheets.includes(sheet.getName()) && column === tgtCol){ // Check if the cell value matches the target value we need to count. const cellVal = e.value; if(cellVal === tgtVal){ const row = range.getRow() // Get the counter cell; const counterCellRange = sheet.getRange(row, counterCol); const counterCellVal = counterCellRange.getValue(); const updateCounterCell = (counterCellVal < 0)? 1 : counterCellVal + 1; counterCellRange.setValue(updateCounterCell) }; }; }; |
Two changes need to be made to the script here.
On line 25, we need to change the tgtSheet variable to tgtSheets and include an array of all the Google Sheet tabs we need to monitor with our Apps Script code.
const tgtSheets = ["Team 1", "Team 2"]; // The sheet names the data is on.
Then on line 38, we need to replace the first part of the if statement with a way that will look through our array of sheets and check if the current sheet contains the selected name.
This can be achieved with the JavaScript includes function. This function appends a selected array (In our case, the target sheets array) and takes a target value as an argument. Our target value is the select sheet that this being edited.
... tgtSheets.includes(sheet.getName()) ...
This final script checks all the cells in the target column in a single Google Sheet tab and checks if they have been changed to “NEEDS EDIT”. Then records the aggregate count of all the cells in a single counter cell in a separate sheet tab.
In our example, we will monitor the “Single List” sheet tab for our desired change and record the total count of changes for all rows in the “Aggregate” sheet tab.
Check out the code:
|
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 |
/** * @OnlyCurrentDoc */ /** * Simple trigger function that runs after each change in the sheet. */ function onEdit(e) { countTimesSpecificCellValSelected_v3_(e); // Count Aggregate }; /** * Gets the aggregate of all the times each cell item changes to the * selected cell value in a given row and reprots in a selecte cell. * @param {Object} e - The event object from the onEdit trigger event. */ function countTimesSpecificCellValSelected_v3_(e){ // ### VARIABLES ### // Update these const tgtSheet = "Single List"; // The sheet name the data is on. const tgtCol = 3; // The column to search for a change. const counterCell = 'Aggregate!A2'; // The column to update the counter. const tgtVal = "NEEDS EDIT" // ### --- ### const range = e.range const sheet = range.getSheet(); const column = range.getColumn(); // check if on the correct sheet and in the correct column. if(sheet.getName() === tgtSheet && column === tgtCol){ // Check if the cell value matches the target value we need to count. const cellVal = e.value; if(cellVal === tgtVal){ const counterCellRange = e.source.getRange(counterCell); const counterCellVal = counterCellRange.getValue(); const updateCounterCell = (counterCellVal < 0)? 1 : counterCellVal + 1; counterCellRange.setValue(updateCounterCell) }; }; }; |
First, we have replaced the counterCol variable with the counterCell variable and set that to cell A2 of the ‘Aggregate’ sheet tab.
const counterCell = 'Aggregate!A2'; // The column to update the counter.
Next, we don’t need to get the row number this time so we have deleted this variable.
Lastly, on line 44, we use the source property of the event (e) object to grab the current spreadsheet. This allows us to use the getRange() method to select our counter cell.
There should be enough samples there for you to work out your own combinations. If you want to test your new skills out, try to:
If you get stuck check out some of the other popular tutorials on using onEdit():
It’s always nice to hear how people use these scripts in their own projects and it can be inspiring to share your use cases with others. Feel free to share your experience and examples in the comments below.
Finally a big thanks to Mike Kanert for the inspiration for this post. Editor, writer, artist, actor, educator, polymath and all-around nice fella, you can find Mike on Instagram @Unremarkable_Us for his latest comic series made by parents for parents:
If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.
~Yagi
You’ve created an awesome Google Apps Script web app for your secret society within your Google Workspace organisation or …dom! dom! DOM! … the world. The problem is that you only want to share your web app with the worthy. Those selected few. 🐐🛐🛐🛐
How do you do this? How to prevent this most coveted of apps from reaching the wrong hands?
It’s actually surprisingly simple.
In this tutorial, we will explore how to validate selected users to provide access to your web app. For our example, we validate users based on whether or not they have edit access to a Google Drive file ( a common occurrence). In the discussion, we will also look at alternative ways of validating emails.
One of the bonuses of the approach we will go through is that it can also be easily adapted for use in Google Workspace Add-ons, and Editor Add-ons like sidebars and dialogue boxes.
We’ll start off with an example and then move to a quick-use guide for those of you who just want to get in and apply the code to your own project. Then for those who want to know how it all works, I’ll dive into the details.
Let’s get started!
Continue reading “How to Validate Specific Users on a Web App in Google Apps Scripts”
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.
Our code contains the following features:
Let’s take a look at what we will be making:
If you are following along with the code, here is the raw Google Sheet.
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).
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 ################## const NOTES_SHEET = "Notes"; /** * Google Apps Script trigger that runs each time the sheet is opened. */ function onOpen() { 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. * */ function updateTOC() { const SS = SpreadsheetApp.getActiveSpreadsheet(); const SS_ID = SS.getId(); const TOC_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) SS.setActiveSheet(SS.getSheetByName(TOC_vars.tocName)); //Set up headers depending on if user selecte 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. //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(); //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}")`; 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]); }; }; }); //Get the TOC sheet. const TOC_Sheet = SS.getSheetByName(TOC_vars.tocName); //Add the table of content data to the sheet. let rowLen = TOC_list.length; let colLen = TOC_list[0].length; TOC_Sheet.getRange(TOC_vars.tocStart, 1, 100, colLen).clearContent(); ///Clear out just in case of deletes. TOC_Sheet.getRange(TOC_vars.tocStart, 1, rowLen, colLen).setValues(TOC_list); //Sort Range Alphabetically. SpreadsheetApp.flush(); TOC_Sheet.getRange(TOC_vars.tocStart + 1, 2, rowLen - 1, colLen - 1).sort(2); }; /** * Acquires the user preferences for the TOC from the 'Notes' Google Sheet. * * @param {object} SS - the SpreadSheet App object. * @return {object} = an object containing. */ function getVariables(SS) { const vals = SS.getSheetByName(NOTES_SHEET) .getRange("A1:B31") .getValues(); //Assign input from 'Notes' tab to keys. const dataReferences = { 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 !== "")) })() } return dataReferences; }; |
The fastest way to get started is to grab a copy of the template file below (File > Make a copy).
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.
Running Google Apps Script for the First time. What’s with all the Warnings!
Yeap, when you copied the sheet across a copy of the code was transferred across with it. Cool, hey!?
Then go to your assigned Contents Sheet Tab and format it how you like. Don’t worry it won’t be removed the next time the TOC is updated.
Hide and protect your notes tab and any anything else you want hidden and protected and you are all done.
First, create a new Google Sheets tab and label it as Content or whatever you want to name your TOC. Format it how you like.
Then go to the Table of Contents – Template and either:
Then copy the Google Apps Script code above and paste it into your code editor.
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 ################## const NOTES_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.
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.
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.
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:
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.
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.
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.
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.
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.
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. Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
|
1 2 |
//############# GLOBALS ################## const NOTES_SHEET = "Notes"; |
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.
|
1 2 3 4 5 6 |
/** * Google Apps Script trigger that runs each time the sheet is opened. */ function onOpen() { updateTOC(); }; |
The onOpen() function is a reserved custom trigger in Google Apps Script. It can take one argument commonly notated as e for 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.
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.
|
1 2 3 4 5 |
... const SS = SpreadsheetApp.getActiveSpreadsheet(); const SS_ID = SS.getId(); const TOC_vars = getVariables(SS); // Get the object of values drawn from the 'Notes' sheet tab. ... |
The first task is to grab the current active spreadsheet from the SpreadsheetApp class. (Line 2)
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' ] } |
|
1 2 |
//Set the Table of Contents to active sheet (focus to this sheet) SS.setActiveSheet(SS.getSheetByName(TOC_vars.tocName)); |
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.
|
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)
|
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.
|
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 (${}).
SS_IDis the unique spreadsheet ID for the current document.sheetID is the unique ID number for the sheet tab.TOC_vars.navTo is the cell where we want to direct the user to in the sheet.|
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 5s if 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)
This concludes the loop through the sheet.
|
1 2 3 4 5 6 7 8 9 10 |
... //Get the TOC sheet. const TOC_Sheet = SS.getSheetByName(TOC_vars.tocName); //Add the table of content data to the sheet. let rowLen = TOC_list.length; let colLen = TOC_list[0].length; TOC_Sheet.getRange(TOC_vars.tocStart, 1, 100, colLen).clearContent(); ///Clear out just in case of deletes. TOC_Sheet.getRange(TOC_vars.tocStart, 1, rowLen, colLen).setValues(TOC_list); ... |
Our first task is to get the Table of Contents sheet object and store it in TOC_Sheet. (Line 3)
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)
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.
|
1 2 3 |
//Sort Range Alphabetically. SpreadsheetApp.flush(); TOC_Sheet.getRange(TOC_vars.tocStart + 1, 2, rowLen - 1, colLen - 1).sort(2); |
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)
|
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. */ function getVariables(SS) { const vals = SS.getSheetByName(NOTES_SHEET) .getRange("A1:B31") .getValues(); //Assign input from 'Notes' tab to keys. const dataReferences = { 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 !== "")) })() } return dataReferences; }; |
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
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:
https://yagisanatode.com/2020/12/11/use-google-sheets-to-store-your-recipes-to-automatically-change-batch-sizes-and-recipe-amount-by-weight/
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).
~Yagi
Google Apps Script, Google Sheets, Javascript
Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet?
I know that I have come across these conditions a number of times in the past. Maybe you have a task list that you want to send your team each time a task is allocated to them. Once they let you know that they are done, by say, entering a set of values or checking a task complete box on the row they need to work on, you want to be able to remove their edit permissions from your sheet.
In this post, we have created a Google Apps Script that will add and remove editors to a Google Sheet based on the spreadsheet’s data. More specifically, this script will:
The best way to probably understand this script is through an example…
