Thursday, 1 October 2020

Why does my custom Excel function never return a value ONLY on Windows?

I am building an Office Excel Add-in using the web add-in framework provided by Microsoft. This add-in also includes a custom function. Currently, the custom function is working on Excel for Mac, Excel online (device agnostic), but not on Windows?

The add-in loads fine, and there are no obvious errors. But when the function is run is just says: #BUSY and never resolves to a value.

WISE is the Excel function.

This is ridiculously frustrating. Here is the FULL code (it is very simple):

async function WISE(symbol, parameter, year, quarter) {
  let param = parameter.replace(/\s/g, '').toLowerCase();
  param = param.replace('&', 'and');
  symbol = symbol.toUpperCase();

  const result = await getAnnualData(symbol, param, year)
  return getVal(result, param)
}

function getVal(data, param) {
  // used to format keys (make all lowercase and remove whitespace)
  let apiResponseDataFormatted = {};
  for (let key in data) {
    apiResponseDataFormatted[key.replace(/ /g, '').toLowerCase()] = data[key];
  }
  let newValue = apiResponseDataFormatted[param];
  if (newValue !== 0 && !newValue) {
    newValue = 'Unavailable';
  }
  return newValue;
}

async function getAnnualData(symbol, parameter, year) {
  let apiPath = requestMap.get(parameter);
  let response;
  try {
    response = await axios(`${API_URL}/${apiPath}/${symbol}?apikey=${api_key}`);
  } catch (error) {
    console.log(error);
    return
  }

  let apiResponseData;
  let currentYear = new Date().getFullYear();

  if (year != null && year !== currentYear) {
    apiResponseData = response.data[currentYear - year - 1];
  } else {
    apiResponseData = response.data[0];
  }
  return apiResponseData
}

Notes:

  • There are no errors with the add-in as our Taskpane functionality runs perfectly.
  • The Taskpane is using async/await for web-requests and it works fine
  • Some variable definitions are hidden in the code block I've provided, so not defining a variable is not the issue.


from Why does my custom Excel function never return a value ONLY on Windows?

No comments:

Post a Comment