Javascript ajax request with Excel 2016 add in

Question

Hi.

Is it possible to fetch data from an external URL using excel 2016 with Javascript ajax call?

I'm trying to set up an add in, that fetches data from an URL, and updates the spreadsheet with the data.

So far, i haven't managed to get a succesful call through.

Answer 1

Hi Morten,

This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to Developing Apps for Office:

https://social.msdn.microsoft.com/Forums/en-US/home?forum=appsforoffice

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi Zhang
TechNet Community Support

---

Please mark the reply as an answer if you find it is helpful.

If you have feedback for TechNet Support, contact [e-mail].

Answer 2

Hi Morten,

>> Is it possible to fetch data from an external URL using excel 2016 with Javascript ajax call?

I am not sure what do you mean by “fetch data from an external URL”? Do you mean you want to get data from url string, from the page of url or this url is a service url which return data?
Anyway, there is no Office add in Object model to achieve your requirement. Office add in operates with data in excel instead of external data which is out of Excel.

For your requirement, I think it is more related with JavaScript. If you want to get data from URL string, you could try the code below:

function parseURLParams(url) {
    var queryStart = url.indexOf("?") + 1,
        queryEnd   = url.indexOf("#") + 1 || url.length + 1,
        query = url.slice(queryStart, queryEnd - 1),
        pairs = query.replace(/\+/g, " ").split("&"),
        parms = {}, i, n, v, nv;

    if (query === url || query === "") {
        return;
    }

    for (i = 0; i < pairs.length; i++) {
        nv = pairs[i].split("=");
        n = decodeURIComponent(nv[0]);
        v = decodeURIComponent(nv[1]);

        if (!parms.hasOwnProperty(n)) {
            parms[n] = [];
        }

        parms[n].push(nv.length === 2 ? v : null);
    }
    return parms;
}

>> updates the spreadsheet with the data
If you have got data, and you have any issues about writing data to spreadsheet, please feel free to let us know.

Best Regards,

Edward

---

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.

Answer 3

Hi Edward

What we need is the following. 

From the add in we want to call a url (using get or post) and then parse the data returned from that url and push it into the Excel using the add-in.

To be specific we will call a url, that returns json. Parse this and push it into the Excel file.

In Jquery this would be handled with an ajax(...) call. We know how to setup the Javascript. What we do not know is whether the javascript add-in allows us to push the data in at all.

Answer 4

Hi Morten,

>> What we do not know is whether the javascript add-in allows us to push the data in at all.

Do you mean you want to push Json data into excel file? If so, I think we could not achieve this directly. With Excel Binding.setDataAsync, Range object, Table object, we could set string, array, TableData and so on. But it seems it is not supported for json. I think you could convert json to array and then write data into excel.

Here is a simple code about setting array value for range.

// Create a proxy object for the active worksheet
   var sheet = ctx.workbook.worksheets.getActiveWorksheet();

   //Queue commands to set the report title in the worksheet
   sheet.getRange("A1").values = "Quarterly Sales Report";
   sheet.getRange("A1").format.font.name = "Century";
   sheet.getRange("A1").format.font.size = 26;

   //Create an array containing sample data
   var values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"],
        ["Frames", 5000, 7000, 6544, 4377],
        ["Saddles", 400, 323, 276, 651],
        ["Brake levers", 12000, 8766, 8456, 9812],
        ["Chains", 1550, 1088, 692, 853],
        ["Mirrors", 225, 600, 923, 544],
        ["Spokes", 6005, 7634, 4589, 8765]];
   //Queue a command to write the sample data to the specified range
   //in the worksheet and bold the header row
   var range = sheet.getRange("A2:E8");
   range.values = values;

For more information about this, you could refer the link below:
# Range object (JavaScript API for Excel)
https://msdn.microsoft.com/EN-US/library/office/mt598656.aspx

Best Regards,

Edward

---

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.