I have a sheet with a formula that is randomly picking a name from a list. I have created a macro assigned to a button/image that will copy the randomly generated value into another cell, but the value that it copies is not the one it produces. For example, if the formula in A1 generates "John", I want to copy that value to B1, so that B1 contains "John", plain text no formula.
My problem is that the end result in B1 will not be "John", but rather a different name from the list (plain text no formula). My best guess is that running the macro makes the formula recalculate several times before actually copying what I want, so that what gets copied and pasted is not the desired result. How can I get the macro to correctly copy the value that it shows me before running it?
The macro attached to the image of the "Run" button I am using is:
var spreadsheet=SpreadsheetApp.getActive();
spreadsheet.getRange('A1').copyTo(spreadsheet.getRange('B1'),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
The formula in A1 is:
=index(A3:A8,randbetween(1,6))
This produces one of the six names in A3:A8 at random.
In a successful result, while A1 contains "John", clicking the button would paste "John" into B1. A1 would then recalculate to a different name. If A1 contains "Steve", clicking the button would paste "Steve", and so on. Currently it will paste any of the names at random.
It's (probably) not possible to get RANDBETWEEN and macros to play nicely together, but one workaround is to use a macro to generate random numbers and paste them into your spreadsheet. You can then use that pasted number as the source for your random determinations, and it won't recalculate unless you run the macro again. That lets you use that particular random outcome in other macros. In my case, I used INDIRECT to let me use the random number as part of a cell reference, thereby picking a random name. Here's the macro I'm using for random numbers:
function getRandomInt(max) {
return Math.floor(Math.random() * Math.floor(max))+1;
};
With this function established, you can put it into other macros as getRandomInt(5), or whatever you want the maximum number to be. I added the "+1" to prevent zero as an outcome, but setting the maximum still works as normal.