I’m building an educational app to help students memorize vocabulary. For this app, I’d like the “cards” to be presented in an order that is randomized or shuffled occasionally. In Google Sheets I could use RAND() or RANDBETWEEN(), but those get recalculated whenever a change occurs, and that’s not what I want. I would like periodic (say, every three hours) shuffling of the order. By the way, in AppSheet, RANDBETWEEN() doesn’t seem to work for sorting because, in a virtual column at least, it produces the same number for every row.
I’ve been bothered by this sort of problem for quite some time now but I think I’ve finally found a solution. I’d like to share it here and see what other participants in this forum think.
Since I want the recalculation to be periodic (every few hours), I’ll start with a big number of hours divided by the number of hours I’d like between calculations:
At the moment, this produces 143,052 – a pretty big number. The next thing I want to do is divide this number by each row number and make sure I have all the decimals that get generated. It’s the decimals, not the integers, that will become the random numbers used to sort the column:
The results for the first three rows are as follows:
Now, obviously, at this point, these number can’t be used for “randomized” sorting because they get gradually bigger . . . just as the row number gets bigger. However, if we can chop out a chunk of numbers from the middle, we’ll be left with numbers that go up and down in a way that seems pretty random:
Moreover, these numbers will change every three hours – again, in a way that seems pretty random.
So, the question is how to accomplish that. I’ll omit a detailed explanation, but here’s the expression I came up with. It may not be the most efficient way to do this but it works. Moreover, you can change the 3s (the number of hours between shuffling) to the interval of your choice:
Note: FLOOR() gets rid of the decimals and leaves the integer. I wanted to do the opposite so I subtracted the the integer from the number with all of the decimals.