Apps Script Course: Advanced Arrays (continued)

We will now discuss array-associated methods that are particularly practical and efficient, though sometimes challenging to grasp initially, such as sort, filter, map, forEach, etc.

The arrow functions we previously saw will be very useful here.

ForEach

Let's start with an example of a for loop that will display a message in the console for each value in the array:

function test() {

  const array = [10, 20, 30, 40, 50];

  for (let i = 0; i < array.length; i++) {
    console.log('Value = ' + array[i]);
  }
}

This code can be simplified using the forEach method, which iterates over each array value and executes the arrow function's instructions:

function test() {

  const array = [10, 20, 30, 40, 50];

  array.forEach(value => console.log('Value = ' + value));
}

To help you better visualize the arrow function (compared to what was previously seen), here's another version with the function assigned to a constant:

function test() {

  const array = [10, 20, 30, 40, 50];

  const displayMessage = value => console.log('Value = ' + value);

  array.forEach(displayMessage);
}

However, unless you need to use the same function multiple times, it's not necessary to assign it to a constant.

If you need to know the index, add a second argument (and {} when there are multiple instructions):

function test() {

  const array = [10, 20, 30, 40, 50];

  array.forEach((value, i) => {
    console.log('Index = ' + i);
    console.log('Value = ' + value);
  });
}

Filter

The filter method allows filtering an array based on one or more conditions and returns a new array with the filtered data:

function test() {

  let array = [10, 20, 30, 40, 50];

  array = array.filter(value => value <= 30);

  console.log(array); // Displays: [ 10, 20, 30 ]
}

Here's another example with an array that corresponds to cell range values retrieved with getValues (an array containing a value array for each row of the range).

Here, rows of the range are retained only if the first value of the row (i.e., at index 0) is greater than 5:

function test() {

  const rangeValues = [
    [1, 1234],
    [7, 7654],
    [4, 4567],
    [8, 8765],
    [6, 6543]
  ];

  const filteredRange = rangeValues.filter(row => row[0] > 5);

  console.log(filteredRange); // Displays: [ [ 7, 7654 ], [ 8, 8765 ], [ 6, 6543 ] ]
}

Sort

The sort method allows sorting an array and returns a new array with the sorted data.

If you don't enter an argument, sort will perform an alphabetical ascending sort:

function test() {

  let array = [2, 50, 5, 30, 4];

  array = array.sort();

  console.log(array); // Displays: [ 2, 30, 4, 5, 50 ]
}

For a numerical ascending sort, add the following arrow function:

function test() {

  let array = [2, 50, 5, 30, 4];

  array = array.sort((a, b) => a - b);

  console.log(array); // Displays: [ 2, 4, 5, 30, 50 ]
}

Or for a numerical descending sort:

function test() {

  let array = [2, 50, 5, 30, 4];

  array = array.sort((a, b) => b - a);

  console.log(array); // Displays: [ 50, 30, 5, 4, 2 ]
}

Another example with a numerical ascending sort of a range of values based on the second value of each line:

function test() {

  let array = [
    [12, 421],
    [7, 987],
    [4, 1234],
    [18, 95],
    [6, 371]
  ];

  array = array.sort((a, b) => a[1] - b[1]);

  console.log(array); // Displays: [ [ 18, 95 ], [ 6, 371 ], [ 12, 421 ], [ 7, 987 ], [ 4, 1234 ] ]
}

Map

The map method applies a function to each value in an array and returns a new array with these values (equivalent to the Google Sheets function MAP).

Here is an example that converts text format values into numbers:

function test() {

  let array = ['10', '20', '30', '40', '50'];

  array = array.map(value => Number(value));

  console.log(array); // Displays: [ 10, 20, 30, 40, 50 ]
}

Reduce

The reduce method reduces an array to a cumulative result by applying a function (equivalent to the Google Sheets function REDUCE).

Here is an example that sums all the numbers in the array:

function test() {

  const array = [10, 20, 30, 40, 50];

  const sum = array.reduce((total, value) => total + value, 0); // 0 = initial value of "total"

  console.log(sum); // Displays: 150
}

Every

The every method checks if all values in an array meet a condition and then returns true or false.

Here is an example that checks if all values in the array are numbers (including those in text format):

function test() {

  const array = [10, '20', 30, '40', 50];

  if (array.every(value => !isNaN(value))) {
    console.log('YES'); // Displayed
  }
}

Some

The some method checks if at least one value in an array meets a condition and then returns true or false.

Here is an example that checks if there is a value with 8 characters in the array:

function test() {

  const array = ['Sheets', '-', 'Pratique', '.', 'com'];

  if (array.some(value => value.length === 8)) {
    console.log('YES'); // Displayed
  }
}

Exercise

In a new sheet, enter values at random in some cells chosen at random in the range A1:E20.

Then write a function that will retrieve the values from this range (in a single array, using the flat method), filter the data to remove empty values, sort these data in alphabetical order and display the result in the console.

function nonEmptyData() {

  // To be completed...

}

Complete the "nonEmptyData" function before moving on to the solution a little further down...

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

A commented solution:

function nonEmptyData() {

  // Cell range
  const range = SpreadsheetApp.getActiveSheet().getRange('A1:E20');

  // Array of values
  let arrayValues = range.getValues();

  // "Flattened" array
  arrayValues = arrayValues.flat();

  // Filter non-empty values
  arrayValues = arrayValues.filter(value => value !== '');

  // Sort in alphabetical order
  arrayValues = arrayValues.sort();

  console.log(arrayValues);
}

Since each method returns an array here, it is possible to chain them together.

Although less readable, this function could also have been written like this:

function nonEmptyData() {
  console.log(SpreadsheetApp.getActiveSheet().getRange('A1:E20').getValues().flat().filter(v => v !== '').sort());
}