A friend of mine asked whether there were any ‘simple’ techniques to stop / start an Azure VM from a mobile application.

Obviously they could use the Azure Portal directly from the browser app on their phone, but this was deemed ‘not-simple-enough’.

Before we get into the solution, if you want to try this for yourself you will need:

  1. An O365 Account with Flow and PowerApps enabled.
  2. An Azure Account with a single VM (v2) (make a note of the Name and it’s ResourceGroup Name).

So, being a big fan of Microsoft Flow I came up with the following Flow Template:

image

The trigger is simple: ‘when a new item is created in a SharePoint list’. Based on the value of the Action field do something.

To create the Flow Template, lets first start with the SharePoint List. 

Here is the SharePoint List:

image

Just two columns: Title which by default is created when a new SharePoint list is created; Action which I have created as a Choice with two values Stop and Start. That is it, that is the SharePoint List in its entirety. To create the Flow, choose the first Flow Template in the list.

image

On selecting the template you will be redirected to the Flow website. On editing the Flow Template you will be presented with the following flow:

image

Remove the last two actions so that you only see the When a new item is created action. Keep Flow open as we will be coming back to it later to complete the template.

Back in SharePoint, now click the PowerApps drop down. By clicking this dropdown, PowerApps will automagically build you a mobile application based on your list. In 2 minutes you have a working mobile application that you can publish and then share. That’s all I am going to cover in PowerApps, the application it builds gives us everything we need. Once you have published the app, go to your mobile device, download the PowerApps app for your device, login with your O365 Account used to create the SharePoint List and you should see the PowerApp created above to open and run. You could even pin the new app to your Home screen thereby bypassing the need to open PowerApps first (PowerApps will open in the background).

So now you have your trigger and your mobile app. All we need to do now is add the actions when a SharePoint List item is added with an action of Start and when an action is added of type Stop. For this I am going to use Azure Automation Runbooks:

To create the Runbook log into the Azure Portal and open your Azure Automation Account. If you don’t have one, click +New and search for Automation. Once your Automation Account is open click on the Runbooks option under Process Automation. For the purposes of this solution we are going to use two Gallery Runbooks, so choose Browse Gallery and search for StartAzureV2Vm and StopAzureV2Vm (Graphical)…for each, choose Import. For more information on how to create or import a Runbook please follow this document.

Once you have both Runbooks in your Account choose one of them and select Edit. For the purpose of this solution we want to stop start a single VM, so edit the Graphical Runbook and edit them so that once the script has connected to Azure, get a single VM only. Your Graphical Runbook should then look something like this:

image

Publish your Runbooks and try each of them out. You will be asked for the name ResourceGroup of the VM and the name of the Single VM.

If they both run successfully we now want to add a Webhook for each Runbook so that we can run the Runbooks remotely via our Flow template. For information on how to o create the Webhook for each Runbook please follow this document. Note, you only get the option once to copy the Webhook Url so make sure you click the Copy button. For ease, I pre-populated the RESOURCEGROUPNAME and VMNAME. As a strectch goal, you may want to try passing these values as part of the request – to do that you should add 2 extra columns to your SharePoint List so that once you have re-created the PowerApp you will have the option of entering the names which you can then use as part of your Flow.

Once you have the Webhooks for the Stop and Start Runbook, it’s now just a case of completing the Flow Template.

image

Following the Trigger, create a Condition. The Condition then is based on the Action Value if it is equal to Start then do the If yes Action, else do the If no Action. The Action is simply a HTTP and choose the Method POST. The Url is either the Start or Stop Runbook Webhook Url you created previously.

Voila! You can know use a mobile app to stop start your Azure VM.

This is obviously a very simple case, but hopefully it gives you food for thought on how you can use a mix of O365 and Azure Automation to manage your Azure Assets.

If you haven’t read my Part 1 of this series then let me recap. Last week the Microsoft Office team announced the general availability of Excel REST APIs for the Microsoft Graph. With these new endpoints it is now possible to interact with your existing Excel Workbooks using REST from your mobile, desktop and web apps without the need to have to open the Excel app – pretty awesome eh?

In my previous post I demonstrated how you can call one of the 300+ built-in functions that Excel supports out-of-the-box. Sometimes though, the built-in functions that come with Excel are not enough. Sometimes we need to build our own Excel calculations to support our own LoB reports and visualisations. In this post I will demonstrate how you can re-use that functionality within your own modern day web and mobile apps.

For the purposes of this post I have created a simple Restaurant Bill Calculator within Excel:.

Excel Bill Calculator

This is a very simple example but it’s the concept of re-use of the calculation logic, in this case Bill+Tip and Amount per person, that I am trying to convey not it’s complexity.

I then created a visual representation of the Excel Workbook in an ASP.NET application – of course I didn’t need to create a web app, I could have created just a web API or a Console app,

Web App Calculator

So in this example we use the new Excel REST API to set the Excel Workbook named items, Bill Amount, Tip, and Number in Party. We then use the new Excel REST API to get the results by asking for the Excel Workbook named items Total Bill and Amount per Person..

In summary, complex business models no longer need to be rebuilt repeatedly; developers can leverage Excel to perform those calculations instantly and retrieve the results with simple API calls.

The full source-code for my sample can be found be here. Please refer to the readme file for pre-requisites and how to configure the app based on your Azure and O365 accounts. One important note, the sample relies on the fact that you have an Excel workbook called RestaurantBillCalculator.xlsx in the root of your tenant’s OneDrive – if the file does not exist the file is uploaded when you run the application for the first time. There are obvious changes that can be made to code to be more performant such as a namedItem that contains all of the fields rather than a nameditem per field, and not checking if the file exists each time.

If you want to start writing your own apps that use the Microsoft Graph then check out the following blog on how to get started with the Graph Excel REST API and as always, refer the Microsoft Graph API documentation for examples and syntax.

Its been awhile since my first post, but I have been waiting for the big announcement that I’m sure is going to change people’s mind-sets on how they interact with Excel today – introducing to you the general availability of the Microsoft Graph REST APIs for Excel.

In this post I am going to give you an example of how to use Excel as a Calculation Service, demonstrating how you can call one of the 300+ Excel functions from a ASP.NET MVC application using the Graph REST APIs for Excel.

The function I am going to call is the one highlighted in the GA blog post – PMT. The Excel PMT function calculates the constant periodic payment required to pay off (or partially pay off) a loan or investment, with a constant interest rate, over a specified period. The syntax of the function is:

PMT(rate, nper, pv, [fv], [type]]

Where:

rate = the interest rate per period as a percentage

nper = the number of periods over which the loan or investment is to be paid

pv = the present value of the loan / investment

[fv] = an optional argument that specifies the future value of the loan / investment, at the end of the nper payments

[type] = an optional argument that defines whether the payment is made at the start or the end of the period.

This function translates to the following REST API request:

POST …/workbook/functions/pmt { “rate”: 0.035, “nper”: 20, “pv”: -2000 }

Note, don’t fall into the same trap as I did when calling the function – as it is a calculation the values of the post request (body content) are numbers not strings!!

Check out the following blog on how to get started with the Graph Excel REST API and as always, refer the Microsoft Graph API documentation for examples and syntax.

The full source-code demonstrating how to call the PMT function using the Graph REST API can be found be here. Please refer to the readme file for pre-requisites and how to configure the app based on your Azure and O365 accounts. One important note, the sample relies on the fact that you have an Excel workbook called book.xlsx in the root of your tenant’s OneDrive. To make the solution more user friendly to a wider audience, why not try extending the code to automatically create a new workbook if Book.xlsx does not exist. [Hint, my next post includes the code to show you how this can be done]

In my next post I will demonstrate how to you can re-use your own existing Excel workbook functions in modern day web applications.  .