Wondering how others have used Excel with Arcgis Online, since this is a common query from clients. I've been using Power Query ( see this excellent walk through) wth Arcgis and it's very easy to setup.
Thanks,
Solved! Go to Solution.
PART 2 Create your temporary token
1) To receive your token for your application replace CLIENTID and CLIENTSECRET with your own.
https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=CLIENTID&client_secret=CLIENTSECRET&grant_type=client_credentials
2) New query, from web and enter the url with your IDs.
3)choose advanced > ok
4) Choose EDIT
5) Select Data Source Settings
6) Change Source and select open file as JSON.
7) press advanced editor, we'll now edit the code so that it points to our tables. modify to code to
let
//Custom Variables
access = Excel.CurrentWorkbook(){[Name="AGOL_ACCESS"]}[Content],
client_id = access{0}[Value],
client_secret = access{1}[Value],
Source = Json.Document(Web.Contents("https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=" & client_id & " &client_secret="& client_secret & "&grant_type=client_credentials"), 65001),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"
By having variables you'll now be able to reuse this excel table for other application tokens, as it points to the table that you previously created.
Next you'll need to get the service URL for your feature layer...
PART 3 coming...
Hello Jean-Yves!
Most of the customers I speak with use ArcGIS Maps for Office. This may not do everything you need, but I'd definitely consider taking a look, as it gives pretty seamless flow between ArcGIS Online and Excel.
Check out: ArcGIS Maps for Office
You can also check in with your Account Manager for more details on this product in relation to purchasing it for your account if you find that it's what you need.
Enjoy!
Rachel G
Esri Support Services
Thanks Rachel,
Yes agreed Arcgis Office is quite useful indeed. However in our case we are having to update data tables from multiple surveys and image attachments built with Collector and survey123. By using PowerQuery you can access the arc Rest to access tables and attachments without having to do much coding at all, pretty nifty.
Wanted to add that with Excel powerquery you may also show the URL to your hosted feature attachments.
1) Create the Query to your attachment table
2) Customize a column;
= SERVICEURL & parentObjectId & "/attachments/" & attachmentID & Token
You can then open all your attachments using the hyperlink from your custom column (note that your token will need to be constantly refreshed).
BONUS: With a little VBA you can then download all your photos to a local folder using the attachment URL. (modified from excel - Convert code for 32 bit to 64 bit - Stack Overflow )
*update: Added a line to add images that are not already present in the folder, reducing downloading time.
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Dim Ret As Long
Sub Download_image_structure()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim countupdate As Long
Dim strPath As String
Dim FolderName As String
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
FolderName = "W:\IMAGES\STRUCTURE\" '<-- folder where images will be saved
'~~> create folder if it does not exist
If Len(Dir(FolderName, vbDirectory)) = 0 Then
MkDir FolderName
End If
'~~> name of sheets with image to download
Set ws = Sheets("STRUCTURES_PHOTO")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow '<~~ 2 because row 1 has headers
strPath = FolderName & ws.Range("F" & i).Value
If Not fs.FileExists(strPath) Then '<-- if image does not exist in folder then skip to next
Ret = URLDownloadToFile(0, ws.Range("I" & i).Value, strPath, 0, 0)
countupdate = countupdate + 1
End If
Next i
MsgBox countupdate & " added images" '<-- how many image added to folder
End Sub
Hi, we're super interested in this also - ArcGIS Maps for office doesn't deliver exactly what we're after. unfortunately the linked post (to rwalker.info) is no longer available - any chance you could share that content? Rashan Walker, is that your blog?
Just saw this, I'll put together the how-to to help you out.
Brilliant! thanks heaps! I've managed to map out the JSON endpoint to the attribute data with a bit of trial and error, but it'd be great to validate if i'm doing it effectively/correctly. The main challenge I'm having is for authenticated services; I haven't worked out how to connect to anything but a publicly shared endpoint
Hello, I am also very interested in the description how to connect from excel to ArcGIS. Will you publish it here?
Best regards!
Still travelling but I'll try to summarize best I can here:
PART 1: CREATE TOKEN
d.Give your application a name, then click "Register"
e. Note the Client ID and Client Secret
IN EXCEL: Insert three Tables
Enter the Client ID and Client Secret you just copied from the application you just created.
I like to name my tables;
From Top to bottom: AGOL_ACCESS, Token_auto, Hosted_FS0
PART 2 Create your temporary token
1) To receive your token for your application replace CLIENTID and CLIENTSECRET with your own.
https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=CLIENTID&client_secret=CLIENTSECRET&grant_type=client_credentials
2) New query, from web and enter the url with your IDs.
3)choose advanced > ok
4) Choose EDIT
5) Select Data Source Settings
6) Change Source and select open file as JSON.
7) press advanced editor, we'll now edit the code so that it points to our tables. modify to code to
let
//Custom Variables
access = Excel.CurrentWorkbook(){[Name="AGOL_ACCESS"]}[Content],
client_id = access{0}[Value],
client_secret = access{1}[Value],
Source = Json.Document(Web.Contents("https://www.arcgis.com/sharing/rest/oauth2/token/?client_id=" & client_id & " &client_secret="& client_secret & "&grant_type=client_credentials"), 65001),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"
By having variables you'll now be able to reuse this excel table for other application tokens, as it points to the table that you previously created.
Next you'll need to get the service URL for your feature layer...
PART 3 coming...