Form validation based on values from a spreadsheet

user_10132804 0
Viewed 2552

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.

google-apps-script
1 Answers
  • 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.

From: 
var idVal = Formapp.createTextValidation();
                   .setHelpText("Inserisci un codice identificativo valido.");
                   .???
To: 
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");
user_71086530
Related