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: