Automatically turning a set of data from one file to another can save you and your team untold amounts of time, not to mention how it will innovate and speed up several areas of your daily automation operations.
Data entry is such a huge part of every business. That is why it is no surprise for teams to have hundreds of spreadsheets they store and manage in their trusted drives. And we are no different here on zenphi.
Just like you, we also tackle data entry in our daily tasks. Just like you, we know the struggle of manually searching our Google Drive and pulling specific Google Sheets files. Once pulled, we have to manually copy and paste these data into whatever form of the file we need it to be at the moment. For such a small startup team, this definitely was not the best use of our time.
Luckily, that struggle was in the past.
Zenphi, as an automation company, knew that to help other people realize the power of automated systems we have to start internally.
We have to make things easier for our own teams so we can provide better service to our clients. That is why we use our own platform heavily internally for automated workflows that can radically decrease our data entry struggle. One of those automated systems is how to dynamically generate Google Slide using Google Sheets data and export them as PDFs automatically.
Interested to find out how? Then, read on. . .
How your team can automatically generate Google Slide using Google Sheets data and Export It to PDF with zenphi
We created an elaborate scenario to demonstrate zenphi’s power of automatically turning Google Sheets Data into Slides and export it to PDF.
Here is the scenario:
As a token of appreciation for their exemplary service, you want to provide your interns a certificate of recognition. To do this, you have to find the spreadsheet file containing the interns’ data and the managers handling their internship. You then have to copy their information and paste it on the certificate temple. Once done producing the certificate, you will email it to the interns one-by-one.
This is already time-consuming if you have several interns. Imagine big companies that have interns of at least twenty to thirty people at a time or more. Add to the list that your company provides internships at least twice a year.
Fret not anymore. Using zenphi and a couple of other tools such as Google Sheets and Google Slides, you can eliminate any manual copy/pasting.
Here’s how the process looks like:
- Using Google Sheets, create a list of your interns’ full names, along with their emails in one column, their internship managers, and the managers’ positions.
- Create a certificate of appreciation template using Google Slides.
- Store the files in a specific Google Drive folder so you can find them easily during the automation configuration. Create a destination folder for all generated certificates.
- Specify the Gmail inbox that will send the certificates.
- Once the automated system is triggered(on demand in our case), it will instantly lookup the Google Sheet containing the interns’ information. Then, it will automatically copy and paste the set of information into the certificate template. After generating a certificate, it will be exported as a PDF and emailed to the intern automatically.
- This process is repeated up to the last name in the list.
This automated process is created using only the following tools:
- Google Sheets to record interns’ data
- Use Google Slides for creating the certificate template
- Google Drive to store the interns’ information and certificate template, and a folder where all generated certificates are stored.
- Gmail inbox to send out the certificates
- Zenphi to automate the entire system.
The step-by-step process to generate Google Slide using Google Sheets data and export it as PDF
Here is the recorded video of the entire process to generate Google Slide using Google Sheets data and exporting it to PDF. You can also use this for reference.
Step #1:
Create a list of your interns with the following data as headers: Full name, user id/email, Assigned on, Manager, Manager’s Title. Name sheet “Employees”(encircled in red).
Create the certificate template using Google Slides. The area in the curly braces is where the necessary data will be input. You can totally customize the template according to your preference and what you would like to include.
Store it in our Google Drive. We will also create a new folder and name it “Generated Documents”. This where the generated certificates will be automatically stored.
Step #2:
We are going Log in to our zenphi account and create a new flow. If you haven’t yet registered, you can create a free basic account using your Google credentials. Let’s name the new flow “Generate Certificates for Employees”.
Clicking the Next button, we will arrive at the next section where we will choose the trigger action. The trigger action is what will activate this automated workflow. Let’s choose a Manual Trigger.
Then click Create.
This is what our blank workflow looks like:
Have you ever tried extracting data from a PDF to Google Sheets? Was it easy? We have here provided easy steps to be followed on How to extract data from a PDF to Google Sheets.
Step #3:
In the Designer panel, click Google Sheets, and from the dropdown list, drag and drop Read Row.
To configure this step, click the Settings icon and start by naming it. Let’s name it “Read Employees”.
At the Connection section, let’s assign the Google Drive where the Generate Slides Employees file is located.
For the File section, click the Google Drive icon and look for the Generate Slides Employees.
Scroll down and enable All Rows and Has Headers.
Add all the columns in our Generate Slides Employees such as Name, user id, Assigned On, Manager, anf Manager Title.
Step #4:
From the Designer panel, click Date Time, and then drag and drop Current Date Time. Then click Format Date Time, this will input the issue date of the certificate.
Configure Format Date Time by clicking the Settings icon.
At the Date section, click the parameter(encircled in red) icon, then Current Date Time. From the dropdown menu, click Result.
Step #5:
Go back to the Designer panel and click Control Flow. From the dropdown list, drag and drop Foreach Loop. This step will lookup each row of the Generate Slides Employees sheet.
Let’s configure this step by clicking the Settings icon and naming it as “Foreach Loop”.
At the Collection section, click the parameter icon, then Read Employees. From the dropdown list, select Rows.
Step #6:
Click Google Slides from the Designer panel once more, then drag and drop Generate Slides. This step is will generate the certificate template and the information from step #5 is copied and pasted into.
Go to settings to configure this step. We will name it “Generate Slides”.
Assign the Connection as the Google Drive where the files we created earlier are stored. In our case, it was Wonder Woman’s Google Drive.
To configure the next section, click the Google Drive icon and get the Employee certificate Template that we created earlier using Google Slides.
For the Destination Folder section, click Google Drive icon again, and then select Generated Documents folder we also created during Step #1.
Now, let’s configure the New Document File Name. Start by clicking the parameter(encircled in red) icon then Foreach Loop.
From the dropdown list, click Item and select Name[A].
Scroll down once again and enable PDF. For the employee name value, click the parameter icon followed by Foreach Loop. Then click Item, and from the dropdown list, select Name[A].
For the Mapped value of {{date}}, click the parameter icon and then Format Date Time. Select Result.
To configure the Mapped value for {{issuer_name}} in the certificate template, click the parameter icon and then, Foreach Loop. Go for Item and form the dropdown list, select Manager[D].
For the Mapped value of {{issuer_title}}, configure by clicking the parameter icon and then the Foreach Loop.
Click Item, and then select Manager Title[E].
Step #7:
Go to the Designer panel, Click Gmail, and then drag and drop Send Email. This is the step where the certificates you generate in PDF forms will be automatically sent to the employees.
To configure this step, let’s click the Settings icon and we will name it “Send Email”.
At the Connection section, assign the Gmail inbox that will send out the certificates.
For the To section, let’s configure by clicking the parameter icon and then the Foreach Loop. Followed clicking Item, and from the dropdown list, select user id[B].
For the Email Subject, we will configure by clicking the parameter icon and then Foreach Loop. then Item, and from the dropdown list, select Name[A].
At the Attachment section, click the parameter icon and then Generate Slides. From the dropdown list, select PDF Payload.
Preview of the entire automated workflow:
