Okay, I admit that the title of this post is a bit 1984’ish. Trust me, that is not the intention, so let me explain this a bit before showing you how to build a production-ready solution using Office 365 and Microsoft Azure for locating your dearest and nearest colleagues when you’re out and about.
I run my own company, Onsight Helsinki, and we’re a team of about 30 people and counting. One of the major philosophies that I try to cultivate within our company culture is flexibility and freedom, along with the corresponding responsibility. What this boils down to is the fact that often our people are scattered around Finland and surrounding countries with their projects, classroom training, and those endless Skype conference calls.
We still have physical desks. Everybody has a dedicated desk because I felt it would make people feel more comfortable on those occasional days when they do visit the office.
So, the view when I (rarely) visit the office is something like in the picture below.
Where’s everybody? I can always check Skype for Business, but it doesn’t help me much.
Armed with this dilemma, I started building a solution that would allow me to provide a way for people to post their location, either semi-automatically or as a fully automated solution. You might call it a tracking solution, but I’d be inclined to call this a voluntary “hey, I’m currently here” location beacon. It could just be that a team member is currently staying in the same hotel as I am, unbeknownst to me. This has happened for us more than once.
First off, I wanted my solution to contain as little custom code as possible. Not because I dislike writing code, but because for such a small solution as this a huge amount of custom code tends to distract us in the long run. It’s also easier for someone else to pick up from here and expand the solution. We built a more one-off solution than a platform for this very specific need.
I started drawing out the overall architecture on a napkin while having solitary lunch in early January. Unfortunately, the napkin did not survive.
I wanted people to have a mechanism for checking in — wherever they are. When they’ve checked in, we record the GPS latitude and longitude coordinates of the device and save them to a shared repository. That would be a SharePoint Online-based custom list as it’s so easy to use, and it’s very flexible should the needs of the solution grow in the future.
From a SharePoint-based list, we can then pick the location metadata and plot people’s location on a map, which I can easily create with Power BI. The same Power BI visualization is also available through the Power BI mobile app for a quick glance of everyone’s whereabouts.
For people to check in using the solution, we decided to use Microsoft Flow — it has a handy button-based triggering mechanism and it can retrieve the latitude and longitude coordinates automatically from the device. With a recent update, Flow can also capture mandatory metadata as part of the trigger event — such as the person’s name.
Power BI is more than happy with latitude and longitude values, but it’s less useful for human eyes. In case you just want to produce a report of everyone’s locations — say, a street address perhaps — I’m employing Azure Functions. With this serverless approach, we have a smart and endlessly flexible solution to gather more data based on simple latitude and longitude value.
Building the Solution
Microsoft Flow is highly useful for building simple or more complex workflows without much deliberation. The process I built is rather simple, and it looks like this:
The Flow is triggered manually on the mobile device through a button, which is visible on a mobile device. This is step 1 in the Flow above. The button also captures the user’s first and last name, which we’ll use to differentiate between multiple check-ins.
During step 2, we’re calling our custom Azure Function, which is used to gather more metadata besides the crude location of the device. This is also a great extension point for future changes, should we have a need for something more elaborate.
Google Maps provides a nifty set of APIs that help us achieve just this. By calling the Google Maps Geocode API, I can pass on a latitude and a longitude and get Google Maps based data in return. For more information on this API, see here.
Unfortunately, Microsoft Flow is somewhat lacking in its capability to call external APIs with finely tuned parameters and contracts, so this is where Azure Functions comes into play.
We created a simple C#-based Azure Function, that takes a latitude and longitude parameter in the HTTP request, passes them on to Google Maps Geocode API with my developer key, and gets the street address (and a lot of other data that I might need later on) back. The Flow calls my function with the mobile device’s latitude and longitude coordinates, which subsequently passes them on to Google Maps, and we get information back.
The relevant portion of the code is here:
// parse query parameter
string latlon = req.GetQueryNameValuePairs()
.FirstOrDefault(q => string.Compare(q.Key, "latlon", true) == 0)
// Set name to query string or body data
latlon = latlon ?? data?.latlon;
var URI = "https://maps.googleapis.com/maps/api/geocode/json?latlng=" + latlon + "&key=<InsertDeveloperKeyHere>";
// // call Google LatLon API
var request = System.Net.WebRequest.Create(URI) as HttpWebRequest;
request.Method = "POST";
request.ContentType = "application/json";
request.ContentLength = 0;
WebResponse response = request.GetResponse ();
StreamReader reader = new StreamReader(response.GetResponseStream());
string json = reader.ReadToEnd();
We can now deserialize the response data (which is a JSON dataset essentially), and look up any information we might need. We’re picking the formatted street address for now, and storing that to the SharePoint list at step 3.
Flow Designer makes it immensely easy to get data from an external API and simply use the response when populating data back to SharePoint Online.
This is the raw data that Flow is able to capture from mobile devices, that are then stored in the SharePoint list.
The Location column holds the data from Google Maps, and Lat and Lon columns contain the exact coordinates for the mobile device at the time of the check in. In addition, we’re using a timestamp column (the Check-in time-column) to filter out older check-ins.
On my Google Nexus 6P, an Android-based phone, the experience looks like this:
Finally, putting the data in a visualization with Power BI is easy. Power BI can employ data from SharePoint Online, and as we have the exact coordinates, we can use a Map control to visualize location data.
In the picture above you can see I’ve checked in from Dublin, and my colleague Heidi checked in from Espoo, a city next to Helsinki in Finland.
It took me about 4 hours to build the end-to-end solution. The trickiest part was to build the Azure Function, as I had to figure out what kind of data I am getting from Google Maps, and how to best deserialize the data in a class that I would find comfortable to use in my solution. Everything else — the Flow, SharePoint list, and Power BI solution is a point-and-click exercise, which allows for an easy trial and error while validating the results.