3

I'm following this tutorial on using Excel Services REST api and trying to read a range.

If I paste the url into my browser it works and I see the range in my browser.

http:<site>/_vti_bin/ExcelRest.aspx/<library>/<file>/Model/Ranges('<range>')

But if I use jquery ajax in a content or script editor webpart, it doesn't work. First I get an authorization challenge which doesn't accept my credentials and then if I cancel that, I get a 401 unauthorized error.

$.ajax({
    url:exact_same_url_as_above,
    type:"GET",
    headers:{
        "accept": "application/json;odata=verbose",
        "content-type": "application/json;odata=verbose",
        "X-RequestDigest":$("#__REQUESTDIGEST").val()
    }
})
.done(function(data,status,xhr){
    console.log(data);  
})

I am the site collection owner and have full control over everything. I have lots of REST api code running in other locations in the site, but this is the first that uses the 2010 _vti_bin access point.

Any idea what is going on?

8
  • try changing the get to a post? Oct 6, 2016 at 21:54
  • Thanks for the idea. Tried it and got the same result. Usually if it works in the browser bar and/or is a read it is just a GET.
    – Rothrock
    Oct 6, 2016 at 23:00
  • Are you running the script in a page within http:<site> ?
    – Christophe
    Oct 6, 2016 at 23:51
  • Yes. On the same page I used a 2013 REST call (/_api/web/lists('guid')/items(#) to retrieve the title and other information about the document and those worked just fine.
    – Rothrock
    Oct 7, 2016 at 14:37
  • Have you tried it in fiddler?
    – MickB
    Oct 10, 2016 at 17:10

1 Answer 1

1
+50

Using postman to run a few tests.

When I include the headers:

    "accept": "application/json;odata=verbose",
    "content-type": "application/json;odata=verbose",

I get 401 Unauthorized like you are experiencing. Not even admins can draw blood from a turnip or json from ExcelRest.aspx

Solution:

If I remove the Content-Type entry I get a response back with the data I am looking for.

Getting the data you are looking for:

Use either the $format=ATOM or $format=HTML or $format=image URL parameters to drive the response content type.

2
  • That is so crazy. Seems like that would be a reason to throw an error or something not require an impossible authentication! In any event it works. I removed content-type from the header, added datType:"text" to the props, used $format=json in the url and added JSON.parse(data) in the done handler.
    – Rothrock
    Oct 12, 2016 at 14:55
  • @Rothrock That was a cryptic comment about added datType:"text" to the props. I strongly recommend a solution found there: sharepoint.stackexchange.com/a/117469/96802. It explains and fixes the 401 error and returns json all right May 4, 2021 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.