Create Invoice in Google Sheets for Communications & Media

Create invoice in google sheets for Communications & Media

in this video I'll show how to create and send PDF invoices using the data in Google Sheets for this I'll not write any code I'll use the uipath studio web for this uipath studio web runs on the browser and it is free to use you will also have the option to generate PDF invoices without sending emails let's first see and understand the data you can see these are the sales data I have in this sales data Google sheet this process will work with Excel online and Microsoft Outlook as well instead of using Google Sheets and Gmail you can also build this automation with Microsoft tools here I have the customer name email service name and prices some of these price sales are blank I have a column for the invoice number and one for the invoice date one for the due date and a total column at the end I want to send them automated invoices in PDF format like this you can have the sales data in any other format by the end of this video you will understand the process and you can create your automation ing to your use cases we need a template for the invoice so the process can use the template to generate the PDF invoices this is the template for the invoices I have created in Google Docs it's a very basic template I created it to demonstrate the process you can design the template the way you want it when you create the template you need to make sure you create the fields for the columns you have in the Google sheet as you see here I have a field for invoice number this is for the invoice date this is this is the customer name and so on I have created a table here for the service and their prices you might have understood already that I have put all the custom Fields inside double curly brackets this is because I'll replace all these with the data in the Google sheet and I need a unique identifier to understand the fields don't worry follow along with me and you will understand how it works let's start building the automation I'll search with uipath Studio web I'll go to the First Link uipath studio web I need to create create an account I'll click on this Tri Studio web I'll use my Google account to create the account I need to create an organization here I'll click on create organization here I have selected my display name my country my state I'll click on next I need a name for the organization so I'll just type learn and subscribe now I'll create the organization it offers some pre-built templ I'll click on the automation tab here I need to create my project I'll create a new project I'll select the manual automation since I'll run the automation only when needed I'll create some more automation using uipath for different purposes subscribe to the channel to stay updated I'll rename the process to send PDF invoices from Google Sheets now I need to read the data which is in the Google sheet I'll click on the plus icon and here I'll search with Google Sheets I'll select this for each row in spreadsheet which is associated with the Google workspace this will create a loop for all the rows which we have in the Google worksheet I'll select this for the first time you need to connect the activity with Google worksheet you see connect to Google Sheets I'll click on this I'll click on connect I'll select my account I'll click on continue I'll allow the access and click on continue the Google account is now connected I can see the account name here here you need to select the file where you have all the data for that click on the folder double click on my drive and here I'll select sales data this is the same worksheet where we have all the sales data I'll click on Save now I need to select the range which worksheet and in the worksheet in which range the data is located the name of the worksheet is sheet 1 and our data is located from A1 to k11 so I'll type sheet one and an exclamatory sign and type the sale numbers A1 colon k11 so it will read the data in sheet one from A1 to K1 if you have the data in a different range you need to mention it here and now it's checking if the range is valid so the range is valid if there is any mistake it will show an error our data has header so this is already selected has headers true now I'll add the next activity I need to add the activity within this for each Loop not outside the loop I'll click on the plus icon here and here I'll search with copy this is the activity copy file this activity will copy the template into a folder then we will fill the template with the data for the customer again for the next row it will copy the template fill the data and so on by the end of the video you'll understand the process here I need to connect to Google Drive so it can access the template and generate the invoices into a Google drive folder I'll click on connect to Google Drive connect I'll select my account continue continue so I want to copy the invoice template and put it into a folder I'll click on this folder icon here I double click on my drive select the invoice template this is the template file I'll click on save destination folder here I'll create a new folder invoices I'll save the generated invoices in this folder the folder is created dat now I'll select this folder as the destination folder I'll click on this folder icon I'll double click on my drive I'll select this folder inv voices in the new name box I will assign the customer names as the file name this column customer names I'll click here from this far each row in spreadsheet I'll select customer name when it copies the template into the folder it will name the copied file using the name from the Google Sheets customer name column now we need to fill out the copy template with the data from Google sheet for that I'll add a new activity I'll click on the plus icon I'll search with Fillin I'll select this fi document template which is an activity for Google workspace I need to connect to Google Docs so this activity can access the data the Google account is now connected now I need to assign the document in which document it will add the data I'll click on the C more option here I'll select use variable now I'll click here click to use variable I'll select the file the copied file this file is coming from the previous activity we are copying the file into the invoices folder the file is stored in a variable now and we are using the variable in this activity now I need to assign the document field so it fills them correctly I'll click on this box I need to add the keys here I'll click on ADD and here's the key name this key value should match the placeholder text we created in the template the key values should match with this I kept the placeholders name same as the Google Sheets columns name the first one is customer name and for the value I'll press control and space and the value will be from this for each row in spreadsheet for the customer name I need to select the customer name column from the Google sheet data for for my convenience I have copied all the keys in this text file so I can quickly copy them one by one without making a mistake the next key will be invoice number I'll press control space bar I'll select invoice number inside this for each row in spreadsheet invoice number the next is customer email I'll copy the customer email here I'll press control space I need to search customer email customer email I'll quickly create all the keys here all the keys are now created I'll save this I'll click on Show additional properties here I can see the symbol using this symbol this field document activity will find the placeholder text you can change it to any other symbol if you need to but you need to make sure you have the same symbol in the template until now it will read the data in the Google sheet copy the template into the invoices folder and then fill the copy template with the data in the Google sheet for that row now we need to convert the file to PDF and send the PDF invoice via email let's do it I'll add a new activity I'll search with download I'll select this download file activity for Google workspace this activity will will download the field template that is the invoice into this automation so we can attach it to an email in the next activity it will not download the file to the computer I need to select file to download I'll click on the C more option I'll select use variable for the variable I'll select the file from the copy file activity from this activity first it copies the file using this copy file activity then it fills the file with the data for the current row so the downloaded file here will have all the data under additional properties I'll need to select the document as PDF and need to select this download document as I'll select PDF here I want to send the invoice in PDF format so the recipient cannot make any changes to it since I'm not going to download any other file type I'll not make any changes for these file types now the final step I'll add a new activity and I'll search with send email here I'll select this send email for Google workspace if you want to send the email using any other provider you can select Outlook Zoho and it has some options for the email marketing providers I'll select the one for Google workspace I need to connect to Gmail so it can send the emails using my Gmail account I'll click on connect to Gmail connect select the account continue allow the accesses and continue for the two field I need to select the customer email column when I click on the field I can see all the variables I'll scroll down I'll select this for each row in spreadsheet under this I'll select this customer email variable you can add a cc field here you can add a custom CC email address if you do not have a column for this to add a custom email address for the CC field I'll click on this C more option I'll select open expression editor here I can type my CC email address and save it but I do not want the C so I'll remove the CC email this email activity looks like what we have seen in the send email with power automate video you can check out these videos to send emails using power automate one with individual attachments for each of the recipients and one with no attachment now I need to write the subject I'll paste a subject here and I want to replace this placeholder with the columns from the Google sheet for the invoice number I will select the invoice number here the invoice number column for the due date I'll select the due date column from the Google sheet for the email I have a draft I'll paste it here this is the draft of the email I want to send here I have some placeholders which I want to replace first I'll add the name here for the name I'll select for each row in spreadsheet I'll select customer name for the invoice number I'll select the invoice number field for the total amount I'll select the total field and for the due date I'll select the due date field I can make the text to bold I'll select the due date I'll click on The Bold option I'll select the total amount I'll click on The Bold option now we need to add the file here I'll select the attachments option I'll click on this and I'll select the variable for the attachment I need to select the download file variable and the download file you see the file variable I'll select this this download file variable downloads the invoice in PDF format and we are attaching the PDF invoice into the email you can create the PDF invoices without emailing if you do not need to for that you do not need this send email activity instead add another activity that will save the PDF invoices in a Google drive folder here I'll show it to you I'll add a new activity I'll search with upload file and down below I can see the upload files activity for Google workspace I'll select this these activity will upload the PDF files into a folder for that I'll create a new folder here I'll name it PDF invoices and now in the automation I'll select that folder but before that I need to select the file for the file I need to select the downloaded file and for the folder I'll click on the folder under my drive I'll select PDF invoices and save now we have both the options in this automation it will generate the PDF invoices using the data in Google Sheets send emails with the PDF invoices and save the PD PF invoices into a folder the process is completely ready now I need to test the activity so I'll click on this test button and here you see it started building the project and then it will run the process here you can see the activities are running one by one it is running the activities for each of the row if I go to the folders here PDF invoices it generated the invoice for the first customer I log to my Gmail account in the send items I do not see any of the emails but I can see the emails in the draft this is because in the email send activity the save as draft option is selected this is the default selection I need to select the false option here so instead of saving the emails into the draft it sends the email I'll delete all the drafts here now I'll run the automation again here in the PDF invoices folder I can see it Ed the first invoice for the first customer and this is how the PDF looks let's check the send emails I can see it already send three emails for the first three customers let's wait until it sends all the emails to all the customers the Run was successful you can see the successful message here it sent 10 emails to the 10 customers with 10 different invoices it generated the invoices for the customers before sending the email in the PDF invoices folder I can see all the invoices for all the customers if you do not need to send the emails to the customers then you can just skip this send email activity when your testing is done you can publish the automation I'll click on publish I'll not change anything here I'll click on the publish button next time when you need to run the Automation in the uipath dashboard visit this orchestrator menu under the automations tab you can see the automation we just created select the process click on this start a job button to start the automation here in this invoices Google drive folder you can see the individual invoices in Google Docs format we created this for this automation process you can delete this when the task is completed and if you need to save the PDF invoices you have all the PDF invoices in this PDF invoices folder that's the end of the video thank you for watching hit the like button and subscribe to the channnel