Form validation based on values from a spreadsheet

Question

I would like to validate text entries in a google form comparing them with a string list in a spreadsheet (something like "4ycTU"). Both files, form and spreadsheet, are in the same directory. So far, I got the string values in an array but I can't figure out how to compare them with the user text entry in the validation.

 function idValidation() {
  // get the form directory 
  var form = FormApp.getActiveForm();
  var formFile = DriveApp.getFileById(form.getId());
  var folder = formFile.getParents().next();

  // get data from the spreadsheet in an array
  var idSheetID = folder.getFilesByName("identificativi").next().getId();
  var idSheet = SpreadsheetApp.openById(idSheetID).getActiveSheet();      
  var idValues = idSheet.getRange(1,2,idSheet.getLastRow()).getValues();

  // create the validation rule
  var idVal = Formapp.createTextValidation();
                     .setHelpText("Inserisci un codice identificativo valido.");
                     .??? 
  }

Is there a way to do this? Thanks a lot.

I have to apologize for my poor English skill. Can I ask you about compare them with the user text entry in the validation? — Tanaike
Don't worry, it's probably my English that is not very good. I need to validate a form entry using a list of strings in a spreadsheet. I'm asking to the user to enter in the form his ID code and I have all the valid codes in a spreadsheet stored in the form directory. Hope this is more understandable :) — Samuele
Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and that was not the direction you want, I apologize. — Tanaike

Tanaike Tanaike · Accepted Answer · 2020-03-10T02:20:37

You want to create the text item to the active Google Form.
You want to give the validation rule to the text item.
You want to use the values from Google Spreadsheet as the validation rule.
You want to make users input the values which are the same with the values retrieved from Google Spreadsheet.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

In this case, it is required to create the value of the validation. Then, the created validation rule is put to the text time and add it to the Google Form.

Modified script:

When your script is modified, it becomes as follows.

var idVal = Formapp.createTextValidation();
                   .setHelpText("Inserisci un codice identificativo valido.");
                   .???

var pattern = "^" + idValues.map(function([e]) {return e}).filter(String).join("|") + "$";
var idVal = FormApp.createTextValidation().requireTextMatchesPattern(pattern).build();
form.addTextItem()
  .setHelpText("Inserisci un codice identificativo valido.")
  .setValidation(idVal)
  .setTitle("title");

Note:

In this modified script, it supposes that the values of idValues have the values you expect.
In this case, when the inputted values are the same with the list of values retrieved from the Spreadsheet, the values can be inputted. If you want to use contains, please modify pattern and idVal in above script as follows.
```
var pattern = idValues.map(function([e]) {return e}).filter(String).join("|");
var idVal = FormApp.createTextValidation().requireTextContainsPattern(pattern).build();
```

References:

Added:

For example, when you want to add the varidation to the existing items, how about the following script? In this case, please replace above script as follows.

From:

form.addTextItem()
  .setHelpText("Inserisci un codice identificativo valido.")
  .setValidation(idVal)
  .setTitle("title");

To:

form.getItemById(itemId).asTextItem()
  .setHelpText("Inserisci un codice identificativo valido.")
  .setValidation(idVal)
  .setTitle("title");

or

form.getItems()[0].asTextItem()
  .setHelpText("Inserisci un codice identificativo valido.")
  .setValidation(idVal)
  .setTitle("title");