ArrayFormula For Google Script that Generates RAndom Number based on cell data

Question

I have the following code to generate a random number between 0 and the number in Cell D16. The information on the sheet is being updated via a form and I want to know if there is a way to do an ArrayFormula with this code so that I can generate the numbers going down a column without having to type the code for each and every row.

function economyNegative() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName('SubmittedPolicies');
    sheet.getRange('E16').setValue(Math.random() * sheet.getRange('D16').getValue());
}

Welcome to [Web Applications Stack Exchange](https://webapps.stackexchange.com/tour). It is unclear what you are trying to do. Do you want the random numbers stay put or get refreshed on every row whenever another form response is submitted? — doubleunary, Jul 28 '22 at 17:15
@TheWizEd duplicates are fine but I don't want them to regenerate everytime the sheet is updated. That's why I thought a script would be the best option — Jason West, Jul 28 '22 at 17:57
@doubleunary Preferably to stay put whenever new data is added. — Jason West, Jul 28 '22 at 17:57
Try `PseudoRandomSequence()`. Consider sharing a publicly editable [sample spreadsheet](https://webapps.stackexchange.com/a/138383/269219) with _realistic-looking_ data, and showing your _manually entered_ desired results there. — doubleunary, Jul 28 '22 at 19:18

score 0 · Answer 1 · answered Jul 28 '22 at 16:53

Answer

The following formula should produce the result you desire:

=ARRAYFORMULA(RANDBETWEEN(1,SEQUENCE(ROWS(E16:E),1,D16,0)))

Explanation

The SEQUENCE function is used with its fourth argument being 0 to generate an array of numbers that are all identical. This is fed into the RANDBETWEEN function (which is wrapped in ARRAYFORMULA) to generate an array of values between 0 and whichever number is returned by the SEQUENCE.

ROWS(E16:E) is used to make sure that the results do not overflow the spreadsheet—the sequence's length is capped by the number of rows available.

If you wish to have the formula only return a single value once and have it stay consistent regardless of how you edit the sheet, please check out this question.

Functions used:

doubleunary · Answer 2 · 2022-07-29T02:11:56.213

If you want that all those random numbers get refreshed whenever another form response is submitted, use ifs() and randarray() in an array formula in row 1 of a free column, like this:

=arrayformula( 
  ifs( 
    row(D1:D) = 1, "Random", 
    isnumber(D1:D), D1:D * randarray(rows(D1:D)), 
    true, iferror(1/0) 
  ) 
)

If you want the random numbers to stay put, use the PseudoRandomSequence custom function in an array formula in row 1 of a free column, like this:

=arrayformula( 
  ifs( 
    row(D1:D) = 1, "Random", 
    isnumber(D1:D), D1:D * PseudoRandomSequence(42, 0, 10^6 - 1, rows(D1:D)) / 10^6, 
    true, iferror(1/0) 
  ) 
)

ArrayFormula For Google Script that Generates RAndom Number based on cell data

2 Answers2

Answer

Explanation