A Pattern for gSheet Integration
I’ve been using UiPath for several years now, and have been slowly improving the patterns I use in development with a goal towards more code reuse, and better user experience. This pattern is the culmination of quite a few insights but I find that the best way to get a pattern improved is to share it with others and get feedback. There are many posts here and videos on the web that explain how to solve the more technical problems around gSheet integration, but this post is about how to layout your workflow and create variables that have several advantages over the more “purely functional” examples. Those are important for solving the myriad of problems that one faces when initially learning a new development tool, but this article is more abstracted because it is about patterns not specific implementations.
This is a visualization of an example workflow, removed from the UiPath Developer palette and made generic. It consists of a few parts, and the placement of the Activities will be discussed because in the low-code world of UiPath, their location in the flow can have performance and speed implications.
The first thing I want to talk about is Server Environments. I know that many people enter into RPA as Citizen Developers and may not have the support of their IT department as they pilot a program to solve some specific problem. So it’s not a given that they will have access to Active Directory or other user administration tools. I mention that because one of the things I wanted to accomplish was to give my developer team access to the ability to pick which environment they were running in (Dev, Prod, UAT, QA, etc.) but not make my users have to pick each time when they run their automations.
But let me get to some specifics.
Variables for Environment
The key to this pattern’s utility is that it helps you create a single gSheet for the users to store their data, while at the same time giving you access to well formed test data to run in other environments. In brief, this part begins with making variables for the user environment and asking the user which they want to be logging into. That suggests two variables immediately:
strENV - a variable to store the environment selection. In my pattern I typically use an Input Dialog activity to capture the value from the user.
strURL - a variable to store the server URL associated with the ENV selection.
And, as we will see below, we also need a variable to identify the correct tab in our gSheet.
strTabName - a variable to determine with which tab in the gSheet you will be interacting.
I used the idea of letting users select the ENV themselves for a good while before realizing that it was confusing to them since they don’t use anything but PROD. But how does one know whether a user is a developer or an end user?
Several solutions occurred to me. It is certainly possible to store a list of approved users who can see this selection menu, and then do a system call to find out what the userId of the current user is and check if they’re in that list. However, if you store that info within your automation bot, you would need to touch each workflow to change it if personnel turnover.
A better solution would be to get it from a domain group or similar - but, as stated above, I know that many UiPath solutions start out as pilot programs or citizen developer projects. To address that challenge, I realized this can be solved with the UiPath Cloud Asset storage! That tool allows you to store “assets” that can be strings, numbers, or Key-Value Pairs. One simple solution would be:
- Store a list of userIds that are allowed to change the ENV
- In the Gather User Info sequence, assign system.Environment.UserName.ToString to a strValue and test to see if that value appears in the list of developers.
- Wrap the Input Dialog in an “If” condition to see if the current user’s ID is in the list, and if so then allow them to pick the environment. Otherwise, just default the value to PROD.
2-Factor Authentication Considerations
(this is a bit of a side-bar) One thing I noticed is that 2FA can lead to time-outs when the user has trouble with a token or phone app. A pattern that seems to resolve that is to place an Input Dialog inside the Use Browser sequence. The browser loads the page, but before anything else happens I have the input pop up to ask the users “Have you finished your 2FA sequence? Press Enter to acknowledge.” This prompt causes the bot to stop and wait until someone presses that button. If a user waits until they have finished 2FA they should have plenty of time to be authenticated without fear of a timeout halting the bot.
Setting up gSheet for ENV tabs
For a long while, I developed my Sheets integration with a single gSheet tab for the users and would move their data when I wanted to test something. This was very silly and not a good idea.
A better approach is to make one tab for PROD. (You don’t have to literally call it “PROD” but for this article that’s the illustration I’m using.) Make another tab for each ENV you might need to test in conceivably. These non-PROD tabs should be cloned from the original and their names noted.
Now when you run the implementation as a developer you can use the strTabName variable to ensure your browser URL and TAB match the ENV choice. When we’re done, a selection of DEV will get you the development environment web page, the development gSheet tab, and any other ENV-dependent settings you need to consider.
ENV Switch
This is a very important part of the workflow. The Switch Activity is going to let you do the abstraction needed to set the appropriate values based off the strENV selection. I often use numbers for selections, but you need to set up the ENV Switch Activity to match whether you use a string, integer, or whatever. Within the CASE selections, you place an Assign activity to set the URL to match the appropriate ENV. Here is also where you will set the default value to PROD.
“ForEach” Sequence based on DataTable
The whole point of most gSheet integrations is to step through row after row data to perform a repetitive set of operations on those entries. So this is the classic “core activity” of UiPath and should be familiar ground to experienced developers. There’s nothing very different in this workflow except that I’ve found it expedient to put the gSheet Write Activity inside this loop as the last step before it repeats.
The purpose of this placement is so that if the bot crashes (an experience we’ve all known, no doubt) that the end user wouldn’t need to start over from scratch. I always put something like a Status and Notes column at the far-right side of my gSheets so that when a bot finishes a work item successfully it can write a “complete” or “failed” status. Then if there’s a failure and I have an idea of why, I add notes to the Notes row so the users will be able to see what went wrong and how to re-run it.
From that pattern, I realized I needed to also add a check at the top of my workflow to see if a row already has a status of “complete” or “failed” and if it does, I skip it and move to the next row with a Continue Activity.
With all these features in place, the users can run their automation and if (for example) 5 out of 50 items failed, those could be checked to figure out what went wrong. Corrected rows could have their status removed and the workflow would then skip the completed items and only work on the items with no status entered.
End Notes
The final thing to remember is what happens when your bots finish their work. If you need to send a user an email, the End Sequence is where that step would go. Another useful thing to put here would be any sequence that might be used to capture the bot’s work accomplishment in a log you can use to track how much you have accomplished with this run, and to give your management more granular utilization numbers built off actual work.
Conclusion
This pattern has improved my user’s experience and made it possible to recover from a crashed bot without needing to manually inspect how far the bot got before crashing. If you have any helpful suggestions on how to improve this pattern, or about successful approaches of your own, please post them so that I can learn from your experiences!