如何在数组公式中使用自定义函数ARRAYREPLACE（）？[谷歌网页]

Question

我使用Thiago Mata的自定义Google Sheets函数来搜索和替换字符串中的多个单词。

Thiago的函数可以在这里找到

实际上，我使用他的函数替换关键字与相应的超链接。

原始字符串存储在列示例！A： A.关键字替换html对存储在另外三个表中：垂直表、水平表和技术表。

例如，在原始字符串中找到的每个“Authors”实例都替换为

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

这是一张谷歌表格，显示了我正在尝试做的事情（在原始数据/公式之外可编辑）

当我将该函数应用于单个单元格时，此关键字替换非常有效。当我手动将公式向下拖动列时，它也非常有效。

问题是，我如何转换专栏示例！B:B转换成工作数组公式？

Answer 1

只需修改ARRAYREPLACE，使其接收一系列单元格，而不是单个单元格。然后，遍历数组（例如，使用map），并对每一行使用与上一个函数对应的代码：

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

然后，在B2中，将输入范围设置为A2，而不是设置为A2:A10：

 
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;
  })
}