CODEWITHSUNDEEP
javascriptgoogle-apps-scriptreduce
haby22
haby22

Reputation: 77

Summarize/tally array with reduce function

I'm new to JS and have hit a roadblock on being able to summarize a Google spreadsheet data array for a mileage application.

I am trying to tally up miles driven by employee id and date but haven't found online resources to do this level of functionality using reduce/filter.


var records = [
    ["2019-08-26", emp1, 111],
    ["2019-08-26", emp2, 79],
    ["2019-09-02", emp1, 111],
    ["2019-09-02", emp2, 59],
    ["2019-09-02", emp3, 22],
    ["2019-09-05", emp3, 26],
    ["2019-09-02", emp1, 12],
    ["2019-09-05", emp3, 50],
    ["2019-09-02", emp2, 49],
    ["2019-09-05", emp1, 69],
    ["2019-09-02", emp1, 42],
    ["2019-09-05", emp3, 21],
    ["2019-09-02", emp2, 101],
    ["2019-09-05", emp3, 78],
    ["2019-09-02", emp3, 35],
    ["2019-09-05", emp1, 47]
]

I've tried for-loops and reduce/map combinations but I haven't been able to get the code to return the data in the way that we would like. The best attempts have been with the reduce function, so I think I'm on the right track? I've tried also mapping just the dates in a separate array to match against but not sure if that would make it more complicated.

The dates, employees, and miles would constantly change so the method that I would have to use has to be dynamic to accommodate future employees, dates, and hundreds of records over time.

What am I doing wrong?

var summary = records.reduce(function(total, employee) {
    total[employee[0]] = total[employee[0]] || []
    total[employee[0]].push({
        date: employee[0], 
        eid: employee[1],
        miles: employee[2]      
    }) 
    return total                                                              
  },{})

UPDATE: I am being asked to identify the "firstTrip" after tallying the miles driven by day and employee. I've tried embedding the Math.max() and Math.min() into the code in the answer but don't think that this would be the right approach. Any thoughts on how I should approach this new development?

[
  {date: "2019-08-26", employee: emp1, miles: 111, firstTrip: 111},
  {date: "2019-08-26", employee: emp2, miles: 79, firstTrip: 79},
  {date: "2019-09-02", employee: emp1, miles: 65, firstTrip: 12},
  {date: "2019-09-02", employee: emp2, miles: 209, firstTrip: 49},
  {date: "2019-09-02", employee: emp3, miles: 57, firstTrip:22}, 

etc

]

I am working in the Google AppScript (GAS) environment because of the Google Sheet data. Any help would greatly be appreciated.

Upvotes: 0

Views: 603

Answers (1)

Macarthurval
Macarthurval

Reputation: 178

In your attempt what you're doing is indexing an object with the date like key and all different entries in that date like values, like this:

{
    "2019-08-26": [
        {date: "2019-08-26", employee: emp1, miles: 111},
        {date: "2019-08-26", employee: emp2, miles: 79}
    ],
    "2019-09-02": [
        {date: "2019-09-02", employee: emp1, miles: 111},
        {date: "2019-09-02", employee: emp2, miles: 59},
        {date: "2019-09-02", employee: emp3, miles: 22}
    ],
    etc...
}

If I understand what you're trying to do, you need to pass like second argument for reduce an array [], not an object {}.

After in each iteration, you must check if already exists an element with given date and employee. If not, push that element, if it does, add the miles to the given object.

The code will be like this:

var summary = records.reduce( (total,record) => {
    var index = total.findIndex( s => s.date == record[0] && s.employee == record[1] );
    if( index == -1 ){
            total.push( {date: record[0], employee: record[1], miles: record[2]} );
    }else{
            total[ index ].miles += record[2];
    }
    return total;
},[]);

You will get this result:

[
    {"date":"2019-08-26","employee":emp1,"miles":111},
    {"date":"2019-08-26","employee":emp2,"miles":79},
    {"date":"2019-09-02","employee":emp1,"miles":165},
    {"date":"2019-09-02","employee":emp2,"miles":209},
    {"date":"2019-09-02","employee":emp3,"miles":57},
    {"date":"2019-09-05","employee":emp3,"miles":175},
    {"date":"2019-09-05","employee":emp1,"miles":116}
]

Edited: For Google APPS script, try with this:

var summary = records.reduce( function(total,record){
    var index = -1;
    total.forEach( function(s,i){
        if( s.date == record[0] && s.employee == record[1] ) index = i;
    });
    if( index == -1 ){
        total.push( {date: record[0], employee: record[1], miles: record[2]} );
    }else{
        total[ index ].miles += record[2];
    }
    return total;
},[]);

Upvotes: 3

Related Questions