Walkthrough: Add Geolocation column to your list in Office 365

A while ago a client (with an Office 365 E3 subscription) came to us with the wish to create a map to plot locations of external contractors on. My first thoughts, as an Office 365 consultant, went towards using the tools at hand. SharePoint 2013/Online has a Geolocation column type and the list view type "Map view". The client agreed to use this feature and I went about setting up the solution. So I posed the self-fulfilling prophecy: "How hard can it be?"...

As the list with the data was already in place, I was neither keen on letting a developer create a solution which either creates a new list with the column in it (and me migrating data) nor writing a solution which adds the column programmatically once. I wanted to add the column directly through (a reusable) script and went on to do my desk research. This ended up taking way too much time as almost all information found…

  • ...were solutions for SharePoint 2013 on premise,
  • ...were articles on the end-result,
  • ...posted failing scripts,
  • ...did not offer information on the Bing Maps key,
  • ...did not offer guides/information specifically for Office 365/SharePoint Online scenario's.

Something as simple as "what to use as the Bing Maps application URL for an Office 365 tenant?" was not to be found.

It took a while, but when I finally got the settings right for a Bing Maps key and a working script, I decided on 2 things;

  1. Create a generic script, because as a consultant I'll want to use this script more than once for multiple tenants.
  2. Write a blog post as a definitive guide to add the geolocation column type in Office 365/SharePoint Online as a resource for the community

Scenario

For the walkthrough I'm using the following scenario; As a global admin for the tenant https://yourcompany.sharepoint.com, I'm adding the geolocation column type to the list "Contact" on the sub site https://yourcompany.sharepoint.com/sites/sales and naming the column "Office".

Step 1. Get a Bing Maps Key

Go to Bings Maps Dev Center; https://www.bingmapsportal.com/

Log in with your Live account (@live.com, @outlook.com, etcetera) or create one to gain access.

Go to My account > Create or view keys

bingmapsdev1
bingmapsdev1

To create a new API key follow the "Click here to create a new key" hyperlink

bingmapsdev2
bingmapsdev2
bingmapsdev3
bingmapsdev3

Fill out the form to create your API key

. Application name: The name you would like to use for your key. It helps you to identify the key in your overview

. Application URL: The URL of your root SharePoint portal (https://tenant.sharepoint.com)

. Key type (Trial/Basic): Choose whether you're using the key for 1) a test site (max 10,000 calls p/mth and max 90-day trial period) or 2) a live site (free for max 125,000 calls p/yr) (more info here)

. Application type: What is the application? App, site,for non-profit use, etc.

In this scenario, the admin fills it out;

  • Application name: Sales Office
  • Application URL: https://yourcompany.sharepoint.com
  • Key type: Basic
  • Application Type: Public Website

After you click Create and the Captcha was filled out correctly, the page refreshes and displays your new key below. You'll receive a 64 character key.

bingmapsdev4
bingmapsdev4

Step 2. Gather required information

For the script to run properly, you'll need the following information;

. Site URL: URL to the site where the list is.

. Login account: at least admin permission as you're changing list settings.

. List Name: name of the list to add the geolocation column type.

. Column Name: title of the geolocation column.

. Bing Maps Key: to register the app and remove the notification in map view.

In this example, the admin has gathered the following info;

  • Site URL: https://yourcompany.sharepoint.com/sites/sales
  • Creds: Admin@YourCompany.onmicrosoft.com
  • List Name: Contact
  • Column Name: Office
  • Bing Maps Key: [PASTE KEY HERE]

Now we can run the script.

Step 3. Run script

Start SharePoint Online Management Shell as administrator

If you don't have SharePoint Online Management Shell, you can download it @ Microsoft Download Center

set-executionpolicy Unrestricted Clear-Host [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') <# Get User input #> $SiteURL = [Microsoft.VisualBasic.Interaction]::InputBox("Enter Site URL, example: https://yourtenant.sharepoint.com/sites/yoursite", "URL", "") $Login = [Microsoft.VisualBasic.Interaction]::InputBox("Office 365 Username, example: youradmin@yourtenant.onmicrosoft.com", "Username", "") $ListName = [Microsoft.VisualBasic.Interaction]::InputBox("List name to add Geolocation column", "ListName", "") $ColumnName = [Microsoft.VisualBasic.Interaction]::InputBox("Column name for the Geolocation column", "ColumnName", "") $BingMapsKey = [Microsoft.VisualBasic.Interaction]::InputBox("Bing Maps key", "Key", "") <# Show results #> Write-Host "/// Values entered for use in script ///" -foregroundcolor magenta Write-Host "Site: " -foregroundcolor white -nonewline; Write-Host $SiteURL -foregroundcolor green Write-Host "Useraccount: " -foregroundcolor white -nonewline; Write-Host $Login -foregroundcolor green Write-Host "List name: " -foregroundcolor white -nonewline; Write-Host $ListName -foregroundcolor green Write-Host "Geolocation column name: " -foregroundcolor white -nonewline; Write-Host $ColumnName -foregroundcolor green Write-Host "Bing Maps key: " -foregroundcolor white -nonewline; Write-Host $BingMapsKey -foregroundcolor green Write-Host " " <# Confirm before proceed #> Write-Host "Are these values correct? (Y/N) " -foregroundcolor yellow -nonewline; $confirmation = Read-Host if ($confirmation -eq 'y') { $WebUrl = $SiteURL $EmailAddress = $Login $Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl) $Credentials = Get-Credential -UserName $EmailAddress -Message "Please enter your Office 365 Password" $Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($EmailAddress,$Credentials.Password) $List = $Context.Web.Lists.GetByTitle("$ListName") $FieldXml = "<Field Type='Geolocation' DisplayName='$ColumnName'/>" $Option=[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView $List.Fields.AddFieldAsXml($fieldxml,$true,$option) $Context.Load($list) $Context.ExecuteQuery() $web = $Context.Web $web.AllProperties["BING_MAPS_KEY"] = $BingMapsKey $web.Update() $Context.ExecuteQuery() $Context.Dispose() Write-Host " " Write-Host "Done!" -foregroundcolor green Write-Host " " } else { Write-Host " " Write-Host "Script cancelled" -foregroundcolor red Write-Host " " }
The actual programming part in the script I modified from the script posted ina blog post by Albert Hoitingh. I wanted to remove the hardcoded values from the code, so the script can be run based on user inputs. So I added the interface (input boxes, confirmation, write-hosts), replaced the hard coded values and added comments.

When you run the script, PowerShell will ask the user to input the information we gathered in Step 2.

geoscript1
geoscript1
geoscript2
geoscript2
geoscript3
geoscript3
geoscript4
geoscript4
geoscript5
geoscript5

After the last values have been entered, the admin will see a confirmation screen where the values can be review and confirmed (if the input is incorrect, the script can be cancelled by entering "N" to not proceed - screenshot).

geoscript6
geoscript6

After confirmation, the admin will be prompted to enter the password.

geoscript7
geoscript7

If everything was filled out correctly, the script will run and returns with the "Done!" notification upon completion.

geoscript8
geoscript8

Return to your SharePoint online list and you'll notice when creating a new view for your list "Contact", you gained the option Map View. When checking the list settings, the column "Office" has been added of the type Geolocation.

mapview1
mapview1

Are you missing information, do you want me to clarify anything, do you want to post a conversation starter or do you just want to say thanks? Leave a comment.