topic Re: Using Excel to access Arcgis online (automated) in ArcGIS Online Questions

Using Excel to access Arcgis online (automated)

Jean-YvesLandry1 — Sun, 25 Mar 2018 10:33:38 GMT

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,

Re: Using Excel to access Arcgis online (automated)

rachelg_esri — Wed, 28 Mar 2018 21:23:35 GMT

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

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry1 — Fri, 30 Mar 2018 14:45:25 GMT

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.

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry1 — Sat, 11 Dec 2021 21:26:32 GMT

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

Re: Using Excel to access Arcgis online (automated)

ZoltanKelly1 — Sat, 20 Jul 2019 02:58:46 GMT

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?

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry1 — Thu, 08 Aug 2019 21:56:50 GMT

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

Re: Using Excel to access Arcgis online (automated)

ZoltanKelly1 — Thu, 08 Aug 2019 22:38:32 GMT

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

Re: Using Excel to access Arcgis online (automated)

JulianeBorge — Mon, 12 Aug 2019 06:59:13 GMT

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

Best regards!

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry1 — Fri, 23 Aug 2019 11:37:09 GMT

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

PART 1: CREATE TOKEN

ArcGIS for Developers
Sign into the same AGOL account that holds your survey feature layer
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

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry1 — Fri, 23 Aug 2019 12:14:50 GMT

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

Re: Using Excel to access Arcgis online (automated)

ZoltanKelly1 — Mon, 09 Sep 2019 07:55:40 GMT

Hi, thanks for this, it’s fantastic! Really appreciate the detail. I’ve managed to follow to this point, if you can post the third part you will make my (and my teams’) day/week/year!

Re: Using Excel to access Arcgis online (automated)

Anonymous User — Fri, 13 Sep 2019 18:23:33 GMT

I would also like to see the final steps, this would prove very useful for me on many projects.

Re: Using Excel to access Arcgis online (automated)

MartinPerez — Sun, 20 Oct 2019 12:31:34 GMT

Thank you for sharing all of this information. There should be a lot of people waiting for Part 3; please publish it. Best regards

Re: Using Excel to access Arcgis online (automated)

DavidCook2 — Fri, 25 Oct 2019 17:17:30 GMT

Jean-Yves Landry‌ any tips on step 3? with limited functionality with dashboard attribute tables, having a direct connection to excel where people still perform lots of tasks would be fantastic!

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry2 — Fri, 25 Oct 2019 22:07:32 GMT

PART 3 Fetching data from the service url and loading it into Excel

Since your survey could be setup in multiple different ways, I'm going to be using the "Nested Repeats" sample from Survey123 connect. The "Nested Repeats" survey is setup where one campground may have many campsites, where campsites may have many equipment, where equipment may have a picture attached.

Layer(0) -Campground

Layer(1) -campSite

Table(2) - campsite_equipement

Table(2) attachments (images)

1. Create sample survey from survey123 connect

2.Once you've published your survey to arcgis online, you should now see your survey under content in a folder named "Survey-Nested Repeats". Press on the feature layer.

3.For this feature layer we can observe that there are 2 layers, and 1 table. You'll notice that each layer and table have a service url. Press on the NestedRepeats Service URL.

4. You'll now find yourself in the ArcGIS REST Services Directory. If you scroll down the page you'll note that under relationships we can see that there is a relationship to the layer campSite (1), as you would expect...

5. At this point you can copy the service URL for the Campground layer. which should look something like this:

Copy up to "FeatureServer/0/" and Save it for later.

6. Scroll to the bottom and click on query. From this table you're able to query your layer. To query all data enter "objectid>0" for Where and wildcard "*" for Out Fields, and "JSON" for Format.

Press Query (Get) ----You should then see all your collected information for that layer in the form of JSON. This is what we want in excel!

7. Now we'll need to recreate this query in Excel. paste the URL you copied at #5 into the table Hosted_FS0. and just like the Query we just did enter objectid>0 for Where and * for Field.

8. Add a new Query (Refer to part 2) and modify code in advanced editor. Once modified press done.

let
//Custom Variables
Hosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content],
fsURL = Hosted{0}[Value],
whereClause = Hosted{1}[Value],
fieldsOut = Hosted{2}[Value],

Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],
token = Token_auto{0}[Value],

//Get Data
Source = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=false&f=pjson&token=" & token), 65001)
in
Source

*The variables in your Hosted_FS0 tables and your token will be merged and the resulting URL will be used to query your survey data.

9.We'll need to "drill down" the Json in order to properly present the data. Right Click on list then click "into Table"

Right click on "List" for features then click "drill down"

Right click on List (column header) and click "To Table" - press ok

click on arrow button, unclick "Use original column name as prefix", click ok

click on arrow button, unclick "Use original column name as prefix", You'll note that these are the columns from your survey. Press ok.

10. If you added campgrounds then you should see your campground data. Exit of the power Query Editor and Keep your changes

*(You'll note that the time is in unix so you'll need to convert it see my other post here How do you convert epoch dates in Excel, Power Bi (query), Access from geodatabase.

11.Your new sheet will now show a new table with all your survey data. You can do this for all your feature layers and tables.

Re: Using Excel to access Arcgis online (automated)

DavidCook2 — Fri, 25 Oct 2019 22:36:29 GMT

Fantastic! Thank you

Re: Using Excel to access Arcgis online (automated)

Jean-YvesLandry2 — Fri, 25 Oct 2019 23:01:54 GMT

Part 4: attachments and downloading images

1. Find the service url for your attachments, in the case of "Nested Repeats" the attachments (photos) are related to campsite_equipment.

You'll note that the URL for campsite_equipment is something like:

https://services1.arcgis.com/Sh1QwLSVKYk2AYjx/ArcGIS/rest/services/service_df0b96fd900d4b23b5af55964ff4ed08/FeatureServer/2?token= wdskajhaods3kvmcasdlfjmakj324hk...

If you scroll to the bottom you'll also find that you have an option called "Query Attachments"

2.Click Query Attachments.

You'll note that the url is similar but instead of ...FeatureServer/2/query... it is ...FeatureServer/2/queryAttachments...

Enter "objectid>0" for Definition Expression, select format JSON, and click Query Attachments (GET). You can then see all your image attachments (this is what we'll fetch from Excel).

3. Back in Excel create a table named Hosted_FS2 with the url of the feature with attachment (you could use the same one as campsite_equipment, since they use the same URL, the only difference is the query.)

4. Create a new query and use the variables from your newly created table. Note that i am only using the service URL variable and my query is in Source;

5. From there you can "drill down"

-right click on list for attachmentGroups - into table

-click on list for attachmentGroups

-right click column header- To table - Ok

-click arrow, then ok

-click arrows on attachmentinfos - Expand to new Rows

-click same arrows on attachmentinfos - select all, click ok

6. For a little extra, you can add a new column with the image URL (giving the ability to access your survey picture through a link from excel)

-Add Column, custom column

EXTRA: since you might want to rename your pictures based on your campground or campsite, you can merge the equipment table with the attachment table.

Hope this helps...

Re: Using Excel to access Arcgis online (automated)

MartinPerez — Fri, 25 Oct 2019 23:04:49 GMT

Hi!

Can't wait to try it. Sorry for being so pushy; we do really appreciate your support :O)

Best regards

Re: Using Excel to access Arcgis online (automated)

MartinPerez — Sat, 26 Oct 2019 00:49:37 GMT

It worked like a charm! I skipped some steps, like creating the tables, and used manual entries for the variables; but worked perfectly.

But ... I hit the feature query limit. I know that there is no limit when querying the objectids, so you can query them first, and then you can query progressively in blocks until getting all the data. mmmmm... it will take me a while to figure out that. Any suggestions?

Thank you again for sharing all this information.

Best regards

Re: Using Excel to access Arcgis online (automated)

MartinPerez — Sat, 26 Oct 2019 11:30:05 GMT

Hi!

There are some forums that request you to show some sort of research and effort to solve your issue before asking for help. So, this is what I tried; that worked, but I understand it is not an efficient way to do it. Please bear in mind that this is my first experience with Excel Power Query :O)

I modified the first data acquisition query to include my intended Where Clause as:

//Custom Variables

whereClause = "some_field is not null",

//(remember that I skipped the table creations, so I needed to include here the actual values)

I added &orderByFields=objectid before &returnGeometry=false in the Source clause:

Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&"&outFields="&fieldsOut&"&orderByFields=objectid&returnGeometry=false&f=pjson&token="&token),65001),

I created a new blank query and copied the first one into it, and made the following modification:

whereClause = “some_field is not null and objectid > 55000“,

This is because I looked for the last objectid from the first query, remember that it is ordered by objectid. This created another worksheet with the next 10,000 block of data.

Repeated the stetp 3. six more times to get the over 60,000 points I were interested in. So I ended with seven worksheets with all the data scattered.

Went to Query tab of the Ribbon (shown when selecting any cell of a queried table) and selected Append, Three or more tables, and selected all these queries:

This created the Append1 query you can see in the list; and it worked quite well for my experience with Power Query.

I deleted the worksheets with the fragmented data, and left only the new one with all the data collected together. This changed all these queries to Connection only:

Improved this process a little, by querying the objectids into a table, and using these values for the where clause of all other queries, to “automate” it a little more.

I ran your original process changing the where clause to:

whereClause = "worked_by is not null",

and adding &orderByFields=objectid&returnIdsOnly=true to the source line:

Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&"&outFields="&fieldsOut&"&orderByFields=objectid&returnIdsOnly=true&returnGeometry=false&f=pjson&token="&token),65001),

then converted into table and drilled down etc. etc. etc.

I then used this new table to address the query limit thresholds as:

let

//Custom Variables

Query7=Excel.CurrentWorkbook()

{[Name="Query7"]}[Content],

objectid = Number.ToText(Query7{9999}[Objectid]),

whereClause = "worked_by is not null and objectid > ",

Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&objectid&"&outFields="&fieldsOut&"&orderByFields=objectid&returnGeometry=false&f=pjson&token="&token),65001),

for the second query.

Then:

objectid = Number.ToText(Query7{19999}[Objectid]),

objectid = Number.ToText(Query7{29999}[Objectid]),

objectid = Number.ToText(Query7{39999}[Objectid]),

objectid = Number.ToText(Query7{49999}[Objectid]),

objectid = Number.ToText(Query7{59999}[Objectid]),

for the remaining queries (two to six, respectively, as the first and second ones where already correctly configured). I have a Query0 because I implemented the whereClause = “some_field is not null and objectid > 55000“, as my first query, and missed the first 10,000 points where objectid > was not needed.

Now, I only need to refresh the objecids query, and then the Append1 query to gather all the data at once. Again, not efficient, but working :O)

So, what when my data layer increases over 100,000 features, or over 200,000? Do I create another four, five, ten more queries and append them all? I don’t think so. But looping around in Power Query seems a scary thing, as Power Query is not a conventional programming language, and remember that this is my first experience with it. Any help will be greatly appreciated.

Best regards