Apps Script - Yagisanatode

Find and Select or Format rows in Google Sheets with Apps Script

In this tutorial, we create 3 Google Apps Script functions that are used to:

Find and select the row containing the first or last value in a Google Sheets sheet tab.
Find and select all rows containing the search value.
Format all rows containing the search text.

You can grab a copy of the starter Google Sheet to play along here:

This tutorial accompanies the YouTube video of the same name:

https://youtu.be/Y7rnQT6qYiA

All functions are built to be run from another function, making it easy for you to integrate them into your own projects.

For our example, we will use the runies function for this purpose.

function runsies() {
  const text = "koala";
  const sheetName = "Animalz!";

  // selectFirstRowWithVal(text, sheetName);


  // selectAllRowsWithVal(text, sheetName);


  const sheetName1 = "Animalz! Format"
  // formatAllRowsWithVal(text, sheetName1)


};

function runsies() {

const text = "koala";

const sheetName = "Animalz!";

// selectFirstRowWithVal(text, sheetName);

// selectAllRowsWithVal(text, sheetName);

const sheetName1 = "Animalz! Format"

// formatAllRowsWithVal(text, sheetName1)

};

All three of the examples below take two arguments as parameters:

text – the search text.
sheetName – the Name of the selected sheet tab.

These functions rely on the Apps Script Spreadsheet App Text finder class to search for the target text within the range.

All examples search a selected sheet tab. However, you can easily modify the script to search a specific range or the entire Google Sheets workbook. You can learn more about how to do this here:

Find All Values in Google Sheets with Apps Script

Table of Contents

Find and activate the first or last row in a Google Sheet with Apps Script

/**
 * Selects the row that contains the first found value.
 * Single sheet
 * @param {string} text - the text to find.
 * @param {string} sheetName - the target sheet.
 */
function selectFirstRowWithVal(text, sheetName) {
  console.log(text, sheetName)

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  const textFinder = sheet.createTextFinder(text);
  const cell = textFinder.findNext();
  // const cell = textFinder.findPrevious();

  const row = cell.getRow();

  sheet
    .getRange(`A${row}:${row}`)
    .activate();

};

/**

* Selects the row that contains the first found value.

* Single sheet

* @param {string} text - the text to find.

* @param {string} sheetName - the target sheet.

function selectFirstRowWithVal(text, sheetName) {

console.log(text, sheetName)

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const textFinder = sheet.createTextFinder(text);

const cell = textFinder.findNext();

// const cell = textFinder.findPrevious();

const row = cell.getRow();

sheet

.getRange(`A${row}:${row}`)

.activate();

};

Lines 10-12 – In this scenario, we activate the first (findNext()) or last (findPrevious())found row based on a search item that can be found anywhere in the row. We then store this cell range in the cell variable.

Line 17 – From the cell variable, we can select the Spreadsheet App Range getRow() method to get the row number of the cell.

Line 19-21 – Next, we can activate the range of the selected row using the activate method.

We set the entire range by using A1-notation here within a template literal (that is a string within backticks ()). In Google Sheets, we can set the full width of a sheet by leaving the last column value blank in the notation, for example:


A1:1 or even 1:1.
Incidentally, you can also select a portion of the width by including an end letter in the range, for example:

A1:K1 or in the script .getRange(A${row}:K${row}`)

You can learn more about selecting the first or last found values in this tutorial:

Find first, last or nth value in a Google Sheets range with Apps Script

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Find and activate first row based on a search value in Google Sheets — Example finds the first instance of ‘Koala’ in the range and selects the row.

Find and activate all rows in a Google Sheet with Apps Script

/**
 * Select all rows that contains the selected value.
 * Single sheet
 * @param {string} text - the text to find.
 * @param {string} sheetName - the target sheet.
 */
function selectAllRowsWithVal(text, sheetName) {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  const textFinder = sheet.createTextFinder(text);
  const allOccurrences = textFinder.findAll();

  const ranges = allOccurrences
    .map(item => `A${item.getRow()}:${item.getRow()}`);

  sheet.getRangeList(ranges).activate();

};

/**

* Select all rows that contains the selected value.

* Single sheet

* @param {string} text - the text to find.

* @param {string} sheetName - the target sheet.

function selectAllRowsWithVal(text, sheetName) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const textFinder = sheet.createTextFinder(text);

const allOccurrences = textFinder.findAll();

const ranges = allOccurrences

.map(item => `A${item.getRow()}:${item.getRow()}`);

sheet.getRangeList(ranges).activate();

};

In this example, we select all of the rows containing the target search value.

Line 13 – First, we modify the Text Finder variable to the findAll method and rename the variable. This will return an array of all the cells where the values are found as an array.

Lines 15-16 – Now, we can map over each item in the array creating a new array of ranges that contain A1-notation of the selected row.

Line 18 – Finally, we can use the Spreadsheet App Sheet Class getRangeList() method to collect all ranges in our array and then activate them.

Find and activate all rows based on a search value in Google Sheets — Finds and activates all rows in the range that contain ‘Koala’.

Find and format all rows in a Google Sheet with Apps Script

/**
 * Format all rows that contains the selected value.
 * Single sheet
 * @param {string} text - the text to find.
 * @param {string} sheetName - the target sheet.
 */
function formatAllRowsWithVal(text, sheetName) {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  const textFinder = sheet.createTextFinder(text);
  const allOccurrences = textFinder.findAll();

  const ranges = allOccurrences
    .map(item => `A${item.getRow()}:${item.getRow()}`);

  sheet.getRangeList(ranges)
    .setBackground('#0b5394')
    .setFontColor('white')
    .setFontWeight('bold')

};

/**

* Format all rows that contains the selected value.

* Single sheet

* @param {string} text - the text to find.

* @param {string} sheetName - the target sheet.

function formatAllRowsWithVal(text, sheetName) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const textFinder = sheet.createTextFinder(text);

const allOccurrences = textFinder.findAll();

const ranges = allOccurrences

.map(item => `A${item.getRow()}:${item.getRow()}`);

sheet.getRangeList(ranges)

.setBackground('#0b5394')

.setFontColor('white')

.setFontWeight('bold')

};

This last function is virtually the same as the previous one. However, we are doing something much more useful, we are programmatically updating the format of the selected rows.

Lines 18-21 – Using the getRangeList method, we can modify the formatting of any range in the list. In our example, we set the background of the row to a dark blue, changed the text colour to white and bolded the font.

Find and format all rows based on a search value in Google Sheets — Finds and formats all rows containing the search item ‘Koala’.

More formatting methods you can use.

Here is a list of common formatting methods that you can apply to a range list:

Clear Format – clearFormat() – Clears the formatting of the selected range.
Set background Colour – setBackgroundColor(colour) – Use CSS notation like hexadecimal colours, colour names or RGB colours
Set font Colour – setFontColor(colour)– Use CSS notation like hexadecimal colours, colour names or RGB colours.
Set font Weight– setFontWeight(type) – Either “bold” or “normal”.
Set font Size= setFontSize(size) – The font size as a number.
Set font Style – setFontStyle(style) – The style, e.g. “Helvetica”, “Poppins”.
Set vertical alignment–setVerticalAlignment(alignment) – “top”, “middle” or “bottom”.
Set horizontal alignment – setHorizontalAlignment(alignment) – “left”, “center”, “right”.
Set text rotation – setTextRotation() – Sets the rotation of the text in each cell in degrees from the original horizontal plain.

In the next tutorial, we will cover how to hide and unhide rows based on found values in a range. The process is a little different, so well worth checking out.

Subscribe to get an email when the next tutorial in this series comes out or get regular updates on my latest tutorials.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~Yagi

Performance of Google Apps Script Text Finder Class on 2 Approaches to Searching Large Datasets

Inspired by research into a recent blog post, the Google Apps Script Text Finder Class’ Find All (findAll()) and Find Next (findNext()) methods were benchmarked over two different datasets containing 50,000 rows. The first dataset contained 1,000 cells matching the search text. The second dataset contained 100 matching cells.

For each dataset, a test was conducted to retrieve either the first 10 matching cells or the first 100 matching cells. The Find All and Find Next approaches were tested and compared on each test.

It was expected that Find Next would perform best on the condition where the dataset contained a large number of found items and only a small number of first cells needed to be reported. The benchmark results suggest that this hypothesis is most likely.

	First number of cells to retrieve	Test	Function	Avg. run time over 100 runs.	Fastest Function	Fastest Avg. Time	Avg. time Difference
1000 items to find	10	1	v2 findAll	1626.24	v3 findNext	1368.45	257.79
	10	1	v3 findNext	1368.45
	50	2	v2 findAll	1578.19	v2 findAll	1578.19	4993.61
	50	2	v3 findNext	6571.8
100 items to find	10	3	v2 findAll	360.94	v2 findAll	360.94	975.16
	10	3	v3 findNext	1336.1
	50	4	v2 findAll	377.13	v2 findAll	377.13	6175.59
	50	4	v3 findNext	6552.72

Table: The average time in milliseconds of 100 runs of each test of Apps Script Text Finder findAll() and findNext() methods. Image link for mobile-friendly viewers.

Method

Sample Data

Two columns of data 50,000 rows deep were generated for this test. Each cell in each column consisted of a number; either 1, 2, 3, 4 or 5. An equal spread of numbers 1 through 4 where added to each row. Each column differs by the number of 5s in each row:

Col A: 1,000 5’s
Col B: 100 5’s

Each column was then selected and randomised with: Data > Randomise range.

Test

Two functions are compared to test their performance based on four test conditions based on 100 runs of each test:

Retrieve the first 10 cells containing the search text where the range contains 1,000 matching search items.
Retrieve the first 50 cells containing the search text where the range contains 1,000 matching search items.
Retrieve the first 10 cells containing the search text where the range contains 100 matching search items.
Retrieve the first 50 cells containing the search text where the range contains 100 matching search items.

The time in milliseconds was recorded using the JavaScript Date.now() method before and after the functions were run. The difference in time in milliseconds was then appended to an array and added to a Google Sheet column for each test type. This culminated in 8 sets of 100 results.

The average of each test was then recorded and used to compare performance.

Note: Performance.now() is not available in Google Apps Script.

Code

All code and results can be found copied from this sheet:

Analysis of Google Apps Script Create Finder Class Retrieve n found values

To explore the code and run your own independent tests, make a copy of the Google Sheet: File > Make a copy.

More detailed breakdowns of the code for each test function can be found in the source tutorial.

Note! There is no v1. The version numbers refer to the tutorial related to this post.

Main Test RUN

This function ran all the test conditions. Modify colPastePosition to add the culminated times to the desired columns. Then uncomment the desired run.

function testRun(){
  const sheetName = "Test Data"
  const text = 5;
  const n_small = 10;
  const n_big = 50;
  const rangeLoc_1000items = "A2:A";
  const rangeLoc_100items = "B2:B";

  let start, end

  const colPastePosition = 10;


  const times = [];

  for(let i = 0; i < 100; i++){

    start = Date.now();

    // Test 1: 1000 items to find. Return first ten, V2.
    const results = test_v2(sheetName, text, n_small, rangeLoc_1000items);

    // // Test 2: 1000 items to find. Return first ten, V3.
    // const results = test_v3(sheetName, text, n_small, rangeLoc_1000items);

    // // Test 3: 1000 items to find. Return first 50, V2.
    // const results = test_v2(sheetName, text, n_big, rangeLoc_1000items);

    // // Test 4: 100 items to find. Return first 50, V3.
    // const results = test_v3(sheetName, text, n_big, rangeLoc_1000items);

    // // Test 5: 100 items to find. Return first ten, V2.
    // const results = test_v2(sheetName, text, n_small, rangeLoc_100items);

    // // Test 6: 100 items to find. Return first ten, V3.
    // const results = test_v3(sheetName, text, n_small, rangeLoc_100items);

    // // Test 7: 100 items to find. Return first 50, V2.
    // const results = test_v2(sheetName, text, n_big, rangeLoc_100items);

    // // Test 8: 100 items to find. Return first 50, V3.
    // const results = test_v3(sheetName, text, n_big, rangeLoc_100items);

    end = Date.now();

    times.push([end - start])



  }

  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Test")
    .getRange(6,colPastePosition,100,1)
    .setValues(times)
  

}

function testRun(){

const sheetName = "Test Data"

const text = 5;

const n_small = 10;

const n_big = 50;

const rangeLoc_1000items = "A2:A";

const rangeLoc_100items = "B2:B";

let start, end

const colPastePosition = 10;

const times = [];

for(let i = 0; i < 100; i++){

start = Date.now();

// Test 1: 1000 items to find. Return first ten, V2.

const results = test_v2(sheetName, text, n_small, rangeLoc_1000items);

// // Test 2: 1000 items to find. Return first ten, V3.

// const results = test_v3(sheetName, text, n_small, rangeLoc_1000items);

// // Test 3: 1000 items to find. Return first 50, V2.

// const results = test_v2(sheetName, text, n_big, rangeLoc_1000items);

// // Test 4: 100 items to find. Return first 50, V3.

// const results = test_v3(sheetName, text, n_big, rangeLoc_1000items);

// // Test 5: 100 items to find. Return first ten, V2.

// const results = test_v2(sheetName, text, n_small, rangeLoc_100items);

// // Test 6: 100 items to find. Return first ten, V3.

// const results = test_v3(sheetName, text, n_small, rangeLoc_100items);

// // Test 7: 100 items to find. Return first 50, V2.

// const results = test_v2(sheetName, text, n_big, rangeLoc_100items);

// // Test 8: 100 items to find. Return first 50, V3.

// const results = test_v3(sheetName, text, n_big, rangeLoc_100items);

end = Date.now();

times.push([end - start])

}

SpreadsheetApp.getActiveSpreadsheet()

.getSheetByName("Test")

.getRange(6,colPastePosition,100,1)

.setValues(times)

}

test_v2 – Google Apps Script Text Finder Class- findAll()

Code breakdown can be found here: link.

This function retrieves the full list of all found cells using the findAll() method from the Text Finder Class. All available found items in the range are then stored in the found variable.

It then relies on a for-loop to iterate through each cell and collect the cell location using the Spreadsheet App Class’ range getA1Notation method. Each cell location is then stored in the locations variable as an array item before returning the array to the initialising function.

The for-loop breaks when the total number of required cell items (the position) equal the index variable (i) in the loop.

/**
 * Finds the first set of matching cells of n in length or less.
 * @param {string} sheetName - the name of the sheet tab. 
 * @param {string|number} text - text to search. 
 * @param {number} n - the max number of items to report.
 * @param {string} rangeLoc - A1- Notation of selected range.
 * @return {array} - Array of cell positions of the found match.
 */
function test_v2(sheetName, text, n, rangeLoc){
  const position = n - 1;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(rangeLoc)

  const found = range.createTextFinder(text)
    .findAll();

  let locations = [];

  for(let i = 0; i < found.length; i++){

    const cell = found[i];

    locations.push(cell.getA1Notation())


    if( i === position) break;
  
  }

  return locations;

}

/**

* Finds the first set of matching cells of n in length or less.

* @param {string} sheetName - the name of the sheet tab.

* @param {string|number} text - text to search.

* @param {number} n - the max number of items to report.

* @param {string} rangeLoc - A1- Notation of selected range.

* @return {array} - Array of cell positions of the found match.

function test_v2(sheetName, text, n, rangeLoc){

const position = n - 1;

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const range = sheet.getRange(rangeLoc)

const found = range.createTextFinder(text)

.findAll();

let locations = [];

for(let i = 0; i < found.length; i++){

const cell = found[i];

locations.push(cell.getA1Notation())

if( i === position) break;

}

return locations;

}

test_v3 – Google Apps Script Text Finder Class- findNext()

Code breakdown can be found here: link.

In this function, a call is made to the spreadsheet to retrieve the found cell value each time findNext() method of the Text Finder Class is called. On each iteration, the getA1Notation method is used to retrieve the cell location. This location is then stored as an array value in the locations variable before being returned to the initiating function.

The function used a while-loop to iterate through each next item found until the counter – or the number of required cells to collect – is reached.

/**
 * Finds the first set of matching cells of n in length or less.
 * @param {string} sheetName - the name of the sheet tab. 
 * @param {string|number} text - text to search. 
 * @param {number} n - the max number of items to report.
 * @param {string} rangeLoc - A1- Notation of selected range.
 * @return {array} - Array of cell positions of the found match.
 */
function test_v3(sheetName, text, n, rangeLoc){


  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(rangeLoc)

  const finder = range.createTextFinder(text)

  let locations = [];
  let counter = n;

  while(counter > 0){
    
    let found = "";

      found = finder
        .findNext()
        .getA1Notation();

    locations.push(found);

    --counter;
  };


  return locations

};

/**

* Finds the first set of matching cells of n in length or less.

* @param {string} sheetName - the name of the sheet tab.

* @param {string|number} text - text to search.

* @param {number} n - the max number of items to report.

* @param {string} rangeLoc - A1- Notation of selected range.

* @return {array} - Array of cell positions of the found match.

function test_v3(sheetName, text, n, rangeLoc){

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const range = sheet.getRange(rangeLoc)

const finder = range.createTextFinder(text)

let locations = [];

let counter = n;

while(counter > 0){

let found = "";

found = finder

.findNext()

.getA1Notation();

locations.push(found);

--counter;

};

return locations

};

Results & Discussion

Analysis of Google Apps Script Create Finder Class Retrieve n found values — Performance in Milliseconds to Retrieve the first 10 or 50 Matching Values over a 50,000 Row Range Contain Either 1000 or 100 Matchable items Using the Google Apps Script Spreadsheet App Finder Class.

Test 1: Retrieve the first 10 cells containing the search text where the range contains 1,000 matching search items.

Version 3 –findNext() performed better on average when there were 1000 potential items to find in the range but only the first 10 items need to be selected. Versions 3’s average speed was 1368.45ms compared to version 2’s average run speed of 1826.24ms. This is a performance increase of 257.79ms for version 3.

Version 2’s lower performance is likely due to needing to collect all available found cells before it can extract the top 10 items.

Version 3, makes 10 calls to the Google Sheets in this example. Compared to version 2, this takes relatively less time than collecting all available found cell references to the search item.

TEST 1: 1,000 randomised items to find in 50,000. Return first 10 matches using v2-findAll and v3-findNext functions.

Test 2: Retrieve the first 50 cells containing the search text where the range contains 1,000 matching search items.

Version 2 – findAll() performed significantly better over 100 runs than version 3 when retrieving the top 50 found cells from a possible 1000. Version 2 was, on average, 4993.61ms faster at an average runtime of 1578.19ms compared to version 3’s sluggish 6571.80ms average.

It was expected that test one and test two’s times for version 2 would be similar and there are only 48.05ms between their average runtimes.

Version 3’s poor performance is likely due to its reliance on calling the spreadsheet to collect the cell data on all 50 calls it needs to make.

TEST 2: 1,000 randomised items to find in 50,000. Return first 50 matches using v2-findAll and v3-findNext functions.

Test 3: Retrieve the first 10 cells containing the search text where the range contains 100 matching search items.

Version 2, again, performed better by 975.16ms than version 3 when there was a smaller potential number of items to find in the range and only the first ten items need to be retrieved.

Here the performance margin between the two versions was closer than in the previous test. Version 2’s average run speed was 360.94ms while version 3’s runtime was 1336.10ms.

With a smaller number of retrieved items, the version 2 findAll() function did not have to work as hard to collect the methods related to each range it collects. Whereas version 3 still needed to make 10 performance-intensive calls back to the Google Sheet each time with relatively no performance change to test one.

TEST 3: 100 randomised items to find in 50,000. Return first 10 matches using v2-findAll and v3-findNext functions.

Test 4: Retrieve the first 50 cells containing the search text where the range contains 100 matching search items.

Predictably, version 2 – findAll() performed the best when the expected match sample is small (100 available matches) and the total first set of cells to retrieve was relatively large (50).

Version 2’s average completion time was 377.13ms compared to version 3’s average of 6552.72ms, performing on average 6175.59ms faster. This is by far the largest margin on performance between the two versions.

Here again, version 3 must perform 50 calls to the Google Sheet, each one retrieving the cell range data. Alternatively, version 2 makes one call to the spreadsheet and then retrieves the cell data for all collected values. This is significantly faster than version 3’s approach.

TEST 4: 100 randomised items to find in 50,000. Return first 50 matches using v2-findAll and v3-findNext functions.

Overall

On datasets that may have the potential to contain a large number of matching items, but fewer required results to return, version 3 may be the best option. In all other cases, version 2 is the most optimal approach to finding data in a range.

It is important to note that it can be difficult to accurately measure performance with Apps Script runs because resource allocation to run a script does seem to vary. Nevertheless, with a sample size of 100 runs, it is hoped that average values will be more accurate than a smaller sample.

Grab Your Own Copy of the Google Sheet and Attached Code here

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

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? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*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

Google Chat Apps for Google Apps Script Developers

In this article, we will look at Google Chat Apps and go over some of the basics of developing a chat app with Google Apps Script.

Note that Chat Apps are currently only available to Google Workspace paid accounts and not standard ‘@gmail’ consumer accounts.

What are Google Chat Apps?

All about Google Chat Apps YouTube Video — Click to go to the video!

Google Chat is a business-level messaging tool for Google Workspace that allows you to chat one-on-one and in groups called Spaces within your business and even with clients and contractors outside of your domain.

Its integrations with Google Workspace tools like Google sheets, docs and slides right inside chat along with a Google Task tab and a convenient file library tab make it a really handy business tool.

But one thing that is often overlooked is that you can add Chat Apps or Chat Bots to your Google Chat Spaces or DM a chat app directly to accomplish tasks conveniently.

There are hundreds of 3rd party Chat App integrations for you to choose from. Anything from project management to customer support and more.

Chat apps can:

Continue reading “Google Chat Apps for Google Apps Script Developers”

Copy and Paste Range Values from one Google Sheet into another with Google Apps Script

You can easily use Google Apps Script to copy a range of data from one Google Sheet to another Google Sheet, just like you would with using the IMPORTRANGE function in Google Sheets. However, there are some clear advantages to importing ranges with Google Apps Script.

In this beginner-friendly tutorial, we’ll create an importRange() Google Apps Script function that you can quickly duplicate and even expand on in your own projects. We’ll also show you how to apply certain formatting and a time trigger to your code.

Note! This tutorial covers how to replace a range with existing data using Google Apps Script. If you wish to append data please head to the ‘Further reading’ section for more tutorials on this topic.

As usual, read what you need and skip the rest.

Continue reading “Copy and Paste Range Values from one Google Sheet into another with Google Apps Script”

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:

var body = DocumentApp.getActiveDocument().getBody();

body.replaceText("{{TEMPLATE TEXT}}", "My New Text");

var body = DocumentApp.getActiveDocument().getBody();

body.replaceText("{{TEMPLATE TEXT}}", "My New Text");

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!

Find text and replace it with new text and a link where the text is the only text in a document.
Find text within a paragraph and replace it with new text and a link.
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.

Table of Contents

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:

Here’s the code:

/**
 * Find an replace text with a single link where there is no
 * other text in the paragraph.
 */
function singleLink() {
  // ## Inputs ##
  let text = "My URL";
  let url = "https://yagisanatode.com/";
  let textToFind = "{{SINGLE LINK}}";
  // ############

  let body = DocumentApp.getActiveDocument().getBody();

  body.findText(textToFind)
      .getElement()         //Gets the current text element.
      .asText()             //Gets the element as a Text item.
      .setText(text)        //Updates the text for that element.
      .setLinkUrl(url);     //Sets the hyperlink for that element.

};

/**

* Find an replace text with a single link where there is no

* other text in the paragraph.

function singleLink() {

// ## Inputs ##

let text = "My URL";

let url = "https://yagisanatode.com/";

let textToFind = "{{SINGLE LINK}}";

// ############

let body = DocumentApp.getActiveDocument().getBody();

body.findText(textToFind)

.getElement() //Gets the current text element.

.asText() //Gets the element as a Text item.

.setText(text) //Updates the text for that element.

.setLinkUrl(url); //Sets the hyperlink for that element.

};

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:

console.log(body.findText(textToFind).getElement().getType().name())

1	console.log(body.findText(textToFind).getElement().getType().name())

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:

We need to do three things here.

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

Check out the code:

/**
 * Find and replace text with a single link where there is more text
 * in the paragraph. 
 */
function singleLinkWithinParagraph(){
  // ## Inputs ##
  let text = "My URL";
  let url = "https://yagisanatode.com/";
  let textToFind = "{{SINGLE LINK IN PARA}}";
  // ############

  let body = DocumentApp.getActiveDocument().getBody();
  
  let foundText = body.findText(textToFind);

  // Get the start and end location of the text in the paragraph.
  let startText = foundText.getStartOffset();
  let endText = startText + text.length - 1;

  // Get the element indext for this section of text.
  let element = foundText.getElement();

  // Replace the text and insert the URL.
  element.asText()
         .replaceText(textToFind, text)
         .setLinkUrl(startText, endText, url);
};

/**

* Find and replace text with a single link where there is more text

* in the paragraph.

function singleLinkWithinParagraph(){

// ## Inputs ##

let text = "My URL";

let url = "https://yagisanatode.com/";

let textToFind = "{{SINGLE LINK IN PARA}}";

// ############

let body = DocumentApp.getActiveDocument().getBody();

let foundText = body.findText(textToFind);

// Get the start and end location of the text in the paragraph.

let startText = foundText.getStartOffset();

let endText = startText + text.length - 1;

// Get the element indext for this section of text.

let element = foundText.getElement();

// Replace the text and insert the URL.

element.asText()

.replaceText(textToFind, text)

.setLinkUrl(startText, endText, url);

};

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:

Start text index – startText
End text index – endText
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.

Here is the code:

/**
 * Find text and replace it with a list of links.
 */
function multiLinkSet(){
  // ## Inputs ##
  const links = [
    {
      title: "My website",
      url: "https://yagisanatode.com/"
    },
    {
      title: "Twitter",
      url: "https://twitter.com/LifeOfSpy/"
    },
    {
      title: "Facebook",
      url: "https://www.facebook.com/yagisanatode"
    }
  ];

  let textToFind = "{{LINKS}}";
  // ############

  let body = DocumentApp.getActiveDocument().getBody();

  // Gets the paragraph element containing the text.
  let element = body.findText(textToFind)
                    .getElement()
                    .getParent();

  // Gets the index location of the para containing the text. 
  let index = body.getChildIndex(element);

  // Removes the paragraph element from the text.
  element.removeFromParent();
  
  //Loop through the list of link objects and add to the document.
  links.slice().reverse().forEach(link =>{
  body.insertListItem(index , link.title)
      .setLinkUrl(link.url)
      .setGlyphType(DocumentApp.GlyphType.NUMBER)
  
  });
};

/**

* Find text and replace it with a list of links.

function multiLinkSet(){

// ## Inputs ##

const links = [

{

title: "My website",

url: "https://yagisanatode.com/"

{

title: "Twitter",

url: "https://twitter.com/LifeOfSpy/"

{

title: "Facebook",

url: "https://www.facebook.com/yagisanatode"

}

];

let textToFind = "{{LINKS}}";

// ############

let body = DocumentApp.getActiveDocument().getBody();

// Gets the paragraph element containing the text.

let element = body.findText(textToFind)

.getElement()

.getParent();

// Gets the index location of the para containing the text.

let index = body.getChildIndex(element);

// Removes the paragraph element from the text.

element.removeFromParent();

//Loop through the list of link objects and add to the document.

links.slice().reverse().forEach(link =>{

body.insertListItem(index , link.title)

.setLinkUrl(link.url)

.setGlyphType(DocumentApp.GlyphType.NUMBER)

});

};

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:

The index location – index
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.

~Yagi

Tag: Apps Script

Find and Select or Format rows in Google Sheets with Apps Script

Find and activate the first or last row in a Google Sheet with Apps Script

Find and activate all rows in a Google Sheet with Apps Script

Find and format all rows in a Google Sheet with Apps Script

More formatting methods you can use.

Like this:

Performance of Google Apps Script Text Finder Class on 2 Approaches to Searching Large Datasets

Method

Sample Data

Test

Code

Main Test RUN

test_v2 – Google Apps Script Text Finder Class- findAll()

test_v3 – Google Apps Script Text Finder Class- findNext()

Results & Discussion

Test 1: Retrieve the first 10 cells containing the search text where the range contains 1,000 matching search items.

Test 2: Retrieve the first 50 cells containing the search text where the range contains 1,000 matching search items.

Test 3: Retrieve the first 10 cells containing the search text where the range contains 100 matching search items.

Test 4: Retrieve the first 50 cells containing the search text where the range contains 100 matching search items.

Overall

Like this:

Google Chat Apps for Google Apps Script Developers

What are Google Chat Apps?

Like this:

Copy and Paste Range Values from one Google Sheet into another with Google Apps Script

Like this:

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

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

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

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

The Data Source

Get the paragraph element containing the text.

Get the index of the paragraph containing the text

Removed the paragraph element from the text

Add the list of hyperlinks

Conclusion

Like this:

Find and activate the first or last row in a Google Sheet with Apps Script

Find and activate all rows in a Google Sheet with Apps Script

Find and format all rows in a Google Sheet with Apps Script

More formatting methods you can use.

Share this:

Like this:

Method

Sample Data

Test

Code

Main Test RUN

test_v2 – Google Apps Script Text Finder Class- findAll()

test_v3 – Google Apps Script Text Finder Class- findNext()

Results & Discussion

Test 1: Retrieve the first 10 cells containing the search text where the range contains 1,000 matching search items.

Test 2: Retrieve the first 50 cells containing the search text where the range contains 1,000 matching search items.

Test 3: Retrieve the first 10 cells containing the search text where the range contains 100 matching search items.

Test 4: Retrieve the first 50 cells containing the search text where the range contains 100 matching search items.

Overall

Share this:

Like this:

What are Google Chat Apps?

Share this:

Like this:

Share this:

Like this:

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

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

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

The Data Source

Get the paragraph element containing the text.

Get the index of the paragraph containing the text

Removed the paragraph element from the text

Add the list of hyperlinks

Conclusion

Share this:

Like this: