MoreBeerMorePower

Power Platform中心だけど、ノーコード/ローコード系を書いてます。

Set Location-type column in SharePoint list from Power Automate

f:id:mofumofu_dance:20210717020139p:plain

Intro

The Location column in SharePoint list is very distinctive composite type field that stores multiple location properties - Country, Street, Postal-code, Lat/Long etc.., and it shows static map in list view.

f:id:mofumofu_dance:20210716234154p:plain

Unfortunately, it is hard to utilize this field well in Power Apps/Automate, since standard connector does not support to set/update this field.

f:id:mofumofu_dance:20210716235431p:plain

In this post, I will show how to set the Location-type field in Power Automate using SharePoint REST API.

1. SharePoint List

Here, simply assume following list columns, add "Location" type with name "loc":

f:id:mofumofu_dance:20210717005234p:plain

2. Get Location-type column in Power Automate

When you make flow using "Get item" action, the action will return linked columns with Location field, but it is not include coordinates property.

f:id:mofumofu_dance:20210717005608p:plain

To retrieve data more precise/fully, you can use "Send an HTTP request to SharePoint" action with following inputs:

Parameter Value
Method GET
Uri _api/web/lists/GetByTitle('<List name>')/items
Headers {"Accept":"application/json;odata=verbose"}

f:id:mofumofu_dance:20210717020303p:plain

The action output includes Location-type field as stringified & escaped data like,

"loc": "{\"EntityType\":\"LocalBusiness\",\"LocationSource\":\"Bing\",\"LocationUri\":\"https://www.bingapis.com/api/v6/localbusinesses/YN116x205484118?setLang=en\",\"UniqueId\":\"https://www.bingapis.com/api/v6/localbusinesses/YN116x205484118?setLang=en\",\"DisplayName\":\"Google\",\"Address\":{\"Street\":\"395 Page Mill Rd\",\"City\":\"Palo Alto\",\"State\":\"CA\",\"CountryOrRegion\":\"US\",\"PostalCode\":\"94306\"},\"Coordinates\":{\"Latitude\":37.42579650878906,\"Longitude\":-122.13890838623047}}"

3. Set Location-type column by REST API

Once the format of the stored data is known, it is easy to set/update data into Location field with specific coordinates.

f:id:mofumofu_dance:20210717013631p:plain

The first action "Compose" makes JSON object including "DisplayName" and "Coordinates";

{
  "DisplayName": "TEST",
  "Coordinates": {
    "Latitude": 47.595383,
    "Longitude": -122.38659
  }
}

and request body in "Send an HTTP request..." is :

{
"Title":"Add Data from Power Automate",
"loc":"@{replace(string(outputs('Compose')),'"','\"')}"
}

Here, in order to stringify and espace double quote, I have used replace(...) and string(...) functions.

4. How to fill in Address info

Although coordinates and displayname was stored, related address information is not auto-resolved by SharePoint side.

Bing Maps connector can help the case to fill in Address info (City, Street, Postal Code, ...).

f:id:mofumofu_dance:20210717014848p:plain

Remarks

Map image showing in dialog is not available when save data using SharePoint REST API.

This is because, the map image is generated based on "LocationUri" in Bing Map service, but your input coordinates does not have it :(

f:id:mofumofu_dance:20210717015010p:plain

If you would like to display static map on list view, view formatting can help this case. (For a detail, see following reference)

docs.microsoft.com