Blog

Dealing with escaped characters in Microsoft Forms responses? If you’ve ever tried to loop through multi-select answers in Power Automate and hit the dreaded “result must be a valid array” error, you’re not alone. In this post, I’ll show you the cleanest way to handle escaped JSON — and it’s simpler than you think. The Problem with Escaped Characters When you select the dynamic answer from a question and run it through an apply to each action you get an error: ...

Invited an external guest into your Microsoft Team only to find they can’t access any files? You’re not alone — this is one of the most common SharePoint and Teams issues I come across. In this post, I’ll show you exactly why it happens and how your SharePoint admin can fix it, including a PowerShell script for those tricky private channel scenarios. “We can’t get your files. We’re working on getting them back” ...

Off the back of my Power Platform Quiz concept where I first used the HTTP API, I have built a solution that in real-time will dynamically serve up files hosted on SharePoint, also using the HTTP API in Power Automate! I first learned about this possibility whilst in discussion with Paul at TachyTelic. This is a working proof of concept and I would love to get your feedback. Imagine that you create documents that need to be served up to the public via your website. You might have version control on these and you might want approval. Quite often the file would be saved to a traditional server or onto a SharePoint document library and then comes the time of publishing. You get in contact with your web team, you send them a copy of the document and they publish the file to the website for you. What happens next? You spot a typo and you need to edit the file and have the Web Team republish that file via the corporate content management system. How much time would you spend on this process each time? ...

Need to combine data from multiple sources — like Excel tables and SharePoint lists — into a single array in Power Automate? In this post, I’ll show you how to create a one-to-one relationship between non-relational data sources using a single Apply to Each loop, filter arrays, and a compose trick to build a clean JSON array for the next stage of your flow. Data Sources Excel Sheet contains two tables of data that are related by the ID ...

Need to send personalised emails with unique attachments — like monthly invoices — to a list of customers? In this post, I’ll show you how to build a mail merge with attachments in Power Automate, pulling customer data from Excel and attaching the right file for each recipient automatically. If you have a requirement for creating invoices in an automated fashion please take a look at some of my other ideas here: ...

Got a CSV file you need to turn into a JSON array in Power Automate — without premium connectors? In this post, I’ll share a copy-and-paste Select Action that does exactly that. Just paste it into your flow, adjust the columns, and you’re done — no Apply to Each loops required. I’ve recently covered how to efficiently parse a CSV here vs the age-old apply to each solution. Updated Solution Available Note, that I now have an ever more improved parsing solution. Handle the column keys dynamically. Any shape or sized CSV without the need to edit the existing flow. Retrieve data from OneDrive, SharePoint and Outlook Attachements. ...

Working with dates in Power Automate can be frustrating — especially when you hit the dreaded “The datetime string must match ISO 8601 format” error. In this post, I’ll walk you through every action and expression available for manipulating dates, and show you exactly how to reformat date strings so they play nicely with Power Automate. You can read up about ISO 8601 here but ultimately it means [YYYY]-[MM]-[DD], I explain how to repurpose your date time string below - click here ...

Ever wished you could populate an Excel invoice template as easily as a Word template in Power Automate? Excel doesn’t offer a built-in “Populate Template” action, but Office Scripts in Excel On the Web (currently in preview as of March 2021) changes everything. In this post, I’ll show you how to use TypeScript and Office Scripts to dynamically complete an Excel invoice via Power Automate. Watch the demo here ...

Believe it or not, there’s no built-in SUM function in Power Automate. If you need to add up a list of numbers, you’re typically stuck using an Apply to Each loop with a variable — and for large lists, that’s painfully slow. In this post, I’ll show you a creative hack using the Select action that summed over 600 numbers in literally zero seconds. I also cover how to handle negative numbers and floats. ...

Looking to convert a CSV to JSON in Power Automate without using an Apply to Each? In this post, I’ll show you a super efficient method using the Select action that processes your entire CSV in zero seconds. If you’ve been looping through rows one by one, you’ll want to see this. Background Are you looking to parse any CSV to JSON without having to worry about the file encoding, the header names, or the length and shape of the CSV? I have a new one solution fits all method here (May 2021) which I encourage you to try. The method I propose below is off the back of my demo of the Union, Except, Intersect actions in Power Automate where I learned something new. How to use the Select Action. How about using this action to create an efficient method for converting CSV to JSON? ...