AUTO QR CODE ATTENDANCE by DR BAHSHA
1. Google sheet add on QR CODE GENERATOR need to be added first.
2. Create Google forms
3. Insert a title
4. Choose Short answer question
5. Insert Staf id
6. Test view Google forms
7. Go back to form editor
8. Generate pre filled link
9. Click at the 3 dots icon at the top left corner
10. A new browser tab will open showing the pre-filled link form.
11. Open excel database
12. Copy any staf id from the list
13. Paste the staf id to the prefilled link form and click submit
14. Copy the prefilled link generated and paste to note pad.
15. Notice the urls are similar except for the last string.
16. The last string is the staf id that we entered.
17. Generate the response link
18. In the note pad replace the string "viewform" to "formResponse"
19. Copy the new URL into a browser tab.
20. The browser will show the Google form submitted page stating that "Your response has been recorded"
21. Next click on the responses tab in the form editor to view the submitted responses from the generated responses link.
22. Click on the Google sheet icon to generate the spreadsheet for the responses.
23. The spreadsheet will show the staf id and the time stamp
24. Adjust the spreadsheet setting to match Malaysia Local time.
25. Generate the mass response link via spreadsheet.
26. In the spreadsheet add new worksheet and rename as RESPONSE LINK
27. Open the excel file and copy the name list and id into the response link worksheet in. The Google sheet.
28. In the response link worksheet, insert a column. Name the column GENERIC URL
29. Go back to the note pad
30. Copy the generic URL from the response link that you have created earlier.
31. The generic URL is all the strings up until the LAST "=" symbol.
32. Paste the generic url into the cell D2
33. Copy the generic url into D COLUMN all of the name in the list by dragging in downward
34. In the cell E1, create another column and rename as response link
35. In this column is where a unique RESPONSE LINK from each staf will be created.
36. In THE CELL E2 ENTER the formula, =D2&B2
37. The formula will combine the generic URL with the staf id generating a unique response link for each staf.
38. Copy the formula to the rest of the rows by dragging it downward.
39. Click on the generated link to test the response link.
40. Generating THE QR CODE
41. In the spreadsheet we need to properly arrange the column so that we can correctly use the add on to generate the qr code
42. Create another column in F1 AND rename as NAME
43. Select column E and F response link and Name
44. Apply the qr codes generator to these cells.
45. Add on QR CODE GENERATOR, OPEN, click generate
46. All done with new QR CODE been generated.
47. In response link in column C tyoe the formula, =VLOOKUP(B2,'Response Link'! $B$2,$C2$24,2,0)
49. Drag down from B2 to copy the formula
50. ALL DONE.