How to make a custom validation function like requireTextMatchesPattern(pattern) function using Google apps script?

Question

Final Objective

The final objective is to have the ability to define custom validator for Google Forms item like requireTextMatchesPattern(pattern).

Use Case

This custom validator will be used for example to compare what the user enters in the form item field with more than one value. or at least to have a custom functionality to execute when the user enters not valid data in the field.

Example

I have 3 participants, I want to make a simple authentication mechanism to make sure that the targeted audiences are going to participate. I have a spreadsheet that contains 3 passwords. The first question in the Form will require the user to enter a password. If the password doesn't match with one of the stored passwords in the spreadsheet, then, a validation message will appear to the user.

Partially Solution

Based on this question we can make a simple validation using requireTextMatchesPattern validator or directly from UI. The problem is that this validator limits the compare values to one.

function validatePassword() {
  // Create new custom form
  var form = FormApp.create('New Form');
  var ss = SpreadsheetApp.openById('SHEETID');
  var password = ss.getSheetByName('SHEETNAME').getRange('A1').getValue();

  // Create first question to check the password, it must be required so that the user does not have access to the rest
  // of the form if failed to log in
  var item = form.addTextItem().setRequired(true);
  item.setTitle('TEST TITLE');

  // Create validation for this question matching the password that we got from the sheet
  var textValidation = FormApp.createTextValidation()
    .setHelpText('You must enter the right password')
    .requireTextMatchesPattern(password)
    .build();
  item.setValidation(textValidation);
}

What I am trying to do is to replace the .requireTextMatchesPattern(password) with a call to a custom validation function that does some validation process and then returns the type of TextValidationBuilder.

Research

I found this source code which defines an interface of TextValidationBuilder. I don't know if it is the key to accomplish the main objective.

Thanks!

user_11058516 · Answer

What I can understand from your question is that for example, you have 3 passwords (words) in 3 cells (Ex: from A1 to A3). Hence, you want to use them as conditions for a form and the issue ahead of you for the moment is that you are only able to do it with only one password (word).

As you probably noticed, the requireTextMatchesPattern(pattern)'s argument is a pattern, therefore you can have a Regex structured as word1|word2|word3, which will verify if the 3 passwords are the correct ones. Your code will look like this now:

function validatePassword() {
  // Create new custom form
  var form = FormApp.create('New Form');
  var ss = SpreadsheetApp.openById('your-sheet-id');
  var passwords = ss.getSheetByName('Sheet1').getRange('A1:A3').getValues();
  // Ex passwords: asd, 123, asd123
  const conditions = passwords.map(element => `${element}`).join('|')
  // Create first question to check the password, it must be required so that the user does not have access to the rest
  // of the form if failed to log in
  var item = form.addTextItem().setRequired(true);
  item.setTitle('TEST TITLE');
  // Create valid ation for this question matching the password that we got from the sheet
  var textValidation = FormApp.createTextValidation()
    .setHelpText('You must enter the right password')
    .requireTextMatchesPattern(conditions)
    .build();
  item.setValidation(textValidation);
}

user_13766699 · Answer

I was nodding enough to solve a problem I had, and while writing a question right here, I realized that I solved my problem hahaha.

I was looking for how to update the restrictions (validation) of a form based on a sheet of spreadsheets, which was updated with each form submission. Of course, I set it as a trigger when the spreadsheets change.

It is a bit complicated to explain, but basically the values recorded in a column are taken in other spreadsheet and "subtracted" from the "accepted" column, then these values are concatenated with the function (textjoin ("|",1,A:A)). Finally, this resulting data is entered into a variable ('allowed') which is entered into .requireTextMatchesPattern (allowed).

This is the simplest way I found for my problem: Update the allowed list to avoid double voting or participation, and at the same time limit the participants with some known information (telephone number, identifier, last name, etc.)

I am sure that with Scripts there may be other solutions, but using spreadsheets seems easier and simpler to me.

Anyway, I leave the formula here in case it works for someone:

function test(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[2];

  var allowed = sheet.getRange("B1").getValue();

  var form = FormApp.openById('urlID');
  var item = form.getItemById(388494367).asTextItem();

  var validation = FormApp.createTextValidation()
  .requireTextMatchesPattern(allowed)
  .build();
  item.setValidation(validation);
}