Auto-generate Certificates from Google Sheets

Almost all the institutes or corporates have some kind of workflow that involves generating PDFs. Be it for course completions, awards, participation or employee relaed certificates

Manually creating certificates for course completions, training programs, or events can be time-consuming and error-prone. With Orshot's visual content automation platform, you can automatically generate personalized certificates directly from your Google Sheets data

Let's get started with the workflow

What you'll need?

• Google Sheet: Where your data is stored

• Orshot Account

  (Free): To design or use a Certificate template(we'll use this one) and get an API for it which we'll use to connect with Google Sheets on n8n

• n8n Account

  : To connect Google Sheets and Orshot to setup the automation

What are we building?

A 3-step workflow in n8n which connects Google Sheets and Orshot to automatically generate Certificate PDFs from your data in Google Sheets

It'll use your Google Sheets column data, pass it to Orshot where the certificate will be generate, and then send the generated certificate PDF back to your Google Sheets in a new column

Need help?

Setting up the automation could be complex, feel free to use the chat button on bottom right or send us an email to ask for help and we'll help you setup the automation

Method 1: No-Code Automation with n8n

Create a new workflow on n8n in your account. We'll start by adding a Google Sheets node. Here are the steps you can follow to build the automation:

Click on "Create new credential" and connect your Google account where you have the Google Sheet:

For this example, we have a simple Google Sheet with two columns: one with Candidate Names which we will use in the certificates, and another with Certificate PDFs where the generated PDF will be stored:

After connecting your Google account, select the Document and Sheet from the dropdown list. In "Trigger On", select when you want the automation to run:

When you click on "Fetch Test Event", it'll display the list of rows in the right panel:

Now we have the first step set up, which will trigger the event whenever a new row is added/updated based on your trigger event.

Now we'll move on to the second step where we'll set up the certificate PDF generation using Orshot. To do that, click on the "+" icon to add a new node, search for "Orshot" in the nodes, and add it:

In the Orshot node, click on "Create new credential" to give n8n access to your Orshot account. To do that, go to your Orshot dashboard > Settings > API Keys and copy your API key:

Now paste the Orshot API key in the Orshot n8n node in the "Token" field and save the changes:

We'll also customize the certificate template in Orshot. To do that, go to Templates > Library page, search for "Certificate" and select the template you like. Click on "Open in Studio" and customize the design as you prefer.

You can also create a new template from scratch if you want. Make sure to parameterize the elements you want to dynamically update—in this case, the candidate name. Select the layer with the name, enable the "Parameterize" toggle for it, and give it a key as shown in the screenshot:

In the Orshot node, select the "Operation" as "generate from studio template", and in the "Template ID" dropdown, select the newly created Certificate template from your Orshot account:

Now we'll map the data for the candidate name from Google Sheets. To do that, under the "Modifications" section, add a modification and you'll see the parameter ID for the candidate name that we set in the Orshot template.

In this case, we set it to "candidateName". Add it, and in the value field, drag and drop the "Candidate Name" column from Google Sheets:

As soon as you click on "Execute Step", it'll trigger the event and generate a PDF for one of the rows. You can view the generated certificate by opening the URL in the "content" field in the right panel:

In the last step, we'll add another node that updates the same Google Sheet with the link to the generated PDFs in the "Certificate PDFs" column.

Add another "Google Sheets" node and configure the settings as shown in the screenshot below. We're selecting the same file, and based on matching candidate names, we're asking the node to update the "Certificate PDF" column with the PDF URL from Orshot:

Once saved, make sure to click the "Active" toggle at the top of your workflow. Setting it to active ensures it's ready to listen to row add/update events in your Google Sheets:

Now whenever you add or update a row in your Google Sheet, it'll instantly add or update the "Certificate PDF" column with the certificate for that candidate, automatically!

You can open the URLs to preview the PDF:

That's it! Your automation is now complete and ready to generate certificates automatically from your Google Sheets data.

Download n8n JSON file for the workflow

Method 2: Programmatic Generation with JSON Data

For developers who prefer a code-first approach, you can generate certificates programmatically using Orshot's REST API for templates with JSON data containing candidate information.

You can design a custom template for your certficate and get a Rest API for it, which you can use programmatically

Using the REST API

Conclusion

If you're generating certificates manually, this workflow will save you a lot of time auomating the process

This not only works with n8n, you can replicate the same workflow in any of automation platforms like Make, Pipedream, Zapier as all of them support both Google Sheets and Orshot integrations