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:

(hour(now()-date("12/30/1970ā)))/3

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:

[_RowNumber]/decimal(floor((hour(now()-date(ā12/30/1970ā)))/3))

The results for the first three rows are as follows:

0.0000137030413900365186053044

0.0000205545620850547779079567

0.0000274060827800730372106089

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:

floor(

((([_RowNumber]/decimal(floor((hour(now()-date(ā12/30/1970ā)))/3)))*1000000000)-

floor(([_RowNumber]/decimal(floor((hour(now()-date(ā12/30/1970ā)))/3)))*1000000000))

*10000

)

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.