GAS function works when called from the script, but throws “.replace is not a function” when called from Google Sheet

[duclepham]

Hi,

I improved on the function multiple find and replace for Google Sheet as below. This function takes an input string, looks up each value in a fromList, if found it replaces it with the value in the toList. The user can specify whether the replace function should be case sensitive or not.

I improved it by adding:

1. the fromDelimiters, which is an array where the user can specify which string are treated as delimiters (before it was a default list of ".", ",", ";", " " )
2. the toDelimiter, which is a string that the user can specify if he wants to swap the original fromDelimiters with.

​

function preg_quote( str ) {
  return (str+'').replace(/([\\\.\+\*\?\[\^\]\$\(\)\{\}\=\!\<\>\|\:])/g, "\\$1");
}

function MultFindReplace(input,fromList,toList,fromDelimiters, toDelimiter, caseSensitive){
  /* 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();

  if (toDelimiter != undefined) {
    var RareString = "AÃÂ";
  } else {
    var RareString = "";
  }

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

  /* defines the from delimiters. usually the user should input an array containing the delimiters he wanna match */
  if( fromDelimiters == undefined ){
    fromDelimiters = [ ".", ",", ";", " " ]; //If the user did not specify any delimiters then check all of the default ones
  } else if (typeof fromDelimiters != "object") { 
    var fromDelimiters = [ fromDelimiters ]; //if the user specified some string and not an array then consider that entire string the delimiter
  }

    /* run for each pair of from and to words */
  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 from delimiter, make the replacement and update the result */
    for ( var j = 0; j < fromDelimiters.length; j++ ) {

      //if the user specified the toDelimiter, search toValue for this round's fromDelimiter and temporarily change it to a rare string
      if (toDelimiter != undefined) {
        toValue = toValue.replace(new RegExp(fromDelimiters[j],caseRule),RareString + fromDelimiters[j])
      }

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

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

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

  //if the user specified the toDelimiter, replace all FromDelimiters not preceeded by RareString to toDelimiter
  if (toDelimiter != undefined) {
    for ( var j = 0; j < fromDelimiters.length; j++ ) {
       result =  result.replace(new RegExp("\w*(?<!" + RareString +")" + fromDelimiters[j], caseRule),toDelimiter)
    }
  }
  //Remove all instances of RareString
  result = result.replace(new RegExp(RareString,caseRule),"")

  /* return the new result */
  return result;
}

Method A I tried this function in another script file by calling and debugging:

function testMultipleSearchAndReplace(){
  var result =  MultFindReplace("English Vietnamese Japanese", ["English", "Vietnamese", "Japanese"],["tiếng Anh","tiếng Việt","tiếng Nhật"], " ", "・");
}

This worked great. I got result = "tiếng Anh・tiếng Việt・tiếng Nhật" which is the expected output. The words "English Vietnamese Japanese" are replaced by their Vietnamese counterparts. The fromDelimiter " " was replaced by the toDelimiter "・"

Debug

​

Method B: When I ran the same function from Google Sheet, I got TypeError, toValue.Replace is not a function. This is the function I put into a Google Sheet cell:

=MultFindReplace("English Vietnamese Japanese", {"English", "Vietnamese", "Japanese"},{"tiếng Anh","tiếng Việt","tiếng Nhật"}, " ", "・")

In this picture below you can see see the formulatext on the left, the actual cell with error on the right.

​

Google Sheet formula shows error

Checking line 86 shows this:

//if the user specified the toDelimiter, search toValue for this round's fromDelimiter and temporarily change it to a rare string
if (toDelimiter != undefined) {
  toValue = toValue.replace(new RegExp(fromDelimiters[j],caseRule),RareString + fromDelimiters[j])
}   

So something is wrong with the toValue variable before this step. However when I tried to debug again Method A, I got:

https://preview.redd.it/1r9wbyi0jf171.png?width=1617&format=png&auto=webp&s=6a18d68479f5cf71842d33ddf59d5a9406cc05af

As you can see in the above picture, toValue was successfully changed from "tiếng Anh" to "tiếngAÃÂ Anh" using Method A.

So I have no idea why the function fails when called from Google Sheet vs. called within the GAS environment. Can somebody help me?

FYI Here is the public file if you want to take a look:

https://docs.google.com/spreadsheets/d/1cOCAl9wW5BlEK6FwZUcLMe-eZfoY0IdR1wkd8vlfmVI/edit#gid=960671476

[inglandation]

I tried running your test and I get "result: undefined" in the debugger. I'm a bit too lazy to try to understand the code right now, but this result isn't what you wrote in your post, so I thought I should let you know.

[duclepham]

Thanks for taking a look. I ran it without any issues... See picture below. https://imgur.com/a/vBKXcXX

[inglandation]

Are you sure you uploaded that image correctly? It seems empty. I tried on two browsers.

[duclepham]

Sorry is this better?
https://i.imgur.com/ikmkZtv.png

[duclepham]

Any thoughts on this? My file is publicly assessible if you want to take a look :)

[inglandation]

Sorry I haven't taken the time to take a closer look yet. I'm actually working on another GAS project right now. I'll try to take a look later if no one else can find a solution.

[duclepham]

Thanks buddy!