This tutorial explains how to bulk generate personalized certificates using Google Sheets. One certificate template will be used to create certificates with different names, which is quite similar to mail merge. The only difference is you will be creating certificates and not emails. Though natively it is not possible, there is another simple way to do this. Whether you want to create certificates for students or employees or someone else, it can be done easily.
To bulk create personalized certificates, you also need to use Google Slides and a free add-on for Google Sheets. Google Slides helps to select a certificate template. You can either create a new certificate template using the preferred theme or use the existing one. Google Sheets is needed to create the list of names that will be added in the certificates. And the add-on helps to add those names from that Google Sheets file to create certificates in bulk. For each certificate, a separate Google Slide is generated which is saved in the destination folder set by you. A separate and unique URL is also generated for each certificate. You can use that URL to open the certificate in Google Slides and then download the certificate as PDF, PPTX, JPG, PNG, or other supported file formats.
How To Bulk Generate Personalized Certificates Using Google Sheets?
Step 1: First of all, open Google Slides and then a certificate template. I’m assuming that you already have a template (or you need to create one). Open the template and add the conditional tag for the name. If you want to show first and last name in the certificates, then add <<Name>> tag. If you want to add first name only in the certificate, then add the tag as <<First Name>>. Rest of the content will remain the same for certificates so you don’t need to change anything. After that, give a name to the certificate template.
Step 2: Create a new Google Slides file, and write “Name” (for first and last names) or simply “First Name” (depending upon the conditional tag set by you in certificate template in step 1) in the first column. After that, add the name of those in that column whom you need to issue the certificates. After that, give name to your Google Slides file. The file name must be the same as that of certificate template.
Step 3: Now open the homepage of AutoCrat add-on Google Sheets, install it, and permit it to run.
Step 4: In your Google Sheets file (which you created in step 2), click on Add-ons menu, select autoCrat, and click on Launch option. This will open add-on wizard.
Step 5: There, you need to create a new merge job. So, use the “NEW JOB” button.
Step 6: Add a name to your job and then move to the next step.
Step 7: Click on “From drive” option and then add the same Google Slides certificate template that you created in step 2.
Step 8: Map source data to the template. That means you need to check if the first “Header row” is set to row 1 (First Name or Name), “header data” is set from row 2, Merge tab is selected to “Sheet1“, maps to column is set to Name (Header Column), etc. Basically, you have to set these options as per your Google Slides file. Look and set these options carefully. Though the options will be set by default, you should check them.
Step 9: Check if “Google Sheets” selected in “Type” section and “Output as” section is set to “Multiple output mode“. Leave File name as it is and move to the next step.
Step 9: Next step is optional so skip that step and then you need to select the folder in your Google Drive where all certificates will be saved.
Step 10: Skip the optional steps again using the Next buttons. After that, the wizard will prompt you if you want to share docs & send emails and Add/remove job triggers. Make sure all options are set to no and complete the wizard.
Step 11: Now the wizard will show the name of your created job and a Play button. Use that button to trigger the job. After that, it will create and store personalized certificates in the set folder for all those names that were added in your Google Sheets file. In front of each name, certificate URL, document merge status, etc., are also added in that sheet.
That’s it! Use the certificates as you want. The add-on also lets you edit and delete your job anytime you want.
The Conclusion:
This is a very nice option to bulk generate personalized certificates using Google Sheets. Though you need Google Slides also for the certificate template, the main part is to be done on Google Sheets with the help of add-on. The whole process looks a bit lengthy but it is easier.
