0

EDIT: Added simplified scenario and steps to reproduce at the end of the question. Please note that the objective is to include external script on Excel-Desktop, and not to get the simplified scenario working by some other means.

We all know and love the stock price example for custom functions where an up-to-date stock price is requested from the source using 'fetch':

https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/excel-tutorial-create-custom-functions?tabs=excel-windows#create-a-custom-function-that-requests-data-from-the-web

If there was an external API to get stock prices, could that also be used? Like so:

                   Stock Price Source
                           |  <----------- API knows how to get the stock price
           External Stock Price API Javascript Library
                           |  <----------- javascript function calls here
                    customfunctions.js
                           |
                         Excel

Or in other words, can the customfunctions JavaScript project include external script like

<script type="text/javascript" src="https://example.com/stock-price-api.js />

I have tried to include such a reference in the custom function project's index html file, included the script URL in the manifest under AllFormFactors/ExtensionPoint for the 'xsi:type=CustomFunctions', added a script reference under 'Resources/bt:Urls', and various combinations thereof, but the script does not seem to be 'known' to the custom functions themselves (the index html file, when loaded into the browser, knows about the script, so I know that the reference is correct). What I have tried was all guess work because I have not found any documentation regarding the use of external APIs. I'm trying to get this to work for Excel on Windows desktop (64-bit).

Can it be done?

EDIT/UPDATE: Just confirmed that it works fine for Excel Online by simply including the external script in the sample project's 'index.html' file. Is this intended to work for Excel Desktop?

To reproduce the issue:

  1. Follow the tutorial to create the basic custom functions project (using the typescript option) and ensure that they work (https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/excel-tutorial-create-custom-functions?tabs=excel-windows)

  2. Create a javascript file 'globalapi.js' and place it into the 'dist/win32/ship' folder (the same folder that contains the custom function javascript). Simply declare a global variable in the 'globalapi.js' file like so:

    var someGlobalString = 'This is a global string';

  3. Include the 'globalapi.js' script in the 'index.html' file; all the scripts included are then:

    <script src="https://officedev.github.io/custom-functions/lib/custom-functions-runtime.js" type="text/javascript"></script>
<script src="dist/win32/ship/globalapi.js" type="text/javascript"></script>
<script src="dist/win32/ship/index.win32.bundle" type="text/javascript"></script>

  4. Modify the 'add' function in 'customfunctions.ts' file so that it returns something different if 'someGlobalString' is defined:

    declare var someGlobalString: any;

function add(first: number, second: number): number {
  if (typeof someGlobalString !== 'undefined') {
     return 99999;
  }
  return first + second;
}

Now enter the custom function '=CONTOSO.ADD(1,2)' into a cell in Excel. For Excel Desktop (started with 'npm run sideload') the result displayed in the cell is '3', for Excel Online (after loading the manifest) the result is '99999'.

How can the difference be explained?

J.R.
  • 1,880
  • 8
  • 16

1 Answers1

1

Sorry, there's a bug right now on the latest windows build with web requests on Windows build 16.0.11406.20002.

https://github.com/OfficeDev/Excel-Custom-Functions/issues/115

We'll update that issue as soon as the next build rolls out to the Insiders channel.

thanks!

Keyur Patel - MSFT
  • 796
  • 5
  • 7
  • Hi Keyur, thanks for taking the time to reply. I think this may be a different issue. I have found that any included script regardless of whether it contains 'fetch' or not seems to work in Excel Online but not in Excel Desktop. I'll update the question with steps to reproduce. Maybe it is something obvious that I am missing? – J.R. Feb 24 '19 at 04:22
  • Thanks JR, are you including fetch via a custom libraries? If so that may not work if that library is using some other mechanism of the browser (ie DOM) to make the web request. In excel windows (and eventually mac), we only load a js runtime (chakracore on windows; jsc on Mac). This means we are like node.js in that all the libraries that you may used to be using on the browser may not work. See the custom functions runtime docs at https://aka.ms/customfunctions for more info. – Keyur Patel - MSFT Feb 24 '19 at 04:41
  • I have updated the question now. As you can see, the 'globalapi.js' is simply declaring a global variable and I'm not sure why the result is different on Desktop/Online. Can that be explained with what you have mentioned? If there is anything else I can do to help resolve or clarify the issue then please let me know. – J.R. Feb 24 '19 at 05:11
  • Thanks J.R., let me check with team and get back on Monday. – Keyur Patel - MSFT Feb 24 '19 at 05:47
  • Hi JR, so you'll need to bundle the file in your output so the javascript appears in your .bundle file. You can modify the webpack.config.js and add a listing to 'globalapi.js' in the 'entry' listing. This commit shows you an example: https://github.com/OfficeDev/Excel-Custom-Functions/commit/e47d18fd4184d7a187e1748b53553fb6cb5ee285 (quick note: if you're using Yeoman, we haven't merged this updated template yet to yo office) – Keyur Patel - MSFT Feb 27 '19 at 16:10
  • Hi Keyur, thank you for getting back to this. The 'globalapi.js' script is just to demonstrate where things start to go wrong, but the actual objective is to reference a separately maintained external API script that cannot be included into the bundle. Would that be possible as well? – J.R. Feb 27 '19 at 20:01
  • Hey! sorry for delay in coming back to this. If you want to reference external javascript, it needs to be bundled with the javascript for the custom function to load properly on Windows so the runtime has access to it. – Keyur Patel - MSFT Mar 17 '19 at 23:31
  • Thanks Keyur; it is not what I was hoping for but if you wanted to post this as a separate answer then I can accept it. If there are any plans to allow for the inclusion of external script in the future then I'd appreciate if you could comment. – J.R. Mar 18 '19 at 02:05
  • @KeyurPatel-MSFT do i need to still follow same approch to bundle all files in one javascript or it can work even without bundling in one file and just referancing other files in html code. – shyam_ Mar 15 '20 at 19:35
  • Hey folks, we have a new capability in developer preview called "Shared Runtime". In this mode, I don't think you'll need to follow the full bundling approach. https://developer.microsoft.com/en-us/office/blogs/build-contextual-ui-experiences-with-office-add-ins/ – Keyur Patel - MSFT Mar 16 '20 at 05:43