Can't figure out how to add duplicate count to end of array-CodePudding

I think I'm totally overthinking this but I can't get out of my head. In Google Sheets, I have a list of work orders that look like this:

Work ID Location    Start Time  Officer Count
123      North      1100    
123      North      1100    
123      North      1100    
222      South      1200    
999      North      1400    
999      North      1400    
333      South      1200

Each work order always has one Officer assigned to it, so I need to count the duplicated work orders and push them to the end of the array under "Officer Count" so it shows how many total Officers needed. For example, 123 would need 3, 222 needs 1, 999 needs 2, and 333 needs 1.

However, the code I have now pops out the right count, just out of order from the original 2D array so I can't push it to the end of the array. Any suggestions?


var rowRange = sheet.getRange(2, 1, 7, 3).getValues();

//Create Work Number array (1D array)
var oneDArr = rowRange.map(function(row){return row[0];});

//Create object to count number of officers to Work Number
var counts = {};
oneDArr.forEach(function(x) { counts[x] = (counts[x] || 0) 1; });

//Create Array from Object
var array = [];
var array = Object.entries(counts);

//Set Values to correct location
// sheet.getRange(11, 1, array.length, array[0].length).setValues(array);

}

CodePudding user response：

Description

Here is an example script to get the count of work orders and place them in the original array.

Code.gs

function test () {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    let values = sheet.getDataRange().getValues();
    values.shift(); // remove the headers

    let ids = values.map( row => row[0] );
    ids = [...new Set(ids)];  // create an array of unique ids
    console.log(ids);

    let count = ids.map( id => 0 ); // initialize count to 0
    values.forEach( row => { let index = ids.indexOf(row[0]);
                              count[index]  ;
                            });
    console.log(count);
  
    // now let put back into spreadsheet
    ids.forEach( (id,i) => { let j = values.findIndex( row => row[0] === id );
                             values[j][3] = count[i]; } );
    console.log(values);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

8:46:20 PM  Notice  Execution started
8:46:23 PM  Info    [ 123, 222, 999, 333 ]
8:46:23 PM  Info    [ 3, 1, 2, 1 ]
8:46:23 PM  Info    [ [ 123, 'North', 1100, 3 ],
  [ 123, 'North', 1100, '' ],
  [ 123, 'North', 1100, '' ],
  [ 222, 'South', 1200, 1 ],
  [ 999, 'North', 1400, 2 ],
  [ 999, 'North', 1400, '' ],
  [ 333, 'South', 1200, 1 ] ]
8:46:21 PM  Notice  Execution completed

References