Add an assembly reference in an Azure Function

When we develop solutions in Visual Studio, we can add references to the assemblies we need in our code. When we create an Azure Function, we do not have this option in the user interface in our browser.

But adding a reference is easy. To add it, just write all reference names with a leading ‘#r’ on top of the csx-file, as shown in the following image.

Get current location in PowerApps with Azure Functions

When creating an app in PowerApps, we can use Azure Functions to add functionality that is not available in PowerApps. Additionally we can use Azure Functions to make business logic available in a PowerApps. In the PowerApps overview pages, Microsoft provides a simple example, how to add an Azure Function to a PowerApp.

This short article will show a practical example for an Azure function. In PowerApps we have access to the current location of the user. That could be helpful in a mobile app on the phone. But, PowerApps does not have function, to get the current location (the city) of the user.

With the help of the Google Maps API, we can create an Azure Function to get the current location from the longitude and the latitude. The code for our function is shown in the following listing.

#r "System.Web.Extensions"

using System.Net;
using System.Web;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
    log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");

    // parse query parameter
    string longitude = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "long", true) == 0)
    string latitude = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "lat", true) == 0)
    // Get request body
    dynamic data = await req.Content.ReadAsAsync<object>();

    if ((longitude != null) && (latitude != null))
        string str = DoSomething(latitude, longitude);

        return req.CreateResponse(HttpStatusCode.OK, str);
        return req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a langitude and a latitude on the query string or in the request body.");

private static string DoSomething(string latitude, string longitude)
    string result = String.Empty;

    var webAddress = String.Format("{0},{1}", latitude, longitude);
    var client = new WebClient();
    var content = client.DownloadString(webAddress);

    var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

    var dict = serializer.Deserialize<Dictionary<string, dynamic>>(content);

    var status = dict["status"];

    if (status == "OK")
        var results = dict["results"][0];
        var address_components = results["address_components"];
        var locality = address_components[2]["long_name"];

        result = locality;
        // error handling

    return result;

To make the Azure Function available in PowerApps, we need a Swagger definition file, as in the following example.

  "swagger": "2.0",
  "info": {
    "version": "1.0.0",
    "title": "GetLocationFromGoogleMaps"
  "host": "",
  "paths": {
    "/api/getlocationfromgooglemaps": {
      "get": {
        "description": "Calls my azure function over https",
        "operationId": "Run",
        "parameters": [
            "name": "code",
            "in": "query",
            "description": "code",
            "default": "{enter the key from your Azure Function here}",
            "type": "string"
            "name": "long",
            "in": "query",
            "required": true,
            "type": "string"
            "name": "lat",
            "in": "query",
            "required": true,
            "type": "string"
        "responses": {
          "200": {
            "description": "Successful response",
            "schema": {
              "title": "The response of the api.",
              "type": "string"

How to add this Swagger definition file to the PowerApps environment of our tenant is shown in the Microsoft example for using Azure Functions in PowerApps.

To use the function in our PowerApp, we use a very simple app. We have three fields to show the longitude, the latitude and our current location. The field for the location is a simple text field, where the Text property of the field was set to the result of the function call of our Azure Function.

The PowerApp will look like this, when we start it on a mobile phone:

In this example for the combination of PowerApps and Azure Functions I used the Google Maps API, but with a developer key we can also do the same with Bing Maps.


Add Lookup Field with Office 365 PnP PowerShell

The PowerShell extensions from the Office 365 Patterns and Practices provide a cmdlet to create a new field in a list (or in the site columns) from an xml. That could perfectly be used to create a lookup field. The xml file should look similar to this (in the example this file is named LookupField.xml):


But, to be able to create the field with this xml file, we need to replace the name of the list in the list attribute with the Id of the list. This could be done with the following script (PatchLookupFieldXml.ps1) that returns the modified xml from the file:

param (

$content = Get-Content $LookupFieldXml
$content = [string]$content

$xmlDoc = New-Object System.Xml.XmlDocument

[System.Xml.XmlElement]$root = $xmlDoc.DocumentElement

$listName = $root.List

if ($listName -ne $null -and $listName -ne "")
    $list = Get-SPOList -Identity $listName

    $listId = $list.Id

    $root.List = "{$listid}"

    Write-Output $xmlDoc.InnerXml
    Write-Output $null

Now we can use these two simple lines to add our lookup field:

$fieldXml = .\PatchLookupFieldXml.ps1 -LookupFieldXml C:\Temp\LookupField.xml
Add-SPOFieldFromXml -FieldXml $fieldXml -List "Documents"


Filter data by the current user in PowerApps

In an app in PowerApps it could be necessary to filter the data by the current user, who is using the app. To achieve this set the Items property of the Gallery to something like this:

SortByColumns(Filter(Timesheet, Author.Email = User().Email), “Date”, If(SortDescending1, Descending, Ascending))

The trick is in the Filter function (also see By using the formula

Author.Email = User().Email

only data created by the current user is shown.

Instead of the Email member you can also use the FullName member. In this case the formula looks like

Author.FullName = User().FullName

Move PowerApps app to another tenant

When you have created a PowerApps App for a list in SharePoint Online and you need to move this app to another tenant (I often create these apps in demo tenants), then you can use the following steps to move your app to another tenant.

Extract the app and the list from the current tenant:

  1. Save your app in the PowerApps Studio as a file to your hard disk
  2. Save your list in SharePoint Online as a list template
  3. Download the saved list template to your hard disk

Add the list and the app to the new tenant:

  1. Upload the list template to the site collection where the list should be created
  2. Create the list in the desired site by using the list template. Use the same name for the list as in the source
  3. Open the PowerApps file in the PowerApps Studio (you should be logged in with a user for the new tenant)
  4. Remove the data source in the app
  5. Add a data source to the app, pointing to the list you created in the previous steps in SharePoint Online
  6. Save the app to the cloud

That’s it. Your app should now be operational in the new tenant. Depending on your app it might be necessary to set the permissions in the SharePoint list and to share the PowerApps app, so it could be used by other users in the tenant.

Connect to Exchange Online with PowerShell

To connect to Exchange Online in Office 365 open a PowerShell prompt and simply run the following commands:

if ($cred -eq $null)
	$cred = Get-Credential

Write-Host "Connecting to Exchange Online"

$exchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -Credential $cred -Authentication Basic -AllowRedirection

Import-PSSession $exchangeSession | Out-Null

Write-Host "Connected to Exchange Online"

Find this script also on Git

Remove the Product Key in Office 2013/2016

Very simple. Just use ospp.vps that could be found in C:\Program Files\Microsoft Office\OfficeNN or C:\Program Files (x86)\Microsoft Office\OfficeNN.

See description in