Google Sheets - Fetch random value from array for each new line created with form

Question

I have a Google Form that fills up a new line with values in Sheet1!, column A and column B.

I am trying to create an =ARRAYFORMULA function in column C, which will populate the C cell in each new line created by the form with a random value, fetched from an array of cells in Sheet2!A1:A8.

The closest I got to doing it is with this array formula I placed in Sheet1!C1

=ARRAYFORMULA(IF(A:A="","",INDEX({Sheet2!A1,Sheet2!A2,Sheet2!A3,Sheet2!A4,Sheet2!A5,Sheet2!A6,Sheet2!A7,Sheet2!A8},RANDBETWEEN(1,8))))

It indeed enters a random value from Sheet2!A1:A8, however, the problem is that it changes all the previous values in column C to the same new random value it fetches for each new line created by the form.

What I need is that each new line created by the form will receive a different random value from the array.

Any ideas anyone?

Answer 1

A script might be your best option because on each change, any random functions are reevaluated.

Go to Tools > Script editor and paste in the following script.

// Append a random value from an array when a form is submitted.
// The event parameter, e, contains the range data to append to the correct row.
function append(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get the form data coming in.
  var inputSheet = ss.getSheetByName('formData');

  // Get the array of random values from sheet 2.
  var randVals = ss.getSheetByName('Sheet2').getRange("A1:A8").getValues();

  // Get the range of the added row to the spreadsheet. Add one to the last column value so you're appending to col 3.
  var row = e.range.getRow()
  var col = e.range.getLastColumn()+1

  // Insert a random value from the values array to the correct row and column.
  inputSheet.getRange(row, col).setValue(randVals[Math.floor(Math.random() * randVals.length)]);

}

Once the script is in, go to Edit > Current project triggers and add a new trigger. Choose "from spreadsheet" and "on form submit" and the save.

Now, when your form is submitted, a random string from Sheet2 will be added to the last column of the spreadsheet.

Answer 2

I had a situation to randomly pick M or F in a column. I used this formula in Google Sheets:

 =IF(RANDBETWEEN(1,2) = 1,"M","F")

Crude way, certainly not for long lists, but it works if your set of values is small.