How to generate PDF invoices from Google Sheets and email them automatically

Put one customer per row. The sheet in the video has the customer name, email, service names and prices (some left blank), an invoice number, invoice date, due date and a total column. Your data can be in a different layout. You only need a column for every value that should appear on the invoice.

Design the invoice however you like in Google Docs. Wherever a value from the sheet should appear, type a placeholder inside double curly brackets, for example {{Invoice Number}}, {{Invoice Date}} and {{Customer Name}}. Use a table for the services and prices.

Keep each placeholder name the same as its column name in the sheet. That makes the mapping step later much easier. The curly brackets are the marker the automation uses to find each field.

Search for UiPath Studio Web and select Try Studio Web. Sign up with your Google account, then create an organization by entering a display name, country and state and an organization name. Nothing needs to be installed on your computer.

Open the Automations tab and create a new project. Pick the manual trigger, because you will run this automation only when you need to send invoices. Rename the project, for example Send PDF invoices from Google Sheets.

Select the plus icon, search for Google Sheets and add For Each Row in Spreadsheet from Google Workspace. Select Connect to Google Sheets, choose your account and allow access.

Pick the spreadsheet (in the video, Sales Data under My Drive). In the range box type the sheet name, an exclamation mark and the cell range, for example Sheet1!A1:K11. The activity checks that the range is valid. Leave Has headers set to True.

Every activity from here on goes inside this loop, so it runs once per row.

Inside the loop, add Copy File and connect Google Drive. For the file, browse My Drive and select your invoice template. For the destination, create a folder such as Invoices and select it.

In the New name box, select Customer Name from For Each Row in Spreadsheet. Each copy is then named after the customer.

Add Fill Document Template from Google Workspace and connect Google Docs. For the document, open the options menu, choose Use variable and pick the copied file from the Copy File activity.

Under the document fields, select Add for each placeholder. The key must match the placeholder text in the template exactly, for example Customer Name. For the value, press Ctrl + Space and pick the matching column from For Each Row in Spreadsheet. Repeat for invoice number, customer email, services, prices, dates and total. Keeping the keys in a text file and pasting them avoids typos.

Under Show additional properties you can see the placeholder symbol. It must match the brackets you used in the template.

Add Download File from Google Workspace. For the file, choose Use variable and pick the copied file again. Because the fill step already ran, this file now contains the customer's data.

Under the additional properties, set the Google Docs download format to PDF. The file is downloaded into the automation for the next step, not to your computer. PDF means the customer cannot edit the invoice.

Add Send Email for Google Workspace and connect Gmail. Set To to the Customer Email column from the loop. You can add a fixed CC address with the Open expression editor option if you need one.

Type or paste a subject and replace its placeholders with the Invoice Number and Due Date columns. Paste your email text into the body and insert Customer Name, Invoice Number, Total and Due Date from the loop. You can bold the total and due date.

Under Attachments, select the file variable from Download File. Other providers such as Outlook are also available if you do not use Gmail.

Add Upload Files from Google Workspace. For the file, select the downloaded file variable. For the folder, create and select a folder such as PDF Invoices.

If you only want to generate invoices without emailing them, keep this activity and delete Send Email.

Select Test. UiPath builds the project and runs the activities row by row. Check the PDF Invoices folder and your Gmail.

On the first run in the video the emails landed in Drafts, not Sent. That is because Send Email saves as a draft by default. Set the draft option to False, delete the drafts and run again. The second run sends ten emails with ten different PDF invoices.

When testing is done, select Publish and confirm. Next time, open the Orchestrator menu in the UiPath dashboard, go to the Automations tab, select the process and choose Start a job.

The Invoices folder holds the filled Google Docs copies the automation made along the way. You can delete them once the run is complete. The final PDFs stay in the PDF Invoices folder.