How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script

So you are a citizen Google Apps Script developer and you’ve decided to make yourself a mail-merge-type project where you want to create new documents from a template. You have discovered the simplicity of the replaceText() method:

Now you want to take it to the next level and replace the text with a hyperlink containing the text and the URL. You might be scratching your head wondering where the replaceTextWithLink() method is or why you can’t simply chain the setLinkUrl() method without making a hyperlink out of the entire body of the document.

via GIPHY

What to do?

In this tutorial, I’ll cover how to find and replace text in a Google Doc with a hyperlink with Google Apps Script under three common conditions:

Danger!!! Word repetition warning ahead!

  1. Find text and replace it with new text and a link where the text is the only text in a document.
  2. Find text within a paragraph and replace it with new text and a link.
  3. Find text and replace it with a list of hyperlinks.

I encourage you to play along. Here is a link to the Google Doc without the code attached:

Test Google Doc.  

Just go to File > Make a copy to get your own copy of the Google Doc. Then Tools > Script Editor.

While you are testing, you can just use undo (ctrl + z) to return the text to its original state.

Let’s dive into the three examples.

1. Find a single item of text as a completed paragraph in a Google Doc and replace it with new text and a link

In our first example, we have a paragraph where we have just the text that we want to replace. Take a look at the image:

Find a single item of text as a completed paragraph in a Google Doc and replace it with new text and a link Apps Script v2

Here’s the code:

Once we have grabbed our body element on line 12, we set up our chain of methods to produce our hyperlink.

First, we use the findText() method to grab the text we want to find in the body. This method takes our textToFind variable as an argument and returns a range element indicating the position of the searched text. Line 14

Next, we get the element that the found range of text is in using the getElement() method. This will be a text element. Line 15

Note! You can find the type of text element by using this approach:

We then call the asText() method to get the current element as … well … um … text so that we can edit it. This allows us to perform rich text editing of the element. Line 16

Now we can set the text we want to use to replace the current text with setText(), inputting our text variable. Line 17

Finally, we add our link using setLinkUrl(). This will take our url variable as its argument. Line 18

Note that this approach will replace all the text associated with the element removing your reference search text and any other text. If you want to replace the selected text in a paragraph and add a link to it, check out the next example.

2. Find text within a paragraph and replace it and add a link.

In this example, we only want to replace the target text (and add a link) that resides inside a paragraph. Here is our example:

Find text within a paragraph and replace it with new text and a link DocApp Google Apps ScriptWe need to do three things here.

  1. We need to find the text element from which template text we want to replace resides.
  2. Get the offset where the found text starts in the overall text.
  3. Replace the text with our new text and link.

Check out the code:

Again we start off by grabbing our body on line 12. We won’t be able to chain our methods too much here because we need to get some extra information out of them. So instead we set foundText to the result of our findText() method call. Line 14

Our next task is to get the start and end locations of the text within the greater text. We can get the start location (or offset) by using the getStartOffset() method. This essentially gets how many characters in our text starts on. Line 17

We then need the location where our text will end. Now, this is not the end location of the current text. It is the location of the text that we are going to use to replace it. To calculate this, we add the startText to the length of our replacement text. We need to subtract one because the startText value is the beginning location of our text and not the character location previous. Line 18

Now we can get cracking and replace our text.

First, we grab the element (text) of our foundText. Line 21

We can then chain our next steps by setting the element to text. Line 24

This time around we can use the beloved replaceText() method to find the text again only searching inside the text element and replacing it with our desired text. Line 25

From here we can now set our link. This time around we will take advantage of setLinkUrl() method’s alternate parameter arrangement which takes:

  1. Start text index – startText
  2. End text index – endText
  3. the URL – url

This allows us to set the link at a specific location in the text.

But what if you want to add multiple hyperlinks to a list, Yagi?

3. Find text and replace it with a list of hyperlinks

In this final example, we want to add a list of links based on a text reference in the document.

Take a look at the document.

Find and replace text with list of hyperlinks DocApp Google Apps ScriptHere is the code:

The Data Source

In this example, we have an array of objects containing the title and URL for each of the links we want to add within our links variable. Lines 6 – 19

Just like in the previous two examples, you could get your data from many other sources. This is just an easy example of data to follow.

Get the paragraph element containing the text.

Before I explain this step, it is important to know that our sample text resides inside a text element that resides inside a paragraph element which will probably reside inside the body element.

Our ultimate goal is to remove the selected text and replace it with a list. If we just remove the text element, we will still be left with the paragraph, which will look like a carriage return (do kids still use that term?). So we will want to remove that whole paragraph.

This means that our first step is to get the paragraph element that contains our text.

We do this first by finding the text (Line 27). We grab the text element (Line 28). This allows us to get the element’s parent with the getParent() method. This is stored in our element variable.

Get the index of the paragraph containing the text

Here on line 32, we grab the index location of our template text. We head back to the body for this one and use the getChildIndex() on our paragraph element of the selected text. This method returns an index of the location in the body element.

The index will allow us to add our list of links in a moment.

Removed the paragraph element from the text

Now that we have the index location of where we need to add our list of links, we can safely remove our reference text.

To do this, we grab the paragraph element and use the removeFromParent() method. Line 35

Add the list of hyperlinks

Our final step is to push our list into our Google Doc at our new index location.

The text will be inserted into the new index location. This means that if we looped through our text and inserted it at the same index each time, the links will appear in the opposite order that we originally had them in our array.

The first step then is to get a reversed copy of the array before we start our loop (We get a copy because we don’t want to change the original array). This is achieved with the Javascript slice() method without any parameters, which collects the whole array. Then we use the reverse() Javascript method on it to reverse the order of the array. Now we have a copy of the array in reverse order, but we haven’t change the original array. Line 37

Now we can run our foreach() loop to iterate through each array item.

Inside each iteration of our loop, we want to use the insertListItem() method  to add our list item to the index location of our Google Doc body (Line 39). This method takes two arguments:

  1. The index location – index
  2. The text – link.text

The method then returns the newly created list item element.

Here we can then add our link using setLinkUrl().

Before we finish with our list item we can set the type of list we want by using the setGlyphType() method. The method takes a ‘list character type’ which is drawn from the Glyph Type enumerator. For our example, we set our list to be numbered.

Give it a crack yourself!

Conclusion

So that’s it. Three different scenarios for you to insert hyperlinks based on a text key in Google Docs with Google Apps Script. Of course, there is more than one way to do things. I would love to hear your approach to these problems in the comments below.

I’d also love to hear how you used these scripts in your own project. It is always inspirational.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website. 

 

~Yagi

How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script

A few days before publishing this post, I put a call out for some Beta testers to get free access to my new course Google Sheet: Learn the Essentials through 3 Detailed Projects. 

Yeah, yeah, yeah, I am super excited about finishing this course. It’s been a labour of love for two and a half months. No wonder I am banging on about it.

Anyway back to the tutorial. I needed a way to provide coupon codes for free access to the course in exchange for some feedback and much-needed tutorials.

To do this, I created a Google Form.  This contained some details and expectations, and then some details about the submitter and a consent checkbox at the end. If the submitter consented and hit submit I wanted them to get an email back with the coupon code.

In this tutorial, we will go through how to create a custom auto email response containing:

  • The submitter’s name in the greeting.
  • Your email message.
  • Your primary signature block from your Gmail account.

To me, this seems like a pretty standard task that anyone might be considering doing. So, I thought I would share the process and the code.

Let’s get cracking!

The Google Form

Here we will go over the highlights and setup as they pertain to the context of the Google Apps Script code.

The form starts off with some information about beta testing the course.

Google Forms beta testing instructions
Click to Expand!

Next, we have the input fields.

  1. Email: This is an atypical field. You create this field by going to the Settings cog and select  Collect email addresses. Here, I also sent the submitter a copy of their responses and limited the responses from each user to one. This made the user use their Gmail account, which added some extra integrity to the form and… well… the user will need a Gmail account to complete the course anyway. This will become important in our code later.
Google Forms settings collect email limit to 1 for beta testing course
Click to Expand
  1. Name: The name of the Beta tester. I’ll grab the first name from this and also record it as part of their testimonial that they consented to.
  2. Occupation (optional): This apparently helps with the credibility of the testimonial and it is also interesting to see the occupation of the people taking the course. Testers can opt-out of this one.
  3. Company (optional): This one was a fizzer. I understand why, you really don’t want to be telling people where you work online. This item was added as part of the testimonials that I researched in preparation for this. I have no idea how course creators got these, which makes me one part, a little suspicious and, one part, blown away by how these folk got students to share who they worked for. In hindsight, I would not add this item. If I personally wouldn’t give out these details, I shouldn’t expect others to.
  4. Consent: This is a mandatory field that the submitter needs to check to confirm their expectation in exchange for the free course. If they don’t check the box, they can’t submit the form and get the coupon code.

The code

We want to get our email auto-responder code to run when the Google Form is submitted to us.  To do this we need to connect our script to the form by opening the Google Apps Script editor up in the Google Form.

You can do this by clicking the vertical ellipsis () in the top-right of the Google Form editor and selecting Script editor.

Google Form access to Google Apps Script Editor
Click to expand!

Code.gs

You can copy and paste this now into your project and rename the project to whatever you are working on. I usually name my project the same as my Form or Sheet if it is directly related.

You can update the subject and htmlBody values to your own data here. You can add HTML to your htmlBody within backticks (your html) Lines 27 & 28

Of course, the coupon code is a big faker-Rooney.  🙄

The code grabs the current users email and name and then emails the respondent with a coupon code after they hit submit on the Google Form.

If you run the code right now you will face some errors. First, we need to set up the trigger to run this code when the user submits it. Also, we need to get authorisation to use scopes, or permissions to give Google Apps Script certain access to your data to run the code and add in an API to access our Gmail signature block.

Add the Gmail API

First up we need to add an advanced API to get access to be able to display the signature block from our Gmail account. You know, to make it look professional.

To add this API in the Google Apps Script editor:

  1. Ensure you are in the Editor.
  2. Select Services +
  3. Scroll down and select Gmail.
  4. Select Add
Add the Gmail API to a Google Apps Script Project
Click to Expand!

Note! As of writing this, the Gmail API only uses your primary signature block and can’t access any other signatures you might have. 

Make sure the Scopes have been Authenticated

You may find, even if you have set up the triggers, that the script doesn’t run because it is missing the permissions you need to run the script. To find out what happened you can check out the execution log in the Google Apps Script IDE  sidebar -4th one down.

Google Apps Script IDE execution log
Click to Expand!

You can then check the errors of your execution log to see what happened.

To fix this ahead of time we can modify the onFormSubmit(e) function a little to run a dummy version of the script.

Before we start, you need to have a least one response in your Google Form. Go ahead now and add in a dummy form submission. You can always delete it later.

Go back into your editor create a new Google Apps Script file and call it Test.gs.

Copy and paste in this code:

Go ahead and run the code. You should get a warning popup for authentication. This will happen just one time and it will be fine for you and anyone who enters the form in future.

Once you have accepted all the scopes your code should run and you will get an email. If you don’t, check the execution log for any errors.

You can also check to see your script scopes by going to the Overview of your Google Apps Script IDE.

Google Apps Script IDE Scopes overview
Click to Expand!

Now we can go back to our Code.gs file and add our trigger to send the email on submission.

Assign the Trigger on Submit

Next, we need to assign a trigger to run our onFormSubmit(e) function in our Code.gs file.

To do this, navigate to the Code.gs file and then:

    1. In the sidebar of the IDE, select the Triggers button indicated by a clock.
    2. Select Add Trigger at the bottom right of the page.
    3. A popup window will appear. Under Choose which function to run select onFormSubmit.
    4. Select event type: On form Submit.
    5. Click Save.

Google Apps Script trigger on form submitClick to Expand!

Run a practice Form Submission

Go ahead and run a dummy form submission again and you should now get an auto-response email with the email you created.

 

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Code breakdown

Pfaawhh!!! 

If you made it to this bit you really want to learn about the nuts and bolts of the script. That’s awesome.

In this, code breakdown, I will go over some of the highlights of the code to help you understand what is going on.

Get the current submitters data

You will notice the single ‘e‘ as a parameter for the onFormSubmit function. This indicates an event object. When a trigger, like our on form submit trigger, is assigned to a function, you can use the event object to get the data that will be drawn from triggering that event. For us, we will get the response from the person who submitted the trigger.

To do this we use the response method on our event object and assign it to our responses variable. Line 3

This will give us access to the same list of methods as you would when you run the Test.gs testAndAuthScopes function’s response variable on the first response in the form

const responses = formResponses[0];

You can find out more options about the formResponse class here.

Here we can access things like the date and time the response was submitted, the response, the response item, the respondent’s email if that was enabled in the form.

For us, we will grab the current respondent’s email first. Getting to the email is a little different than accessing other responses. If you remember when you set up your form, you can force users to add their email to the form in the settings menu.

The email here will always be at the top of the form. To access it, you need to use the getRespondentEmail() function. Line 4

Next, you can access any form item response in your form by calling the getItemResponses() method that will return an array of responses in your Google Form. You can then count from zero down your form to find the number of the item that you want to draw the response from and grab it with an array call, [n].

For us, we need to get the first item after the email, which is the name. This is the zeroeth item in the list. Line 5

Alternatively, if you have already grabbed the item’s ID, then you could use, getResponseForItem(item).

You would have probably have noticed the trim() method on the end of each email. This JavaScript method allows us to simply take any whitespace that the respondent accidentally added to their response or email – I’m guilty of accidental extra spaces too. 

Get the first name of the respondent

We want to personalise our email response and use the respondent’s name in the email. These days, it’s generally preferable to use a casual first name approach.

We don’t know if the respondent entered their first name or full name. We know with a certain degree of confidence that they will also not add in their title in this input as well. With this in mind, we can extract all the letters up to the first name and safely assume that this will be their first name (No one likes entering a first and last name input when they can do it in one line).

Here’s our code:

Here we create our firstName variable. The first thing we need to check is if the user added just a first name or a first and last name.  This is done with a JavaScript ternary or conditional operator that is basically a single line if statement.

In our condition inside the braces, we use the indexOf method on our name variable. This method takes one variable, the searchable value. In our case, this is an empty space, (" "). If a space is found it will return the index or numerical location of the space. If no space exists, it will return -1.

We check to see if there is no space, which will equal -1. If this is the case, then we just report the name variable, because the user only entered their first name on the form.

However, if there is a space, then we want to create a new string with all the characters up to the first space. We do this by using JavaScript’s substring method. This method takes a start and end index inside the string. For us, our start is the first value which is zero. To get the end index we can use indexOf() again on name to get the location of the space.

Use our signature block on the email

We want to look professional here and add our Gmail signature block to the end of our email like we normally do when we send an email. You would think this would be a pretty simple process, but actually, it is a little challenging.

To access the signature, we have to use the Gmail API Advanced Service we installed earlier.

Here’s the code:

Google Advanced APIs are written a little differently than what is shown in the doc’s when we use them in Google Apps Script. This can make using them a little confusing to get your head around. Let’s take a look at the Gmail API documentation for this method:

GET https://gmail.googleapis.com/gmail/v1/users/{userId}/settings/sendAs.list

In Google Apps Script, this boils down to:

Gmail.Users.Settings.SendAs.list({userId}).sendAs

We replace the {userId} hint with the special "me" input to reference your own account.

Running this on its own will give you a list of all of your emails and their data that are assigned to your account. kind of like this:

 

For me, I have a number of domain accounts along with my primary Gmail account assigned. As you can see in the sample data above, you can see a signature object in the first array. This object contains all the HTML we need to generate our signature block at the end of the email.

We can access this by using the JavaScript filter method. This method creates a new array based on parameters you intend to use to filter. For us, we want to filter down to just keep our default Gmail account. Line 7 

This will give our list of objects and we can then select the signature object (Line 7) which we store in our signature variable (Line 4).

Note, that as of writing this email, there is no way to access alternate emails.

We can then use this at the end of our email message.

Emailing the respondent

Finally, we can email the respondent with our custom message. We do this with the MailApp class and call the sendEmail() method. This method can take a number of possible variations of parameters, but I like to use the object parameter because it gives me more flexibility.

With this approach, you can modify a number of different objects. For our project we will use the following:

  • to: This is the email of the respondent that we will send our message to.
  • subject: Just like in a normal email, you can add your subject text here.
  • htmlBody: You can add your HTML for your text here. You can always use one of the numerous email template builders to do this without hard coding HTML for most of your task. However, you will need to wrap your code in backticks and then at the very bottom add in the signature paragraph.

Here is a link to more emails on MailApp for you to get familiar with it:

Conclusion

In this tutorial, we looked at creating a custom email responder that is triggered when a Google Form is sent. We looked at how to add triggers to run our code when the form is submitted. Then we made sure all of our permissions and scopes were added so that our code would run. We also had to add the Gmail API advanced service to get our signature block.

So where to next?

You might want to run different custom email responses based on the respondent’s response. Here you could look at the user’s response and then perhaps use an if statement or switch to send the code to a specific set of htmlBody that relates to that particular response.

What do you think you would use this for? I would love to hear in the comments below.

If you’ve liked this tutorial and want to get regular updates on what I am working on next, you can subscribe down below this post.

Happy coding!

~Yagi

Create custom prefilled Google Forms links in custom emails with Google Apps Script

Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form.

Great, that’s cool. But that wasn’t what got me excited. They had exposed the raw URL link to the form in the email and I noticed that there were some references to my name, my support number and a few other things in the URL query parameters.

I clicked the link to the Google Form and, as expected, the Google Form appeared with these values prefilled into my form.

We this is a pretty cool convenience, I thought. How did they get all the query paths to each form item?

A couple of days passed and I had a chance to figure it all out.

In this tutorial, I’ll walk you through accessing the prefill tool in Google Forms. Then, if you are keen on doing some coding, we’ll create a little custom feedback form for unique users that we will deliver via email.

Let’s play!

Google Forms prefill tool

Accessing the Google Forms prefill tool

First, take a look at my example Google Form:

Go ahead and type forms.new in your Chrome browser address bar and create a few form items so you can play along.

Once you are done, got to the top right next to your avatar and you will see a vertical ellipsis. Give it a good old click.

A popup window will appear. Four items down and you will see the menu item, Get a pre-filled link. Go on, you know you want to click it. I won’t judge.

Google Forms menu buttons ot Get pre-filled link
Click to Expand!

A new window will appear in your browser with a sample of your form. Go ahead and fill out any part of the form that you want to have prefilled.

We’ll fill out the first three items in our form. Here, take a look:

Google Forms prefill screen
Click to Expand!

As you can see above I have added my name (Yagi the Goat), a ticket number (6047) and issue (Login – Passwords).

You might have noticed down the bottom left of the screen a grey box with the prompt, Prefill responses, and then ‘Get link’.

Go ahead and scroll down to the bottom of your form and click the Get link button (1).

Google Forms prefill get link & copy link
Click to Expand!

Then click the COPY LINK button in the grey bar (2).

Paste your link in a new browser tab and hit enter to check that the pre-fill is what you wanted.

If you are happy with the prefill results, then paste the pre-fill link somewhere safe for you to use later.

You should end up with a URL a little like this:

https://docs.google.com/forms/d/e/1FAIpQLSd4QDc4MRkoERExe9KeMLww9P7VNRHFOfpBLwX_Mo-g5TJ0Vw/viewform?usp=pp_url&entry.1046214884=Yagi+the+Goat&entry.2009896212=6047&entry.415477766=Login+-+Passwords

You should be able to see some of the pre-fill items in your URL that you added earlier. We’ll go onto this later if you are following along to the Google Apps Script portion of this tutorial.

 

Why would you use a pre-fill in a Google Form?

At first, I was a little lost at the usefulness of using a standard static pre-fill for your Google Form. Surely not all people on your form will need to choose the same thing. I mean, you may as well leave it out of the form, right.

However, after a bit of noggin scratching, I thought that maybe you could use a static prefill like this for a standard response to help most users skip filling in unnecessary parts of the form while still making it flexible enough for the user to change the form if they need to.

When it does become an awesome tool is when you can use the URL generated and update fields to customise it for each user.

In the next part of this tutorial, we will do just that with the help of some Google Apps Script and then add our form to a custom email.

 

Create a custom prefilled form link and email it

In this portion of the tutorial, we are going to create a custom pre-filled form link by altering our copied pre-filled form link and then send a custom email to a user with their name and their own unique Google Form link.

The example

Let’s assume we have our very own tech support team. After we complete each ticket, our team are eager (yeah right!) to find out how well they performed in their support of the client.

The team stores each completed ticket details in a Google Sheet like below:

Support ticket Google Sheets for Google Forms prefill V2
Click to Expand!

Looking at the image of the Google Sheet above, we only want to send an email to those clients whose checkbox in column I is unchecked – indicating that they haven’t received and email yet.

We then want to send an email to our users with a message and a link to our unique pre-filled Google form.

For example, our last user, Andrew Bynum, would get an email like this:

Custom feedback email with link to prefilled Google Form
Click to Expand!

Then when Andrew clicked on the form link he would be navigated to his own pre-filled Google Form with the first 3 items filled in like below :

Custom Google Form pre-fill for specific user
Click to Expand!

The anatomy of the pre-fill URL bar

That was generated with this bespoke URL:

https://docs.google.com/forms/d/e/1FAIpQLSd4QDc4MRkoERExe9KeMLww9P7VNRHFOfpBLwX_Mo-g5TJ0Vw/viewform?entry.1046214884=Andrew+Bynum&entry.2009896212=11007&entry.415477766=Billing&gxids=7628

If you look carefully, you will see some of the input we put in our form when we were using the Google Forms pre-fill tool.

https://docs.google.com/forms/d/e/1FAIpQLSd4QDc4MRkoERExe9KeMLww9P7VNRHFOfpBLwX_Mo-g5TJ0Vw/

This portion of the URL directs the user to the Google Form, with the ID of the form in blue above between the last two forward slashes.

viewform?entry.1046214884=Andrew+Bynum&entry.2009896212=11007&entry.415477766=Billing&gxids=7628

Next, you can see 3 occurrences of entry followed by a number (in red) then equals to the pre-fill input we added (in green). Note that if a prefill item has a space, it is replaced with a plus (+) symbol.

We start to write out our code we can replace these pre-filled inputs with a variable that can update for each user we send our form to.

Time to check out the code to see how we do this.

The Code

This is a pretty basic procedural code so we will simply pack it into one function. No need to go crazy here:

Main variables

Variables to update

We need to first set up some main variables that we will reference in our project. First, we will get access to the Google Sheet that contains the ticket data for our clients – the Tickets file we mentioned earlier – using the SpreadsheetApp class.

We then call the openById() method which takes one argument, the file id. This can be found in the URL and should look similar to the one in the example. This is then put in the SS variable. Line 10

Next, we need to get to the sheet tab our data is in. For us, this is Ticket. So we reference this sheet tab name with our getSheetByName() method and store it in our SHEET variable. Line 11

We will want to indicate what row our user data starts because we don’t want to include our headers. Here we set our ROW_START variable to 2 because our first user is in row 2.

Getting data range and values

Our next task is to get the range of all the data we need to add our pre-fill values, emails and client name data along with our checkbox to see if we need to email that user. We may as well select all the columns and grab the last row.

To grab the full range of our data we use the getRange() method. Which can take many types of arguments, but for us, we want to give it 4 number values:

  • Row start
  • Column start
  • Number of rows
  • Number of columns

We’ll add our ROW_START in our…um…row start argument. Our column start in the first column. Then we grab the last row, which will likely change often by using the getLastRow(). This will update as new entries come in.  We then subtract this by the row start and add 1 to exclude the header. Line 13

To then get the values of the range we use our new range variable and call the getValues() method. This will produce a 2d array of all the data on our sheet. Line 14

Keeping track of emails sent.

Our checkboxes in column keeps track of who we have sent our feedback form to. We will update the checkbox with a tick if we have sent the form using some code.

Before we jump into our loop through each ticket we need to keep track of where the boxes are unticked and where the row of unticked boxes finish. We do this by setting up an object to store untick start and end rows that we will preset as false and update as we loop through the rows.

If you wanted to speed things up in a bigger Google Sheet you could store the start row in a Properties Service like in the post below, but that’s beyond the scope of this tutorial.

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Looping through our data and setting up our column variables

Now that we have the values of our Google Sheet in our VALUES variable, we want to loop through the 2d array and set some variables to each column of data we want to use in our script. We use the forEach method for our loop here with the first argument being the array containing all the cell data in the row and the second one, the row index:

Next, we need to assign some variables to each relevant row item that we will use in either our email or our pre-fill. To do this we will use some destructuring to cleanly build our variables:


The columns in our sheet contain the following:

  • Date
  • Name
  • Email
  • Ticket #
  • Issue
  • Details
  • Response
  • Status
  • Feedback Sent

The bolded items are the only columns we want to use. In our destructured variable assignment, we create an array of all the variables we want to use and put a blank comma space between the variables we don’t want to use.

Creating the first name variable

It’s kinda weird these days to address someone by their first and last name in an email greeting. Some people even find it a little insincere or annoying. So we might want to just stick to the more popular informal first name.

To get our first name, or fname, we use the Javascript substring method to just get the first part of our string up to just before the first space. The substring method takes 2 arguments. The start position and end position. We find out the end position by using the indexof method that searches a string of text and if it finds the corresponding value, it will report the position of the value, but if the value does not exist it will report -1.

The resulting code would look like this:

Now, we are not certain if our users have put in a second name, or even have one for that matter. So if we just created our fname varaiable with this code we would get a weird error if we had a single name.

To fix that, we are going to use a ternary operator that we will first use to check if the name variable is a single name or not. Here again, we use the indexof method to check if there is a positive number. If so we will use the code above to generate our name. Otherwise, we will use just the name. Check out the full line of code:

Swapping spaces between words for “+”

When we create our custom pre-fills we noticed that spaces were repaced with plus symbols “+” in the URL. We want to keep the full name and the issues in our prefill and we know that both items potentially contain spaces in the text. To change the spaces to plus symbols, we will use the Javascript replace method with the help of a little bit of regular expressions.

The replace method takes two arguments, the item to search for and the item you want to replace it with. Because the item we are searching for is a space it’s good practice to use a regular expression rather that ” ” to be certain you catch it. Our regular expression looks like this:

The \s is the symbol for spaces. The two / mean anything between. The g is the symbol for global. So essentially this expression is saying that is is looking for any occurrence of a space all over (globally) in the string.

We’ll update the two original variables (which will upset the functional programming purists, but hey, it’s only a small bit of code) so our two lines will look like this:

Sending off our email

In the next section of our function (Lines 33-46), we check to see if we need to send an email, and if we do, we send it away with our pre-filled link to our form.

First, we use an if statement to check if the current feedback cell is false, then we are good to send the email.

Sendemail()

Next, we invoke the GmailApp Google Apps Script class and then use the sendEmail method. The sendEmail() method can take a few different argument structures, but I like to use the full method approach that takes the following:

  1. Recipient: The email of the person you are sending your email to.
  2. Subject: What your email is about.
  3. Body: We’ll put in a placeholder here, “see HTML body” because we want to use HTML to make our email look fancy.
  4. Options: The are a lot of options you can put inside the curly braces {} of this object, but for us, we just want to add htmlBody. Which allows us to add HTML to our email.

Let’s have a look at the sendEmail() method so far:

The HMTL Email

We will use template literals to create our string of HTML text. Template literals start and end with backticks (`). If you want to add a variable into the string all you need to do is add ${your variable}. The other bonus is that you can happily put your string on new lines of your code without having to close and concatenate your string each time.

Let’s take a look at our htmlBody value:

You can see that it all looks like pretty standard HTML text separated by paragraph tags <p> and breaks </br>. We’ve added in the first name (fname) in the greeting at the start and then created a link to our pre-filled form that we have customised with our variables.

Here is what each entry looks like:

  • entry.1046214884=${name}
  • entry.2009896212=${ticket}
  • entry.415477766=${issue}

Once this part is complete the emails are all sent off. Time to update our Google Sheet to show we have done this job.

Updating the checkboxes

The checkbox process occurs at the end in two stages here. First as we are iterating through our forEach loop we need to keep a record of the first unchecked box and the last one.

Remember earlier that we had set up the variable, uncheckedBoxRange, before we started the loop. Now we want to check if this is the first time we have found an unchecked box. If it is we want to update uncheckedBoxRange.start with the current index plus the ROW_START value to get the row number and also update the uncheckedBoxRange.end.

If we have already found the first occurrence of an unchecked box, we skip updating the start value and just update the end value.

Outside our loop, we then need to use our uncheckedBoxRange object values to update our checkbox columns in our Google Sheet.

First, we need to get the total number of emails we sent. We do this by subtracting the uncheckedBoxRange.end from the start and add 1.

We then want to create a string of true values equal to the uncheckedCount. This can be done fairly cleanly by the new Array constructor that can take an argument to generate amount of values in an array.

Next, we use the fill method to identify what we want to fill each array value with. For us, this is a child array with the value true in each. Why a new array inside our main array? Because each row of a sheet is its own array.

We then use the Google Apps Script getRange() method again to select our range referencing our start row of unchecked boxes, column nine, the total number of unchecked boxes. We don’t have any other columns to worry about so we don’t need a fourth argument.

Finally, we use the setValues() method inserting our newly created array of true (or ticks) into our checkboxes.

Conclusion

To run your code from the Google Apps Script IDE simply click on run and follow the prompts:

Alternatively, you could set a time trigger to run your code daily or weekly or when the Google Sheet changes, or have a button or menu item that you click in your sheet to run the code.

Here are a few tutorials on the topic:

So what do you think? Would you use pre-fill in your own project? I would love to hear how you applied custom pre-fill. It’s always interesting to see what creative things people develop.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script

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:

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 (Tools > Script editor).

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.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

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:

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