Randomly Replace Text in a Cell in Google Spreasheet

Question

I have a block of about a 100 cells on a Google Spreadsheet. Some of them contain the string A. I want to randomly choose some of those and randomly replace them with one of B, C, D or E.

So, basically I want something like this:

if current_cell == "A":
   if random() > 0.2: -- proceed with probability 0.8
     current_cell = random_choice(["B", "C", "D", "E"])

I tried writing a formula on the same cell with an IF but that is going nowhere. How do I achieve this?

Answer 1

This works against a 10x10 grid and may handle other dimensions as well (didn't test it though). Oh, and it turns the background of cells it changes red to help with testing. Remove that where the comment indicates.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var col = sheet.getLastColumn();
  var range = sheet.getRange("a1:j10");
  var rangeValues = range.getValues();

  var length = rangeValues.length;
  for (a = 1; a < col+1; a++){ 
  for(i = 1; i < length; i++){
    var cell = sheet.getRange(a, i);
    var values = cell.getValues();
    if(values[0][0]=='a' && randNums() > 2) { 
      Logger.log(randNums());
      cell.setValue(randLets()).setBackground('red'); //remove .setBackground('red') to leave the background alone
    }  
    }
  }
}

//letters to select from
function randLets() {
    var letter = "";
    var possible = "BCDE";
    letter = possible.charAt(Math.floor(Math.random() * possible.length));
    return letter;
}

//rand number 1-10
function randNums() {
   var rand = Math.floor(Math.random() * 10) + 1  //between 1 and 10
   return rand; 
}