How to make a simple authentication in Google Forms using Google apps script?

Question

Objective & Implementation

I created a questionnair using Google Forms. I want to authenticate users using a simple one time password that should be entered in the first question.

The password is stored inside a spreadsheet that contains only one cell. https://prnt.sc/r8i8q1

The script below reads the content of this cell and create a custom validator against the value of the first item of the form.

function validatePassword() {
  var passwordSpreadsheet = "[passwords_spreadsheet_id]";
  var ss = SpreadsheetApp.openById(passwordSpreadsheet);
  var passwordSheet = ss.getSheetByName("passwords");

  var form = FormApp.getActiveForm();
  var items = form.getItems();
  var item = items[0];

  if (item.getType() == 'TEXT') {
    var textItem = item.asTextItem();

    var namedRanges = passwordSheet.getNamedRanges();

    if (namedRanges.length > 0) {
      var range = namedRanges[0].getRange();
      var values = range.getValues();

      var currentPassword = values[0][0];

      var textValidation = FormApp.createTextValidation()
        .requireTextMatchesPattern(currentPassword)
        .build();
      textItem.setValidation(textValidation);
    }
  }
}

Current Situation

The above code is working as expected for one password, but, the problem is that I couldn't find a way to create a custom validator against range of values.

Questions

Is there any way to have this simple authentication mechanisim in Google Forms via Google Apps Script?
Is there a way to make this password a One Time Only password?
If (1.) and (2.) are not available, then, what is the best way to authenticate Google Forms?

What happens if someone submits a form without a valid password? — Cooper
– Cooper, Commented Feb 27, 2020 at 22:46
The current behavior is that the user will not have the ability to continue with the questionnaire. — mohjak
– mohjak, Commented Feb 28, 2020 at 9:45
I was just wondering how this works because they have to submit the form to provide the password and by that time all the data has been submitted. It's true, you can just choose not to do anything with it but how does user who just made a mistake by entering the wrong character no about his error? — Cooper
– Cooper, Commented Feb 28, 2020 at 17:56
@Cooper the process will be done before the form submission. the first question will be validated before the for is submitted. — mohjak
– mohjak, Commented Feb 28, 2020 at 20:00

Mateo Randwolf · Accepted Answer · 2020-03-02 08:56:17Z

1

Solution

Using Apps Script

The following piece of code will make a password verification according to what you right on the Spreadsheet as a password. It has comments to explain what line of code does:

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);
}

Using the UI

The esieast way to validate forms with password is to use the custom functionalities of Google forms. In your case you should follow these steps:

In the first section of your form only place a required short text answer.
Go to the three dots in the lower right part of the question and select Response Validation
Then in the options change them to Regular expression, Matches and introduce your desired regular expression (it could just be a String) and the right error message.

Here is an example of this in action:

I hope this has helped you. Let me know if you specifically need to get the passwords from the Spreadsheet. Let me know if you need anything else or if you did not understood something. :)

edited Mar 2, 2020 at 8:56

answered Feb 28, 2020 at 9:56

Mateo Randwolf

2,9021 gold badge8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mohjak Over a year ago

The script worked thank you! There is a concern here, is there a way to have this question as the first one when the script is triggered? because there is no mean to have this question as the last question in the form, otherwise, it will lose its initial goal.

Mateo Randwolf Over a year ago

create a new custom form and run the script in order to have this as the first question. Then add the rest of the questions you had in the form in the next section of the new form. Please consider accepting/upvoting my answer to let other users know that this has worked :D

mohjak Over a year ago

@MateoRandwolf I will try your suggestion.. could you please update your answer to have the create of a custom form instead of the current active form ... Thank you again! :D

mohjak Over a year ago

@Cooper if the user has the same password that stored in the spreadsheet, then, he can resubmit the form, otherwise, he cannot.

Sujal Sheth Over a year ago

if you use "validation" using regex match method, secret/password match is then embedded in google form element, can be easily visible by hacker by doing inspect element using browser developer tool. all regex match strings are part of form page loaded on browser

Collectives™ on Stack Overflow

How to make a simple authentication in Google Forms using Google apps script?

Objective & Implementation

Current Situation

Questions

1 Answer 1

Solution

Using Apps Script

Using the UI

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Objective & Implementation

Current Situation

Questions

1 Answer 1

Solution

Using Apps Script

Using the UI

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related