Which preferred IDE for Office JS Excel addins

Question

I am trying to develop add-ins for Excel with the Microsoft JS API. I have read the book from Michael Zlatkovsy about building office add-ins using Office.JS. but it is a very steep learning curve (having some background on VB 6, C, C# and Java. The problem here is that I have no idea which is the best approach to develop the add-ins?

1. Is it Visual Studio 2017
2. Visual Studio Code with Yo-man generator?

I tried to contact Michael Zlatkovsky, via the email address mentioned in his book, but sadly I never got a reply and on the Microsoft docs they just state the 2 options but they do not mention the fact that a Visual studio project is not in TypeScript and if you convert it to TypeScript, only the generated JavaScript is debugged, there is no way in getting the debugger to work directly on the TypeScript code as it should.

The error messages from the debugger in general are also not really precise. So any advice in the easiest way to develop the add-ins would be appreciated. I never needed so much time to learn a new technology than with the Office JS approach.

Many thanks in advance,

Tyishatyke

Answer 1

Great question.

Creating Office.js add-ins -- or even just a modern website in general, and Office.js add-ins really are just a website plus a manifest -- is certainly more involved than creating a VBA/VSTO project. So to set expectations, there will be a learning curve.

Depending on your level of comfort with web tech -- and your willingness to learn React if you don't know if yet -- I would definitely recommend going down the path of using either Yo Office or Create React App. Both of those would fall into the second camp, of using something ("yo" or CRA) to generate a project for you that you'd be using with VS Code, rather than starting out with the Office Add-ins project templates in Visual Studio (which are a little out of date at this point, and don't lend themselves as easily towards modern web tech like React). You would still use Visual Studio for debugging, however. The experience isn't 100% seamless, but it's not bad, either. For example, the add-in Script Lab (https://aka.ms/scriptlab) was developed precisely in this way.

Last I checked, "yo office" -- even if you selected "React" as the option -- was still creating its own custom project rather than leveraging the structure of create-react-app. Personally, I prefer CRA for its project structure and its simplified dependencies and configuration (no webpack configuration to manage, etc.). It's fairly easy to convert a CRA app into an Office Add-in, at least for regular taskpane add-ins (custom functions are a different story, and for that you'd probably still want to keep the "yo office" structure and scripts).

If you're interested in going down the create-react-app route and Office.js-ify it, here are the steps:

Start with the CRA instructions, choosing the TypeScript option (more info at https://create-react-app.dev/docs/adding-typescript)

On top of what it generates, you can go ahead and add Fabric React (for UI) as well as any other libraries. If you want to see what Script Lab uses, see https://github.com/OfficeDev/script-lab/blob/master/packages/editor/package.json, though it might be a tad overwhelming. The most useful super-simple library that's great "bang for the buck" is styled-components, which makes it easier to write your UI by essentially letting you in-line CSS into your JS/React in a very easy way. Script Lab also makes use of Redux and related libraries (typesafe-actions, redux-saga, reselect) which are useful once they are set up, but they might be a overkill for you (esp. if you are learning this as you go). So for now, I would stick with just the basic typescriptified create-react-app + Fabric React + optionally styled components, and call that good.

Once you have a functional website, how do you go from website to add-in? Well, first of all, you add a CDN reference to Office.js (see https://learn.microsoft.com/en-us/office/dev/add-ins/develop/referencing-the-javascript-api-for-office-library-from-its-cdn). Note that even though the rest of your project will be modern modular javascript (and hence import statements, etc.), for the Office.js reference, you'll use a script tag. You'll also want to "npm install --save-dev @types/office-js" to get the latest TypeScript definitions for Office. And speaking of JS, somewhere in your bootstrapping logic (e.g., whatever wraps ReactDOM.render), add a call to "Office.onReady()" -- see https://learn.microsoft.com/en-us/office/dev/add-ins/develop/understanding-the-javascript-api-for-office#initialize-with-officeonready for more info.

Note that when you add the "Office.onReady", you may run into a compile-time issue complaining about "no-undef". As a workaround, just put the comment "/* global Excel, Office */" into the JS file that it complains about, and things will magically work. (More info here: https://github.com/OfficeDev/office-js-docs-pr/issues/691)

Next, you will need to create/add an Office manifest (an xml file describing the URL of your site, the ribbon button arrangement, etc). The file is somewhat cumbersome to work with, but the good news is that you don't need to touch it too often. You can use a similar file to what yo office generates, adjusting paths (e.g., to images) as necessary. You'll likely end up with two or more manifests, one for localhost and one for your production environment... but you can start with just your localhost.

Once you've authored the manifest, you will want to sideload it into Office. For desktop, use the instructions at https://learn.microsoft.com/en-us/office/dev/add-ins/testing/create-a-network-shared-folder-catalog-for-task-pane-and-content-add-ins. For Office Online, use https://learn.microsoft.com/en-us/office/dev/add-ins/testing/sideload-office-add-ins-for-testing. For Mac, use the instructions at https://learn.microsoft.com/en-us/office/dev/add-ins/testing/sideload-an-office-add-in-on-ipad-and-mac. If it fails to load, it means that you have an issue in the manifest, which happens all the time -- it's a very picky format. In that case, see the troubleshooting steps at https://learn.microsoft.com/en-us/office/dev/add-ins/testing/troubleshoot-manifest.

On a side-note, I'm surprised (and sorry to hear) that you tried to reach out to me by email and didn't get a response. I don't recall seeing an email -- if I would, I would have answered it (at least with a short response redirecting you to StackOverflow :-) ). This is the first I've heard of an email not reaching me. Maybe try again? And I'll check my spam folder too in the next few days, to make sure your new email didn't end up there...

Best!

~ Michael

Damicke

Hi Michael, thanks for your detailed description! I am digging into your suggestions. I just get some more questions, sorry. Do you think that React is the way to go for somebody who has only basic knowledge of JavaScript? – Tyishatyke

React certainly has a learning curve — but it is great technology, and moreover it is necessary if you want to make use of Fabric React (which would give your app an office-y look and feel). If you want, on leanpub.com/buildingofficeaddins, I have an option for “Book + 1 Hour Skype call / coaching” (with all profits from that still donated to charity, just like the book), if you think it’s be useful to discuss in person and/or for me to guide you through the beginning stages. – Damicke

With yo office to be preferred over VS templates: what's a good way to get an F5 debugging experience in VS from a TypeScript add-in created using yo? Currently I haven't found a good way to debug my add-in in desktop Word, as even using Attach debugger as described in the docs seems broken and didn't do anything at all :( – Reminisce

@DirkVollmar, I recommend you start a separate question if debugging isn't working for you. I know that for older versions of Office (which used the IE11 engine for rendering add-ins) and with Visual Studio 2017 installed (note: requires 2017, not 2019), "Attach debugger" definitely worked. For instructions with newer Office (which might use Edge browser for rendering), see learn.microsoft.com/en-us/office/dev/add-ins/testing/… -- though I personally haven't tried it. – Damicke

Thanks for your reply. Using the Microsoft Edge (EdgeHTML) Developer Tools as described in learn.microsoft.com/en-us/microsoft-edge/devtools-guide works. I'm looking for a simple way to get F5 debugging in VS working (again) with the yo templates though, as this allows using the same IDE for writing code and debugging, and to easily start debugging. Still investigating why Attach Debugger isn't working, might result in a separate question. – Reminisce

Hi Michael, I just purchased the book again + 1 H skype call Coaching as well. So I do not know how to proceed? Kind regards, – Tyishatyke

@g00golplex, just reach out to me by email (via the LeanPub "contact the author" option) and we can coordinate. – Damicke

Answer 2

Is it Visual Studio 2017

Visual Studio Code with Yo-man generator?

Why can't you have both?

I'm new to JS/Office Add-Ins, so take this with a grain of salt, but I spent way more time then I'd care to admit working on testing my dev environment.

Here is where I ended and some notes:

• I prefer editing code in VSCode
• VS 2017 has best debugging which is imperative
• VS 2017 files are outdated and proj files are "flat"
• Yeoman templates use polyfill, which enables more, but adds complexity.
• Yeoman uses NPM/NodeJS which has its pros/cons. Easy to add extra libraries is a nice feature.
• I hate webpack...

My final Dev Environment was made like this:

• Installed VS 2022 and make an Office Add-In template
• Copied everything? or just Scripts/Content? (I forget) to a VS 2017 template. I remember one issue being I had to use the 2017 compiler in packages.config but I was able to use the newer office.js.

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="jQuery" version="3.6.0" targetFramework="net472" />
  <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.0" targetFramework="net461" />
  <package id="Microsoft.Office.js" version="1.1.0.18" targetFramework="net472" />
  <package id="OfficeUIFabricCore" version="9.6.0" targetFramework="net472" />
</packages>

In VS you will have to update 2x Schemas on your local dev computer for TaskPaneAppVersionOverridesV1_0.xsd and OfficeAppBasicTypesV1_0.xsd. Basically you need to replace/update the files under --> C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Xml\Schemas\1033 with the ones from here --> https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-owemxml/82e93ec5-de22-42a8-86e3-353c8336aa40

I then used yo office to generate a JS Taskpane and CustomFunction template. I combined the node_modules folder and the packages.json and some of the HTML/JS files.

I then deleted all the Home./FunctionFile stuff from the VS template and added the yo folder to the ExcelWebAddIn1Web folder. (Making sure the yo folder exists).

In the manifest for VS2017 I pointed everything to the dist folder that npm run build output. <SourceLocation DefaultValue="~remoteAppUrl/yo/dist/taskpane.html"/>

So each time I need to test I have to open a cmd prompt and run npm run build then hit start in VS. I'd love to automate it so I could launch/hit stat from the CLI if anyone knows how. (update below)

Finally, on a side note about WebPack. Originally I stayed w/ Yo until I tried adding files to taskpane.html with relative references. NPM would output a taskpane.html with hashed files names like ABC123.js but the file name would be XYZ456.js. It seemed if I used http in src attribute then NPM wouldn't hash the file name, but if I used relative paths, it made a hash w/ the relative path included in the hash output.

Long story short, to overcome this I made a taskpane.npm which was just a copy of the first taskpane.html that npm run build output.

And then in yo_build.cmd this is what I have (I added sendkeys.bat to switch back to VS and start solution)

@ECHO OFF
setlocal ENABLEEXTENSIONS
pushd %~dp0
cls

npm run build && xcopy /y /s .\src\taskpane\taskpane.npm .\dist\taskpane.html && call sendkeys.bat "ExcelWebAddIn1 - Microsoft Visual Studio  (Administrator)" "{F5}" 

This enables me to keep my own taskpane.html and gives me the added functionality of adding on extra whatever to the end of my npm build. I have a feeling I'll add more to that script at some point.

In the end, I have a Yo Office w/ TaskPane/Custom Function running under VS 2017 w/ files from VS 2022 injected. I tend to use VS 2017 for coding more, as it has to be open for debugging, but I have a feeling I'll open VSCode before production to check/prettier etc.

Notes: I was able to get all 3 things working w/ Excel in the same Add-In via this method, taskpane, command-add-in and custom function!

I wasn't able to get this to work w/ TypeScript templates from yo office. I'd be tempted to try again, but by the time I got it working after re-doing it X amount of times, I wasn't going to re-start and I do remember trying at least once and it failed.

I also added this to my commands.js to get the macro to load upon Excel starting to streamline things:

Office.onReady(() => {
    // If needed, Office.js is ready to be called
    Office.addin.setStartupBehavior(Office.StartupBehavior.load);
    //Office.addin.showAsTaskpane();
    //Office.addin.hide();
    /*
         let sheets = context.workbook.worksheets;
        sheets[onEventName].add(async (eventData: any) => {
             // EXECUTE YOUR EVENT HANDLER HERE
        });
       
    Excel.run(async context => {
        let sheet = context.workbook.worksheets.getActiveWorksheet();
        sheet.onChanged.add(onChange);

        await context.sync();
        console.log("A handler has been registered for the onChanged event.");
    });
     */
    Excel.run(async context => {
        helloworld();
    });
    
}); 

and this to helloworld func to avoid console errors about event being unable to complete on first run.

try { event.completed() } catch (error) { }

Strickman