- If an attempt is made to add an element already in the array, it is rejected and the MsgBox dialog is displayed to that effect.
- If the newly entered element is not already in the array, it is added using the array push() method which adds the new element to the end of the array.
- The key method is indexOf(), it returns -1 if its argument is not already in the array, lastIndexOf() could also have been used and would yield identical results.
Generating A Sub-Set Of An Input Array Using The filter() Method
- Using one array as input to generate a new array, based on some programmer-defined rule, is a common task. In a more realistic setting, the input array would be generated at run-time rather than pre-populated as it is here.
- One could use a for loop to process the input array and implement the filtering in the body of the loop. However, the filter() method is specifically designed to do this.
- The filter() method takes either a function object or an in-line function as used above, and returns a Boolean for each element. Elements generating true are included in the return array.
Apply A Function To Each Element Of An Input Array Using The Array map() Method
- The map() method, like the filter() method shown above, applies a function to each element of the input array. However, unlike filter(), it returns an array of the same length as the input array.
- All or some of the elements of the input array may be altered in the output array.
- The above example applies the string toUpperCase() method to each element.
- map(), like filter(), is more useful when the input arrays are populated at run-time.
A More Practical Example - Filtering Duplicates From Spreadsheet Input
- As stated above these array methods are more useful when the arrays that they operate on are generated at run-time.
- One practical application is to use them to filter out duplicates from an input Google spreadsheet range.
- The writeUniqueList() below performs this filtering by taking an input range to filter and an output range to where it writes the unique list.
- It is tested in the function test_writeUniqueList() where the input and output ranges are defined.
- To use, add some data to a sheet, the test code below uses range "A1:A9", paste the code into the Script Editor and run the test function:
- The above code uses array methods to add some useful functionality, namely, produce a list of unique values from an input range. There does not appear to be a way to perform this task using the Google spreadsheets interface.
- To simplify matters, it throws an exception if the input range is not single-column.
- Once again, the key method that is used to determine uniqueness is indexOf().
- Possibly the trickiest part of the code is the application of the map() method to the array of arrays returned by the range getValues() method. It is important to remember that the array that getValues() returns is not a simple array of scalars, to turn it into an array of scalars, the map() method applies the array join() method with an empty string argument to each input element which turns a single element array into a scalar value.
- When defining the output range in the calling function, the range method getCell() was used. This is a one-based method, like the Cells property of the range object in Excel VBA. getCell(1,1) returns a range object representing the top left cell of the given range object, just like Cells(1,1) in Excel VBA.