Leveraging Google Apps Script with RPA

What is Google Apps Script? 

Google Apps Script is a programming language developed by Google for interacting with files in their Google Workspace ecosystem. This includes Google Drive, Gmail, and all of Google’s office tools, such as Sheets and Docs. Whenever I am asked what it is, I always call it a “VBA for Google docs.” 

Google Apps Script is not strictly a robotic process automation (RPA) tool since RPA is used more for imitating human actions against user interfaces. However, it can be used to support RPA bots if in-depth manipulation of Google Workspace is required. Additionally, if a business process is exclusively conducted within Google Workspace, then it can completely replace an RPA solution.

What Are Some of the Advantages of Google Apps Script? 

Cost 

Just like its Microsoft counterpart, Google Apps Script does not require any licensing and is free to anyone with access to Google’s Workspace. 

Integration With Other Tools 

It is possible to run Google Apps Script functions from another tool. This allows for developing hybrid solutions. For example, if UiPath does not have the capabilities to do the spreadsheet manipulation required (e.g., set a cell color), then the UiPath bot can call a script to do that part. 

Many Ways to Run

Google Apps Script code can be executed in many user-friendly ways within the Google Workspace environment. The first way is via a button press. Google allows you to insert a button into Google Spreadsheets and bind a function in the document to the button.

The second way is adding a trigger based on events occurring in the document. This includes when the document is opened, edited, changed (which is different from editing in that it is the structure of the document altered, for example, adding a new sheet), or if a form response is submitted. For instance, you could use an On Edit trigger to add formulas to a table if a new line is created. 

The third way is adding a trigger based on date and time. This allows for the script to run at regular intervals. For example, if a report is updated every day at midnight, the script can be scheduled to run ten minutes later and process the data in the report. 

The fourth and final way is to add a trigger based on updates to a calendar. 

When Can Google Apps Script Be Useful? 

Since robotic process automation (RPA) tools only provide the capabilities (if any in the case of Google Workspace) the developers thought would be helpful, there can be required functionality not included in the tool. In the case of UiPath, this would include setting a cell’s color or hyperlinked text in a cell. A Google Apps Script function can fill in the missing functionality in these situations. 

Google Apps Script can also be leveraged for in-depth data processing that would be too involved or difficult to do with an RPA tool.

Additionally, if the entire process can be done within the Google Workspace, or if the automation scope can be reduced to only requiring the Google Workspace, then Google Apps Script can handle the process for no licensing cost. 

What Are the Limitations of Google Apps Script? 

Max Runtime 30 minutes 

As a free tool, there are bound to be limitations. The largest of which is a 30-minute runtime cap. This can cause issues if you have a longer running process. There are ways this can be handled and coded for if it is an issue, but it is a factor to consider. 

Storage Limitations 

If you are using a free version of Google Workspace, you are limited to 15 GB of storage space. It is possible to pay for more, but this removes the advantage of it being a free tool. 

Based on JavaScript (Harder to Teach) 

One of the most often declared advantages of low-code RPA tools is that anyone can learn to use them since they are drag-and-drop. However, Google Apps Script is based on JavaScript, meaning you must write code. This could lead to businesses being less willing to adopt it if they would have to pay for developers to write and maintain it if they are dead set on the citizen developer model. 

Can you integrate Google Apps Script with other RPA tools? 

There are a couple of different ways you can integrate a Google Apps Script project into a larger solution. 

Solution Needs to Execute Script 

If the solution needs to initiate the Google Apps Script function, then the solution can initiate the script using an API call. In UiPath, this functionality is built into the GSuite library. 

Document Needs to React to Changes 

If documents need to react to changes made to it by the solution, then an On Edit trigger can be leveraged. This can also allow for splitting the required work up across multiple machines. 

Script Needs to Initiate Solution 

If a script needs to initiate the overall solution, things get a little more complicated. This can be accomplished depending on what the other tools are. For example, if the overall solution has an email trigger option, the script could send an email to initiate the bot. However, it may not always be possible, depending on the tool used. 

Google Apps Script Use Case: Purchase Order (PO) Extraction 

Solution Description 

One use case I developed was to manage information on POs for various projects. Since the process could be done entirely within Google Workspace, it was a good candidate for a Google Apps Script solution. The use case involved three different processes, two of which would run on inconsistent schedules and possibly need to run multiple times per month. This made the scripts good candidates for using a button trigger for each phase of the trigger, with a time-based script for the one that runs daily. 

Import PO Information 

The first part of the use case is a script that executes at the beginning of the month, and every day of the last 8 – 11 days of the month. On those days, it performs the steps below.  

The script goes through a Google Spreadsheet file with links to multiple other Google Spreadsheets used for different projects and checks for a sheet for the current month. If the spreadsheet does not have a sheet for the current month, the script copies a template into the spreadsheet and names it for the current month. 

After all the spreadsheets have a current month sheet created, the script iterates through a list of POs pulled from Workday. For each of those POs, Google Apps Script checks to see if it can find the spreadsheet for the project associated with the PO. If it cannot find it, it writes the details to a file and skips to the next PO. If it can find it, it checks to see if it has already been added to the sheet for the current month. If it has, it updates the information on the same line. If it has not, then it adds a new line for it. 

Extract Received POs 

The second part is executed near the end of the month. Throughout the month, the project managers add additional information to the POs regarding payments made toward them. After the project managers have completed updates to their project files, the script performs the following steps. 

The script goes through the project spreadsheet list and opens the current month sheet. Then, the script finds all the POs with an amount received listed and adds the details to an output file. Then, if any required columns are not filled in, it indicates an error for the PO and highlights the row it is on. 

Once the Google Apps Script extracts from all project files, it sorts the output by the amount received. Finally, it adds an extra column with a formula to help manually review the output. 

Generate Upload File 

The third and final part is also executed near the end of the month after the second script has been run and any corrections to the output have been made. After the output has been validated, the script opens a spreadsheet holding monthly uploads and creates an upload for the current month by copying a template sheet. Once the sheet is created, the script opens the output file from the previous script and iterates through each PO listed. If the status listed is anything other than “Success,” it skips to the next PO. Otherwise, it takes the PO number, and the amount received and adds them to the upload file. 

Impact of Solution 

There are three main benefits of the solution developed. The first is standardizing the project files since the script initializes them. Before this, each project manager would set up the project file themselves, each doing so slightly differently. 

The second is general time savings since this solution prevents hours of manual review and copy-pasting. As with any automated process, a time savings element is included, and this process is no different. 

The third allows you to rerun the process quickly if there are any updates. If the report is updated, the project managers do not need to comb through it to find what has changed. The script handles it for them the next day. If the project managers update the amount received or update their approval for a PO, the second script is rerun, and it picks up the changes. 

Wrapping Up: Google Apps Script & RPA

While it is not a full RPA solution, Google Apps Script is an extremely powerful addition to any developer’s toolbox. The major downside of it is limited run time, but the lack of any licensing cost offsets this. If there is any Google Workspace interaction required for an RPA project, Google Apps Script can add functionality that may not otherwise be available to you, including completely replacing other tools for processes entirely in Google Workspace.