CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheetsfilterreduce
ireth92
ireth92

Reputation: 75

How to reduce array's numeric values, skipping strings

Using Google Sheets' Script Editor, I need to calculate the average of each column of a table such as this one:

+---------+---------+---------+---------+
|         |  User 1 |  User 2 |  User 3 |
+---------+---------+---------+---------+
|         | 20/1/17 | 21/1/17 | 22/1/17 |
+---------+---------+---------+---------+
| Movie 1 |    6    |    2    |    0    |
+---------+---------+---------+---------+  
| Movie 2 |    2    |    1    |    5    | 
+---------+---------+---------+---------+
| Movie 3 |    3    |    1    |    2    | 
+---------+---------+---------+---------+

I want the result to be:

+---------+---------+---------+
|  User 1 |  User 2 |  User 3 |
+---------+---------+---------+   
|   3.6   |   1.3   |   2.3   |
+---------+---------+---------+

I have this code, which works if I use it on a table composed by numbers only. The problem is my table has strings in it, so the .reduce returns a #NUM! error (Result was not a number).

var sum = array.reduce(function(r, e, i) {
  e.forEach(function(a, j) {
    r[j] = (r[j] || 0) + a
  })
  if (i == array.length - 1) r = r.map(function(el) {
    return el / array.length
  });
  return r;
}, []);

return sum;

How can I perform the average with the numeric values only? I tried using .filter, but it didn't seem to work on Google Apps Script Editor, or maybe I was just using it wrong (very likely).

This is the condition I was using to filter: 

(typeof el !== 'number')

Upvotes: 1

Views: 279

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about following sample? You can use this on Google Apps Script.

var array = [
            [,, 'User 1', 'User 2', 'User 3'],
            ['Movie 1', '20/1/17', 6.0, 2.0, 5.0],
            ['Movie 2', '21/1/17', 2.0, 0.0, 2.0],
            ['Movie 3', '22/1/17', 3.0, 1.0, 3.0]
            ];

var sum = array.reduce(function(r, e, i) {
  e.forEach(function(a, j) {
      r[j] = (parseInt(r[j]) || 0) + a;
  })
  if (i == array.length - 1) r = r.map(function(el) {
    return isFinite(el) ? el : "";
  });
  return r;
}, []);
return [array[0].splice(2, 3), [x for each (x in sum)].splice(2, 3)];

>>> [[User 1, User 2, User 3], [11.0, 3.0, 10.0]]

Upvotes: 2

Related Questions