How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?
is there an easy way (workaround) how to disable automatic re-calculations of volatile functions like =RAND()
=RANDARRAY()
=RANDBETWEEN()
in google spreadsheet (?)
- without scripts
- without addons
- without macros
in case of building a key generator, where we need to work with multiple RANDBETWEEN
outputs, a re-calculation takes a place on every cell change, and those RANDBETWEEN
numbers can't stay for a longer period of time in our sheet, which we constantly edit.
there's a buggy freezer addon out there and lots of non-universal script variations requiring installation & some degree of modding / knowledge, so how to do this the old way?
Solution 1:
first, let's see what says the fox
=WHATTHEFOXSAY()
is a unique easter egg google sheets function (discovered by @kishkin) that randomly generates a pre-set string of text on user demand which is a huge deal because while the generation is random, the recalculation is not affected by onEdit, onChange nor onOpen events, so with some tweaking, we can generate a random number without using volatile functions like RAND()
, RANDBETWEEN()
, ARRAYRAND()
which can't be freezed / paused
the fox can speak only these phrases:
A-oo-oo-oo-ooo!
Hatee-hatee-hatee-ho!
Wa-pa-pa-pa-pa-pa-pow!
A-bubu-duh-bubu-dwee-dum
Fraka-kaka-kaka-kaka-kow!
Jacha-chacha-chacha-chow!
Gering-ding-ding-ding-dingeringeding
Joff-tchoff-tchoff-tchoffo-tchoffo-tchoff!
eg. what we are getting with this function is random 1 of 8 chance, which is not much, so we will need to scale it up (let's say by order of 9 fox tails). sadly function is not supported by ARRAYFORMULA
, therefore we need to construct {}
an array:
={WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}
then we convert this fox language into numbers like:
=INDEX(LEN(
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}))
next, we need a switch. nothing fancy, just a simple IF
statement and one checkbox:
=ARRAYFORMULA(LEN(IF(A1=TRUE,
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()},
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))
this will give us the ability to have full control over recalculation simply by switching the checkbox. and the last step is to scale up our initial 1/8 chance by adding more variations into our randomness by multiplication of all numbers with PRODUCT
and for more fun we multiply it by PI
raised on 3rd power:
=ARRAYFORMULA(PI()^3*PRODUCT(LEN(IF(A1=TRUE,
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()},
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}))))
unlimited blade works
so far it doesn't look like we created something useful... a checkbox with some randomly looking number cycling back and forth (which is at this point not random at all tho)... so let's move to the next level. our true objective is to create a string of digits from which we extract certain parts and convert them to final output. for that we need to harness the power of SEQUENCE
which we TRANSPOSE
for extra power:
=TRANSPOSE(SEQUENCE(5, 4, 3, 2))
this translates as a grid of 5 columns times 4 rows where the first cell starts with number 3 and the value of every next cell (column-wise) is larger by 2
now let's define our final output:
- 3x unique strings
- each string has 16 characters
- with numbers from
0
to9
- and lower case letters from
a
tof
at this point our SEQUENCE
will look like this:
=TRANSPOSE(SEQUENCE(16, 3, 29, 73))
16 = columns
3 = rows
29 = starting point
73 = stepping
for the 3rd and 4th argument (29
& 73
) we can use some nice prime numbers and multiply our whole sequence again by PI()^3
for extra chaos. also we need to convert our numeric output into a plain text string to avoid 1.79769E+308
notation so we use TEXT
for that:
=ARRAYFORMULA(TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE,
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()},
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"))
at this point, we have a pretty nice distribution of digits across the whole grid
ultimate hex / password generator
every character out there has its own code. you can check it by running this AF in your sheet:
=ARRAYFORMULA({{"character", "code"};
{CHAR(SEQUENCE(2500, 1, 33)), SEQUENCE(2500, 1, 33)}})
ROW(48:57) = 0-9
ROW(65:90) = A-Z
ROW(97:122) = a-z
ROW(1040:1071) = А-Я
ROW(1072:1103) = а-я
ROW(913-937) = Α-Ω
ROW(945-969) = α-ω
for our example purpose we need character codes for 0-9
and a-f
:
ROW(48:57) = 0-9
ROW(97:102) = a-f
we put it in array {}
and sort it in descending order (it's because we don't want to lock ourselves out from reaching lower positioned characters on the list). then we JOIN
it with or |
separator ready to be regex-ed:
=JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))
basically, the idea is to REGEXEXTRACT
first found number from the above-joined string within our grid of numbers, and just in case there is no match we add IFNA
fallback with some number (102
in this example) from our ranges:
=ARRAYFORMULA(IFNA(REGEXEXTRACT(
TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE,
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()},
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"),
JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))
and summon the CHAR
characters:
=ARRAYFORMULA(CHAR(IFNA(REGEXEXTRACT(
TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE,
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()},
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"),
JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102)))
now the last step is to smash it with a query and remove residue empty spaces - more on query smash here
=ARRAYFORMULA(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
CHAR(IFNA(REGEXEXTRACT(TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE,
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()},
{WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"),
JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))),,
9^9)), " ", ))
summary
- are those strings really random? ofc not - they are just "random enough" because the "entropy" rules are known to us, but good luck cracking it by hand...
- the main key point is that there is no 3rd party recalculation happening so the values stay paused and fresh values are generated upon switching the checkbox
- will these strings repeat over a period of time? - yes they may, just as regular
RAND()
function - is there a possibility of generating two identical keys on one go? - yes, it may happen, but it can be countered by several ways to lower the chances or introduce
UNIQUE()
or adding more fox tails - please note this special occasion: onOpen event will re-calculate
WHATTHEFOXSAY()
one time after every checkbox switch !! so it's recommended to refresh the spreadsheet withF5
key after every checkbox switch you make if you need to work with frozen value
in case you wish to have a fresh automatically generated string in certain periods of time you can insert a volatile function TODAY()
or NOW()
- but note that recalculation will happen every time you edit any cell or every minute / hour when you don't edit anything
(depending on ..//File > Spreadsheet settings)
how about not to re-calculate on every cell change and re-calculate on every checkbox switch or every 6 minutes? once a week? every odd month? only mondays and wednesdays? every 11th hour? possible! we need one more simple IF
statement placed anywhere in the spreadsheet which will host our logic gate and output the values in array {}
constellation so we could refer with our fox formula to a cell which does not hold formula with volatile elements. example: