Student Automated Report Card Request using Google Form

This tutorial will teach you how to make a automate the report card distribution on your students. This will lesson will require you the following 

1. Create a Google form Request - A Google form where students will enter their Name and LRN. Once the LRN is entered, it will automatically trigger the form to retrived the Grades from SUMMARY GRADE SHEET of the adviser which is also located in Google Drive. 

2. SUMMARY GRADES WORKOOK - A Google Sheet Workbook containing the LRN, NAMES, and SUMMARY GRADES of the students. 

3. A REPORT CARD TEMPLATE - A Google Sheet template of official Report Card Card. 

Let us first Create the Google Form. 

Create a Google Form. A request form where students request a copy of their card in PDF format that will automatically send to their email

Steps: 

1. Log-in to your Google Account. (DepEd QC Google Account) 

2. Access our School Repository for Grades located in our Shared Drive. 

3. Right click on your Folder and Select Google Form. Make sure that the Google Form, Destination Response Sheet, Summary Grades Sheet and Report Card Sheet is located in one Google Drive Folder. 

4. In Google Form, type the title and the description of the form. 

5. Create a new question (Short Answer) for Last Name and make it a required question

6. Repeat it to First Name, Middle Name, LRN and Parent's Email. Make all required. 

7. Go to Responses and Click the Google Sheet icon. 

8. Select the default response destination and click Create. 

9. In response destination sheet, add new worksheet by clicking the Add Sheet button. 

10. Right click on new sheet then select Rename. 

11. Rename the new Sheet as variables. 

12 In cell A3, type SUMMARY and in cell B3 you will need the URL of the SUMMARY GRADES 

13. To get the URL, go back to your SUMMRAY FINAL GRADES WORKBOOK and open it. 

14. Right click on the URL of the SUMMRY FINAL GRADES WORKBOOK 

15. Select the URL and COPY it. 

16. Go back to variables sheet of response destination sheet and paste the URL with double quotes "". Note that the URL is now on cell B3. 

17. In the response destination sheet, add columns for 

AGE
SEX
FIL1

ENG1

MATH1

SCI1

AP1

ESP1

TLE1

MAPEH1

MUS1

ART1

PE1

HEA1

GENAVE1

FIL2

ENG2

MATH2

SCI2

AP2

ESP2

TLE2

MAPEH2

MUS2

ART2

PE2

HEA2

GENAVE2

18.  These new columns will hold the data for each grades per subject that will be coming from SUMMARY FINAL GRADES. 

19. Go back to SUMMARY FINAL GRADES and highlight the data starting from LRN column up to the last column where the grades are located. 

20. Now let us create the formula so we can automatically import the data needed in SUMMARY GRADES FINAL every time a student answer the form. This formula will be triggered when the correct LRN is encoded. 

First,  we will use a VLOOKUP formula. combined with IMPORTRANGE. Here the column F is where the LRN will appear. 

21 Once the formula is encoded, you need to select Allow access so the data coming from SUMMARY FINAL GRADES  will be imported. 

22. Next we need to convert the formula into an ArrayFormula by selecting the formula and press CTRL SHIFT ENTER. In this waym the formula will be automatically applied to the next cell below. 

23.  Next let's add IF, NOT, ISBLANK formula so that if there will be no data in column F2, it will not show an ERROR . 

24. Open Report Card Workbook 

25. Setup the data needed to be mail merge starting from the personal information of the student. Make sure that all are enclosed with <<data>>  

26. And the Subject Area needed. Make sure that all are enclosed with <<data>> 

27. Go back to destination response sheet and click Extension, select Autocrat and click Launch. 

28. In Autocrat, click NEW JOB. 

29. Type the Job Name and click NEXT. 

30. Choose the template you made by clicking From drive then click Next. 

31. Select your Report Card you set up earlier. 

32, Once selected, click Next. 

33. Make sure to map correctly your data from destination response sheet to your report card sheet then click next. 

34. In the File settings, click the light blue button arrow to show the tags. 

35. Select the needed tag. 

36. Select the filename bos and right click on it then select Paste. 

37. Select PDF type and click Next. 

38. In the Choose destination folder, click Choose folder. 

39.  Select the destination folder for your Report Card and click Select. 

40. Once the folder is selected, click Next. 

41. In Share docs and send emails, select Yes on Shared doc and Select No on Allow collaborators to re-share and Send from generic no-reply address then click Next. 

42. Below, input the email address in the email To box and input the Subject and Message then click Next. 

43. In the Add/remove job triggers, select No and Save it. 

44. When  your grades are readt for distribution, and the students  are ready to answer your Google form, go to Manage triggers, 

45. Select Enable Form Triggers. This will activate the mail merge once student answer your Google Form. 

46. An email will be automatically sent to students email and their parent's email 

47. The report card can be open using PDF reader. Students can either download it or save it on their Google Drive.