Web query Excel JS api

Question

I am trying to find the Excel Javascript API alternative for the VBA function to get data from an URL to an Excel sheet. In VBA I use Querytable with the add method to get data from an URL using this code snippet:

Dim URLprefix As String
Dim ProjectID As String
Dim SnapshotID As String
Dim ResultType As String
Dim Hash As String
Dim lngNumberOfCols As Long


URLprefix = "https://mywebsite.com"

With Sheets("Definition")
    ProjectID = .Range("B3")
    SnapshotID = .Range("B4")
    Hash = .Range("B5")
End With


Set ws = Worksheets("Import")
ws.Select

ws.Cells.Select

Cells.Delete


    'Get the Produc definitions from the EvalDB query
    ResultType = "product"
    Set QT = ws.QueryTables.Add("URL;" & URLprefix & ProjectID & "&id_snapshot=" & SnapshotID & "&type=" & ResultType & "&hash=" & Hash, Destination:=Range("$A$3"))
    QT.Refresh BackgroundQuery:=False
    'Get the property definitions
    ResultType = "property"
    Set QT = ws.QueryTables.Add("URL;" & URLprefix & ProjectID & "&id_snapshot=" & SnapshotID & "&type=" & ResultType & "&hash=" & Hash, Destination:=Range("$D$1"))
    'get the results
    ResultType = "result"
    QT.Refresh BackgroundQuery:=False
    **Set QT = ws.QueryTables.Add("URL;" & URLprefix & ProjectID & "&id_snapshot=" & SnapshotID & "&type=" & ResultType & "&hash=" & Hash, Destination:=Range("$D$3"))**
    QT.Refresh BackgroundQuery:=False

This is the core of the VBA snippet:

Set QT = ws.QueryTables.Add("URL;" & URLprefix & ProjectID & "&id_snapshot=" & SnapshotID & "&type=" & ResultType & "&hash=" & Hash, Destination:=Range("$A$3"))
    QT.Refresh BackgroundQuery:=False
    'Get the property definitions
    ResultType = "property"
    Set QT = ws.QueryTables.Add("URL;" & URLprefix & ProjectID & "&id_snapshot=" & SnapshotID & "&type=" & ResultType & "&hash=" & Hash, Destination:=Range("$D$1"))

This works fine in VBA

But it does not work with an XMLhttp request in Office JS API and there is no information at all on the microsoft website nor anywhere else. I use the following function found in this post here on stack overflow

var HttpClient = function () {
        this.get = function (aUrl, aCallback) {
            var anHttpRequest = new XMLHttpRequest();
            anHttpRequest.onreadystatechange = function () {
                if (anHttpRequest.readyState === 4 && anHttpRequest.status === 200)
                    aCallback(anHttpRequest.responseText);
        }

            anHttpRequest.open("GET", aUrl, true);
            anHttpRequest.send(null);   
        }
    }

there is never a readystate or xmlhttp status but if I go to Excel->data->from web and use the same URL the data is correctly imported. So clearly I am not doing it right but I have no idea where to search

Update: here the snippet for calling the XmlHTTP request:

 // Run a batch operation against the Excel object model
            Excel.run(function (ImportData) {
                // Create a proxy object for the active sheet
                var sheet = ImportData.workbook.worksheets.getActiveWorksheet();
                // Queue a command to write the sample data to the worksheet
                var client = new HttpClient();

                sheet.getRange("A1").values = client.get(QueryString, function (response) {
              });


                // Run the queued-up commands, and return a promise to indicate task completion
                return ImportData.sync();
            });

As matter in fact, after digging deeper I indeed saw that this is a CORS error:

HTML1300: Navigation occurred. FunctionFile.html SEC7120: Origin https://localhost:44301 not found in Access-Control-Allow-Origin header. FunctionFile.html SCRIPT7002: XMLHttpRequest: Network Error 0x80700013, Could not complete the operation due to error 80700013. FunctionFile.html

But how to solve this without creating and hosting a web service to consume the data from the other domain?

Many thanks for your help

regards

Welcome to Stack Overflow! Since you're new here, I recommend reading ["How do I ask a good question?"](https://stackoverflow.com/help/how-to-ask) for some tips. You've got a lot of VBA code here but very little about what you're doing in Office.js. Could you provide the code you're attempting to use? The `httpGetAsync` method here doesn't explain how it's being called or what you're expecting to get back. — Marc LaFleur, May 16 '18 at 14:22
Hello. indeed it is a CORS issue.But did somebody @Microsoft think of how to cope with that? In a more generic way. If I want to access an XML file on a server, I can do that perfectly with VBA but when I do the same thing with the Office API, it does not work. :-(. Until now I did not find any other solution then asking the admin of the host application to disable CORS checking server side, which is imposes a huge security risk... — g00golplex, Jul 20 '18 at 07:23
Hi all, has nobody a solution how to cope with those kind of errors? How to fetch data from a server you cannot control? — g00golplex, Dec 10 '18 at 11:06

Web query Excel JS api

0 Answers0