On cell change return random date from selected range

Question

function genRND(e) { 

  if (e.range.getA1Notation() === 'S1') {

   var AS = SpreadsheetApp.getActiveSpreadsheet();

   var DSTsheet = AS.getActiveSheet();

   var ED = DSTsheet.getRange('T1').getValue();
   var SD = DSTsheet.getRange('T2').getValue();

  for (var NR = 0; NR < 59; NR++){
   if (DSTsheet.getRange('E' & NR).getvalues() != '' && 
    DSTsheet.getRange('T' & NR).getvalues() != 'SOLD'){

     var RNDNUM = Math.floor(Math.random() * (ED-SD)) + SD; 
     var rndDATE = DSTsheet.getRange('A' & RNDNUM);

     DSTsheet.getRange('S' & NR).setValue(rndDATE);
    }     
  }

 }
}

I need actually to perform RANDBETWEEN only when cell S1 value change (it can be some other value every time) and not RANDBETWEEN on every change of any cell.

So, trigger is set to S1 and it should look for T1 and T2 to detect beginning and end of range. Math.random should ran some number between that range. All the way up here it works!

After, in loop it should check is unit SOLD or without STATUS, skip those and return random date by using RNDNUM (and list of dates is in A column).

Shortly, it doesn't return anything as a result.

I've tried checking RNDNUM and it does return properly what it should, but next vale isn't returned - rndDATE (if it is important value that should be returned is in date format)!

Chris · Accepted Answer · 2019-02-16T23:43:09.093

0

It looks like you are not getting the value of the range, but the Range obj:

var RNDNUM = Math.floor(Math.random() * (ED-SD)) + SD; 
var rndDATE = DSTsheet.getRange('A' + RNDNUM).getValue(); //Get value, not Range Obj

DSTsheet.getRange('S' + NR).setValue(rndDATE);

Expanding based on Tanaike's comment and then this answer:

& is a bitwise 'AND' that expects 2 numbers and returns a number. It's use is discouraged in Javascript. To combine a string with a number, use +.

edited Feb 16 '19 at 23:43

answered Feb 16 '19 at 08:20

Chris

2,057
1
15
25

1

Although I think that your indication is correct, the bitwise operator of ``&`` is used to declare the range in this script. In this situation, an error occurs at ``DSTsheet.getRange('A' & RNDNUM)``. So how about modifying from ``&`` to ``+``? I think that this might be useful for other users. – Tanaike Feb 16 '19 at 23:31
1

@Tanaike, yes, that was the case and thnx for explaining. Guess I worked too much in VBA so automatically placed & instead of + – Mr K Feb 22 '19 at 07:15

score 0 · Answer 2 · answered Feb 16 '19 at 18:08

0

Actually,

var rndDATE = DSTsheet.getRange(RNDNUM, 1).getValue();
DSTsheet.getRange(NR, 21).setValue(rndDATE);

solved case. I don't know why ('A' & RNDNUM) wasn't proper range format?

answered Feb 16 '19 at 18:08

Mr K

111
13

In your situation, the bitwise operator of ``&`` is used like ``'A' & RNDNUM``. By this, this result becomes ``0``, and when this is used for the range like ``DSTsheet.getRange('A' & RNDNUM)``, an error occurs because that is run as ``DSTsheet.getRange(0)``. If you want to use like ``A1``, please use ``+`` operator. So it's ``DSTsheet.getRange('A' + RNDNUM)``. – Tanaike Feb 16 '19 at 23:24
1

@Tanaike thnx for explanation. I really was in blur why I kept getting error – Mr K Feb 17 '19 at 08:18

On cell change return random date from selected range

2 Answers2