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.

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

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


~Yagi

Why your COUNT function might not be working in Google Sheets

It can be frustrating when our Google Sheets functions don’t go the way we expect them to.

In this quick tutorial, we cover two common reasons why your COUNT function might not be running the way you expect it to. One beginner issue and one a little more advanced.

Counting Characters

When you count a range of numbers you use the COUNT function. However, if you are counting all the characters in a range you need to use the COUNTA function.

COUNTA counts all the values in a dataset including:

  • All duplicate values (You can use COUNTUNIQUE for just unique values).
  • Cells containing white space. That is, cells with spaces in them but no text.
  • Cells with a text string of zero. This is usually generated as a result of a formula.

To use COUNTA simply select a range or set of ranges.

=COUNTA(Range)

=COUNTA(C2:C25)

Counting the wrong number of values in a range generated from formulas

Quite often we are trying to template our Google Sheets for reuse in other projects or tasks. Take a look at this example:

Example of empty string in a cell Google Sheets
Click to Expand!

Here in Column K we have a formula that joins the first and last name together from columns I & J if it sees text in column J otherwise it will return an empty string. Here is the formula:

=IF(J2 = “”,“”, JOIN(” “,I2:J2))

This formula is then dragged all the way down the column so that it can be used again for another project.

The problem

The problem is that if we try and use COUNTA to get the values in Column K we will return a wrong number.

Why?

Because COUNTA will also count any cell that contains a string of characters with a length of zero (“”). So this will include and count all the cells in our range that contain our formula where we don’t want to display a value.

So how do we fix this?

The solution

Option 1

The most logical option is to try and use another column to reference your count. Unfortunately, this isn’t always possible.

Option 2

If your formula returns an error on an empty cell and you want to hide it, you could use the IFERROR function instead of the IF("","",... option to create an empty cell.

Here you wrap your formula inside the IFERROR function. You then have an optional argument to add what to do if there is an error or leave it blank if you don’t want anything to appear in the cell.

=IFERROR( your formula)

When you run a COUNTA on this column now it will return the correct count.

Option 3

If you can’t do either of these options then you could create a formula that checks the length of each string of data in each cell and if that cell length is greater than zero, count the cell.

First, you can get the length of a cell with the LEN function that takes a cell value. In our example, we will use the first cell item in our full name column K.

=LEN(K2)

We need to be able to use this LEN value on the selected range that we want to count. Here, we can use the ARRAYFORMULA function. This will allow us to put in a range of values in our LEN formula and get the length of each one. The result will be an array containing the length of values for each cell in the range.

=ARRAYFORMULA(LEN(K2:K))

Finally, we can use COUNTIF to count all the values in our new array of lengths that are greater than zero.

=COUNTIF(ARRAYFORMULA(LEN(K2:K)),“>1”)

Conclusion

Check out the video below for more information.

Do you have a clever way of dealing with this problem? I would love to hear about it in the comments below.

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

New course 🎉🎉🎉 Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off 😃

It’s the big day!

I’m very excited to introduce you to my new course:

Google Sheets: Learn the Essentials with Three Detailed Projects

Whether you are looking to skill up, make your work-life easier or impress your boss then this course will take you from Google Sheets beginner to producing corporate-level Google Sheets with three detailed projects.

And because this is the opening week, I am offering a whopping 60% off the course for the next 7 days only.

Just use the Coupon Code…

NEWCOURSEDISCOUNT

…on the checkout page and you can start the course today.

CHECK OUT THE COURSE!

Google Sheets Project Main Page
Click to Expand!

First 5 Chapters Free

I have also opened up the first 5 Chapters of the course for you to try completely FREE.

Go on and give it a try now!

CLICK HERE!

Share the love

If you are already a Google Sheets legend, you know how important it is to learn how to master spreadsheets. If you know someone transitioning industries, just starting out or simply needing to boost their skillset please share this post.

This is a whopper of a discount and not one to miss.

The offer expires on Sunday the 9th May 2021.

DON’T MISS OUT!

Why I built the course

This has been a project I wanted to build for a long time. The best way to learn something is by getting hands-on and building a project.  That’s how I know I learn best.

I wanted to build a Google Sheets beginners to pro course that I wanted when I first started in the world of Google Sheets. Something with real-life examples and a set of projects that I can build and maybe even modify and reuse in the future. Something that not only gives me the how-toos but the why I should be building and designing my spreadsheets in a certain way.

Go on, check out the link to find out more about the course and try out the first 5 chapters on me. I know you are going to get a lot out of it.

FIND OUT MORE!

This offer expires on Sunday 9 May 2021.

~Yagi 🐐