Automating Processes in Excel: Part 3 Publishing for Collaboration

This is part 3 of a series focusing on using RPA for automating processes in Excel – we’re covering everything from when and why to use RPA, to details around how to most effectively design the solutions and get the most bang for your buck.

Part 1:  Why RPA is not VBA

Part 2:  Design Considerations for Stability

Part 3:  Publishing for Collaboration

Part 4:  To Click or Not To Click?

Sharing is Caring

If you’ve been following this blog series, you remember I shared some statistics showing the extent to which Excel permeates throughout business processes.  So, what’s happening with all of these millions of spreadsheets that are being created every day?  They’re being shared, of course!  Perhaps a team of employees splits up the data in a sheet that represents their work list, or perhaps there’s a hand off point from person to person as they transform raw data to meaningful reports for further analysis.  Whatever the case, the processes require collaboration.  Thankfully, there are a few tricks you can use when automating Excel-based processes using RPA that will greatly ease the ability for groups of users to collaborate on spreadsheets.

Keeping it Within the Family

1. Set up a SharePoint site

There are lots of document repository options out there, but for the sake of simplicity we’ll focus on the most common in SharePoint, which of course plays nicely with MS Excel.  You’ll want to set up a separate site for each business unit (e.g. Credit Team Automations), and a separate document library for each process/group of processes (e.g. Collection Reminders Process).  This will get you the right balance of granularity without too much maintenance effort.

2. Create permissions and roles

Any SharePoint site will come with a few standard roles out of the box.  If you are ok with granting access across the entire site (business unit), you can create the roles at the site level.  If you need the access levels to vary by document library (process), you will need to create process-specific roles.  My recommendation is as follows for the fictional “Process 1”:

• Process1 – Full Control
  • Set this role up with full permissions, then assign it to a single process owner plus the Bot accounts
• Process1 – Contribute
  • Set this role up with standard read/write permission levels, then assign it to any team leads
• Process1 – Lite
  • Set this role up with minimal read/write permission levels, then assign it to any end users
  • *Important* – make sure to DE-SELECT the “Use Client Integration Features” permission…you’ll see why shortly

3. Set up security access

Once you’ve got a document library set up and permission levels/roles defined per process, you are in good shape to begin securing the documents.  A typical folder structure for a process automation might look like this…

• Templates
  • Part 2 of the blog series talked about the importance of Gold Templates…this is the spot where you will store all of the artifacts that the Bot needs to complete its work.
  • You’ll want the tightest security on this folder – limit to the “Full Control” group
• Outputs
  • This will be where any output spreadsheets will be published by the Bot for the end users to allow for collaboration. You may consider breaking this folder down further by date, depending on the frequency of the process.
    • g. subfolders 2020-01, 2020-02, etc.
  • You’ll assign the “Full Control”, “Contribute”, and “Lite” groups to all have access to this folder, with their respective varying permission levels

4. Use Excel Online

One key current limitation to collaborating on Excel documents is the ability for concurrent users’ actions to conflict with each other.  This can lead to imperfect results, especially if users download the documents to their MS Excel client on their machines.

An important mitigation technique is to enforce the use of Excel Online, which opens the Excel sheet directly in the browser from SharePoint and retains most functionality.  In fact, we’ve already enforced this by setting up our “Lite” user permissions, as those users will not even see the option to open the spreadsheet in the Excel client.  This way, changes to the document will be saved and visible to all concurrent users immediately with little risk of overwriting/conflicting changes.

Give it a Try!

I’ve seen these tricks work in the real world, but only after doing some firsthand learning the hard way.  So, I’d love to hear from you.  My challenge to the reader is to go give these tips for automating processes in Excel a try – set up a test SharePoint site, and create a simple workflow that publishes an Excel file.  I’d love to hear your thoughts on how these collaboration recommendations play in your environment!

ryan.mac@ashlingpartners.com