1

is there a easy way (without heavy scripting) how to disable automatic re-calculations of volatile functions like =NOW() =TODAY() =RAND() =RANDBETWEEN() in google spreadsheet?

in case of building a key generator, where I need to work with multiple RANDBETWEEN outputs, a re-calculation takes a place on every cell change, and those RANDBETWEEN numbers cant stay for example a week in my sheet, which I constantly edit.

most of my google searching told me that this cant be done because those functions are volatile and this can be done only in MS Excel by setting calculations on "manual". also most of those answers suggested copy/pasting of such volatiles, but this can be bothersome if there are too many of them...

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    You seem to be assuming that "heavy scripting" would be involved. In fact, very light scripting could be enough: A function to produce a value equivalent of NOW() or RAND(), which would be run only on demand, for example when registered from the spreadsheet menu. – ttarchala Apr 27 '18 at 08:38

2 Answers2

2

actualy, there is a simplistic solution to achieve such "freezing" (for 30 minutes) of these volatile functions. (tho, its maybe not a "smart one", but so far it was very effective)

here is a small tutorial for generating two "frozen" keys implementing RANDBETWEEN:

  1. create new spreadsheed and name it "KEY1"
  2. set values for range A1:P1 like: enter image description here

  3. insert this in cell A3:

    =index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))& index($A$1:$P$1;RANDBETWEEN(1;counta($A$1:$P$1)))

  4. create a copy/duplicate of whole spreadsheet and name it "KEY2"

  5. create new (3rd) spreadsheet and name it "ALL_KEYS"

  6. enable sharing for all 3 spreadsheets and in advanced options select "Can edit" (please note that with not doing this step all will result in #REF error because those spreadsheets needs to be linked to each other)

  7. in this 3rd spreadsheet set cell A1 and A2 as follows:

    =IMPORTRANGE("paste-here-whole-url-of-KEY1-spreadsheet";"Sheet1!$A$3") =IMPORTRANGE("paste-here-whole-url-of-KEY2-spreadsheet";"Sheet1!$A$3")

  8. create new (4th) spreadsheet and name it as you wish (and also enable sharing)

  9. insert each formula in any cell (or even any Sheet tab across whole spreadsheet) example:

    D3 =IMPORTRANGE("paste-here-whole-url-of-ALL_KEYS-spreadsheet";"Sheet1!$A$1") D4 =IMPORTRANGE("paste-here-whole-url-of-ALL_KEYS-spreadsheet";"Sheet1!$A$2")

  10. right click on cell C3 and select "Data validation..." then as Criteria select "Checkbox" and check "Use custom cell values". then as TRUE input number 1 and as FALSE input number 0

  11. do same for C4 cell (this will work as on/on switch rather then on/off)

  12. now go to spreadsheets "KEY1" and "KEY2", and in both of them insert this into A2 cell:

    "KEY1" -> A2 =IMPORTRANGE("paste-here-whole-url-of-4th-spreadsheet";"Sheet1!$C$3") "KEY2" -> A2 =IMPORTRANGE("paste-here-whole-url-of-4th-spreadsheet";"Sheet1!$C$4")

  13. close spreadsheets "KEY1", "KEY2" & "ALL_KEYS", and never open them again

  14. done! bonus step: to make sure that those two randomly generated keys are unique you can add alert like =IF($A$1<>$A$2;"unique";"reload") in "ALL_KEYS" spreadsheet, and then import it into 4th spreadsheet like =IMPORTRANGE("paste-here-whole-url-of-ALL_KEYS-spreadsheet"; "Sheet1!a10")

SUM: now whenever you enable/disable your "switch" it will generate random fresh key for you which will stay there until next pushing of the switch (not even RELOAD of browser tab will change those randomly generated keys). as you can see there is a slight offset key1 in "KEY1" and key1 in "ALL_KEYS" & "4th", but that does not merit anyhow...

enter image description here

...maybe this could be used as security measure to check if someone from google ever opens your personal spreadsheets ;)

player0
  • 124,011
  • 12
  • 67
  • 124
2

This is a bit of a work around I used when I needed to create a random score generator.

I created a separate tab with the random information being generated. Then I used the macro recorder to record a copy + paste values onto my main tab from the random tab. Lastly I linked the macro to a button.

That way the random number generator could be volatile with every update, but I only got the random information imported when I wanted.

That is a bit of scripting, but not too heavy.

bbull
  • 21
  • 1