SOLVED
Home

GeoLocations - Upload values to existing/new List

%3CLINGO-SUB%20id%3D%22lingo-sub-354940%22%20slang%3D%22en-US%22%3EGeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354940%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20are%20the%20possible%20options%20to%20upload%20geo%20coordinates%20into%20a%20GeoLocation%20field%20of%20a%20list.%3C%2FP%3E%3CP%3ECan%20the%20values%20be%20uploaded%20from%20Excel%3F%3C%2FP%3E%3CP%3ECan%20we%26nbsp%3Bconvert%20%26amp%3B%20assign%20single%20text%20values%20to%20a%20GeoLocation%20column%3F%3C%2FP%3E%3CP%3EPowershell%3F%3C%2FP%3E%3CP%3EThere%20are%26nbsp%3B2%20options%20to%20code%20it%2C%20as%26nbsp%3Bdocumented%20in%20the%20link%20below%2C%20but%20are%20there%20other%20options%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fgeneral-development%2Fhow-to-add-a-geolocation-column-to-a-list-programmatically-in-sharepoint%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fgeneral-development%2Fhow-to-add-a-geolocation-column-to-a-list-programmatically-in-sharepoint%3C%2FA%3E%3C%2FP%3E%3CP%3ETx%20%26amp%3B%20brgds.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354940%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPnP%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358820%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358820%22%20slang%3D%22en-US%22%3EHi%2C%20tx%20for%20your%20reply.%20The%20screenshot%20is%20classic%20experience.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358814%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358814%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ethere%20is%20no%20difference%20from%20classic%20or%20modern%20experience%20in%20creation%20%2F%20configuration.%3C%2FP%3E%3CP%3EI%20see%20difference%20only%20in%20editing.%3C%2FP%3E%3CP%3EWhat%20about%20your%20screenshot%3F%20Is%20it%20modern%20experience%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358800%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358800%22%20slang%3D%22en-US%22%3EHi%20Federico%2C%20that's%20strange.%20The%20list%20from%20which%20I%20start%20is%20configured%20for%20Classic%20experience.%20Any%20idea%20what%20other%20configuration%20settings%20could%20have%20an%20impact%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358369%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358369%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142%22%20target%3D%22_blank%22%3E%40Eric%20Deferm%3C%2FA%3E%2C%20try%20classic%20experience%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358316%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358316%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F138334%22%20target%3D%22_blank%22%3E%40Federico%20Porceddu%3C%2FA%3E%2C%20didn't%20know%20one%20could%20also%20specify%20the%20Altitude%20%26amp%3B%20Measure.%20The%20programming%20interface%20supports%204%20values%2C%20whereas%20the%20user%20interface%20only%20allows%20you%20to%20enter%202%20fields%20(Long.%20%26amp%3B%20Lat.)%20or%20does%20your%20interface%20look%20different%20from%20mines%3F%20Tx.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20350px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84689i2D4945FEC757A471%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22GeoLocationField-EditDialogBox.png%22%20title%3D%22GeoLocationField-EditDialogBox.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358145%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358145%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142%22%20target%3D%22_blank%22%3E%40Eric%20Deferm%3C%2FA%3E%2C%20nice%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Einstead%20use%3C%2FP%3E%3CPRE%3EoListItem%5B%22Location%22%5D%20%3D%20%22POINT%20(%22%20%2B%20xlRange.Cells%5Bi%2C%2010%5D.Value2.Replace('%2C'%2C%20'.')%20%2B%3C%2FPRE%3E%3CP%3Eyou%20can%20use%20(numbers%20123%20are%20i.e.)%3C%2FP%3E%3CPRE%3E%20%20%0Avar%20geolocationValue%20%3D%20new%20FieldGeolocationValue%0A%7B%0AAltitude%20%3D%20Double.Parse(%22123%22)%2C%0ALatitude%20%3D%20Double.Parse(%22123%22)%2C%20%0ALongitude%20%3D%20Double.Parse(%22123%22)%2C%0AMeasure%20%3D%20Double.Parse(%22123%22)%2C%0A%7D%3B%0AoListItem%5B%22Location%22%5D%20%3D%20geolocationValue%3B%3C%2FPRE%3E%3CP%3ECheers%2C%20Federico%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358122%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358122%22%20slang%3D%22en-US%22%3E%3CP%3ETx%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F138334%22%20target%3D%22_blank%22%3E%40Federico%20Porceddu%3C%2FA%3E%26nbsp%3B%20for%20your%20extended%20reply%2C%20elaborating%20on%20several%20potential%20options%2Falternatives.%3C%2FP%3E%3CP%3EApologies%20for%20my%20delayed%20reply%2C%20finally%20I%20decided%20to%20go%20for%20some%20C%23%20coding%20%26amp%3B%20Visual%20Studio.%20As%20a%20newby%20to%20VS%2C%20it%20took%20me%20a%20while%20to%20get%20everything%20right.%20As%20there's%20no%20attach%20option%20%3A(%3C%2Fimg%3E%20%2C%20I'm%20sharing%20code%20below%20(probably%20not%20fully%20according%20to%20best%20practices%20on%20C%23%20programing%2C%20but%20it%20worked%20for%20me).%26nbsp%3B%20I'll%20sure%20will%20also%20check%20the%20PS%20solution%20and%20let%20you%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%2F%2F%20%3CA%20href%3D%22https%3A%2F%2Fcoderwall.com%2Fp%2Fapp3ya%2Fread-excel-file-in-c%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcoderwall.com%2Fp%2Fapp3ya%2Fread-excel-file-in-c%3C%2FA%3E%3CBR%20%2F%3E%2F%2F%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fgeneral-development%2Fintegrating-location-and-map-functionality-in-sharepoint%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fgeneral-development%2Fintegrating-location-and-map-functionality-in-sharepoint%3C%2FA%3E%3CBR%20%2F%3E%2F%2F%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fgeneral-development%2Fhow-to-add-a-geolocation-column-to-a-list-programmatically-in-sharepoint%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fgeneral-development%2Fhow-to-add-a-geolocation-column-to-a-list-programmatically-in-sharepoint%3C%2FA%3E%3CBR%20%2F%3E%2F%2F%20upload%20the%20necessary%20assmeblies%20%26amp%3B%20references%20in%20your%20solution%3C%2FP%3E%3CP%3Eusing%20Microsoft.SharePoint.Client%3B%3CBR%20%2F%3Eusing%20System%3B%3CBR%20%2F%3Eusing%20System.Security%3B%3CBR%20%2F%3Eusing%20System.Collections.Generic%3B%3CBR%20%2F%3Eusing%20System.Linq%3B%3CBR%20%2F%3Eusing%20System.Text%3B%3CBR%20%2F%3Eusing%20System.Threading.Tasks%3B%3CBR%20%2F%3Eusing%20Excel%20%3D%20Microsoft.Office.Interop.Excel%3B%3C%2FP%3E%3CP%3Enamespace%20ConsoleApp1%3CBR%20%2F%3E%7B%3CBR%20%2F%3Eclass%20Program%3CBR%20%2F%3E%7B%3CBR%20%2F%3Estatic%20void%20Main(string%5B%5D%20args)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%2F%2FCreate%20COM%20Objects.%20Create%20a%20COM%20object%20for%20everything%20that%20is%20referenced%3CBR%20%2F%3EExcel.Application%20xlApp%20%3D%20new%20Excel.Application()%3B%3CBR%20%2F%3EExcel.Workbook%20xlWorkbook%20%3D%20xlApp.Workbooks.Open(%22c%3A%5C%5Ctemp%5C%5CSites2.xlsx%22)%3B%3CBR%20%2F%3EExcel._Worksheet%20xlWorksheet%20%3D%20xlWorkbook.Sheets%5B1%5D%3B%3CBR%20%2F%3EExcel.Range%20xlRange%20%3D%20xlWorksheet.UsedRange%3B%3CBR%20%2F%3Eint%20rowCount%20%3D%20xlRange.Rows.Count%3B%3CBR%20%2F%3Eint%20colCount%20%3D%20xlRange.Columns.Count%3B%3CBR%20%2F%3E%3CBR%20%2F%3EClientContext%20context%20%3D%20new%20ClientContext(%22https%3A%2F%2F%3CTENANT%3E.sharepoint.com%2F%3CSITE-COLLECTION%3E%22)%3B%3CBR%20%2F%3EsetOnlineCredential(%22%3CUSERNAME%3E%22%2C%20%22%3CPASSWORD%3E%22%2C%20context)%3B%20%2F%2Frequired%20in%20our%20case%20due%20to%20SSO%3CBR%20%2F%3EAddListFields(context)%3B%3CBR%20%2F%3EConsole.WriteLine(%22Fields%20added%20successfully%22)%3B%3CBR%20%2F%3EAddListItem(context%2C%20rowCount%2C%20colCount%2C%20xlRange)%3B%3CBR%20%2F%3EConsole.WriteLine(%22List%20items%20added%20successfully%22)%3B%3CBR%20%2F%3E%7D%3C%2FPASSWORD%3E%3C%2FUSERNAME%3E%3C%2FSITE-COLLECTION%3E%3C%2FTENANT%3E%3C%2FP%3E%3CP%3Eprivate%20static%20void%20AddListFields(ClientContext%20context)%3CBR%20%2F%3E%7B%3CBR%20%2F%3EList%20oList%20%3D%20context.Web.Lists.GetByTitle(%22SitesGeoLocationList%22)%3B%3CBR%20%2F%3E%2F%2F%20oList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Name'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%20%26gt%3B%20covered%20by%20Title%20field%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Status'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Type'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Mineral'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Technique'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'LegalEntity'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Country'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Region'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Latitude'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Text'%22%20displayname%3D%22'Longitude'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Fields.AddFieldAsXml(%22%3CFIELD%20type%3D%22'Geolocation'%22%20displayname%3D%22'Location'%2F%22%3E%22%2C%20true%2C%20AddFieldOptions.AddToAllContentTypes)%3B%3CBR%20%2F%3EoList.Update()%3B%3CBR%20%2F%3Econtext.ExecuteQuery()%3B%3CBR%20%2F%3E%7D%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FFIELD%3E%3C%2FP%3E%3CP%3Epublic%20static%20void%20setOnlineCredential(string%20userName%2C%20string%20password%2C%20ClientContext%20context)%3CBR%20%2F%3E%7B%3CBR%20%2F%3ESecureString%20secureString%20%3D%20new%20SecureString()%3B%3CBR%20%2F%3Eforeach%20(char%20c%20in%20password.ToCharArray())%3CBR%20%2F%3E%7BsecureString.AppendChar(c)%3B%7D%3CBR%20%2F%3Econtext.Credentials%20%3D%20new%20SharePointOnlineCredentials(userName%2C%20secureString)%3B%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3Eprivate%20static%20void%20AddListItem(ClientContext%20context%2C%20int%20rowCount%2C%20int%20colCount%2C%20Excel.Range%20xlRange)%3CBR%20%2F%3E%7B%20%2F%2F%20Replace%20site%20URL%20and%20List%20Title%20with%20Valid%20values.%3CBR%20%2F%3EList%20oList%20%3D%20context.Web.Lists.GetByTitle(%22SitesGeoLocationList%22)%3B%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20(int%20i%20%3D%202%3B%20i%20%26lt%3B%3D%20rowCount%3B%20i%2B%2B)%20%2F%2Frow%201%20contains%20column%20headers%3CBR%20%2F%3E%7B%3CBR%20%2F%3EConsole.Write(%22%5Cr%5Cn%22)%3B%3CBR%20%2F%3EConsole.Write(i%20%2B%20%22%20-%20%22%20%2B%20xlRange.Cells%5Bi%2C%201%5D.Value2%20%2B%20%22%5Ct%22)%3B%3CBR%20%2F%3EListItemCreationInformation%20itemCreationInfo%20%3D%20new%20ListItemCreationInformation()%3B%3CBR%20%2F%3EListItem%20oListItem%20%3D%20oList.AddItem(itemCreationInfo)%3B%3CBR%20%2F%3EoListItem%5B%22Title%22%5D%20%3D%20xlRange.Cells%5Bi%2C1%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Status%22%5D%20%3D%20xlRange.Cells%5Bi%2C2%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Type%22%5D%20%3D%20xlRange.Cells%5Bi%2C3%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Mineral%22%5D%20%3D%20xlRange.Cells%5Bi%2C4%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Technique%22%5D%20%3D%20xlRange.Cells%5Bi%2C5%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22LegalEntity%22%5D%20%3D%20xlRange.Cells%5Bi%2C6%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Country%22%5D%20%3D%20xlRange.Cells%5Bi%2C7%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Region%22%5D%20%3D%20xlRange.Cells%5Bi%2C8%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Latitude%22%5D%20%3D%20xlRange.Cells%5Bi%2C9%5D.Value2%3B%3CBR%20%2F%3EoListItem%5B%22Longitude%22%5D%20%3D%20xlRange.Cells%5Bi%2C10%5D.Value2%3B%3CBR%20%2F%3EFieldGeolocationValue%20oListItemGeoValue%20%3D%20new%20FieldGeolocationValue()%3B%3CBR%20%2F%3Eif%20(xlRange.Cells%5Bi%2C%2010%5D.Value2%20!%3D%20null)%3CBR%20%2F%3E%7B%20oListItem%5B%22Location%22%5D%20%3D%20%22POINT%20(%22%20%2B%20xlRange.Cells%5Bi%2C%2010%5D.Value2.Replace('%2C'%2C%20'.')%20%2B%20%22%20%22%20%2B%20xlRange.Cells%5Bi%2C%209%5D.Value2.Replace('%2C'%2C%20'.')%20%2B%20%22)%22%3B%20%7D%3CBR%20%2F%3EoListItem.Update()%3B%3CBR%20%2F%3Econtext.ExecuteQuery()%3B%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355974%22%20slang%3D%22en-US%22%3ERe%3A%20GeoLocations%20-%20Upload%20values%20to%20existing%2Fnew%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355974%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142%22%20target%3D%22_blank%22%3E%40Eric%20Deferm%3C%2FA%3E%3C%2FP%3E%3CP%3Eunfortunately%20Geolocation%20fields%20are%20unsupported%20for%20user%20in%20MS%20Flow%2C%20you%20could%20use%20this%20template%2C%20but%20it's%20not%20supported%20%3A(%3C%2Fimg%3E%20I%20tried.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fus.flow.microsoft.com%2Fen-us%2Fgalleries%2Fpublic%2Ftemplates%2F5b923a189f4448bd95d05def287f0d9d%2Fcreate-an-item-in-sharepoint-for-a-selected-row%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fus.flow.microsoft.com%2Fen-us%2Fgalleries%2Fpublic%2Ftemplates%2F5b923a189f4448bd95d05def287f0d9d%2Fcreate-an-item-in-sharepoint-for-a-selected-row%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84206iDF35C8C4FFE78D09%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Immagine.png%22%20title%3D%22Immagine.png%22%20%2F%3E%3C%2FSPAN%3EMS%20PowerApps%20too%20doesn't%20support%20GeoLocation%20fields..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20create%20text%20column%20and%20populate%20your%20geolocation%20field%20with%20a%20Remote%20Event%20receiver%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fsp-add-ins%2Fcreate-a-remote-event-receiver-in-sharepoint-add-ins%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fsp-add-ins%2Fcreate-a-remote-event-receiver-in-sharepoint-add-ins%3C%2FA%3Eor%20an%20Azure%20Function%20using%20webhooks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fapis%2Fwebhooks%2Fsharepoint-webhooks-using-azure-functions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fapis%2Fwebhooks%2Fsharepoint-webhooks-using-azure-functions%3C%2FA%3E%2C%20in%20you%20want%20to%20use%20Web%20UI%20Experience%20(your%20text%20field).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20simply%20way%20could%20be%20powershell%20import%20from%20csv%20%2F%20Excel%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgallery.technet.microsoft.com%2Foffice%2FAdd-SPOListItemsFromCSV-76077b14%26nbsp%3B%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgallery.technet.microsoft.com%2Foffice%2FAdd-SPOListItemsFromCSV-76077b14%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EFederico%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eps%3A%20I%20just%20add%20a%20uservoice%2C%20you%20can%20vote%20for%20it%20if%20you%20want%20%3A)%3C%2Fimg%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsharepoint.uservoice.com%2Fforums%2F329214-sites-and-collaboration%2Fsuggestions%2F36941659-geolocation-field-compatibility-with-excel-flo%26nbsp%3B%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsharepoint.uservoice.com%2Fforums%2F329214-sites-and-collaboration%2Fsuggestions%2F36941659-geolocation-field-compatibility-with-excel-flo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

What are the possible options to upload geo coordinates into a GeoLocation field of a list.

Can the values be uploaded from Excel?

Can we convert & assign single text values to a GeoLocation column?

Powershell?

There are 2 options to code it, as documented in the link below, but are there other options?

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/how-to-add-a-geolocation-column-...

Tx & brgds.

8 Replies
Solution

Hi @Eric Deferm

unfortunately Geolocation fields are unsupported for user in MS Flow, you could use this template, but it's not supported :( I tried.

https://us.flow.microsoft.com/en-us/galleries/public/templates/5b923a189f4448bd95d05def287f0d9d/crea...

Immagine.pngMS PowerApps too doesn't support GeoLocation fields..

 

You can create text column and populate your geolocation field with a Remote Event receiver https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/create-a-remote-event-receiver-in-sharepo... or an Azure Function using webhooks https://docs.microsoft.com/en-us/sharepoint/dev/apis/webhooks/sharepoint-webhooks-using-azure-functi... , in you want to use Web UI Experience (your text field).

 

A simply way could be powershell import from csv / Excel https://gallery.technet.microsoft.com/office/Add-SPOListItemsFromCSV-76077b14 

 

Cheers,

Federico

 

ps: I just add a uservoice, you can vote for it if you want :) https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/36941659-geolocat...

Tx @Federico Porceddu  for your extended reply, elaborating on several potential options/alternatives.

Apologies for my delayed reply, finally I decided to go for some C# coding & Visual Studio. As a newby to VS, it took me a while to get everything right. As there's no attach option :( , I'm sharing code below (probably not fully according to best practices on C# programing, but it worked for me).  I'll sure will also check the PS solution and let you know.

 

// https://coderwall.com/p/app3ya/read-excel-file-in-c
// https://docs.microsoft.com/en-us/sharepoint/dev/general-development/integrating-location-and-map-fun...
// https://docs.microsoft.com/en-us/sharepoint/dev/general-development/how-to-add-a-geolocation-column-...
// upload the necessary assmeblies & references in your solution

using Microsoft.SharePoint.Client;
using System;
using System.Security;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("c:\\temp\\Sites2.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

ClientContext context = new ClientContext("https://<tenant>.sharepoint.com/<site-collection>");
setOnlineCredential("<username>", "<password>", context); //required in our case due to SSO
AddListFields(context);
Console.WriteLine("Fields added successfully");
AddListItem(context, rowCount, colCount, xlRange);
Console.WriteLine("List items added successfully");
}

private static void AddListFields(ClientContext context)
{
List oList = context.Web.Lists.GetByTitle("SitesGeoLocationList");
// oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Name'/>", true, AddFieldOptions.AddToAllContentTypes); > covered by Title field
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Status'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Type'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Mineral'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Technique'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='LegalEntity'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Country'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Region'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Latitude'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='Longitude'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Fields.AddFieldAsXml("<Field Type='Geolocation' DisplayName='Location'/>", true, AddFieldOptions.AddToAllContentTypes);
oList.Update();
context.ExecuteQuery();
}

public static void setOnlineCredential(string userName, string password, ClientContext context)
{
SecureString secureString = new SecureString();
foreach (char c in password.ToCharArray())
{secureString.AppendChar(c);}
context.Credentials = new SharePointOnlineCredentials(userName, secureString);
}

private static void AddListItem(ClientContext context, int rowCount, int colCount, Excel.Range xlRange)
{ // Replace site URL and List Title with Valid values.
List oList = context.Web.Lists.GetByTitle("SitesGeoLocationList");

for (int i = 2; i <= rowCount; i++) //row 1 contains column headers
{
Console.Write("\r\n");
Console.Write(i + " - " + xlRange.Cells[i, 1].Value2 + "\t");
ListItemCreationInformation itemCreationInfo = new ListItemCreationInformation();
ListItem oListItem = oList.AddItem(itemCreationInfo);
oListItem["Title"] = xlRange.Cells[i,1].Value2;
oListItem["Status"] = xlRange.Cells[i,2].Value2;
oListItem["Type"] = xlRange.Cells[i,3].Value2;
oListItem["Mineral"] = xlRange.Cells[i,4].Value2;
oListItem["Technique"] = xlRange.Cells[i,5].Value2;
oListItem["LegalEntity"] = xlRange.Cells[i,6].Value2;
oListItem["Country"] = xlRange.Cells[i,7].Value2;
oListItem["Region"] = xlRange.Cells[i,8].Value2;
oListItem["Latitude"] = xlRange.Cells[i,9].Value2;
oListItem["Longitude"] = xlRange.Cells[i,10].Value2;
FieldGeolocationValue oListItemGeoValue = new FieldGeolocationValue();
if (xlRange.Cells[i, 10].Value2 != null)
{ oListItem["Location"] = "POINT (" + xlRange.Cells[i, 10].Value2.Replace(',', '.') + " " + xlRange.Cells[i, 9].Value2.Replace(',', '.') + ")"; }
oListItem.Update();
context.ExecuteQuery();
}
}

Hi @Eric Deferm , nice :)

 

instead use

oListItem["Location"] = "POINT (" + xlRange.Cells[i, 10].Value2.Replace(',', '.') +

you can use (numbers 123 are i.e.)

  
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse("123"),
Latitude = Double.Parse("123"), 
Longitude = Double.Parse("123"),
Measure = Double.Parse("123"),
};
oListItem["Location"] = geolocationValue;

Cheers, Federico

Hi @Federico Porceddu , didn't know one could also specify the Altitude & Measure. The programming interface supports 4 values, whereas the user interface only allows you to enter 2 fields (Long. & Lat.) or does your interface look different from mines? Tx.

GeoLocationField-EditDialogBox.png

Hi @Eric Deferm , try classic experience :)

Hi Federico, that's strange. The list from which I start is configured for Classic experience. Any idea what other configuration settings could have an impact?

Hi,

there is no difference from classic or modern experience in creation / configuration.

I see difference only in editing.

What about your screenshot? Is it modern experience?

Hi, tx for your reply. The screenshot is classic experience.