CODEWITHSUNDEEP
google-apps-scriptconditional-statementsgoogle-forms
schnydszch
schnydszch

Reputation: 435

Google Forms App Script conditional statements

I'm trying to figure out how to do conditional statements tilizing app script in Google form with my already existing form. I have an email field which supposedly checks from a google sheet values in one column (email column). If what has been inputted into the email field exists in the Google sheet column for email, an alert prompts that the email already exists, help text will also show with a message like "email already exist". If the Ok button of the alert prompt is clicked, user can now go back into the Google form and edit their answers into the email field, and if the email address inputted does not exist in the Google sheet, nothing happens, and the user can proceed answering the form and lastly submit the form. I have tried textvalidation but it seems text validation would only be able to answer one part of what I wanted to do with my form - show a help text. Below is my working app script:

var sheet = SpreadsheetApp.openById("IdOfMyGoogleSheet");    

function validationTest() {
    var data = sheet.getRange("Form Responses 1!F2:F").getValues();

    var form = FormApp.openById('formID');
    var item = form.getItemById(itemID)asTextItem();;

    var textValidation = FormApp.createTextValidation()
       .setHelpText("Email already exist")
       .requireTextContainsPattern(data)
       .build();
    
       item.setValidation(textValidation);

}

Thanks in advance!

Upvotes: 0

Views: 1598

Answers (1)

Martí
Martí

Reputation: 2861

The first this to note is that a pattern is a regular expression. You also need to use requireTextDoesNotMatchPattern instead of requireTextContainsPattern. Here is what I've tested:

function setPattern() {
  // Get the firm item
  const form = FormApp.openById('form ID')
  const item = form.getItemById('itemID').asTextItem()

  // Get the list of values
  const spreadsheet = SpreadsheetApp.openById('Spreadsheet ID')
  const sheet = spreadsheet.getSheetByName('Form Responses 1')
  const values = sheet.getRange(1, 1, sheet.getLastRow(), 1)
    .getValues()
    .flat()

  // Transform the values into a regex expression (pattern)
  const pattern = `^(${values.map(_escapeRegex).join('|')})$`

  // Contruct and set validation
  const validation = FormApp.createTextValidation()
    .setHelpText("Email already exist")
    .requireTextDoesNotMatchPattern(pattern)
    .build()
  item.setValidation(validation)
}

function _escapeRegex(str) {
  return str.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')
}

Note that this allows users to add non-emails to the field, but not to repeat values.

References

Upvotes: 1

Related Questions