I am trying to make a google sheet that assigns a random alphanumeric sequence to every new row added. However, when I use the rand() function within the array formula the outputs are all the same. I'm wondering if there is any way in order to call RANDALPHA() - a custom function - for each row.
This is what RANDALPHA is from: https://yagisanatode.com/2018/08/23/google-sheets-random-alphabetic-random-alphanumeric-and-random-alphanumeric-character-custom-functions/
try:
=INDEX(IF(A:A="";;RANDARRAY(ROW(A:A))))
=INDEX(IF(A:A="",,SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
CHAR(IFNA(REGEXEXTRACT(""&RANDARRAY(ROWS(A:A), 15)*(10^15), JOIN("|",
SORT({ROW(48:57); ROW(65:90); ROW(97:122)}, 1, ))), 48))),,9^9)), " ", )))
ROW(48:57) = 0-9
ROW(65:90) = A-Z
ROW(97:122) = a-z
RANDARRAY(ROWS(A:A), 15)*(10^15) - the first 15 is equal to the length
of randomly generated sequence
on the other hand, if you are happy with chaos instead of a random chance you can do:
=INDEX(IF(A:A="",,SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(CHAR(IFNA(REGEXEXTRACT(""&
TEXT(PI()^EXP(1*PI())*SEQUENCE(ROWS(A:A), 15, 17, 17)^10, "#"), JOIN("|",
SORT({ROW(48:57); ROW(65:90); ROW(97:122)}, 1, ))), 48))),,9^9)), " ", )))
which won't recalculate at all while you edit your sheet
or if you prefer newly generated values every day:
=INDEX(IF(A:A="",,SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(CHAR(IFNA(REGEXEXTRACT(""&
TEXT(TODAY()*PI()^EXP(1*PI())*SEQUENCE(ROWS(A:A), 15, 17, 17)^10, "#"), JOIN("|",
SORT({ROW(48:57); ROW(65:90); ROW(97:122)}, 1, ))), 48))),,9^9)), " ", )))
I wish I understood exactly how INDEX works in @player0's answer, but anyway, while playing with it trying to understand it, and copying his use of RANDARRAY, I found that ARRAYFORMULA somehow also works.
=ARRAYFORMULA(IF(LEN(A:A),RANDARRAY(ROW(A:A),1),""))
