How to use custom function ARRAYREPLACE() in a array formula? [Google Sheets]

Question

I'm using Thiago Mata's custom Google Sheets function to search and replace multiple words in a string.

Thiago's function is found here

Practically speaking, I'm using his function for replacing keywords with their corresponding hyperlinks.

The original strings are stored in column Examples!A:A. The "keyword-replacement html" pairs are stored in three other sheets: 'Verticals', 'Horizontals', and 'Technologies'.

For example, each instance of "Authors" found in the original string is replaced with

    <a target="_blank" href="https://www.flauntmydesign.com/authors" title="Click for more business examples targeting authors">Authors</a>

Here is a Google Sheet showing what I'm trying to do (editable outside the original data/formulas)

This keyword replacement works great when I apply the function to a single cell. It also works great when I manually drag the formula down the column.

The question is, how do I turn column Examples!B:B into a working array formula?

Answer 1

Just modify ARRAYREPLACE so that it receives a range of cells instead of a single cell. Then, iterate through the array (for example, using map) and, for each row, use the code corresponding to your previous function:

function ARRAYREPLACE(dataInput,fromList,toList){
  return dataInput.map(row => {
    const input = row[0];
    // ... YOUR PREVIOUS FUNCTION
    return result;
  })
}

Then, in B2, instead of setting the input range to A2, set it to A2:A10:

Full code:

 
function ARRAYREPLACE(dataInput,fromList,toList,caseSensitive){
  /* solution from Iamblichus */
return dataInput.map(row => {
const input = row[0];
  
  /* default behavior it is not case sensitive */
  if( caseSensitive == undefined ){
caseSensitive = false;
  }
  /* if the from list it is not a list, become a list */
  if( typeof fromList != "object" ) {
fromList = [ fromList ];
  }
  /* if the to list it is not a list, become a list */
  if( typeof toList != "object" ) {
toList = [ toList ];
  }
  /* force the input be a string */
  var result = input.toString();

  /* iterates using the max size */
  var bigger  = Math.max( fromList.length, toList.length) ;

  /* defines the words separators */
  var arrWordSeparator = [ ".", ",", ";", ":", " " ];

  /* interate into the lists */
  for(var i = 0; i < bigger; i++ ) {
/* get the word that should be replaced */
var fromValue = fromList[ ( i % ( fromList.length ) ) ]
/* get the new word that should replace */
var toValue = toList[ ( i % ( toList.length ) ) ]

/* do not replace undefined */
if ( fromValue == undefined ) {
  continue;
}
if ( toValue == undefined ) {
  toValue = "";
}

/* apply case sensitive rule */
var caseRule = "g";
if( !caseSensitive ) {
  /* make the regex case insensitive */
  caseRule = "gi";
}

/* for each end word char, make the replacement and update the result */
for ( var j = 0; j < arrWordSeparator.length; j++ ) {

  /* from value being the first word of the string */
  result =  result.replace( new RegExp( "^(" + preg_quote( fromValue + arrWordSeparator[ j ] ) + ")" , caseRule ), toValue + arrWordSeparator[ j ] );

  /* from value being the last word of the string */
  result =  result.replace( new RegExp( "(" + preg_quote( arrWordSeparator[ j ] + fromValue ) + ")$" , caseRule ), arrWordSeparator[ j ] + toValue );

  /* from value in the middle of the string between two word separators */
  for ( var k = 0; k < arrWordSeparator.length; k++ ) {
    result =  result.replace( 
      new RegExp( 
        "(" + preg_quote( arrWordSeparator[ j ] + fromValue + arrWordSeparator[ k ] ) + ")" , 
        caseRule 
      ), 
      /* need to keep the same word separators */
      arrWordSeparator[ j ] + toValue + arrWordSeparator[ k ] 
    );
  }
}

/* from value it is the only thing in the string */
result =  result.replace( new RegExp( "^(" + preg_quote( fromValue ) + ")$" , caseRule ), toValue );
  }
  /* return the new result */
  return result;
  })
}
 
 
 

And call it like this:

Formula:

=arrayreplace(arrayreplace(arrayreplace($A2:A10,Verticals!$A:$A,Verticals!$B:$B),Horizontals!$A:$A,Horizontals!$B:$B),Technologies!$A:$A,Technologies!$B:$B)