Google Apps Script Taking one random cell from each row

Question

So I have a sheet as seen here. https://docs.google.com/spreadsheets/d/1r2ogg1ldR0CFjOJ6mVObY-WRVipJx_X3pQ0yM2HYEog/edit?usp=sharing.

I am trying to write a script that goes through every row in range A1:D and chooses one random cell to put in the F column of the same row.

I am new to GAP so Im not sure how to write the exact script for that. This is what I got so far

   function random() {
       var sss = SpreadsheetApp.getActiveSpreadsheet();
       var ss = sss.getSheetByName('Sheet1'); //the sheet that has the data
       var range = ss.getRange('A1:D'); //the range I need
       var data = range.getValues();

       for(var i = ; i < data1.length; i++) { //at this point im just guessing based on online codes
           = Math.floor(Math.random()*(j+1)); //method of randomization
          ss.getRange('the i row of F column').setValue(data[i][1]); //choosing the row that is being used, selecting the first item of the shuffled array
        };
        }

What is the origin of your "j" value ? Which variable should be instanciate with the formula = Math.floor(Math.random()*(j+1)); ? — Pierre-Marie Richard
sorry for the confusion, i meant that as the values of different columns. — User9123

Pierre-Marie Richard Pierre-Marie Richard · Accepted Answer · 2017-06-26T09:40:03

You've been pretty close to the solution:

function random() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sss.getSheetByName('Sheet1'); //the sheet that has the data
  var range = ss.getRange(1,1,ss.getLastRow(), 4); //the range you need: 4 columns on all row which are available
  var data = range.getValues();

  for(var i = 0; i < data.length; i++) 
  { 
    var j = Math.floor(Math.random()*(data[i].length)); //method of randomization
    var element = data[i][j]; // The element which is randomizely choose
    ss.getRange(i+1, 6).setValue(element); 
  }
}

I've made some modification on your orignal code:

getLastRow() get the position of the last row which is use. Otherwise, you collect a array the size of your entire sheet.

The Math.random() function is multiply by the size of a row, so you can have as much column as you want.

Google Apps Script Taking one random cell from each row

1 Answers