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

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());
}

CodePudding user response：

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:

CodePudding user response：

If you want that those random get refreshed on every row 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) 
  ) 
)