Google Sheets Buttons and Stopping RANDBETWEEN Calculations

[Jacob K]

Hi everyone,

I'm attempting to create a button in Google Sheets that, when clicked, will fill a cell with a random word selected from a column full of prompts (creating a story prompt generator). I have the random selection working but I would like for the cell, once filled with the random word, to then be JUST the word that was chosen and not the formula so that the sheet doesn't keep changing the word whenever I add more calculations, prompts, refresh, etc. 

When I try to copy and paste just the value with a macro all it gives me is a blank cell when I use the button the macro is assigned to. I've tried having it fill different cells and copy from those cells too, but it still turns out blank. 

Any alternate ideas for the same outcome are welcome! In other words, I want to create a series of buttons that will fill different cells with random values from a list of prompts, and those cells will ONLY change when I click their corresponding button again.

Thanks for the help!

Here is the script:

function AgentsTest() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('A10').activate();

  spreadsheet.getCurrentCell().setFormula('=INDEX(\'Story Engine Cards\'!$A$2:$A,RANDBETWEEN(1,COUNTA(\'Story Engine Cards\'!$A$2:A)))');

  spreadsheet.getRange('A10').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getRange('A11').activate();

};

[Alex]

Hi!

I thin you need a script instead formula

You can copy the sample from https://docs.google.com/spreadsheets/d/1ucUQRY5s7sqqEcnNTsPvR8WDsMjpPW98C9VwgB3FdOE/edit?usp=sharing

Cheers!