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. 

[KevinOKeeffe]

Hi Nicholas, just wondering were you able to find out if edits made in excel were able to be pushed back into the hosted features service?

[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