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,

[NicholasLaMar]

Hi! Thanks for sharing. I've been able to successfully connect my hosted feature service to an Excel sheet. I can see the data, but it appears that the ability to edit the attribute information is only one way (i.e. hosted feature service to Excel). Is it possible to make edits in the Excel sheet and push those edits back to the hosted feature service?

[Jean-YvesLandry2]

It's possible since you have the ability to update your data from ArcRest, but I have not tested this myself with excel so I'm not sure what the best way would be. 

[DavidCook2]

I am almost certain this is not inherently possible with the Power Query tool. In my experience even when connecting to an access database, even locally, edits are always only one way. 

[ToryChristensen]

Hello all,

I'm struggling to figure out which values i need to input for the Advanced Editor Script. so far i have entered the folowing Variables in red(see below):

let
//Custom Variables
Hosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content],
fsURL = Hosted{0}[https://services1.arcgis.com/E5n4f1VY84i0xSjy/arcgis/rest/services/service_6c410d1aeeec49f4be7a0b951... ]
Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],],
whereClause = Hosted{1}[objectid>0],
fieldsOut = Hosted{2}[*],

token = Token_auto{0}[868JC4M971rlJUGHDKjd...],

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

Am i missing anything here or have i entered the correct inputs for this script? Thanks

[MartinPerez]

Hello Tory!

What I see for this code is that there is a missing comma (,) after the fsURL statement; and an extra "]," after the Token_auto = statement.

Please take a look and let us know.  We are very tankful with this solution, as we use it quite commonly, and works like a charm.  Hope this helps.

Best regard,

[ToryChristensen]

Hi Martin, thanks so much for your response!

I have made a few changes based on your comment, but what i'm getting now is the error message 'Invalid identifier' for the fsURL. I double checked my format for the URL and it seems to be the same as the example listed in this forum.

Any advice you could provide would be greatly appreicated!

Cheers

[MartinPerez]

Hey Tory!

I hope we get this working, as it worked for me, and I would like to give back a little.

May I ask if your are showing the values of the table into the statements for easy understanding of your post, or are they in the table and the statements really have [Value] in the code as in:

whereClause = controltable{1}[Value],?

I ask because, if they are in the table and the URL has quotes in there, take them out (just as it is in your previous post code, where the URL has no quotes).  Otherwise, as I changed the code for myself, I really have no entries in any table for the URL, so my statement is:

fsURL="https://arcgis.p.../FeatureServer/0/",

To test it, you can get rid of the tables temporarily and write the code as:

let
//Custom Variables
//Hosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content],
fsURL = "https://services1.arcgis.com/E5n4f1VY84i0xSjy/arcgis/rest/services/service_6c410d1aeeec49f4be7a0b951...
//Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],],
whereClause = "objectid>0",
fieldsOut = "*",

token = "868JC4M971rlJUGHDKjd...",

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

as these variables are really all strings being concatenated in the Source statement.

Take a look and let us know :O)

Best regards,

Martín 

[DavidCook2]

Fantastic! Thank you

[MartinPerez]

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

[MartinPerez]

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)

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

2. 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),

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

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

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

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

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

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