[MUSIC PLAYING] Hello, my name is Mark Rackley, and thank you for joining me for my TEC virtual session on Help! I'm Stuck Supporting the Power Automate Flows My Developer Created.
Now I created this session as a way to show admins what their developers may be doing in Power Automate because, over the years, as Power Automate has grown and gained functionality, it's now becoming that go-to session for workflow automation in Microsoft 365, and more and more developers are adopting the technology.
But because us developers are kind of pesky developers, we're also doing things that, unless you're a developer, you may not quite understand. So the goal of this session is to kind of walk through some of the things that are being done today by your developers in Power Automate so that when you see these things happening in Power Automate, you're better able to understand it, better able to debug it, and maybe even go back to your developers and say, hey, we need you to refactor this to make it a little bit easier for us to deploy.
So our overview today-- we're going to talk about quickly talk about what is JSON because JSON is being used everywhere in Microsoft 365 these days, and it's really pivotal to have a good, solid understanding of where it's used and what exactly JSON is, so should be a quick review over that.
And then we're going to talk about SharePoint REST, and these are endpoints that your developers are using to do advanced functionality from within Power Automate. And there's some really cool stuff you can do with this, so we're going to do some demos on using SharePoint REST within Power Automate it as well.
And then we're going to talk about a really cool functionality that's fairly new. It's called Office Scripts, and we're going to show you how developers are likely executing JavaScript and Typescript from within Power Automate in ways that you did not know of, and all the caveats and gotchas that you're going to have to be aware of if they're using this functionality.
A little bit about me, and then we'll jump in. My name is Mark Rackley. I have got over 30 years now of development and architecture experience. It makes me feel really old to say that, but I've been using SharePoint since 2007, been a Microsoft MVP for the past eight years. I organize my own event in Branson, Missouri. You're all welcome to join next time it's around your time. I'm a blogger, writer, speaker, and yeah, follow me on Twitter @mrackley, and if you have any questions, email me-- mark.rackley@avanade.com.
So let's jump right in. Let's talk about JSON objects. And what are JSON objects? JSON stands for JavaScript Object Notation, and it's used for storing and transporting data. And you'll know you're using a JSON object when you see this structure here, where we have the curly braces to denote that JSON object, and then we have a property with its value here. So this is a very simple, basic JSON object that has a FirstName of Mark and a LastName property of Rackley.
So when you start using JSON and you want to start creating JSON objects, I highly recommend that you download Visual Studio Code-- free tool, and what's great about it is it does syntax checking on your JSON objects. So if there's an error in your JSON object, you'll get the little error indicator. In this instance here, I'm missing a comma after the property FirstName, which is causing the error to occur. Now when you've got JSON objects that are hundreds and hundreds of lines long, it can really, really difficult to debug those, so having a tool like Visual Studio Code is really going to save you a lot of time.
So these JSON objects-- they can also store other JSON objects. In this example here, we have a JSON object that has FirstName, LastName, and Address, and address is JSON object that has the street, city, and state information. So you can really start to get really in depth with these JSON objects because they can keep just being nested further and further down.
You can also store multiple JSON objects in what's called an array. So you'll know you're in an array of JSON objects when you see these square brackets. So in this example, we have an array of objects, and we can see we have one, two, three different objects in this array. And again, this helps us to be able to store multiple JSON objects in one JSON object, and again this can be nested as well. So you can have arrays and arrays and arrays that contain JavaScript and more arrays. So these can just keep getting nested, and it can get pretty nasty if you have to go in there and edit these things.
So when you want to pull data out of these JSON objects, it's important to know how to reference those objects. So if you do have an array of these three objects and you wanted to get the value for the first name of the very first object, you would need to reference that object and use those square braces to say, hey, I'm entering this array, and then you give the index in the array of the object that you want to get. So if you wanted the very first entry, you would do the zero index of that object then do dot and the name of the property. And in this instance, it would return to you "Mark."
Now if you're not a developer, if you're not familiar with indexing, these arrays in the JSON objects are zero-based indexes. That means, even though there's three objects, the very first entry is not entry one. It's entry zero. So you do need to take that into account. And if you're doing things to get the dynamic length of a JSON array, you have to do some math there to do that. So you can see here, I'm saying that to get the very last entry in my array, I need to get whatever the length of the array is and then subtract 1 from it because the array is three elements long, but the last entry in it is entry number two.
So enough talking about what JSON objects are. Let's talk about where they're used in Microsoft 365, because they're used everywhere. One of the first places they're used is using for creating site designs and site templates in Microsoft 365. And within the slide deck that you will be able to get your hands on, I have links that have more information about these JSON objects and where they're used and how you can get started using those objects. We don't have time to actually dive into all of those today, but I wanted to let you just understand the scope to which JSON objects are being used.
So in this instance, we have a JSON object for creating a site template. And you can see, we have these properties and values. So here, we're applying a theme called Contoso Explorers. We're creating a list called Customer Tracking. And using this JSON object, it's a recipe for how this site would be created, using that site design and site template.
They're also used for the list view and column view formatters. So whenever you see those list views in SharePoint, they have the nice images and the columns and they're really nicely formatted, or if you use a Microsoft list and you see that the person column in a Microsoft list has a picture of the person, that's using a JSON object and the column formatter to make that look visually appealing to you. So again, check out these links to learn more about it.
And I also put links to samples. If you see this bottom link here to GitHub, this is a whole list of sample JSON for both list view and column view formatting. Always start with an example when you're doing this stuff too because, again, these JSON objects can get really big, so find an example that's close to what you want and then tweak it. There's no need to write this stuff from scratch.
You also use JSON objects in Power Apps. This is what the JSON-- this is what it would look like in Power Apps to set the value of a lookup field. So you can see, we do have that same JSON object structure.
When using Adaptive Cards-- so Adaptive Cards are just being more and more used within Teams, within Viva Connections, and all over the place. So definitely, you need to learn about Adaptive Cards, but in order to master Adaptive Cards, you do need to understand JSON objects. And the link on this page actually takes you to a Adaptive Card designer. And let me just jump out to that so you can see that, because that's worth looking at.
So Microsoft has created for us this Adaptive Card designer. And with this Adaptive Card designer, you can actually go in and drag in different elements into the card for how you want it to look. You can edit your text directly here, and you can generate the card you want without having to edit and manipulate that entire JSON object. And then, when you're done, you can just copy this JSON object down here, and this is what the JSON looks like for this Adaptive Card in the Adaptive Card designer.
The one last thing I'll point out here because it catches me every single time-- make sure you select the correct host app when you're wanting to create a card because if you wanted to create a Viva Connections card and you do not select the Viva Connections host app, the generated JSON is not going to work for you for Viva Connections, so make sure you go back and change that to the correct host app.
And like I said, Viva Connections are also used-- in Viva Connections, the samples here that I have for this link, for links to some samples for the Adaptive Cards, are really great. I use them all the time for creating a quick view for those Viva Connections cards and then editing for it to look like how I want it to look. So definitely check those out.
And then, finally, JSON objects are used in Power Automate. One of the reasons you're watching the session is for the Power Automate piece. And so you can-- when people use a lot of things within Power Automate that create these JSON objects-- that return JSON objects, you've got to know how to work with them in order to work with the data returned from those calls, whether you're calling a REST service, an Azure service, or some other service within Power Automate.
So the other piece that we're going to cover today is using SharePoint's REST, and SharePoint's REST is a way for us to work remotely with SharePoint sites, lists, users, and so much other information within SharePoint. SharePoint developers have been using these for years and years and years now to get our jobs done, and it turns out that, you know what, there is an action within Power Automate where we can execute these exact same REST queries, and it's a nonpremium feature, so we don't have to pay extra to use it. I'm all about the free stuff. I don't want to pay extra when I don't want to.
You'll know you're using the SharePoint REST interface when you see this API as part of the path in your URL to get to that REST endpoint. For instance, in this example here, I'm doing a call to my SharePoint site, and here's that _api, and I'm doing a function to get a list by the title. In this case, the list title is Products. So that's how that looks.
And you can see the action that we get within Power Automate. Whenever you select the action to send an HTTP request, you're going to specify the site address for which site you're going to be executing the risk query for, what type of method you're going to be doing-- it could be a get if you're getting data, or it can be a post if you're going to be writing data back-- and then the endpoint that you want to do. And that endpoint for the action is that _api entry point into the REST service.
So let's actually see this in action. I do have a link here on the page for you that has one of the flows I've created that I've exported. So if you want to go to this aka.my link, you can actually download and import the flow that I created so that you can look at and play some of the same functionality.
So what I want to do is I want to come over here into Power Automate, and I want to create a flow. And the flow that I want to create is something that you can't easily do out-of-the-box in Power Automate. I want to see, for the current user, if they are a site owner for the site for which they're executing this flow. And there are a lot of reasons you might want to do this. You might want to restrict what certain users can do by checking to see if they're a site owner within the flow itself to do that. That's a great way to restrict that functionality. And we can do that with a couple of REST calls.
So there's two REST calls we actually have to execute. First one is called _api/site/owner. And this API endpoint will give you the owner's group for your SharePoint site. And I can actually test this out. Another great thing about REST is you can actually test it out in your browser. So if I open up my site URL in the browser, and I put in that entry point into the REST endpoint after the site name and press Enter, this is the result of that REST query. And I can actually look at what that result looks like to know how it's structured. And if I do a search for the title, you can see that the group for the owners is called Demos Owners.
This is important information so that now, I can use the next REST query. The next query is _api/Web/SiteGroups/GetByName(' ')/users, where we pass in the name of the group. And we now know the name of our group is Demos Owners. So I can copy this into my URL and then put the name of that group. So we can see here, I'm doing a call to get by name Demos Owners and give me the users.
If I press Enter here, and let's look for me-- I know I'm an owner of that site, so I'm going to search for my name. And you can see here that it does, in fact, have me in there. There's my title. There's my name. There's my user principal name. There's my email address. So now all of this content here is a list of all the owners for that site that I referenced.
So now, within Power Automate, we can execute one REST query to say, hey, what's the name of the owners group for this site? Now, hey, give me all the users for that ownership group, and now let's iterate through those users to see if the current user is one of those users. So let's build it out quickly, and we can see what that looks like.
So within flow, we're going to create a new flow, and we'll just do an instant flow so we can trigger it manually. And we'll say, "Check if user is site owner." And we are going to manually trigger this flow.
First thing we want to do is we want to do a search for action, and let's search for SharePoint REST. And here, we have this "Send an HTTP request to SharePoint," where I need to build out that call, that SharePoint REST call. So I need to go find my site, which was Demos. The method is get, and then the URI, again, is that _api/site/owner, and there you go. We now have made this REST call.
But the question is, how do we actually work with this REST call? How do we work with the data that is returned? So to do that, this REST call actually returns back an array of JSON objects, but we have to understand what that array of JSON objects looks like.
So what I'm going to do is I'm just going to test our flow now. So I've put in this action to make the REST call. I'm now just going to test the flow, and it's going to execute this REST query. And then we can use the results of this REST query to help us figure out the structure of that JSON response. So we ran it. It succeeded.
And now, if we look at the outputs, we can see the body that is returned, and we can see that is a JSON object that contains other JSON objects. And we can also see that the information we're looking for, the name of the owner's group is in a property called Title. So this is important information, and that's what we need to understand how to read the JSON to understand how this data is structured.
So I need to get the title property out of the response to this REST call. So one of the things I need to do is I need to copy this body that was generated by this test, because we need it for that blueprint for the object. Now I can go back into Power Automate, the flow, and edit it and add a step to parse JSON. You see, we have an action here called "Parse JSON."
The content for this is going to be the body from the HTTP request, which is that response from the REST call, and then for the schema, we want to click "Generate from sample." And the sample is what we just copied from the test that we ran. So I'm pasting in those results, and by doing that, it's going to generate this schema so that Power Automate now knows the structure of this content.
So now that we've done that, I can execute my next REST query, which is going to be to get all the users for that group. So let's do a SharePoint REST call again. It's going to be our same site. It's going to be the demo sites. Their URI is going to be this /SiteGroups/GetByName. But for the name-- we need to put the name in there-- we've got to use the response from this JSON.
So I'm going to insert, from the parsed JSON action, the Title field because you remember, the Title field had the name of the group. So let's click on Title. So now we are calling this query for the owners group for that site. And we need to do a parse Jason on this one as well. So let's go ahead and save this. And we have to run it again so we can get that output so we can pause the JSON.
So let's test it, run the flow, and let's look at our output now. So here is our output for that call, and you can see it's a JSON object again. And you can see that the information for each user is stored in the object. So you can see, here's the email for the user. Here is the title. If we scroll down here, we should see me in there. There's me. You can see, here's my email. Here's my title. So all the information for all the orders gets returned by this rest query.
So I'm going to copy this information, go back to edit the flow. We're again going to add that parsed JSON action, and we are going to use the body from that second HTTP call. And we're going to generate the schema from what we got from our test. And there we are. We're done there. So we now have all the data we need to determine whether the current user is the site owner or not.
So next thing I want to do is I want to initialize a few variables that we're going to use. So it's "init variable." First variable is going to be currentuseremail, which will be a string. And the value of that is going to be the user email for the person who triggered the flow.
Next thing I want to do is initialize one more variable, and that variable is going to hold the email address that we're looking at as we loop through all of the responses for that REST query. So that's going to be a string. So now I'm going to set that email variable, and we're going to set the email variable to-- from the Parse JSON 2 action, this one, we want to get the email from that one. So here is the email property from that object, and you can see, by doing that, Power Automate put us into a loop. So we can loop over each one of those entries because that value was stored in an array in that object. So Power Automate takes care of that for us.
So now we're looping through each one of those emails that get returned, and I want to check to see if the current user is in that list. So I'm going to add a condition. I'm going to check to see if the current user's email is equal to the email variable. And if it is, let's just do a really simple action. Let's send an email. But this is where you would actually put your functionality that the site owner is going to do, that only site owners can do, to restrict that functionality. But let's just do "Send email." We'll send the email to the person who executed the flow, and we'll just say, "Hello site owner," just to prove it works. And save it.
All right, and now we'll test it one last time so you can see all that work together. Any questions? I can't hear you if you have them, I'm sorry.
All right, so that said it was done. Let's make sure it didn't fail. It's going to take a few seconds to iterate over all of them, and it said it succeeded. So let's jump over to email and see if I have an email waiting for me. And look, I have an email waiting for me. It says "Hello site owner" and "Hi." So it executed the code. Got the name of the site owner's group, went through all the users of the site owners group, noticed that I was one of them, and so it recognized me as a site owner. So there's a lot of things you can do with that.
So I wanted to walk through creating the Power Automate flow that actually executed those queries and worked with the data. So we're going to go through a couple more examples now, and for these, we're not going to build them step by step. I know you don't have all that time to do that, and I don't have all the time to do it for you anyway. But I want to walk you through some other ways of using REST, and then we're going to jump to the section for using Office Scripts, which is a really nice piece of functionality.
So back in flow, I am going to look at my flows, and I have one here called Email Price History. And this functionality, I think, has a lot of use because one of my favorite REST endpoints within SharePoint is the version history endpoint because let's say you have something like a list of products, and you want to know what is the price history for this product? When did the prices change? I mean, that's critical information.
Well, I mean, you could send a user to the version history, and they can look at it here, but then they've got to go through every piece of version history. They've got to ignore all the stuff that wasn't price changes. And it's not user friendly. It doesn't look like something you can really report on or anything. So it's not a great user experience to have to go to that version history to track something like a price change.
So what we can do is we can use SharePoint REST endpoint that gives you the version history for an item, and then we can just display just that version history to the user. So in this example flow here, we are going to do just that. So the first thing that this flow does is that for selected item in that list, when somebody modifies the item in that list, we are going to check to see if it was the price that changed because if the price didn't change, we don't care about doing anything more with this flow.
So for the item that's selected we want to get the changes that occurred since the last time it was changed. And then we're going to check to see if the value that changed was the price value because, again, that's what we care about. So if the price is not what changed, we're just going to exit the flow. We don't need to be wasting our time executing the rest of the code. So if the price didn't change, let's leave. But if it was the price that changed, we now want to go through and actually get the user, the owner of that product, the price history for the item.
So we've determined it was the price that changed. So now I'm going to make one of those SharePoint REST queries. And that endpoint is _api/web/lists/GetByTitle, the name of the list, /items, where we passed in the ID of the selected item, and we have this parameter for the versions. And this will return to us all the versions-- the version history for that specific item.
Next thing we have to do is that Parse JSON, which it's old hat to how to parse JSON and how to get that schema. So we'll parse the JSON, and then we're going to go through each item in that array that gets returned, and we're going to check to see, first of all, is it the price that changed? If it was something other than the price that changed from the previous entry in the array, we don't want to mess with it. We, again, only care about price change. If someone saying changed the name of the item or changed who the owner was, this is-- we only care about those price changes. Let's get rid of the noise.
So if it was the price that changed, I'm simply going to create an HTML table with a row in that table for the price, the person who modified it, and when they modified it. And then we'll do that for the entire list of just getting the price changes, finished building out this HTML table, and building out some-- using some variables to detect if it was the price that actually changed.
And the last thing we're going to do is we're going to send an email to the owner of the product and let them know the price changed for an item and give them that price history that is the results of that REST query that we put into an HTML table.
So if we come over to our list and I edit this item to update the price-- let's change it from 750 to 775-- and then let's save that. And that may take just a couple of minutes to run here. So let's go back over to flow and look at our run history. And it succeeded, so let's jump over to my email. And lo and behold, we do have an email that has the price history for the item. It tells us the price was changed, tells us what the previous price was or the new price is, then it has the full price history for the owner of the product.
So again, you can do something else with this data. You could get it into Excel spreadsheet, make a nice graph out of it, maybe use Power BI to create a report on it so you can see a trend line. But this is a way that we can grab that data and process it and do something else with it in Power Automate.
All right, so this brings us to the last portion of our session, and this is where I want to talk about something called Office Scripts. So Office Scripts are a way that you can create JavaScript scripts or Typescripts scripts and execute them from within Power Automate. And there's a lot of power you can do with that.
So to do that, you're going to use Excel, and you're going to record your script or write your script in Excel. And from there, there is an action within Power Automate called the Run Script action, where you specify that function you created in Excel, and you pass in, also, any parameters you need for it. So in this example on the screen here, I'm passing in two numbers that I want to convert-- you can tell by the name of the script, I want to convert to hex. So it's fairly simple, straightforward. So we're going to just jump in and I'm going to show it to you and you can see how that works.
So first thing to know is that the Office Scripts only work for Excel Online. Excel desktop does not even have the option. So to get started with it, you can just create a new Excel workbook, and then, in Excel online, you're going to see at the top a section for Automate. So Automate does not exist in Excel desktop. So if you don't see it, you might be using Excel desktop.
When we click on Automate, you're going to see we have the options to record, to create a new script, and then we see a list of all of the scripts that we have. So if you want to record a script, you could actually come in here, click on Record Script, and it's going to tell you over here on the right-hand side that you are recording. And then you can just start manipulating and doing things to the Excel spreadsheet that you want to do.
Let's say that I want to put a value of $1,000 in this cell, and then, let's say I want to put a formula in this cell. We'll do the decimal to hex. And the value that we want to convert to hex is going to be from that cell-- sorry, this cell. And then we can close it off. Press Enter. Now it's converted. So 3E8 is hex for 1,000.
So now, if I click on Stop Recording, it is going to create a script for us. And if we look at this script, we copy it where we can see it better over here-- so it created the script with a function where it passes in the workbook, and then what it's doing is it is getting the selected sheet we're working on. And you can tell, it is setting the range and getting the range for B2 and C2, which were the cells we're using, and it was setting the values and executing the formula. So this is a script that does all of that now.
So if you really wanted to, you could edit this script and say, well, I don't want to use this value 1,000. I want the user to be able to pass in the value they want to use. I can simply edit the script to say something like, well, I want to pass in my number that is-- it'll be a string. And then we can replace this 1,000 here with mynumber. And by doing that, we have now created a script that you can pass in a value from Power Automate. Power Automate will then execute on-- it'll now take that number that you passed, and stick it in that cell, execute the formula on it, and it's converted to hex. And then you can return that number back to Power Automate, and then use it in your flow. So that's a simple example of a script using Office Scripts.
So I want to show you a more complicated example. So I have another function here called GetPriceHistory. And if we look at GetPriceHistory, this is doing the same thing we were doing in that previous flow, but doing it all in JavaScript and TypeScript. So within my function, I'm passing in the content that's going to be whatever the response is from that SharePoint REST call to get me the versions. And once I do that, I'm going to do a JSON pass on it.
So the same thing we did in flow, we're going to be doing it in JavaScript and say, hey, take this string of stuff, turn it into a JSON object. Let's create an HTML table that's going to store our price history, and then we're going to set some variables for this price and the current price so that we can kind of compare as we loop through all the version history, is it the price that changed?
So now, again, using JavaScript, we're going to just iterate through all of those entries, all of those results from the REST query. I'm going to get the price that is for this current entry. And I'm going to check, hey, was this a price that changed? If it was a price that changed, I'm just going to append that table row to that HTML table. And finally, I'm going to return that table.
So does all that makes sense? Simple function-- passing in the content. This can be the response from the REST call. Write some JavaScript to turn it to a JSON object. Iterate over it. Built out that same HTML table with the price history. And then return that HTML table. So that within Power Automate, if we look at the flows there, you remember how complicated that last flow was-- all those steps we had to do. It was a little bit messy. But using the script method, we can actually do this same functionality, but in much fewer steps.
So now all I have to do is execute that REST query, then do the Run Script action, where I tell it which script I want to execute, where I want to execute the get price history, and I'm just passing to it the body that was returned by that REST query. So we didn't have to parse here in Power Automate. We don't have to do anything else. Make the call, pass the results to this script, and then we want to just send an email to the owner, where we're going to be sending out, in the email body, the result that we got back from the Office Script, so much more simple.
This one is also ordering for a selected item so we could show you both of these working in parallel. So let's go back over to our List, Site Contents, My Products, and then now I can select the item. Go to Automate and then Email Price History. And so run the flow. Says it's done. Let's make sure it didn't break. Says it's running, and this will actually run a little bit faster than the previous flow we created as well. That script is going to run faster than all those actions. Says it succeeded. Email came in. And then there's the same price history for using the Office Script.
So there's two ways of doing things-- calling the SharePoint REST endpoints that your developers may be doing today, and you'll need to understand that. And here's where it gets really important that you understand what's going on with those Office Scripts because here's where we go with the asterisks for the caveats and the, oh, you need to be aware of.
So when you're using these Office Scripts-- those scripts that you're writing are not actually stored in that Excel file. The scripts are stored in the user's OneDrive who created the script. So if you go into OneDrive and you look at the Document section-- so let go over here to my OneDrive, and if you go to OneDrive Documents, there is going to be a folder for Office Scripts. And this is where all of those scripts are stored that you wrote in that Excel spreadsheet.
So may not make the most sense. You wouldn't just think about this logically, or at least I wouldn't. So if your developers are doing this, if they're using these Office Scripts, those scripts are in their OneDrive. And if they leave the organization and you don't know they've done this, those scripts are going to stop working. So be very careful with these being used. Maybe look at something like a service account so you don't run into that issue of a user leaving and then your scripts stop working. Educate your developers on that aspect of it because it's very important.
The other piece is that when you're using these Office Scripts, you are limited to 400 runs per script per day. So if this is going to be some action it's going to be run 1,000 times a day, this is probably not going to be the best solution for you. But for those solutions that make sense, it's a great tool.
The last thing I want you to keep in mind is that with these scripts, you can actually populate an Excel spreadsheet and you can run formulas on data in Excel spreadsheet. But if multiple users are executing the script at the same time, they may be colliding with each other in that spreadsheet, and you want to be careful of that too. So think about your use cases for using Office Scripts and all the things that might go wrong and see if it'll actually fit nicely into your environment without any issues.
So yeah, so here's some links for some more resources if you want to learn more about Office Scripts. It's a great tool. It does make things a lot more easy for us developers to get things done, but I can see how it makes the admins heads explode just a little bit, so education goes a long way here.
And with that being said, we're done. So hopefully you learn some things you didn't know before about Power Automate, some things that you can do that you didn't do before. If you have any questions, please feel free to reach out, I would love to hear you. And have a great day.
[MUSIC PLAYING]