0

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?

  • 1
    Google scripts an option for you or must it be in vanilla functions? – Tom Woodward May 19 '18 at 3:00
  • Preferably I would have liked to do it with a sheet formula but a google script can be as efficient :-) – Elad Ratson May 20 '18 at 8:48
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.

  • Thank you @Brian. I tried the code but I get Error: TypeError: Cannot read property "range" from undefined. (line 13, file "Code") – Elad Ratson May 24 '18 at 12:53
  • You can't run it from the code editor because that range is defined when the form is submitted. Go to your form, make a test submission, and then look. Also, make sure your Sheet names match. You can change either the sheet or the script. – Brian May 24 '18 at 14:23
1

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.

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.