Create Certificates from Google Form Responses
With Document Studio, you can generate certificates from Google Form responses. The presentation files can be created in Microsoft Office PowerPoint, PDF or Google Slides format and your slides are automatically saved to Google Drive for easy collaboration.
Google Forms Quiz Certificate
For this example, we have a Google Forms quiz that collects responses from students. Each question in the quiz is assigned points and the score is released when the respondent submits the form. The responses are stored in a Google Sheet.
The certificate is issued to the student only if they score more than 80% in the quiz.
Configure Response Google Sheet
Open the Google Sheet that is set as the response destination of your Google Form. You'll notice that Google Forms has automatically added a Score column in the sheet that contains the total quiz points earned by the student.
We'll add another column titled Quiz Percentage that calculates the percentage of points earned by the respondent using an Array Formula in the header row. Assuming that the Score is in the B column of sheet, copy-paste the following formula in the first cell of any empty column of the sheet.
=ArrayFormula(IF(ROW(B:B)=1, "Quiz Percentage", IF(ISBLANK(B:B), "", ROUND(B:B*100 / 30, 1))))
The formula looks at the row number of cell and if it is the first row, it will set the value of the cell to Quiz Percentage. If the cell is not the first row, it will calculate the percentage of points earned by the respondent and round it to one decimal place. The ISBLANK check is needed to skip rows where the score is not available.
Create Certificate Template in Google Slides
Create a new Google Slide presentation and add a new slide with variable fields. For instance, if you wish to include the student's name in the certificate, you can create a new text box and add {{ Full Name }} in the box. We'll also add variable fields for the date when the form was submitted (Timestamp) and the percentage score obtained. Here's how our template looks like:
Create Slides from Google Form Responses
Install the Document Studio add-on and launch the add-on inside your Google Forms. Give your workflow a descriptive name, like School Quiz Certificate Generator, and click on Continue to move to the conditions page.
Since we want to create a certificate for students who score more than 80% in the quiz, we'll add a condition to the workflow.
Configure Certificate Generator
Click Continue and then choose Create File from the list of available tasks. Here you need to pick the Google Slides template that you created in the previous step for the Document Template field.
Specify a folder in Google Drive where the generated files are uploaded. You can also chose to save the certificate files in custom folders that are dynamically created from user's answers in the Google Forms.
If you wish to send this certificate to the student's email address, check the Attach this file in email option and add another Email task to send the email. The certificates can be exported as PDF, PowerPoint or Google Slides.
You may also use Scriptlets in any of the input text field to generate dynamic content.
Activate the Certificate Workflow
Click the Preview button to generate a dummy certificate based on data in the second row of the response Google Sheet. You can open your Google Drive to view the generated file. Please note that conditions are not checked when generating the preview certificate.
Click Done to locally apply your changes. On the next screen, turn on the Run on Form Submit option and the workflow will run whenever a new Google Form submission is received.
Click the Save button and the workflow will now appear in the workflow dashboard where you can edit, delete or manually run the workflow.
You can also embed dynamic QR Codes, Barcodes, Google Drawings, Images, Charts and Graphs in the presentation slides.