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