1

I am using Script lab in office 365 and facing difficulty in writing a function which fetches data from API(url). I need help in merging the below code with custom function in Java Script.

from the below code that I am able to get API results in script lab console but I want final results in excel screen(cell). Currently I can see all the ticker.name instead of specific. 

var request = new XMLHttpRequest();

request.open("GET", "https://api.coinmarketcap.com/v1/ticker/", true);
request.onload = function() {
  // Begin accessing JSON data here
  var data = JSON.parse(this.response);

  if (request.status >= 200 && request.status < 400) {
    data.forEach((ticker) => {
      console.log(ticker.name, ticker.rank);
    });
  } else {
    console.log("error");
  }
};

request.send();

For final result I should type =coinmarket.rank(bitcoin) in excel cell and the result should show me the rank of bitcoin from the list of other crypto currencies

Vishwesh
  • 11
  • 5

1 Answers1

0

have you seen this topic?

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-web-reqs

It shows you how to make web request inside custom functions. I'd recommend using Fetch, but it also shows you how to do an XHR request:

/**
 * Gets the star count for a given Github organization or user and repository.
 * @customfunction
 * @param userName string name of organization or user.
 * @param repoName string name of the repository.
 * @return number of stars.
 */
async function getStarCount(userName: string, repoName: string) {

  const url = "https://api.github.com/repos/" + userName + "/" + repoName;
  let xhttp = new XMLHttpRequest();

  return new Promise(function(resolve, reject) {
    xhttp.onreadystatechange = function() {
      if (xhttp.readyState !== 4) return;

      if (xhttp.status == 200) {
        resolve(JSON.parse(xhttp.responseText).watchers_count);
      } else {
        reject({
          status: xhttp.status,

          statusText: xhttp.statusText
        });
      }
    };

    xhttp.open("GET", url, true);
    xhttp.send();
  });
}
Keyur Patel - MSFT
  • 796
  • 5
  • 7
  • Thanks for quick reply. I tried it but somewhere I am still making mistakes. – Vishwesh Aug 07 '19 at 18:31
  • Also I would like to know why I am not able to register more than 5 functions in script lab? I am currently on monthly channel (Targeted), version 1908 (build 11911.20000). The moment I register 6th function the last registered gets obsolete. Currently I am seeing ("Preview") when I register a new function in script lab interface. This was the same issue when I was on office insider and was able to register only 1 function at a time. – Vishwesh Aug 07 '19 at 18:44