Automating Core Management Tasks Using Microsoft Office 365
Hi!
If you've landed on this page, you probably attended ABRF or know someone who did. Welcome to our corner of the internet!
I want to start by telling you a little about myself and the Molecular Biology Core Facility here at East Tennessee State University. I'm Regenia and I'm the assistant director/ resident computer expert in our core. I believe that words matter and that jargon can both make a thing seem like something it is not and can create barriers to entry for people who want to learn a new topic. With this in mind, the more correct way to say "resident computer expert" is "technophobe with okay internet search skills who likes to figure stuff out and then attempt to explain it." Yes, that is a more appropriate description of who I am.
Our facility serves 56 unique principle investigators (at the time I'm writing this) and all of the people who work with them (students, techs, postdocs). When I started as Assistant Director, my first tasks were to update the webpage, figure out a scheduling solution that was COVID-protocols friendly, simplify our invoicing procedure, and "you know, just bring the core into the 21st century." Luckily, I'm married to a computer scientist and our IT staff at ETSU are friendly. I also really like efficiency, so despite lacking most of the skills required for these tasks, I was happy to give them my best effort. Our current workflow as presented at ABRF and the references provided on this page are the results of about ten months of futzing.
If you find any of it useful or have ideas about how to improve it, we have a button where you can fill out a form to let us know.
This button takes you to a form.
Objectives:
Stop using multiple emails and paper sign up sheets to schedule equipment reservartions, training, and consultations.
Reduce the time required to generate monthly user invoices, while improving readability and searchability and reducing errors.
Figure out how to make most of this happen with as few button pushes and as little recurring time as possible.
Only use software that is user friendly and low cost (okay, honestly... free to us).
The Tools: Microsoft Office 365
While there are several completely free apps available to help with scheduling, a
lot of them required paid versions to access all features and ran into scaling issues
when trying to coordinate several calendars. We also wanted something that we could
somehow integrate with our invoices, which were created in Microsoft Excel.
Our current workflow used Bookings, Forms, Excel, One Drive, and Power Automate. Each
institution can select the Microsoft license that works best for them, which determines
what programs may be available to you. If you log in on www.office.com and click "All
Apps" on the app launcher (the menu found by clicking the nine small squares in the
upper left corner), you can see what is available to you with your institution's license.
Results: Our New Workflow
Now instead of emailing our users repeatedly to schedule instrument use or training, they access our Bookings calendar and select a time that works for them. Bookings connects to calendars for our personnel and our instruments to only show available time. Ordering of reagents and services is completed using Forms.
Using a combination of "flows" created in Power Automate, we get a daily email of all scheduled Bookings and a table in Excel with all of our instrument use and orders. Our invoice is set up to reference this table and allows generation of an up-to-date invoice for any of our users with the click of just one button. Monthy invoicing is completed with less than 15 minutes of hand-on time.
How I Did It: A Few Dozen Tutorials
We stand on the shoulders of giants. I don't know if the people I found making really good tutorials online would consider themselves giants, but they were VERY helpful. I've narrowed down my list to the most useful few.
-
Can you send me to a tutorial that will give me the most basic introduction to Power Automate?
Absolutely. Here's a video from Leila Gharani. She is my go-to Microsoft Office GURU and will answer some other questions later in this list too. I've had such luck learning about EVERY Office app from her that I start with her YouTube channel when I want to learn something new. Thanks Leila!
-
How do I email a daily summary of Bookings events?
Megan V. Walker is the only person I have found who has made this tutorial. I did find it useful to go back and forth between the written tutorial on her blog and the video. Thanks Megan!
Written Tutorial:
Email daily summary of Bookings using Power Automate
Video Tutorial:
-
How do I populate an Excel table with data using Power Automate?
-
How do I use Power Automate and Office Scripts to split a workbook with lots of sheets into a bunch of PDF files?
The best explanation I could find for this came from Damion (last name unknown). To make his flow work, you'll need to add a couple of Scripts to the folder called "Office Scripts" in the "Documents" folder on your OneDrive. He's posted images of the entire flow and the Script files to his GitHub. You can also grab the Scripts from our GitHub linked below, where we saved in in a repository that includes the Power Automate flow ready to download. ALL credit for the two scripts used in this flow belongs to Damion and we are super grateful he has shared them because this is a crucial part of our new work flow.
-
Wait! What is an Office Script?
A Script is the same thing as a Macro, except Scripts are designed for the cloud-based Microsoft Apps. You can record a script using just mouse movements and clicks.
-
How do I rename an Excel sheet based on a cell value so that each PDF that the tutorial above creates has a specific name?
I used the answer from this forum and a bit of focused futzing to write my own script for this to work specifically with our invoicing spreadsheet. You can access it in our GitHub.
-
Now I have a bunch of different PDF files and need to email each one to somebody different. Can I do that with Power Automate too?
Yes!
-
Can you walk me through your entire workflow step by step?
Yes. I'm working on making my own videos to put ALL of the pieces together. In the mean time, drop me a line to let me know you're interested. I can share works-in-progress with you or set up some time to chat and screenshare on Zoom.
Replicate It.
Maybe you came here to learn a new skill that you can use to automate your own processes. But maybe you came to get some files to replicate our processes in your core. You can access all of the files necessary to do so in our GitHub. GitHub is a place for people who code to store and revise code of their own and collaborate to ideally make it better. My spouse, the computer scientist, says that my futzing has resulted in writing a few lines of actual code and that I should store the resulting files there.