CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
cookie dough
cookie dough

Reputation: 11

Google Sheets Custom Function for Arrays

I'm trying to create a custom function in Google sheets that searches a column range for a specific value and returns true if it does. I've tested the function and it works fine when run in the script editor, however when I try to utilize it in Google sheets it does not work.

When I define the array argument the function runs fine, but when I try to pass a range in Google sheets as an array the function always returns false. This is my first time coding with Javascript/Google sheets functions, so I know I must be doing something wrong but I can't figure out exactly what the issue is. According to the developer page, passing a range of cells should automatically make an array as far as I understand, so I'm not sure what I did wrong.

So,

=columnMatch(A2; "hello", "hi", "good morning") will return true, BUT

=columnMatch(A2; B2:B) will always return false.

(where A2 is defined as "hi", and column B contains the list of values I wish to test for)

My code for the custom function is below:

**function columnMatch(submittedLocation, locationlist)** 
{
  
  for(var i = 0; i < locationlist.length; i++)
  {
     if(locationlist.includes(submittedLocation)) 
      return true; 
  }

  return false; 
} ```

Upvotes: 1

Views: 664

Answers (1)

andrewJames
andrewJames

Reputation: 22042

You can reduce this to a one-line function:

function columnMatch(submittedLocation, locationlist) {
  return locationlist.flat().filter(String).includes(submittedLocation);
}

How this works:

The submittedLocation is a single value.

The locationlist is a range - a 2-dimensional array of values:

[ ["val1"], ["val2"], [...], ... ]

To handle the 2-D list of words, we first flatten it to a simple array.

Then we filter out any empty values to avoid a very large array with not much actual data in it. We keep only non-empty strings. This step is not required, but is recommended.

Finally we can see if our search value is included in the array - and just return the true/false result of that boolean test.

Upvotes: 1

Related Questions