Google Forms - Multiple Unique One Time Pass Code - one form

[timbo343]

I might be trying to do the impossible here but is there a way that we can have one google form that when accessed the user has to put in a one time code that is unique to them and the code cannot be used again.

 

Behind the scenes i imagine there being a spreadsheet with 2 columns, USED and CODE. The codes would be totally random and given out to users and il all unused codes in the USED column say "NO"

 

For example John would be given 0001 and Amy would be given 0002.

 

Once the forms have been submitted, the code is no longer valid and USED column in the spreadsheet no says "YES".

 

I need something that will lookup to..

1. See if the codebis valid

2. If so.. does it have a USED value of "NO"? If true, allow the user to proceed. If false, deny entry.

 

We cannot use the option Only accept 1 response as this is to be anonymous.

Edited February 13, 2021 by timbo343

[Ditto]

Is this for a user logged in to their managed user account?

[Sanchez]

I did something a bit like this a while ago. I set up a form where the initial section only had a single mandatory text question (just called passcode). I then used text field validation to only allow valid codes to progress to the next section, essentially creating a password protected form.

 

To keep people from using other codes we randomly generated 6 character codes so the odds of someone guessing a valid code were slim.

 

We couldn't prevent the same code being used twice but if there was more than one response with the same code we just used the most recent and assumed it was the same person updating their responses.

[Ditto]

Just noticed the 'anonymous' line! Could you add a bit more background info as to the wider background. i.e. what are you doing once someone has the code and used it?

Other thoughts. Have you looked at the numerous Google Form add-ons? In the past I have used some Google scripting in Google sheets, which might offer a solution, albeit a bit techie, which is where an add-on helps if you don't have the programming skills.

[timbo343]

We are trying to make something work for Parent Governor elections where each parent needs to vote only once and it must be anonymous - obviously it can only be anonymous to a point but my thought about sending a one time unique code out would have worked but trying to get this to work is proving to be a bit more difficult than first thought.

 

@Sanchez how did you manage to allow only specific codes? I've looked at the response validation and i would have been great to map this validation to a list of codes in a spreadsheet so that any code in this list would allow access.

Edited February 21, 2021 by timbo343

[Sanchez]

I used a spreadsheet to generate the random codes in one column then in the next column used a simple concatenate formula to join all the codes in to one string with the codes being separated by | symbol. So the join formula was basically to join the cell above to the cell to the left with the resulting string getting longer in each row with the final row having all the codes in which was pasted into the validation field.

 

Been looking at using app script to update the form validation rule and remove used codes. I was hoping to do it purely in app script but it looks like you can't read an existing validation rule you can only update blindly over the top of the current rule so you would have to link the script to the answer spreadsheet to check for used codes and recreate the validation rule each time the form is submitted.

 

Might give it a try when I'm back at work this week as I'm trying to improve my app script skills.

[timbo343]

I used a spreadsheet to generate the random codes in one column then in the next column used a simple concatenate formula to join all the codes in to one string with the codes being separated by | symbol. So the join formula was basically to join the cell above to the cell to the left with the resulting string getting longer in each row with the final row having all the codes in which was pasted into the validation field.

 

Been looking at using app script to update the form validation rule and remove used codes. I was hoping to do it purely in app script but it looks like you can't read an existing validation rule you can only update blindly over the top of the current rule so you would have to link the script to the answer spreadsheet to check for used codes and recreate the validation rule each time the form is submitted.

 

Might give it a try when I'm back at work this week as I'm trying to improve my app script skills.

 

WOW! i guess that string was huge!? and it would have been a regex validation when setting this up in Google Forms?

 

We are looking at doing it with parents of a primary school for now but could lead on to secondary schools.

 

update: Oh My Days! That will do me nicely! Just got it to work! Thank You!

Edited February 22, 2021 by timbo343

[Sanchez]

WOW! i guess that string was huge!? and it would have been a regex validation when setting this up in Google Forms?

 

We are looking at doing it with parents of a primary school for now but could lead on to secondary schools.

 

update: Oh My Days! That will do me nicely! Just got it to work! Thank You!

 

Glad you got it to do what you wanted.

I played around with apps script and got it to remove used passcodes.

 

function updateValidCodes(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var passcodeSheet = SpreadsheetApp.getActive().getSheetByName('Passcodes').getDataRange().getValues();
 var responses = SpreadsheetApp.getActive().getSheetByName('Form responses 1').getDataRange().getValues();
 
 // Master list of all passcodes from spreadsheet on sheet 'Passcodes' in cell B1
 var allowed = passcodeSheet[0][1];

 //Cycle through form responses, select used passcodes and remove them from the 'allowed' regEx
 for (var i = 1; i < responses.length; i++) {
   var responseData = responses[i];
   // The following line assumes the passcode response is in the second column of the spreadsheet, update if needed
   var passcode = responseData[1];
   allowed = allowed.replace(passcode,"");
 }

 // Tidy up regEx by removing additional pipes
 allowed = allowed.replace('\|\|','|');
 
 // Open form and update the validation rule
 var form = FormApp.openById('enterFormIdHere');
 

 // Uncomment the following lines to get a the Id of the passcode question
 //var allItems = form.getItems();
 //for (var i in allItems) {console.log(allItems[i].getTitle() + ': ' + allItems[i].getId());}

 // Select the question you want to update
 var item = form.getItemById(2043396165).asTextItem();

 //Create validation rule
 var validation = FormApp.createTextValidation()
 .setHelpText('Please enter a valid (unused) code.')
 .requireTextMatchesPattern(allowed)
 .build();

 // Set validation rule
 item.setValidation(validation);
}

 

I added the script to the responses spreadsheet and set the trigger to 'on form submit', it's worked nicely in my testing.

Going to be using this for our next governor election. It's not perfect but more than good enough for our purposes.

[timbo343]

Glad you got it to do what you wanted.

I played around with apps script and got it to remove used passcodes.

 

function updateValidCodes(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var passcodeSheet = SpreadsheetApp.getActive().getSheetByName('Passcodes').getDataRange().getValues();
 var responses = SpreadsheetApp.getActive().getSheetByName('Form responses 1').getDataRange().getValues();
 
 // Master list of all passcodes from spreadsheet on sheet 'Passcodes' in cell B1
 var allowed = passcodeSheet[0][1];

 //Cycle through form responses, select used passcodes and remove them from the 'allowed' regEx
 for (var i = 1; i < responses.length; i++) {
   var responseData = responses[i];
   // The following line assumes the passcode response is in the second column of the spreadsheet, update if needed
   var passcode = responseData[1];
   allowed = allowed.replace(passcode,"");
 }

 // Tidy up regEx by removing additional pipes
 allowed = allowed.replace('\|\|','|');
 
 // Open form and update the validation rule
 var form = FormApp.openById('enterFormIdHere');
 

 // Uncomment the following lines to get a the Id of the passcode question
 //var allItems = form.getItems();
 //for (var i in allItems) {console.log(allItems[i].getTitle() + ': ' + allItems[i].getId());}

 // Select the question you want to update
 var item = form.getItemById(2043396165).asTextItem();

 //Create validation rule
 var validation = FormApp.createTextValidation()
 .setHelpText('Please enter a valid (unused) code.')
 .requireTextMatchesPattern(allowed)
 .build();

 // Set validation rule
 item.setValidation(validation);
}

 

I added the script to the responses spreadsheet and set the trigger to 'on form submit', it's worked nicely in my testing.

Going to be using this for our next governor election. It's not perfect but more than good enough for our purposes.

Oh nice... would love to try it to be honest!

 

Any advice on how to get it working?

Edited February 23, 2021 by timbo343

[Sanchez]

Quick Guide to getting it working, probably a bit over detailed but it might help anyone else that comes across this.

 

Start by setting up your form, The first section should just have your 'passcode' text question (set as required and without any validation rules) and nothing else (except maybe some introductory text). Create the rest of your form as required from section 2 onwards. Go into the settings and turn off 'Collect email addresses' this is not just for the form being anonymous but it would shift the columns in the result spreadsheet and that would require a minor code change. At this point you can create a response spreadsheet and open it up for editing.

 

In your spreadsheet you should have a sheet called 'Form responses 1' column A should be a Timestamp and B should be whatever you called your Passcode question with the rest of your questions following on.

You now need to create a second worksheet called 'Passcodes'. On this page you can create your passcodes in any why you like as long as the final string ends up in cell B1 (otherwise a minor code tweak is required). I used the following to generate the code in column A

=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)

and in column B1 I used this formula

=A1&"|"&B2

I then copied it down to create my super long string in B1. After the codes are generated I copy and paste values over column A so the Codes don't keep changing.

 

 

With the spreadsheet set up we can add the script. From the tools menu of the spreadsheet select script editor. Delete everything in the right-hand panel add paste in the code posted above.

 

 

At this point you will need to put in the form's ID in line 21 this is the random string of characters taken from the URL of the form when in edit mode. We now need to find the unique ID of the passcode question on the form, to do that we uncomment lines 25 and 26. Save the code with the disk icon (you can also name your Apps Script in the top-right corner) and run it. The first run will take longer and ask you to authorise access to needed Google services.

The first run will fail (as we haven't found and put the correct question Id in line 29) but the execution log should show the titles of all your questions followed by their Ids.

 

 

Replace the Id in line 29 with the correct one from your execution log and comment out lines 25 and 26 again. Run your code again and it should complete successfully. You can check that it's worked by looking at the passcode question on your form, it should now have a validation rule with all your passcodes applied.

 

The final step is to get the script to trigger every time the form is submitted. From the script editor select the 'alarm clock' icon on the left-hand side to open the triggers page and then choose 'Add Trigger' at the bottom right. The Add Trigger dialog will popup, the only change needed is to choose 'On form submit' for 'Select event type'. Save the trigger and your form should be ready. Test it to make sure passcodes are being removed (if you delete responses from the spreadsheet the codes will come back the next time the script is triggered this lets you 'reset' the passcodes after testing).

[timbo343]

Quick Guide to getting it working, probably a bit over detailed but it might help anyone else that comes across this.

 

Start by setting up your form, The first section should just have your 'passcode' text question (set as required and without any validation rules) and nothing else (except maybe some introductory text). Create the rest of your form as required from section 2 onwards. Go into the settings and turn off 'Collect email addresses' this is not just for the form being anonymous but it would shift the columns in the result spreadsheet and that would require a minor code change. At this point you can create a response spreadsheet and open it up for editing.

 

In your spreadsheet you should have a sheet called 'Form responses 1' column A should be a Timestamp and B should be whatever you called your Passcode question with the rest of your questions following on.

You now need to create a second worksheet called 'Passcodes'. On this page you can create your passcodes in any why you like as long as the final string ends up in cell B1 (otherwise a minor code tweak is required). I used the following to generate the code in column A

=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)

and in column B1 I used this formula

=A1&"|"&B2

I then copied it down to create my super long string in B1. After the codes are generated I copy and paste values over column A so the Codes don't keep changing.

 

[ATTACH=CONFIG]60984[/ATTACH]

 

With the spreadsheet set up we can add the script. From the tools menu of the spreadsheet select script editor. Delete everything in the right-hand panel add paste in the code posted above.

 

[ATTACH=CONFIG]60985[/ATTACH]

 

At this point you will need to put in the form's ID in line 21 this is the random string of characters taken from the URL of the form when in edit mode. We now need to find the unique ID of the passcode question on the form, to do that we uncomment lines 25 and 26. Save the code with the disk icon (you can also name your Apps Script in the top-right corner) and run it. The first run will take longer and ask you to authorise access to needed Google services.

The first run will fail (as we haven't found and put the correct question Id in line 29) but the execution log should show the titles of all your questions followed by their Ids.

 

[ATTACH=CONFIG]60986[/ATTACH]

 

Replace the Id in line 29 with the correct one from your execution log and comment out lines 25 and 26 again. Run your code again and it should complete successfully. You can check that it's worked by looking at the passcode question on your form, it should now have a validation rule with all your passcodes applied.

 

The final step is to get the script to trigger every time the form is submitted. From the script editor select the 'alarm clock' icon on the left-hand side to open the triggers page and then choose 'Add Trigger' at the bottom right. The Add Trigger dialog will popup, the only change needed is to choose 'On form submit' for 'Select event type'. Save the trigger and your form should be ready. Test it to make sure passcodes are being removed (if you delete responses from the spreadsheet the codes will come back the next time the script is triggered this lets you 'reset' the passcodes after testing).

Thank you soooo much!!!

 

The only thing i had to do was to leave line 25 and 26 uncommented but its working a treat!!

 

If the codes are changed the script editor needs to be re-ran and saved.

Edited February 28, 2021 by timbo343

[Sanchez]

Thank you soooo much!!!

 

The only thing i had to do was to leave line 25 and 26 uncommented but its working a treat!!

 

If the codes are changed the script editor needs to be re-ran and saved.

I'm glad you found it useful. I'm just starting to get my head around apps script and love the many ways it can manipulate the Google apps. Just finished setting up a script that dynamically generates our daily bulletin doc from scratch (from form submissions) really chuffed with that one!

[timbo343]

I'm glad you found it useful. I'm just starting to get my head around apps script and love the many ways it can manipulate the Google apps. Just finished setting up a script that dynamically generates our daily bulletin doc from scratch (from form submissions) really chuffed with that one!

Excellent!!

 

To be hoenst, if you know what to do with the code the world is at your feet with it all.

[sejsejsej]

This is really great and I need it to work on my end. I'm trying but can someone help me with the below, I believe I had follow the steps properly but I'm assuming I might've missed something since this is what I am getting from the Execution log.

[Ditto]

Do you need to replace 'enterFormIdHere' with the actual FormId in line 21 - a string of alphanumerics?

[timbo343]

This is really great and I need it to work on my end. I'm trying but can someone help me with the below, I believe I had follow the steps properly but I'm assuming I might've missed something since this is what I am getting from the Execution log.

[ATTACH=CONFIG]62080[/ATTACH]

 

@Ditto is correct, line 21 needs your Form's ID number.

 

Edited June 8, 2021 by timbo343

[timbo343]

Found a slight issue with the passcodes - it would appear the regex expression only allows for around 4800 characters which when used in a Secondary for lots of users, the voting form doesn't.

 

Basically an 8 digit code only allows for around 533 possible votes because the expression for each code has 9 characters, why is it 9? because the | is counted as a character.

 

For our parents to cast votes using 1 form, we need to be able to have a possible 2000 passcodes. Even with a 2 digit code, it doesn't allow for the total number of passcodes that we are looking for due to 3 characters in use for each passcode so.. 4800 / 3 = 1600 codes.

 

If anyone knows a way round this, i would like to know how.

[soqlee]

Sanchez, this is brilliant! Thanks so much. I got it to work on my form. Your Guide is excellent; appreciate the detail.

 

 

Edited November 3, 2021 by soqlee

[gh5000]

Found a slight issue with the passcodes - it would appear the regex expression only allows for around 4800 characters which when used in a Secondary for lots of users, the voting form doesn't.

 

Basically an 8 digit code only allows for around 533 possible votes because the expression for each code has 9 characters, why is it 9? because the | is counted as a character.

 

For our parents to cast votes using 1 form, we need to be able to have a possible 2000 passcodes. Even with a 2 digit code, it doesn't allow for the total number of passcodes that we are looking for due to 3 characters in use for each passcode so.. 4800 / 3 = 1600 codes.

 

If anyone knows a way round this, i would like to know how.

I would possibly think about changing your thinking around the process and using form validation. It's a great idea but you're getting into some limitations.

 

If you aren't already I would use the Google forms prefilled link feature to generate a unique link for each parent. Because you are prefilling the Id field for them you can make it pretty long and unguessable.

 

Then on the form responses you only look for responses that have an Id that is in your table you used to generate the mail merge. If there are two+ take the latest one.

[Sanchez]

Found a slight issue with the passcodes - it would appear the regex expression only allows for around 4800 characters which when used in a Secondary for lots of users, the voting form doesn't.

 

Basically an 8 digit code only allows for around 533 possible votes because the expression for each code has 9 characters, why is it 9? because the | is counted as a character.

 

For our parents to cast votes using 1 form, we need to be able to have a possible 2000 passcodes. Even with a 2 digit code, it doesn't allow for the total number of passcodes that we are looking for due to 3 characters in use for each passcode so.. 4800 / 3 = 1600 codes.

 

If anyone knows a way round this, i would like to know how.

In testing I haven't seen the same behaviour. On my test setup I've just added 2000 passcodes and the regex is complete. I copied it into a doc and the word count comes back at 2000. Tried a few codes and they all work for me.

Edited November 4, 2021 by Sanchez

[Jestag]

From line 3 down, it not working for me...

[timbo343]

From line 3 down, it not working for me...

I've got another form to setup this week so will see if it still works for me and post back.

[soqlee]

The app script was working and removing used passcodes. Then it stopped and I got a this error message. Any help with this appreciated.

 

Exception: You do not have permission to call FormApp.openById. Required permissions: https://www.googleapis.com/auth/forms

updateValidCodes

@ Password.gs:21

[Sanchez]

I'll take a look at the code tomorrow and see if anything needs updating.

[timbo343]

For me, the original process still works which i have just tested today and i don't get any errors. The only thing i had to do was to allow the permission to the script editor.