Welcome to AlanPs1.io

This blog documents some of what I do every day. If you are a Sys Admin, an IT Pro or a DevOps Professional you might just find it useful. The content you will find should suit the beginner, intermediate users as well as advanced users. Each post has some nuggets of joy. I hope you find some content that is useful.

The content is mostly relating to Office 365, PowerShell and Power Platform such as Flow & PowerApps. I will be sharing tech tips and tutorials mostly linked to Office 365 administration and showing you ways to automate business processes using Flow, PowerApps and PowerShell. My focus lies mainly with Office 365 and I use the necessary tools to work with data from the various Office 365 APIs and Microsoft Graph etc. You will see me use PowerShell and the Power Platform to harness data and hopefully do something useful with it. Then, as time goes on I should be able to introduce more Azure related content as my discovery of all things Azure progresses.

Flow: Rename Files in OneDrive For Business That Have Dots, Periods or Special Characters

Greetings Flow Fans.

This process essentially strips dots and special characters from existing files that reside within OneDrive or OneDrive for business, it renames them then overwrites the existing file with the newly named version. This has been created as part of a request by Frixel from the Flow Community.

Note: The "List files in folder" action only lists 20 files OOTB and doesn't list files in sub folders either.

Here is a glimpse of the completed Flow that will be broken down step by step below.

We will now go to step 1 in building the Flow and kick things off manually with a button.

Now list the files in the folder.

Initialise some variables.

Read More

Flow: Strip Dots and Special Characters from File Names

Hi there Flow Fans.

A while back I posted in the "Flow Cookbook" with a post called "Save Outlook Attachments And Guard Against Duplicate File Name Overwrite" and here on my blog a post called "Excel Formats Only | Save New Email Attachments to SharePoint". Whilst this How-to blog post works very well, I do mention this:

"Below uses a split to allow us to isolate the file type or extension as well as the file name. It breaks into 2 minus the dot (period). This will work perfectly, assuming there are no dots in the file name itself."

There have been a lot of users in the community contact me saying that these processes work very well for them, although a couple who have mentioned they have "dots" (periods) in their file names hence there can be issues with the flow. This post will address that, so if we consider a file name called "this=is&a.spreadsheet.03.19.xlsx" then we will split this into 2 whilst stripping out any potential problem characters, dots, ampersands, equals signs etc.

So we started with …

File Name: this=is&a.spreadsheet.03.19.xlsx

We will be left with …

File Name: thisisaspreadsheet0319

File Extension: .xlsx

We can then incorporate this process to the Flow I have referenced at the beginning or any other Flow that has issues relating to file names (or any string really) contaning dots, or special characters.

In order to start off this simulated process, I will start with a button. This just means it is easy to test and helps you get the concept of what we are actually doing.

Let's simulate the file name using a "Compose", renamed to "Compose File Name With Dots".

Note: There will be several "Compose" actions used in this Flow, just rename them exactly as you see them and you will be able to copy and paste the code.

Note: You will probably replace this in production by targeting which ever value is relevant. Attachment Name perhaps when working with Outlook Email.

this=is&a.spreadsheet.03.19.xlsx

Read More

Flow: Stripping Unwanted Characters or Special Characters From a String

Hi All.

For those who may need a way to strip special characters from a string then here is a method using Microsoft Flow. You will create an array of special characters then iterate through your string data replacing any of the matching characters then outputting the sanitised string at the end. Whilst I am using a compose called "Compose Email Subject", this could be any string when you decide to incorporate this into your Flow.

So, we will take this: https://emea.flow.microsoft.com/manage/environments/

And make it this: httpsemeaflowmicrosoftcommanageenvironments

Here is the Flow.

Read More

Flow: Send A Happy Birthday Email Message To Everyone In Your Organisation

Quite regularly I hear requests that centre around wishing users a Happy Birthday within an organisation. There is an extremely popular template within the Flow template gallery that does just that, it is called "Send a Happy Birthday email to employees on their birthday" and it uses a SharePoint list. It work's really well, although sometimes date formats can become an issue for the Flow implementer when they are imported from the list. Also, the organisation's users need to be added to the list itself in advance. Whilst that isn't a huge chore for most people, below is a process that requires no list and will send an email to every enabled user in your O365 tenant at 9am on the day of their birthday.

Sound, interesting?

Well, read on and follow the steps and you too will be able to implement this process.

Your email will look like this.

Pre-requisites: You will need a suitable Office 365 group. This may be an "All Users" group or a segmented group that only has users who have agreed to recieve such an email. Maybe it's a group that omits the CEO or those who may not celebrate their birthday. I will leave that to you to decide, but in this example a pre-configured "All Users" group is in use.

Here is the complete Flow.

Read More

Flow: Excel Formats Only | Save New Email Attachments to SharePoint

If you want to save just "Excel files" or just "Word Files" to a SharePoint Library, then multiple file formats must be considered. Excel has several formats and so does Word although below are the most common types that you are likely to receive by email.

MS Excel = .xlsx, .xls, .csv (and others)

MS Word = .docx, .doc (and others)

When using "Dynamic content", a common way to get the file name is to use "Attachments name" from the "Get email" action. This is great and very simple, but what if you already have files of the same name in that location?

The answer, it will overwrite the old file. This may be what you want and if so great, but what if a system generates a file called "report.csv" every day and sends it to you. You may want to keep a hold of all the reports.csv version that arrive daily.

Wouldn't it be better to save reports.csv as reports-2019-03-08-19-15-20.csv so that each new version doesn't conflict with the last?

This is ultimately what we will achieve in this how-to Flow blog post.

The method I will now step through triggers on receipt of a new email, captures the file format then adds a time stamp to the end of the file name on save.

Here is the completed Flow.

Read More