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.