Trigger a Power Automate Flow from Excel with Office Scripts or VBA | Excel Off The Grid
Vložit
- čas přidán 25. 07. 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post & find the code to copy ★
exceloffthegrid.com/power-aut...
★ Link to HTML encoding references on W3Schools ★
www.w3schools.com/tags/ref_ur...
★ About this video ★
Office Scripts can be very useful, but they are limited by being bound within one workbook. What if we could instead use a Power Automate flow to give us full cross-application functionality? Well, there is no official solution, but here is how to run a Power Automate flow within Excel or VBA.
0:00 Introduction
0:52 Setting up the Power Automate flow
4:11 Editing the URL for parameters
7:28 Running Power Automate from an Office Script
9:38 Running Power Automate from VBA
10:38 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Marc, you seem to have a good solution for every question I have! Thanks for putting out such helpful and specific tutorials.
I’m glad I could help. Clearly you’re doing the same things as me, but a few weeks behind.
If you ever get ahead, I’ll come to you for solutions. 😀
Am Wowed by the simple yet concrete steps...
Thank you. 👍
You answered exactly what i needed..... Thanks!!!
Glad I could help! 👍
Awesome.. SUPER LIKE !!!
Thanks Faraz - I think this opens up a lot of new possibilities.
I'm still hoping for a proper connection at some point.
A loosely coupled alternative is to have Excel send an email (like to a privated shared mailbox created for this purpose, for example). In the subject you can specify a key text (that gets filtered in PA to activate the flow, so it will only do it with certain emails) in the body of the email you can put all the data you want, in JSON format for example, and then have the PA flow activated when an email arrive to that specific mailbox with the specific subject, parse the JSON and voilá. We've used this many times to trigger PA flows (like from Typeform for example). One benefit is that it can be used offline without Internet (and when you get back online all the flows will be triggered). Another benefit is that you can track and debug the entire solution by simply opening the mailbox from a mail client. PA has a component to parse JSONs so, and Excel a VBA function to send emails, so it's all quite simple. Thank you for your video!
Presumably you are using Excel Desktop and send the email via Outlook with VBA.
hi,how do you declare the user name in the URL so that take the excel user name who trigger the flow? i am just working on a shared excel sheet and wondering if user name can be variable depending on the user triggering the flow
Thanks so much! What if my company restricted the use of triggering through an URL, is there a similar option to trigger the flow through Excel?
Great stuff! It's just a shame that this is not available with a Microsoft 365 personal license
Agreed, maybe one day.
Any thoughts on doing this with PA Desktop. Without a premium license?
So you want to run Power Automate Desktop from an Excel workbook? 🤔
Sorry, that’s not a use case that I’ve tried.
Hi Sir, for the office script, I got error if click on run scripts or add button
An unexpected error occurred. Close the task pane to try again.
however click on the URL generated and paste in B2, and click directly got no issue
what could be the possible cause ?
Thank you !
Sounds like a software bug to me. As there is no reason why that would be the case.
does running Office script requires enabling macros in the excel workbook?
Scripts are saved in a separate file, and can be run in any workbook.
They do not trigger the warning message to enable macros. They are built with modern security in mind and therefore do not have the same risks as macros.
@@ExcelOffTheGrid Thanks a lot for sharing this, I actively looked for triggering power automate flow from excel solution and today CZcams suggested your video for me! using Script (which i was not familiar with) seems to be the best option for me now.
I was trying to follow your tutorial, but when I click the link in the excel file it returns the following message:
{
"error": {
"code": "DirectApiAuthorizationRequired",
"message": "The OAuth authorization scheme is required. Please add authentication scheme and try again."
}
}
I noticed also "When HTTP request is received" trigger has now additional field above "HTTP GET URL" asking who can trigger the flow.
Do you know how this issue could be resolved?
UPDATE: I found the solution. This additional field "Who can trigger the flow" needs to be set to "Anyone". If this is set to "Any user in my tenant" or "Specific users in my tenant" it will require authentication.