A Case Study for Automation
I recently worked with a client, a medical clinic, with a simple request: automating biweekly reports. Every two weeks, the clinic needs to generate a report for each contractor. The biweekly report contains all of the invoiced and paid sessions of the clinic and contractor's clients. A Google Sheet had already been created to compile the information into a single sheet. The difficulty came in creating PDFs and emailing them to each of the contractors. Manually generating, saving and attaching the reports was too time consuming for the already overloaded admin duties. A task ripe for automation.
The task in essence is quite simple: change a cell value to generate the report, output to PDF, and email the file.
1. Get the list of contractors
When the application is run, the name of the cell containing the contractors names is asked for. From this cell, we can find the range that defines the list of contractors. We could easily just hard code in the name of the cell, but we lose the flexibility of using the application in other sheets, or moving the cell. The range we find is the same as defined in the “data validation” window that populates the drop down list.
At the same time, we ask for the cell of the contractors email, for use later when emailing the PDFs.
2. Set the sheet’s driving cell value to the contractor’s name
Setting the value of the drop down list causes the rest of the spreadsheet to generate the report. No code is required here, just work your spreadsheet magic.
3. Create a PDF
Using the script, we are able to output the current sheet as a PDF to the same folder in the google drive. We can use the name of the sheet and the contractor to create a sensible filename for the PDF.
4. Email the PDF
With Gmail and Google Scripts, sending an automated email is incredibly easy. Our script only needs to provide an address, subject, message, attachment, and a name to appear as the sender. As we are always sending the same reports, we can include a static message that indicates the report period using the sheet name. When you realize how easy automated emails are to send, you’ll want to use them everywhere!
Do you also have reports that need to be generated and emailed? Good News! I’ve included the above script for you to use. Open a Google Sheet, and navigate to Tools > Script Editor. Copy the contents of the script file into a new script, re-open your spreadsheet, and that's it! Look under Add-ons for the new script. See below for a download link.
If you are interested in having your business automated, contact me at caden.armstrong@smartbenchsoftware.com, and inquire about my bespoke software solutions. I specialize in creating add-ins and integrations for the engineering industry.
Kommentare