Intermediate | Flow of the Week: Automate emailing a weekly .xls file from a SharePoint List.
Greetings Flow Fans!
Ever feel like there aren’t enough hours in the day to complete those mundane tasks on top of meetings, projects and high-pressure deadlines? I certainly do. If you’re like me, you know that time is precious in the working world. But what if I showed you how to give yourself back time and never worry about that busy work again? With Microsoft Flow, you can!
I am a social content writer and Community Manager of the PowerApps Community (previously Microsoft Flow Community Manager.) I spend most of my work day writing, designing, editing, and reading when I’m not in meetings or events. Flow came to my rescue when I was writing content for the Microsoft Flow Twitter Account. Every week, I was responsible for providing the social media team with a list of tweets, graphics, and media for the following week. to be sent out each week.
My Flow automates submitting Twitter content each week. At first, my process consisted of back and forth emails between teams, random technical issues when sending content, and zero formatting. With Flow, I am able to write organized content, send it to the proper stakeholders, and deliver content consistently on time. This Flow can work for various scenarios in a variety of industries. It’s a huge timesaver and easy to create!
To create this Flow, you will need the following:
· SharePoint Site & List
· Office 365 Outlook Account
· OneDrive for Business Account
Now, let’s start building the Flow
1. Sign in to Microsoft Flow if you haven’t already.
2. Click on My Flows at the top of the page.
3. Select Create From Blank +.
4. Type Schedule Recurrence in the search box. Enter the day of the week, time of day, and interval frequency values in the fields below.
Next, let’s add an action.
Type Compose into the box, and select the expression utcNow(). Selecting this action will return each flow run’s timestamp as a string, for example: 2017-03-15T13:27:36Z:
5. Type SharePoint into the box, and select the action Get items. This will connect to a SharePoint list with the content scheduled for that week.
6. Next, type Create HTML Table in the box, and select value extracted from your previous SharePoint action. Select Custom in the Columns box. Select the values that correspond with the column titles from your selected SharePoint list from Get Items. This Data Operations action will pull data from the SharePoint list and convert it into an HTML table.
Time for another action!
7. Type OneDrive for Business and select the action Create File. Select the folder you wish to map to in Folder Path. Next, select Output for both the File Name and File Content boxes. Be sure to include .xls after the Output value in File Name. This will indicate that the HTML file will be saved as an Excel spreadsheet.
And add an action!
8. Type OneDrive for Business and select the trigger When a File is created. Select the folder path you entered in your previous action.
9. Type OneDrive for Business and select the action List Files in Folder. Select the folder path you entered in your previous action.
10. Now for the final OneDrive for Business action. Type OneDrive for Business and select the action Create share link by path. Select File Path for the File Path Box. Choose to send a direct link within your organization. This allows the excel file created from our SharePoint list to be sent as a direct link ONLY to someone within your team or organization.
11. Type Office 365 Outlook and select the action Send an Email. This will be sent to the social media team with a link that will download a copy of our Excel Sheet file. In Body, you MUST select Add Dynamic Content and add the value WebURL. If you do not include this in the email body, recipients will not have a way to download the Excel file.
I hope you enjoyed this walkthrough of my content flow. If you liked what you saw today, let me know! Leave a message below, Message me on the Power Users Community (@mackenzie_lyng), connect with me on Twitter, or email me directly!
Until next time! ?
