Google Sheets Macro won't copy/paste correct randomly generated value

Question

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.

Here is a link to the sheet.

Please share a copy of your spreadsheet, excluding private and confidential information, and ensure tha5 it includes an example of a successful outcome. — Tedinoz
I have edited my post to add a link to the spreadsheet, as well as describing my desired outcome. — Brian
RANDBETWEEN is causing the problem. This topic is worth reading Google Scripts seems to be Caching Cell Values - Any way to avoid? as is How to Force New Google Spreadsheets to refresh and recalculate?. Though I'm guessing that you may have read one or both already. As they point out the cell value is cached and only changes when the spreadsheet is recalculated. So any change is going to recalculate RANDBETWEEN. A workaround doesn't immediately occur to me. — Tedinoz
Okay. Shame it won't be able to work. Thanks for your insight. — Brian
Well, I did say it doesn't occur to "me", so that's not the be-all-and-end-all. Of course, another option might be looking at the outcome that you are trying to achieve and thinking about whether that could be modified. I do recall a reference to "wrapping" a custom function. — Tedinoz

Brian Brian · Accepted Answer · 2019-11-22T00:36:23

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.

Google Sheets Macro won't copy/paste correct randomly generated value

1 Answers