Generate Automated Weekly Excel Reports using Python and WayScript

Introduction

As businesses become increasingly busier, having the ability to automate tasks can provide a substantial amount of time. In this article we’re going to learn how to create a script that allows you to generate and e-mail reports automatically, giving you more time to focus on other important tasks.

Building our Script

To begin creating the script, a Time Trigger is needed. This is a key aspect because it provides the program with a fixed time to run the script. This example uses a weekly time trigger, but WayScript offers a number of standard and customizable options that can be chosen from the drop-down menu in the left toolbar.

Next, the Microsoft Excel module should be added. From here, upload the preferred file; the data then shows up in the toolbar. If you have headers, click “Ignore Headers” and import each column. By doing this, you have created variables that will be used later in the script.

Now, it’s time to use Python to manipulate the data and provide a report. Add the Python module underneath the Excel module, and click “Edit Code” to the left. This displays a text box where pandas can be used to create the necessary code.

The first lines of code place our raw data into a data frame and perform any necessary calculations on the data, using those variables created earlier. After this code is generated it should be cleaned up by writing the Panda series as a list. Before running the code, this is what it should look like.

The last module to add is the Gmail module. Using the toolbar, select the e-mail account to send the report from and the e-mails that will receive the report. The subject line and body can be customized to your preference; however, it’s important to add the “info_to_return” box generated from running the Python code into the message body.

This provides the manipulated data in the report. Switch the slider on the Time Trigger to the active position to finish the script.

This quick script gives business managers the option to automate their reports so that they arrive to employees on time, every time. To see the step-by-step process of creating this script, watch our YouTube video below!

Conclusion

Questions about this script or anything else? Join our discord. We're always around to help. If you want to work the full script template, just find it here.