2

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?

  • 1
    One column of 100 or like a 10x10 grid? – Tom Woodward Oct 20 '16 at 19:38
  • @Agnishom: Are you looking to replace values in place or on another area of the same sheet, another sheet/spreadsheet? – Rubén Oct 21 '16 at 0:38
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; 
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.