Automation: Rest API Call to Excel
There’s a few things you need to know but I’ll make sure to link how to get the following:
Goal: Get info from Rest API call and add them to a row in the table (Excel).
In this example, I’ll be using Windows Security Center, https://security.microsoft.com/ for my API.
NOTE: While writing this blog, I came across Anders Jensen’s video on his channel, go check him out for more Power Automate!
Step 1: Open and create a new flow, in my example, I’ve created “Manually trigger a flow”.
Step 2: Add new step and make sure to add the App Registration information on this step
Step 3: Create a step for Parse JSON. If you have multiple HTTP, please make sure to name them accordingly so you can keep track of ‘Content’ in this step. Select the “Body” from the HTTP step
Step 4: Generating the schema can be easy if you have the result of the REST API. This is why I recommend creating a Powershell script before trying to automate using Power Automate. If you run the REST API successfully, you should be able to generate the body. At this point, you can copy the output and click the “Generate from sample” and paste the output. Thankfully, Microsoft 365 Defender Website has an API explorer which I can grab the output to create a sample.
Step 5: Create a step to “Add a row into a table” and make sure to link the location and the table. Set up the dates and the property of your choosing. In this case, the properties I need are the Exposure Score and Secure Score.
In Summary - Make sure to use APP Registration for your Power Automate flow. And before diving into Power Automate, make sure to know what you need or want, and it would be helpful if you could manually do it yourself by create a script.
Please let me know your thoughts down below or tweet at me.
