The Need:
Distributing information from a report to an variable group of people
The Old Way:
Dozens of individuals pulling the same information from different sources
The New Way:
A multiple step automation pulls an Excel report of published titles and key internal personnel from the product database, sends it to an email box, saves the attachment as a file in a SharePoint folder, turns the Excel spreadsheet into an Excel table, adds the rows of the table to a SharePoint list, and alerts the key internal personnel via email that their book has published.
This post will walk you through how to do this!
Case Study:
I work at a book publishing company. As you might imagine, a book's publication date is of great interest to a large number of people and departments in our organization.
- Book editors want alert authors
- Sales teams want to alert customers
- Support teams want to order gratis copies for authors
- Payments teams want to issue author and contributor payments due upon publication
- Marketing teams want to track campaigns and stuff (I'm not totally sure what the marketing team does with this date because they are already marketing the book!)
- Executive assistants want to collate information for reports
- ...and etc
Dozens of people were pulling reports from the same sources at the same time, filtering on project status, copying rows from one spreadsheet to another, sending emails... and because this work was happening in silos no one doing the work realized how much duplicated effort was spent on this task. A chance conversation between two people working in a mostly empty Covid office brought this situation to light.
Step 1: Humans collaborated to create a new type of report
Our data and analytics team worked with us to create a report that contained the columns/fields that we needed for this report. We included product categories and marketing codes that would make it easy for different people to filter and sort a cumulative list of projects depending on their personal needs. We also included the columns for each person attached to the project in the database: Editor, Project Manager, Marketing Manager, Etc.
Step 2: The published title report is delivered automatically, on a schedule.
Every day, before 8:00 AM, an email arrives in a mailbox with an unvarying subject line. The published title report is attached to that email as an Excel document.
Step 3: Microsoft Flow saves the email attachment to a folder, and adds a table to the Excel document.
Flow works with tables in Excel, so you have to convert this spreadshet into a table. To do this, you have to give it a range. Be generous. Pick a range with enough rows to include all possible rows on the spreadsheet. If your table range is too small, some rows of data will be left out of the rest of the process. I picked 75 rows. It is far more than I ever need but it is not such a large number that it will slow down flows as it checks each row.
Overview of Save File to SharePoint and Create Table
I will expand these flows in another post (link at the end of this one).
Step 4: Add each row of the table to a SharePoint list.
The trigger for this step is "When a file is created in a folder"--your previous step. It will want to start its process as soon as that file hits the folder. BUT THE COMPUTER HAS NOT CREATED THE TABLE YET. Flow needs the table in place to add any items to a SharePoint list from Excel, so... force it to wait with a delay step. This is where the number of rows in your table will matter. I have a small, 75-row table so I only wait five minutes before proceeding. If you are dealing with very large documents that have hundreds of rows, give it more time. It depends on your server speed, your VPN, your risk tolerance. Run a few tests, see what works for you. It's an automatic process; you are making the computer wait for the computer. You can be as generous with time as you were with rows.
This SharePoint list will have a column for each column on your Excel sheet. Ensure that people columns in Excel are patched to "Person or Group" columns on the SharePoint list so you can take advantage of the embedded SharePoint functionality in the next step.
Overview of Add Items in Excel Table to SharePoint List
Step 5: Email project stakeholders directly from the SharePoint list.
The last step is simple: When an item is created, email the people attached to the project and tell them that the book is published! I add a delay here, too, which possibly is not necessary, but I don't know how long it takes Microsoft Flow to add all fields to a row. It's the computer's time that is possibly wasted, but not mine.
In the email I put all stakeholders in the TO field and leave a note in the body of the email pointing that out. I also tell them that if they want to forward this to someone who isn't listed, they can have at it. Why not share the good news! Finally, this email comes from a mailbox that I never will check, so I let recipients know that they shouldn't bother replying to it. I do have my name and email address at the bottom of the message for problems, suggestions, or questions about the alert.
Overview of Send Email to Stakeholders
Couldn't all of these flows be combined into one flow?
Yes. But.
I created three flows where one could work for simplicity and to make troubleshooting easier. There are Apply to Each and Condition boxes and it can get very complicated trying to lay this all out, especially when you try to manage them from smaller computer screens. I also tend to build modular flows, and scpy/reuse flow pieces I've already created; when I create a series of flows rather than one giant flow, I can very easily "save as" or share pieces with other people. If there is a mistake or failure in one step of this larger process, it is easier to fix and find.
Create an automatically distributed, automatically compiled weekly report from the daily ones.
To automatically create and distribute a weekly (below) or monthly (stretch goal) list of published titles, you will need the SharePoint list created in Step 4 and a "shell" or template file in a SharePoint folder. This shell file will have the same columns as the daily Excel file that comes via email. The first time you create it, save it as a table with all the column formatting you want in the table that will be distributed weekly. Adjust your column widths, set your date formats, whatever will make this easier for the recipient to see and use.
This scheduled flow runs once a week. The first thing it does is delete all the current rows of the shell document and repopulate it with the latest projects from the previous week. My weekly report runs on a Tuesday, so each Tuesday the computer identifies projects on the SharePoint list that have publication dates greater than 11 days ago (Saturday) and less than or equal to 4 days ago (Friday). It adds these items from the list to the Excel table.
The table is saved as a new file in the folder, with the date range in the file name. Our latest file was named Titles published from 24-Jul-2021 through 30-Jul-2021.xlsx. This is the filed that is attached to the email that goes out to the weekly distribution list.
Everything so far has been automatic, but it does take a human to update the distribution list inside the TO field in the email that contains the weekly report. The body of the email contains instructions to recipients to contact me if they need to be removed or add someone else to the list. No human has to send the email--the computer still handles it--but names have to be manually added or removed.
You could make a monthly list if you could set the right condition. I do not know how to do this yet, but it looks like this post on the Microsoft Support forum might point you in the right direction. It uses a new-ish formula:
subtractFromTime(startOfMonth(addToTime(utcNow(),1,'month')),1,'day')
Please report back if you figure it out!
Overview of Weekly Published Titles Report and Email
Click here to see detailed breakouts of the flow maps above.
A note on a text:
The Machine Stops, a short story by E.M. Forster. Remarkable for how much the humans have convinced the computers to do for them, especially for a story written in--wait for it--1909. 1909! The first scene is of a woman's distaste for being interrupted by a doorbell during an online meeting. Also, reading it during Covid adds new layers of horror.
Comments
Post a Comment