2

if I use the following function within Google Sheets it doesn't return the value "not found". The logs tell me: "Execution cancelled".

This happens at the line: 

var found = text.match(re);

If I change searchText to "abc" it works like a charme.

function example()
{
  var text = "abc cba";
  var searchText = "abcd";

  var re = new RegExp(searchText,"g");

  var found = text.match(re);

  if (found === undefined) { 
    return "not found";
  }
  else {
    return found;
  }
}

Why is the script execution cancelled and how can I prevent this behavior without using the regex twice by using e.g. text.search(re) combined with if before the match() ?

Axel94
  • 33
  • 7

1 Answers1

1

Cause:

The return value of string.match ,

An Array whose contents depend on the presence or absence of the global (g) flag, or null if no matches are found.

and

null !== undefined

So, when it's null, the else statement executes and returns null to sheet. The "execution cancelled" is irrelevant and probably from old logsissue

Solution:

Use null to compare return value.

Snippet:

if (found === null) {
Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks, that works. I didn't expect this. If I use the debugger it stops at the null return in the mentioned line above. I thought that the script then completely stops. Seems not to be so. A debugger error? – Axel94 Mar 05 '20 at 14:42
  • @Axe I can't reproduce this in the debugger. It runs without error when I test it. – TheMaster Mar 05 '20 at 16:08
  • 1
    if you going step-by-step thru the code with debugger? My google apps integrated debugger cancels everytime the runtime and in the logs appears the "execution cancelled" message ... – Axel94 Mar 05 '20 at 16:48
  • The only way to go step by step is if you marked a breakpoint. Did you put a red dot on the line number? – TheMaster Mar 05 '20 at 16:52
  • yes of course :-) and if I use the step in button it executes the line "var found = text.match(re);" and if it matches I can go to the next step. If it doesn't match the scripts stops and in the logs is the cancelled message ... – Axel94 Mar 05 '20 at 22:58
  • @Axel I was able to reproduce it. Seems like a bug. In any case, a new editor based on vscode is coming soon. So it would be pointless to report it now. PS: Consider accepting the answer, if it solved your problem. – TheMaster Mar 06 '20 at 07:19