How To Build An Automation That Lasts – Part 1

This series will discuss how to write a script that can last which is challenging to do as processes and businesses are continually evolving.  The article will be written utilizing Google Script. However, the approach would apply to any tool you are using including more well-known tools such as VBA Macros.

1 – Remember that the process will more than likely change at some point including the user doing the process.

Therefore do not hardcode variables in your script. I repeat DO NOT HARDCODE VARIABLES IN YOUR SCRIPT. You may have a report that sends out all items over $50k in an email to various members of finance, but what these individuals may be interested in today, might be different tomorrow. The threshold could go higher or lower depending on company performance or change in personnel.

By realizing that parameters can and do change – you can future proof how you set up your automation. The main way to do this is to set up your script so someone that does not understand code can modify it.

The simplest way to do this is by setting a designated sheet as a control or input sheet. This allows the user to enter what they want the variable to be.

Referencing the report above – perhaps you want the user to control the dates of the report and the amounts reported. See below for a Google Sheet example:

Setting up your automation with a control sheet serves two purposes. It shows the user what is required to make the script perform properly, and allows them to easily enter it in a format they understand. In this scenario, he just needs to enter the begin and end date, as well as the required amount. This also allows the report to be flexible for the inevitable process change – for example raising or lower the threshold of what is included in the report. To get the variables into your script you would just need to utilize the below coding:

Another option to allow users to have control over the variables in a script is input boxes – which all scripting tools include in some variation. Again see Google Script option below:

When the finance professional runs the code, it will produce the following input boxes for all questions asked, and the user simply needs to enter the requested information.

Input boxes are especially useful when needing to enter a username or password into an accounting or finance program where you do not want to store the information on a sheet. After the user enters their info, it is only stored until the script completes and then it is dropped – obviously assuming no one is logging the information behind the script. You will see this usage more learning about scripting tools such as Sikuli on this site.

In theory, both control sheets and input boxes will get you to the same place. However, in practice, I have found that input boxes while perceived as more technical actually are misused more often by the person executing the script. In a simple scenario like the one above, there would not be an issue. However for larger scripts, the amount of pop up boxes and decisions become too much for a user to follow. Accordingly, a good mantra in scripting as it pertains to Digital Finance is that simple is better, and therefore recommend the control sheet option. However,  no matter what option you choose – remember it is important to build flexible scripts by not hard-coding any variables in your code.