Is there a way to add values to some random cells from a range?

Question

I have the following code:


function test1() {
  var app = SpreadsheetApp;
  var activesheet = app.getActiveSpreadsheet().getActiveSheet();

  for (var j = 2; j<= 3; j++) {
    
activesheet.getRange(j,2).setValue("C");

  }
}

This code will add the character "C" to the cell B2 and B3 which is great.

I am looking to get my code to add two "C" characters to 2 random cells from the first 30 cells of the B column.

Any help is much appreciated. Thank you.

You could use Math.random() to generate random cells between 1 and 30. — TheWizEd, Jul 21 '22 at 20:06

score 0 · Answer 1 · answered Jul 21 '22 at 20:12

0

I assume that you don't want duplicate values so i would grab two values from shuffled array. To avoid going into the rabbit hole with shuffling array exsample uses underscore

_.shuffle([...Array(30)].map((value, index) => index+1))

answered Jul 21 '22 at 20:12

Mateusz Rorat

127
8

Except _.shuffle() requires Lodash. – TheWizEd Jul 21 '22 at 20:20
https://stackoverflow.com/questions/2450954/how-to-randomize-shuffle-a-javascript-array you can use some shuffle method from this thread – Mateusz Rorat Jul 21 '22 at 20:22
Yes but if you notice all it’s doing is using Math.random() – TheWizEd Jul 21 '22 at 20:36
Math.random() with a proper multiplier still can produce two identical values. I assume that's unwanted here – Mateusz Rorat Jul 21 '22 at 20:43

Medo · Accepted Answer · 2022-07-21T22:27:31.650

0

This code may help you

function test1() {

  var app = SpreadsheetApp;
  var activesheet = app.getActiveSpreadsheet().getActiveSheet();

  let randomCellIndex;
  let usedCell = [];

  for (var i = 0; i < 2; i++) {
    
    // make sure not to use same index twice 
    do
      randomCellIndex = Math.floor(Math.random() * 30) + 1;  
    while (usedCell.indexOf(randomCellIndex) !== -1);
    
    usedCell.push(randomCellIndex);
    activesheet.getRange(randomCellIndex,2).setValue("CC");

  }

}

edited Jul 21 '22 at 22:27

answered Jul 21 '22 at 20:31

Medo

418
7
11

I think it’s Math.floor(Math.random()*30+1) – TheWizEd Jul 21 '22 at 20:48
Thank you, this is exactly what I was looking for! also how can I do the same for Column C as well? – steve88 Jul 21 '22 at 20:51
change the number 2 to 3 in the code activesheet.getRange(randomCellIndex,2).setValue("CC"); – Medo Jul 21 '22 at 22:29

Cooper · Answer 3 · 2022-07-21T21:31:34.753

Adds character string to range n times making a unique selection each time.

function addCharsRandom(chr = "AB", a1rg = "", n = 3) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = (a1rg.length < 2) ? sh.getActiveRange() : sh.getRange(a1rg);
  if (rg.getDisplayValues().flat().length >= n) {
    rg.setBackground("yellow")
    const w = rg.getWidth();
    const h = rg.getHeight();
    const r = rg.getRow();
    const c = rg.getColumn();
    let rA = [];
    let cA = [];
    [...Array.from(new Array(n).keys())].forEach(i => {
      let row, col;
      do {
        row = r + Math.floor(Math.random() * h);
        col = c + Math.floor(Math.random() * w);
      } while (rA.indexOf(row)>-1 && cA.indexOf(col)>-1);
      rA.push(row);
      cA.push(col);
      sh.getRange(row, col).setValue(sh.getRange(row, col).getDisplayValue() + chr).setBackground("white");
    });
  } else {
    SpreadsheetApp.getUi().alert("Range is to small for number of unique selections");
  }
}

if a1rg equals "" then it will select the active range.

Demo:

Is there a way to add values to some random cells from a range?

3 Answers3

Adds character string to range n times making a unique selection each time.