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

Time:07-22

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.

CodePudding user response:

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() * 31);  
    while (usedCell.indexOf(randomCellIndex) !== -1);
    
    usedCell.push(randomCellIndex);
    activesheet.getRange(randomCellIndex,2).setValue("CC");

  }

}

CodePudding user response:

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))

CodePudding user response:

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:

enter image description here

  • Related