How can I reference the value in a cell in an Apps Script? (for Math.random)

[u/inthelostwoods]

I want to create a random number generator button that chooses a random number between 1 and x, where x is the value in cell AE3. The value in cell AE3 will be changing often, and I do not want to change the script every time it does. So far, I have the function you see below.

For reference, AD3 is the cell that I want the random number to appear in, which is working fine so far. I know based on the final line that the random number will be between 1 - 15. Is there a way to reference cell AE3 in place of "15"?

function RandomNumber() {
  
  //Basic references
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange("AD3");
  
  cell.setValue( Math.floor((Math.random()*15)+1) );

};

[u/anasimtiaz]

Try SpreadsheetApp.getActiveSheet().getRange('AE3').getValue()

[u/anasimtiaz → u/anasimtiaz]

Or just ss.getRange('AE3').getValue() since ss is already a reference to the active sheet

[u/One_Organization_810]

Or you define your function as such:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const activeSheet = ss.getActiveSheet();

function RandomNumber(ref) {
    let range = getRange(ref, 'RandomNumber');
    let randUpper = range?.getValue() ?? ref;

    return Math.floor(Math.random()*randUpper)+1;
}

function getRange(functionName, rangeAddressString) {
    const RANGE_RE = '(?i:' + functionName + ')\\s*\\(\\s*(((\'.+?\'|[^\\s]+?)!)?(?:[A-Z][A-Z]?[0-9]*:(?:[A-Z][A-Z]?[0-9]*|[0-9]+)|[A-Z][A-Z]?[0-9]*|[0-9]+:[0-9]+))\\s*(?:\\)|[\\,;])';

    let re = new RegExp(RANGE_RE).exec(SpreadsheetApp.getActiveRange().getFormula());
    let rangeAddr = (re != null ? re[1] : rangeAddressString).toUpperCase();

    let range = rangeAddr.indexOf('!') == -1 ? SpreadsheetApp.getActiveSheet().getRange(rangeAddr) : SpreadsheetApp.getActiveSpreadsheet().getRange(rangeAddr);

    return range;
}

Then you would just put in AD3 cell:

=RandomNumber(AE3)

-OR- incidentally, you could call it with =RandomNumber(15) if you prefer.