Get the number of max values in a minute

Question

I'd like to get the number of max values in a minute based on the data in my Sheets (this is my array of my Sheet above)

.

So I've created a function under apps script to obtain what I want (the desired result is 7 for this demonstration table).

function getNumberByMin(){
   var ws = SpreadsheetApp.getActiveSpreadsheet();
   var ss = ws.getSheetByName("Sheet1");
   var tValues = ss.getRange("A2:B").getDisplayValues().filter(d=>d[0] != "");
   var minCheck = "", nbValuesByMin = 0, nbMaxValuesByMin = 0;

   for (let i = 0; i < tValues.length; i ++){
     var minSplit = tValues[i][0].toString().split(":")[1];
     // If "minCheck" is empty, "minCheck" is equal to "minSplit" and increment "nbValuesByMin"
     if (minCheck === ""){
       minCheck = minSplit;
       nbValuesByMin ++;
     }
     // Else if "minCheck" is equal to "minSplit", increment "nbValuesByMin"
     else if (minCheck === minSplit){
       nbValuesByMin ++;
       // If it's the last value of the array and "nbValuesByMin" is superior than "nbMaxValuesByMin", save "nbValuesByMin" in "nbMaxValuesByMin"
       if (i === tValues.length - 1 && nbValuesByMin > nbMaxValuesByMin){ nbMaxValuesByMin = nbValuesByMin; }
     }
     // Else if is not the same minute
     else{
       // if "nbMaxValuesByMin" is empty, save "nbValuesByMin" in "nbMaxValuesByMin", save the minute and initialize "nbValuesByMin" at 1
       if (nbMaxValuesByMin === 0){
         nbMaxValuesByMin = nbValuesByMin;
         minCheck = minSplit;
         nbValuesByMin = 1;
       }
       // else if "nbMaxValuesByMin" is not empty
       else{
         // if "nbValuesByMin" is superior than "nbMaxValuesByMin", save "nbValuesByMin" in "nbMaxValuesByMin", save the minute and initialize "nbValuesByMin" at 1
         if (nbValuesByMin > nbMaxValuesByMin){
           nbMaxValuesByMin = nbValuesByMin;
           minCheck = minSplit;
           nbValuesByMin = 1;
         }
         // else, save the minute and initialize "nbValuesByMin" at 1
         else{
           minCheck = minSplit;
           nbValuesByMin = 1;
         }
       }
     }
   }
   Logger.log(nbMaxValuesByMin)
 }

This function work perfectly but I've so many values and the runtime is too long. I would like optimize this.

Answer 1

This is my implementation for the core section:

  let oMins = {};
  let iMax = [0,0]; //times, minute

      for (let i = 0; i < tValues.length; i++ ){
        const mins = tValues[i][0].toString().split(":")[1];
        if ( oMins[mins] == undefined ) oMins[mins] = 1;
        else oMins[mins]++;
      }
      for( blobs in oMins ) {
        if ( oMins[blobs] > iMax[0] ) iMax = [oMins[blobs], blobs];
      }

Once done, iMax will contain the count and the last minute with that count.

This implementation is shorter than the one in the provided sample, but not faster; it's just the test i done.

Gowl, note that the execution time (in your sample) is two orders of magnitude larger in the first three lines where tValues are populated than the core of the computation!