Using Excel to access Arcgis online (automated)

[Jean-YvesLandry1]

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,

[rachelg_esri]

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

[Jean-YvesLandry1]

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.

[Jean-YvesLandry1]

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[ZoltanKelly1]

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?

[Jean-YvesLandry1]

Just saw this, I'll put together the how-to to help you out.

[ZoltanKelly1]

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

[JulianeBorge]

Hello, I am also very interested in the description how to connect from excel to ArcGIS. Will you publish it here?

Best regards!

[Jean-YvesLandry1]

Still travelling but I'll try to summarize best I can here:

PART 1: CREATE TOKEN

1.    ArcGIS for Developers 
2. Sign into the same AGOL account that holds your survey feature layer
3. Select New Application

           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

[Jean-YvesLandry1 · Accepted 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...