Here’s the solution.
I add this custom function in the Tools > Script Editor, then use it as a custom function.
The closest date from now will be ranked 1st, then 2nd etc., based on each activity.
function customrank(arr) {
arr = arr.filter(function(r) {
return r[0] != "";
});
return arr.map(function(r1) {
return arr.reduce(function(rank, r2) {
return rank += (r2[0] == r1[0] && r2[1] > r1[1]);
}, 1);
});
}
Awesome it is working!
First time I use script editors tho… if someone copy the app & google sheet, will they have the custom code too?
It’s me again @ThinhDinh. Thanks again for the perfect solution.
If I want the rank to be user specific, what would be the custom function?
The scripts won’t be copied over when someone else is duplicating your app.
If you want it to be user specfic then capture the user email in each row, then join the email and the activity in a helper column.
Something like.
={"Joined info";ARRAYFORMULA(IF(A2:A<>"",A2:A&""&B2:B,"")}
Then use that helper column and the date for the rank formula.
Here @F_rank, I demonstrated what I mean in the previous comment, then used a query to order the table so you know the rank is working correctly.
@ThinhDinh you are da man. Seriously, it’s like magic. It’s a serious asset to know custom functions!
I like to think that one day Glide will be able to do all that, but until then we have you 
