Month: December 2020

2020 – Year in Review

It’s always nice to a have a bit of a look back at the previous year to see how you went, what you’ve achieved, what you can work on and where you want to go in the future. 2020 was a rough one for everyone and it was no different for this goat 🐐. Some extremes of this year pushed me and my wife to some pretty major life changes. Some hard; some very, very positive.

The executive summary is like this:

  • My wife and I moved countries.
  • I left my managerial job to focus on Yagisanatode and developing fulltime.
  • I revealed the man behind the goat.
  • My site has exploded in views ( I am incredibly grateful ❤)
  • I’ve produced more content than ever.
  • I became a Google Developer Expert in Google Workspace.
  • The jokes are still bad.
  • The website got two overhauls.
  • I started my first Youtube Series.

Read on to get the full details:

The Website

Stats

My main goal was to reach 1 million unique visitors by the end of 2020. Why is this important? Well, I think it is just a pretty good metric to show that my work is helping people, people are sharing my tutorials on sites like support.google.com and Stack Overflow, and the posts are clear enough for Google search to recognise that I am making good content.

All-time Stats

So did I reach 1 million unique visitors? No, but frustratingly almost. As of lunchtime on the last day of 2020, I got to 981,988 visitors since my first ever post back in October of 2017.

For me, that is still pretty awesome, and I am truly thankful for all the people who take the time to visit my site and hopefully find something useful to help them with their own project.

Here are few more all-time stats since I first started:

  • Total posts: 135
  • Total views: 1,350,035
  • Total visitors: 981,988

That’s an average of 1.37 page views per person.

I now have 90 legendary email subscribers that receive an email every time  I post and 320 you-beaut followers overall.

2020 Stats

This year, I have published 51 posts (up 13 posts from last year). 27 of these posts have been a part of the Google Sheets Basics Youtube Series. I wrote 18 posts on Google Apps Scripts, 5 on Google Sheets and 1 posts on Google Slides.

I wrote a total of 94,891 words this year (up 6,197 words from 2019) and on average wrote 1,861 words per post.

The most popular post written this year was a Google Apps Script post:

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet

The most popular Google Sheets post written this year was:

Google Sheets: How to use OR inside a FILTER

This year’s visits and views have skyrocketed too:

  • Views: 816,785 – up 372,419 views from 2019
  • Visitors: 597231 – up 278262 visitors from 2019

Covid-19 made it an incredibly busy first half of the year in my day job. In many months I had to reduce my usual 2 posts a month to only one, just to keep up with the demands of my full-time job. Much of the increase in posts has been since October, where I have produced up to 16 video posts a month and up to 5 long-form posts a month.

Oh. I also got my first DD0S attack (You know you really made it then 🤣). Fortunately, My WebHost GreenGeeks were all over it and let me know and helped me improve the security of my site.

Site Improvements

This year saw some significant upheavals to the Yagisanatode website. I have improved the site security and made two runs at improving my Site Speed some moderate success. I’ve also worked on improving the sites layout to make it more browsable.

Site Speed

Site speed still needs a lot of work though, though I have made use of better caching and other plugins to improve the loading speed of the website. My biggest issue is my use of gifs and images. I think visual cues are important for you so you can see what is happening in the process. However, now I am starting to change to using short looped mp4’s instead of gifs and using an expand-box for larger images to reduce load speeds.

Mobile performance is still a pain-point for me and I am really struggling to improve this. Hopefully, with time, I will figure something out.

Layout

My website’s layout has been really bugging me, and while I have made a lot of changes this year, I still have more work to do in 2021.

The first run at the Google Apps Script and Google Sheets pages

The first clue that  I needed to make some improvements was some feedback from family (Thanks Earnie and Kerry), who also happened to be academic administrators. They found it challenging to find a learning progression in my posts in a sort-of course structure.

I responded to this by creating unique Google Apps Script and Google Sheet pages where not only did I have a list of lastest posts but also ordered some posts into courses and categories of ability. I also provided resources for further study.

The Post Grid

Around April I got a great email from a reader who suggested that I move from displaying single-lines of posts with a rather long preview to a grid of posts containing the feature image, title and tags.

Yagisanatode Grid View
Click to Expand!

I included this in the Google Sheets and Google Apps Script pages of the site. The bump in browsing habits suggests that it seemed to have helped user retention which might mean that I am helping you find what you need better.

Updating the Homepage

In December, I finally managed to get around to updating the homepage. Before, there was the old list posts, one under the other with a little bit of a preview of the writeup.

My wife helped me to redesign the home page so that it made browsing more accessible and highlighted recent posts, while also briefly showcasing what the site is about. It is one-thousand times better than what it was, but there is still room for improvement.

My favourite part of the whole process was creating the feature buttons.

Help! I would really love your advice on how to improve the layout of this website. If you have any suggestions, liked or loathed the changes, please share your thoughts in the comments below.

yagisanatode home buttons
Click to expand

Other Business Accomplishments

Becoming a Google Developer Expert

Without a doubt, my proudest moment this year was being accepted as a Google Developer Expert or GDE for Google Workspace. Essentially this is recognition from Google for the work I put into supporting the community.

It also gives me the opportunity to give feedback on Google Workspace products, but the biggest bonus is the community you become a part of. It’s hard to describe how amazing these people are. Their support and dedication is something I greatly admire.

A huge thank you goes out to Sourabh Choraria for recommending me to the Google Dev Advocate team. Martin Hawskey for the first GDE interview and Dev Advocate, Charles Maxam for accepting the very first goat as a GDE.

Yagi logo circle gde 2020 square

YouTube

While I had a tiny YouTube presence to help demonstrate my two Google Workspace add-ons, it wasn’t until June this year that I decided to produce a YouTube series of Google Sheets shorts that walk viewers through most of the tools that Google Sheets provide. The videos cater for the absolute beginner and go into my usual pedantic detail on each topic.

Most of the videos have a table of contents so you can quickly get to what you need and move on with your project.

You will also start to see these videos pop up in some of my long-form posts to help with instructions.

There is everything from styling your Google Sheet, to filtering data and printing and exporting.

There are still  15 more videos in this series. You can check out the series below:

https://www.youtube.com/watch?v=P-MqeQpJexk&list=PLSDEbLgMgqvrBy6u3TAN5LuEZBbOaxZGy

Add-Ons –

Updated Sections to Sheets

This year, I didn’t get around to publishing any more Google Workspace Add-ons. However, I did make some minor changes to Sections to Sheets an Add-on where you can select a column of data and separate it into individual Sheet Tabs categorised by the values in your chosen column.

Don’t worry. It’s totally FREE!

After some feedback from users, I have slightly improved the performance of the processing of the values into separate sheet tabs and taken the restriction off only having single-item categories.

Add-on Stats

Current Stats for my add-ons are:

Spacer: 427 users

Sections to Sheets: 3,194 users

Personal Life

The Move Back to Australia

After 5 years of living in the Middle East, we travelled back to Australia at the end of June into two weeks of hotel quarantine before emerging into a mask covered Sydney.

I had an increasingly worsening back injury. So our plan at the time was to hold out in Australia and get my back sorted out before returning back to work overseas.

Leaving the Job

Unfortunately, the injury was a little worse than expected. Also, COVID wasn’t lightening it’s grip on the world any time soon. On top of that, I was facing exponentially increasing demands from work and the request to return to the Middle East without my wife.

Mrs Yagi and I are joined at the hip so with all this in mind, we tended our resignations and I commenced the handover to my replacement.

I’m sad to see the job go, I created a lot of administrative automation and processes (in Google Workspace 😉) that I can say that I am proud of and save my staff unnecessary work. Nevertheless, the position is in good hands and I am sure that my replacement is doing a tip-top job.

Yagisanatode All The Way!

After making the decision to leave my job, I really don’t think I hesitated in throwing myself at Yagisantatode.com full time. I love developing automation solutions in Google Workspace and teaching people how to reduce their administrative burden.

Running a business and having a side hustle are very different things, however. There is registering a business, doing taxes again, meeting clients, optimising the balance between earning a survivable income and providing great content for readers and a panoply of so much more.

So from October to the end of the year, I spent the rest of my time moving Yagisanatode from a weekend hobby business to a fulltime enterprise. I am learning a lot on the way, making mistakes and getting better (I think). I have some changes and directions I want to test in the coming year to figure out how I am going to best settle into this new life.

I think I can fill some of the gaps in the Google Workspace education world and be unique enough to support, buffer and encourage my fellow Google Workspace educators. One of the things I love about our particular community is the support and encouragement. There is no aggressive competition but rather a genuine desire to enrich the knowledge pool and support one and another.

The Man Behind the Goat

And at last, one of the most fun things I got to do was update my About page and disclose who I was. To be honest. It wasn’t much of a secret if you did a bit of digging (and a few did 😂).

So, yeah… Hi! I’m Scott. I’m from Australia. Here’s me pic, mates:

about-me Yagisanatode

Slightly less goaty than you imagined?

Google Workspace

So much has happened at, I feel, the right time for Google Workspace in 2020. It really took up the slack with supporting educators scrambling to provide online classes with Google Classroom and quickly expanded the capabilities of Google Meet, their video conferencing software, and then provided it for free.

I know, hand on my heart, Google Workspace saved me this year, and for that, I want to thank all the Googlers involved in developing all the Google Workspace tools.

Not only that, Google Workspace had come up with some amazing updates and new products. These are my two favourites for 2020:

  • Google Apps Script – V8 Runtime: The start of the year kicked off with the Apps Script team updating the runtime engine from Rino to V8. Thus opening it up to modern Javascript methods and syntax.
  • Google Apps Script – New IDE: The end of the year equally wrapped up with a bang with the release of the much anticipated new IDE for Apps Script. It is much more modern and userfriendly with a bunch of cool tools.

The Future

With the move from a weekend side-gig to a fulltime job, you can guess that I have a lot of plans ahead for the coming year.

Plans & Goals

Project

I am currently in the process of developing a web project. Is it Google Workspace related? No, not really, but there are a few things I want to achieve with this project:

  • Improve my front-end dev chops.
  • Create some resources for a few pure HTML-CSS-JavaScript tutorials.
  • Get familiar with another Google product, Firebase.
  • And finish up with a website that people would like to engage in and generate a little passive income.

2 Courses

Once I have finished up with the project, I plan on creating two Google Workspace related courses that I think will be very relevant for 2021. The plan is to release the first one by March.

Posts and Videos

Long-form tutorials on Google Apps Scripts, Google Sheets and other Google Workspace related topic will continue with a minimum of 2 a month. I will be testing out a few approaches to make them more accessible and succinct. Please bear with me while I work this out and if you have any feedback, don’t hesitate to let me know.

The Google Sheets: Beginners video series will continue for up to 44 videos. Then I have two more short video series that  I hope to push out throughout the year for you.

Talks

I would really love to do some live talks. Either online or at a meet. It’s just one of those goals that, although terrifying, is important to accomplish.

I’d love to do some talks in rural towns in Australia and introduce them to Google Workspace. I think it would benefit small and medium rural businesses by broadening their reach and saving them a bunch of time and money.

A little more time with loved ones and exploring

I do tend to give everything I’ve got and work myself to the bone to get things done. This year it affected my health. So a major goal for this year is to dedicate moments to reflect, relax and spend time with my best mate, Mrs Yagi.

To work on

Speaking skills

If anything has come out of producing YouTube videos it is that I very much have the stereotypical Aussie male mumble. This year, I need to really improve my speaking skills and strive towards a one-shot recording of my short videos. Wish me luck.

Condensing posts

My long-form posts have started to get carried away. I think while this year, that I really started to delve into more in-depth multi-part problems in Google Apps Script, but I do think I am slipping into a bit of a waffle in my post. This is wasting my time writing the post and your time reading it.

One of the challenges of my writing is to try and write to as broad an audience as possible. Unfortunately, with that comes more explanation. I do encourage you to simply read what you need and skip the rest, but I do think I can go too far in my explanations. Time to trim the fat a little and develop some tools to help make explanations clearer but also more concise.

Meeting more people in the community

The social restrictions that were created by COVID created the perfect hermit environment for me. However, as much as I love and embrace solitude, I know that the feedback loop of more isolation is probably not the best for someone with reclusive tendencies.

I do feel myself losing what social skills I had.

I do enjoy people’s company and more so, learning from them and sharing ideas. Just the thought of the initial engagement terrifies me a little. But it is time to toughen up and bite the bullet and get out there and reach out to people online and, when it’s safe to do so, in person.

I would love to go to some developer meetups and cons around Australia or be apart of any online conventions or meets. Please let me know if there are any around that you think might be interesting. I might be a bit clumsy in my introductions, have no idea what to say or ask and may stand ominously in the corner, but I am interested in you I can guarantee.

Finally, I hope to engage more with my readers in the comments of this site and on Twitter. I think the start of the year I could not reply as much as I wanted to due to work and that made me feel I was letting you down.

Wrapping up

That’s a wrap for 2020. Thanks for following along on this wild ride of a year. I appreciate everyone who has taken the time to read my tutorials. I thank all the people I have engaged within the comments, via email, on Twitter over at the Google Groups Community and the GDEs.

Finally, a massive thank you to my wife for her support, love, advice and hugs.

Have a wonder 2021!

We’ll see you on the other side!

~Yagi

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script (Updated Feb 2022)

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. 

Let’s take a look at what we will be making:

https://youtu.be/JuOYG10vLF4

If you are following along with the code, here is the raw Google Sheet.

Table of Contents – Follow Along – Empty Code

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

Quick Use Guide

Using the Template

The fastest way to get started is to grab a copy of the template file below (File > Make a copy).

Table of Contents – Template

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.

Adding Your Table of Contents toolkit to an existing Sheet.

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:

  1. Make a copy of the Notes tab data. Create a Notes tab and paste it into the exact same location.
  2. Right-click on the Notes tab of the Template Google Sheet. Select Copy to > Existing spreadsheet. Then search for the current Google Sheet you are working in.
copy google sheets tab to existing spreadsheet
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.

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:

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

Google Sheets Table of Contents setup page

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:

google sheet Table of contents counter and title

Or include the Sheet Tab name as a third row.

google sheet Table of contents counter title sheet tab name

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:

additional table of contents items with INDIRECT in Google Sheets
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.

Table of Contents with Extra Columns using INDIRECT

 

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.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We 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.


Code Breakdown

Global Variables

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

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.

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

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)

Loading the sheet on the Table of contents tab

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

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

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

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 (${}).

  1. The SS_IDis the unique spreadsheet ID for the current document.
  2. The sheetID is the unique ID number for the sheet tab.
  3. 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)

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.

Adding the Table of contents to the desired sheet

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)

  1. Row Start
  2. Column Start
  3. Row height
  4. 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.

Sorting the data

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

The getVariables() function takes the spreadsheet as an argument and returns an object, for 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:

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 Sheets Beginners: Trim Whitespace (27)

Trim Whitespace removes all spaces in a text string, leaving just a single space between words.

Search not working? This may be why.

This is a quick and easy tool that you can apply to all of your cells.

https://youtu.be/RooWBx_-iLU

If you enjoy the video and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Google Sheets Beginners: Workbook Edit Version History (26)

Google Sheets keeps a detailed version history of your workbook. You can use this version history to track changes and identify when, where and who made a change. You can make a copy of an old version or revert your current sheet back to an old version.

Find out how to check your workbooks changes history in Google Sheets.

In this tutorial, I’ll cover,

  1. Why you might need to check the version history. 00:05​
  2. How to see the version history 00:18​
  3. What you can see opening version history 00:32​
  4. How to expand to see specific individual history 00:52​
  5. How to see edits individually by colour. 01:01​
  6. Toggle the view on and off. 01:38​
  7. How to Restore a version 01:57​ 
  8. How to Name a version. 02:30​
  9. How to Make a Copy. 03:10​
  10. How to exit version history 03:47
https://www.youtube.com/watch?v=DqNO1XVtrnQ

If you enjoy the video and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Google Sheets Beginners: Cell History (25)

Google Sheets

Did you know you can peek in on the history or each one of your Google Sheets cells?

via GIPHY

Find out how in this short video tutorial:

https://youtu.be/omrlbyiVMb4

If you enjoy the video and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi