How to make a google apps script that takes a random value from a column when out separate sheet

Question

I'm sorry for the question, but I am not really a software developer and not familiar with java. I've been struggling with this problem for days, and the deadline is reaching… so I hope to find some help. Already really appreciated!!

What I am looking for is: I have a Google Form that is linked to a Google Spreadsheet. So every answer of the google form goes to that spreadsheet. These answers are used in a google apps script to create a google doc file (based on the answers of the form).

Now in that Google Form, there are some Yes/No questions. When they choose ‘Yes’ on the FIRST question, the idea is that the google apps script takes a random cell of the FIRST column of a (separate) ‘answer’ sheet.

When they choose ‘Yes’ on the SECOND question, the idea is that the google apps script takes a random cell of the SECOND column of the ‘answer’ sheet. And so on.

When they choose ‘No’ on a question, nothing needs to happen.

Later on, these random chosen answers/cells also needs to be placed in the google sheet. (like the Name and Email)

I’ll show what I have for now:

function autoFillGoogleDocFromForm(e) {
  var Timestamp = e.values[0];
  var Name = e.values[1];
  var Email1 = e.values[2];
  var OpenQuestion = e.values[3];
  var YesNoQuestion1 = e.values[4];
  var YesNoQuestion2 = e.values[5];
  var YesNoQuestion3 = e.values[6];

var TemplateResponseFolder = DriveApp.getFolderById("Unique-folder-ID");
var copy = TemplateFile.makeCopy(Name, TemplateResponseFolder);

var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();

// replace the value between {{xyz}} by the variable in the google doc.
body.replaceText("{{Name}}", Name);
body.replaceText("{{AchterNaam}}", LastName);
body.replaceText("{{email1}}", Email1);
body.replaceText("{{OpenQuestion}}", OpenQuestion);
body.replaceText("{{YesNoQuestion1}}", YesNoQuestion1);
body.replaceText("{{YesNoQuestion2}}", YesNoQuestion2);
body.replaceText("{{YesNoQuestion3}}", YesNoQuestion3);

  doc.saveAndClose();
}
// the script above works fine.

But I really have no idea how to make the ‘random lookup function’ work and store that value as a variable to use it in a google doc. I’ve been trying with:

function selectRandomCell(searchColumn,resultCellRow,resultCellColumn) {
  var ss = SpreadsheetApp.openById("Unique-ID-of-the-answer-sheet");
  var range = ss.getSheetByName("Sheet1").getRange("A1:A").getValues();
  var values = [];
  
  for (var i = 0; i < range.length; i++) {
    if (range[i][0] == "") {
      continue;
    }
    else {
      values.push(range[i][0]);
    }
  }

  return values[Math.floor(Math.random() * values.length)]);
}

Please help me. (And sorry for my English)

Answer 1

Solution:

function autoFillGoogleDocFromForm(e) {
  var Timestamp = e.values[0];
  var Name = e.values[1];
  var Email1 = e.values[2];
  var OpenQuestion = e.values[3];
  var YesNoQuestion1 = (e.values[4] === `Yes`) ? selectRandomCell(1) : `No`;
  var YesNoQuestion2 = (e.values[5] === `Yes`) ? selectRandomCell(2) : `No`;
  var YesNoQuestion3 = (e.values[6] === `Yes`) ? selectRandomCell(3) : `No`;

  var TemplateResponseFolder = DriveApp.getFolderById("Unique-folder-ID");
  var copy = TemplateFile.makeCopy(Name, TemplateResponseFolder);

  var doc = DocumentApp.openById(copy.getId());
  var body = doc.getBody();

  // replace the value between {{xyz}} by the variable in the google doc.
  body.replaceText("{{Name}}", Name);
  body.replaceText("{{AchterNaam}}", LastName);
  body.replaceText("{{email1}}", Email1);
  body.replaceText("{{OpenQuestion}}", OpenQuestion);
  body.replaceText("{{YesNoQuestion1}}", YesNoQuestion1);
  body.replaceText("{{YesNoQuestion2}}", YesNoQuestion2);
  body.replaceText("{{YesNoQuestion3}}", YesNoQuestion3);

    doc.saveAndClose();
}

function selectRandomCell(targetColumn) {
  const answerSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`NAME_OF_YOUR_ANSWER_SHEET`);
  const values = answerSheet.getRange(2, targetColumn, answerSheet.getLastRow()-1)
                            .getDisplayValues()
                            .flat()
                            .filter(String);

  return values[Math.floor(Math.random() * values.length)];
}

• The first change is determining what the Yes/No responses are.

We accomplish this as (e.values[4] === 'Yes') ? selectRandomCell(1) : 'No' which translates to (is this value 'Yes'?) If so, get a random cell from column 1. If not, leave it as 'No'.

• The second change is simplifying the selectRandomCell function.

Based on which column number you enter, the function will take all values of that column and select one at random, and return it back for us. If this function is 'called' in our YesNoQuestion variable, the result of this function becomes the value of that variable.

What You Need To Do:

• Create or update the sheet that you would like the 'Yes' answers to be selected from. The name of this sheet should be in this Spreadsheet and the name of the sheet must replace NAME_OF_YOUR_ANSWER_SHEET in the selectRandomCell() function.
• If you are not going to use Columns A, B, C (1, 2, 3), update the numbers being 'passed' to in the selectRandomCell() function of the YesNoQuestion variables to match the corresponding column(s). Please also note, the selectRandomCell function is also expecting the first row to be selectable values.

If you get any errors, or need further explanation, just let me know!