none
How to pull JSONP to a sheet RRS feed

  • Question

  • I'm working with Apps for Excel and need to populate a sheet with rows being output by an MVC4 web-app. It's cross-domain. I've seen only a single sample app pulling YouTube content via REST and it doesn't translate to my use case.

    How do I hit a web-app that serves JSON content (multiple rows and columns) and insert that to a blank sheet?

    Wednesday, January 29, 2014 3:51 AM

All replies

  • Hi,
    You can refer to the code below to make a cross-domain call:

    function crossDomanAjaxCall() {
            $.support.cors = true;
    
            $.ajax({
                contentType: 'application/json; charset=utf-8',
                url: 'http:/www.crossdomain.com/home/data',
                type: 'Get',
                dataType: 'jsonp',
                data: "data",
                async: false,
                cache: false
            }).success(function (response) {
                app.showNotification("Success", response.statusText);
            }).error(function (response) {
                app.showNotification("Error", response.statusText);
            });
    }
    

    And the thread below may be helpful:
    http://social.msdn.microsoft.com/Forums/en-US/2b431c22-0708-4d2c-b235-6253800246c0/issues-with-performing-an-ajax-call-within-an-office365-app-for-excel?forum=appsforoffice

    >> How do I hit a web-app that serves JSON content (multiple rows and columns) and insert that to a blank sheet?<<
    There is no way we can write the JSON content direct to the worksheet. You need to parse it to the string or array. There is two ways we can write data into the work sheet. The first is using setSelectedDataAsync write data to the select value. And the second is using Binding.setDataAsync write data to the binding object.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 30, 2014 8:44 AM
    Moderator
  • And the thread below may be helpful:

    http://social.msdn.microsoft.com/Forums/en-US/2b431c22-0708-4d2c-b235-6253800246c0/issues-with-performing-an-ajax-call-within-an-office365-app-for-excel?forum=appsforoffice

    Like the poster at that thread, I'd very much prefer taking the non-JSONP route so I'm trying the code block pasted below. I have the same javascript code sitting in a regular webpage which will return my JSON data correctly. Header returned = access-control-allow-orig...* (firebug is inserting that ellipsis).

    any thoughts?

            $(document).ready(function () {
                app.initialize();
                $.support.cors = true;
                $.ajax({
                    crossDomain: true,
                    url: "http://localhost:2524/account/GetIDsAreNull",
                    type: 'get',
                    dataType: 'json',
                    cache: false,
                    async: false,
                }).success(function (response) {
                    app.showNotification("Success", response);
                }).error(function (response) {
                    app.showNotification("Error", response.statusText);                
                });
    
    //Error: Access is denied. 

    This code works if run from a browser - including IE 11 as well as IE11 running in Emulation mode at versions 10 and 9 (i didn't think I'd need to go further back). But IE11 in compatibility mode fails.

    So the question becomes - are special configuration options required to pull Cross Platform JSON results?


    • Edited by am.steve Friday, January 31, 2014 3:28 PM provides result of additional troubleshooting.
    Friday, January 31, 2014 11:02 AM
  • Hi steve,

    The error of "access is denied" is caused by the security of apps for Office. By default, the Apps for Office are using “https” protocol. When you call http Ajax from Apps for Office using https protocol, you will encounter “Access Denied” error. For a workaround, you could change your App’s protocol to Http or call Ajax via Https.
    To disable the Secure Sockets Layer, please refer to figure below:


    Another way is using HTTP access control (CORS); in this way, you need to add “Access-Control-Allow-Origin” header on service side.

      function crossDomanAjaxCall() {
            $.support.cors = true;
    
            $.ajax({
                crossDomain: true,
                url: 'http://website/request/data',
                type: 'POST',
                dataType: 'json',
                data: "data",
                async: false,
                cache: false
            }).success(function (response) {
                app.showNotification("Success", response.statusText);
            }).error(function (response) {
                app.showNotification("Error", response.statusText);
            });
        }
    

    You can get more detail from the relative thread:
    Issues with performing an ajax call within an Office365 app for Excel
    Best regards
    Fei

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by ksimple Wednesday, February 26, 2014 4:41 AM
    Monday, February 10, 2014 9:58 AM
    Moderator