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.
3
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!
1
There is nothing obvious in the code that would explain why it runs slowly, but in in many cases, spreadsheet formulas can run faster than scripted solutions. Try this:
=query(
filter(minute(A2:A); istime_strict(A2:A));
"select Col1, count(Col1)
group by Col1
order by count(Col1) desc";
0
)
The formula will get these results:
| count | |
|---|---|
| 47 | 6 |
| 45 | 5 |
| 46 | 5 |
| 41 | 4 |
| 42 | 4 |
| 44 | 4 |
| 30 | 1 |
To get just the largest count, extract it from the second column of the second row, like this:
=choosecols(
chooserows(
query(
filter(minute(A2:A); istime_strict(A2:A));
"select Col1, count(Col1)
group by Col1
order by count(Col1) desc";
0
);
2
);
2
)
When a script runs slowly, the culprit is often found in the spreadsheet rather than in the code. See these spreadsheet optimization tips.
(new requirement introduced in a comment)
need to save the data every day in a database
Use a simple script that retrieves formula results from the spreadsheet and writes them in the database. Run the script on a time-driven trigger.