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

[Sanchez]

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

I've done some checking and it's still working fine for me too.

 

If you're having a problem on line 3 I would double check the name of the second sheet in your form responses spreadsheet is Passcodes (case sensitive) as this it what line 3 is looking for.

[Sanchez]

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

It looks like the script has permission to access sheets but not forms (those are the only two api's needed by the script).

 

Timbo343 mentioned needing to allow permissions again in the script editor so that might be something you need to do. Just run the script in the script editor and you should be prompted to give updated permissions. I'm guessing Google has changed something in the api which has triggered the need to approve it again.

[soqlee]

So I have a mail merge script included in the spreadsheet as well as the passcode script.

 

The passcode script is asking for the "https://www.googleapis.com/auth/forms.currentonly" permission rather than just the "https://www.googleapis.com/auth/forms" permission. It's doing this is because the mail merge script has a tag in it that causes the script to only ask for "currentonly" permissions to prevent scripts from asking for more permission than they need, however in this case it's also blocking the form submit passcode script from gaining the permissions it needs.

 

I think this is what's going on. Looking into how to get around this. In the meantime, good to hear that script is working.

[soqlee]

UPDATE...solved...Sanchez passcode script works....the issue had to do with permissions. A modification was needed to the apsscript.json manifest file to add the "oauthScopes" field to the json file with all of the permissions the script was asking for, then modified the forms permission to be for all forms rather than the current form.

[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.

 

@Sanchez

 

Could we take a look at the script again please.

 

It seems when the script is running the regex is being added incorrectly to the question.

 

It's putting in multiple | | | so the regex for the response validation is accepting anything.

 

For example, the list of setup codes is abcdef|123456789|poiuiut but when the script is ran, it's adding it to the question as abcdef|||123456789|poiuiut or abcdef||123456789|poiuiut however the extra | are random.

 

Could you help us out on this please?

[timbo343]

So, i've found the issue.

 

Line 14 in relation to

allowed = allowed.replace(passcode,"");

 

Once the passcode has been used, the script is replacing a used passcode with nothing so we are getting lots of || or ||| or |||| next to each other.

 

replacing this line with

allowed = allowed.replace(passcode,"*RANDOM_TEXT_HERE*");

stops the || or ||| or |||| and so on. Instead it creates |*RANDOM_TEXT_HERE*|

 

So the updated script looks like this:

 

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,"enterRandomWordsOrCharactersHere");
 }

 // 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(QuestionID).asTextItem();

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

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

Edited November 11, 2022 by timbo343

[konan2020]

I know its very old post, but the code didnt work for me, only the first password used to validate the form, not all the list also didnt remove the used password, any more explanation how to make it work?

[timbo343]

I know its very old post, but the code didnt work for me, only the first password used to validate the form, not all the list also didnt remove the used password, any more explanation how to make it work?

Have you got the triggers set up?

 

If you'd like me to take a look at the sheet / form let me know.

[konan2020]

Have you got the triggers set up?

 

If you'd like me to take a look at the sheet / form let me know.

 

thanks a lot for your feedback

i figured out that the problem I didnt make the codes in the form of regex, is there a way to make it as a list so can be more readable for better tracing?

or can make it with unique user and password?

[timbo343]

thanks a lot for your feedback

i figured out that the problem I didnt make the codes in the form of regex, is there a way to make it as a list so can be more readable for better tracing?

or can make it with unique user and password?

Good to hear you solved the issue.

 

To answer your question, i'm not sure as the application / script was built to parent governor anonymous one time voting so we didn't have to send out letters and envolopes to all parents.

 

The list you are referring to should be self populating, when a code has been used, i get the script to change to something random like CodeBeenUsed-DoNotUse with random numbers on the end so i can see the script / codes being used.

 

As for unique user and password, im sure that could work maybe and would need some kind of lookup value to make sure that - for example question 1 and question 2 matched each other.

[konan2020]

Good to hear you solved the issue.

 

To answer your question, i'm not sure as the application / script was built to parent governor anonymous one time voting so we didn't have to send out letters and envolopes to all parents.

 

The list you are referring to should be self populating, when a code has been used, i get the script to change to something random like CodeBeenUsed-DoNotUse with random numbers on the end so i can see the script / codes being used.

 

As for unique user and password, im sure that could work maybe and would need some kind of lookup value to make sure that - for example question 1 and question 2 matched each other.

 

really thanks for your feedback, is there any tutorial to use the lookup value with app script? so can be done automatically?

 

also i will use static codes but to be seen as a list for more friendly analysis i guess will be hard

[timbo343]

really thanks for your feedback, is there any tutorial to use the lookup value with app script? so can be done automatically?

 

also i will use static codes but to be seen as a list for more friendly analysis i guess will be hard

 

I'll see if i can get something typed up over the weekend for you.

[konan2020]

I'll see if i can get something typed up over the weekend for you.

thanks a lot, waiting for that, also if have tutorials so i can try too will be great