google-apps-script google-sheets google-forms

How can I create a conditional path to another section in google form base on what user put in their short answer?

I'm trying to create a google form that branch off to another section if their phone number or email matched what I have on a linked google sheet. I've writting a script that tries to verify user phone and email, but it doesn't seem to work. When I click "Next" using invalid phone and email, it still letting it pass to the section after it, not branching it off to another section. This is my script, any advice is welcome. If there's simplier way I can achive this, feel free to let me know.

// Main function that calls other functions depends on user input
function validateSheet(e) {
  var form = FormApp.getActiveForm();
  var formResponses = form.getResponses();
  var lastResponse = formResponses[formResponses.length - 1];

  var itemResponses = lastResponse.getItemResponses();

  var existingUser = itemResponses[0].getResponse();

  console.log("validateSheet function is running");

  logItemIds();

if (existingUser === "Existing user"){

    var sectionID = null;
    // User phone input
    var phoneNumber = itemResponses[1].getResponse();
    var phoneColumnName = "Phone number";

    // User email input
    var email = itemResponses[2].getResponse();
    var emailColumnName = "Email";

    if (phoneNumber != undefined){
      var validatePhone = checkPhoneNumber(phoneNumber, phoneColumnName);
    }
    else {
      var validatePhone = false;
    }

    if (validateEmail != undefined){
      var validateEmail = checkEmail(email, emailColumnName)
    }
    else{
      var validateEmail = false;
    }
 
    if (validatePhone || validateEmail){
     // go to information section
     sectionID = 470515527;
     jumpToSection(sectionID);
    }
    else {
      // go to registration.
      sectionID = 1348206646;
      jumpToSection(sectionID);
    }
  }
  else{
    sectionID = 1189173207;
    jumpToSection(sectionID);
  }
}

// Checks for user phone number
function checkPhoneNumber(phoneNumber, phoneColumnName){
  console.log("checkPhoneNumber function is running");
  console.log("sheetURL:", sheetURL);

  var sheet = SpreadsheetApp.openByUrl(sheetURL).getSheetByName("Form1");
  var data = sheet.getDataRange().getValues();

  var columnName = data[0];
  var columnNumber = columnName.indexOf(phoneColumnName);

  console.log("checkPhoneNumber function is running");
  console.log("sheetURL:", sheetURL);

  for (var i = 1; i < data.length; i++){
    var row = data[i];

    if (row[columnNumber] === phoneNumber) {
      return true;
    }
  }

  return false;  
}

// Check for user email
function checkEmail(email, emailColumnName){

  console.log("checkEmail function is running");
  console.log("sheetURL:", sheetURL); // add this line

  var sheet = SpreadsheetApp.openByUrl(sheetURL).getSheetByName("Form1");
  var data = sheet.getDataRange().getValues();

  var columnName = data[0];
  var columnNumber = columnName.indexOf(emailColumnName);


  for (var i = 1; i < data.length; i++){
    var row = data[i];
    if (row[columnNumber] === email){
      return true;
    }
  }

  return false;
}

// Jumps to the section with given section ID
function jumpToSection(sectionID) {

  // logItemIds();
  console.log("Trying to jump to: " + sectionID);

  var form = FormApp.getActiveForm();
  var item = form.getItemById(sectionID);
  var items = form.getItems();
  var index = items.indexOf(item);

  for (var i = index + 1; i < items.length; i++) {
    var nextItem = items[i];
    if (nextItem.getType() == FormApp.ItemType.SECTION_HEADER || (nextItem.getType() == FormApp.ItemType.PAGE_BREAK && nextItem.asPageBreakItem().getPageNavigationType() == FormApp.PageNavigationType.CONTINUE_LEGACY)) {

      var response = form.createResponse();
      var nextPageItems = nextItem.getType() == FormApp.ItemType.SECTION_HEADER ? nextItem.asSectionHeaderItem().getParent().getItems() : (nextItem.getType() == FormApp.ItemType.PAGE_BREAK ? nextItem.asPageBreakItem().getPageNavigation().getPage().getItems() : null);
      
      if(nextPageItems) {
        response.withItemResponse(nextPageItems[0].asListItem().createResponse()); // Jump to the specified section
        response.setDestination(FormApp.DestinationType.NEXT);
        response.submit(); // Submit the response to jump to the specified section
      } else {
        console.log("Error: Unable to jump to next section");
      }
      
      break;
    }
  }

}

I've been able to branch off to a different section with multiple-choice questions. For example, if they select "New user" or "Existing user". But I can't seem to figure out how to do it with short answers like phone number or email input.

Solution

You have a Google Form containing a question which separates "Existing users" and "New Users".

In the case of "Existing users", you have questions to collect their email address and their phone number.
You want to validate these responses against data held in a Google Sheet as the responses are received and before the form is SUBMITTED.

Google Forms provides several methods of real-time, online, pre-submission validation. However, it NOT possible to provide real-time, online, pre-submission validation of Form answer against a spreadsheet.

BUT...

one of the available validation methods is Regex.
Validation patterns can be uploaded from a spreadsheet.
As "Existing user" values are added to the spreadsheet, updating the form removes "old" validation pattern and replaces it with the "new" pattern.

The following is an example of updating validation of phone numbers for Existing Users in a Google Form.

Spreadsheet Data

Existing User: Phone question - BEFORE Regex

Existing User: Phone question - AFTER Regex

There are two functions:

updateValidation(): updates Regex for a given form for a given question. The script can easily be adapted to update validation patterns for other questions.
getItems(): a utility that extracts a list of the questions. This helps with identifying the Item ID for any specific question.

/*
// this script is bound to a Google Sheet
// the sheet contains the email addresses and phone numbers to be used for validation
*/
function updateValidation() {
  
  // Open a form by ID and add a new text item.
  var formID = "insert form id"
  var form = FormApp.openById(formID);

  // EXISTING PHONE NUMBERS
  // get the existing phone numbers in Column C
  var sheetName = "Sheet1" 
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName(sheetName)
  var data = sheet.getDataRange().getValues()
  // return just the data for Column C - zero-based
  var phonedata = (data.map(function(e){return e[2]}))
  // drop the header
  var phones = phonedata.shift()
  // build the regex variable
  var phoneRegex = "("+phonedata.join("|")+")"
  // Logger.log(phoneRegex) // DEBUG
  // get the Existing phone question
  var itemID = 1750232304
  var existingPhoneItem = form.getItemById(itemID).asTextItem()
  // create the validation  and set the validation
  var textValidation = FormApp.createTextValidation()
  .setHelpText('10 digit number')
  .requireTextMatchesPattern(phoneRegex)
  .build()
  existingPhoneItem.setValidation(textValidation);

}

/*
// use this utility to list the questions in the form
// and identify the item IDs that relate to specific questions
*/
function getItems(){

  // Open a form by ID and add a new text item.
  var formID = "1KZhyYkqtuESsYBvkphoj3KFashY7M1aBxqfRWVz3K4M"
  var form = FormApp.openById(formID);
  // get the items
  var items = form.getItems()

  for (var i=0;i<items.length;i++){
    Logger.log("Title: "+items[i].getTitle()+", ID: "+items[i].getId()+", Type: "+items[i].getType())
    // phone id = 1750232304
    // email ID = 2140411732
  }
}