Google Sheets: how to trigger random function?

Question

I have a sheet on Google and keep looking for a solution on how to make randomizing script work only when it's needed, not every time I open the sheet or refresh it.

Here's the example. Let's take the sheet looks like this:

B5 cell has the following code:

=index(B1:B3; randbetween(1;3); 1)

It randomly tells me the hair color every time I refresh or load the document.

But what if I want the new hair color only once a week, but need to open the document from time to time?

I have tried to put a flag to the code.

And changed the code of B5:

=if(B7=true; index(B1:B3; randbetween(1;3); 1); )

So it initiates randbetween function only when the flag is checked.

BUT! When the flag is unchecked, the hair color is shown empty (right, 'cause it has a space in IF function). How do I keep the hair color the same as it was before and make it change only if I check the flag?

Here's the spreadsheet.

Answer 1

in your B5 use:

=ARRAYFORMULA(IFNA(VLOOKUP(
 IFNA(REGEXEXTRACT(TEXT(PI()^3*PRODUCT(LEN(IF(B7=TRUE; 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}; 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 (TRANSPOSE(SEQUENCE(1; 1; 29; 73)))*PI()^3; "0"); 
 JOIN("|"; SORT({3;2;1}; 1; IFERROR(0/C2)))); 2); 
 {ROW(B1:B3)&""\ B1:B3}; 2; 0)))

and for weekly recalculation use this in C1:

=IF(IFERROR(WEEKDAY(TODAY(); 2); 0)=1; {"";1}; {"";0})

so the value of B5 will change every time you check / uncheck the checkbox and every понедельник (Monday)

---

more on WHATTHEFOXSAY function here

---

Answer 2

Maybe you just make a button for running a function that you will click once a week?

function onOpen() {
    var ui = SpreadsheetApp.getUi();

    ui.createMenu('The name of menu')
        .addItem('Button name', 'functionName').addToUi(); 
}