Getting Started with OfficeJS

If you are just getting started with OfficeJS, then this post is for you. I will assume you have some understanding of what Office Web Add-ins are. However, there is one very basic point I will demonstrate in this tutorial:

  • An Office Web Add-In is simply a web site and an XML “description” file. I detailed this in this blog post. And we are going to build on that.

So, you are brand new to OfficeJS, and want to get started. The first thing you need to do is get your development environment up and running. There is a script for that:

Microsoft Office Development Environment Script

NOTE: For more information on this script, please read my blog post.

Next, you will need to pull down the Web Add-in Side Loader tool. Keep this ZIP on your desktop. This will be used later in the steps below to make it really easy for you to install the Add-in to Excel. However, if you prefer to do the steps manually, see the link below. For more information on this tool, please see my blog post and/or the GitHub repository.

Now that you have the development environment setup, lets build your very first, super basic Excel add-in:

  • Create a folder on your desktop called “MyFirstAddIn”
  • Open Notepad and copy/paste the code from HERE, then click File, Save, name it “manifest.xml”, change the type to “All Files *.*”, browse to folder on your desktop, and click Save.

NOTE: In this step you created the manifest file. This file will be used to “install” the add-in into Excel. Essentially, it will tell Excel where the web page is for the task pane.

  • Close Notepad, then open it again. Copy paste this code HERE, then click File, Save, name it “home.html”, change the type to “All Files *.*”, browse to the folder on your desktop, and click Save.

NOTE: In this step you created the primary file for website. This one file contains both the HTML and JavaScript needed to make the taskpane load in Excel.

  • Now you need to side load the manifest and you do that by first opening the ZIP file on your desktop: Set-WebAddin (v1.0.0.0).zip and extracting the Set-WebAddin.exe to the “MyFirstAddIn” folder.
  • Press Window+R to open the run dialog, and type CMD and press enter.

NOTE: This will open the Windows Command Prompt which you will use to sideload the add-in and run it.

NOTE: The word “sideload” is a fancy way to say install it for only your instance of Excel. It is also known as Developer Sideloading.

  • Change the directory to the folder on your desktop with this command:
cd "%userprofile%\desktop\MyFirstAddIn"
  • Next, type this command:
Set-WebAddin -test -manifestPath "%userprofile%\desktop\myfirstaddin\manifest.xml"

NOTE: In this step you have sideloaded the add-in to Office. This essentially tells Office you have an added an Excel add-in via the information in the manifest file which, when loaded tells Excel everything it needs to know about it, including where to find it. If you prefer to do the sideloading steps manually, you can follow them here.

  • Now you are almost ready. Type this last command (that is a dot/period at the end, very important):
http-server .

NOTE: The above steps start a local the http-server on your computer so that it will serve the webpage in the browser from the folder (.) you are current inside. You can now test this by clicking this link.

  • You are now ready to go. Simply open Excel and a new Blank Workbook. Then on the Insert tab, click the down arrow next to My add-ins and click BasicAddin. It should look like this:
The BasicAddIn in the Developer Add-ins menu
  • Your add-in Task Pane should load.
  • Click in cell A1, type “Hello World!”, and then in your Task Pane, click the “Click here” button and you should see “Hello World!” in the bottom of the page.
  • Once you are done, you can press CTRL+C in the command prompt window to shutdown the server and then to uninstall the add-in you can use this command:
Set-WebAddin -cleanup -manifestPath "%userprofile%\desktop\myfirstaddin\manifest.xml"

And that is it. There are three things to note here:

  • You created your first web add-in.
  • You did not use VSCode or any Integrated Development Environment. You did this only with Notepad.
  • But as an added bonus – you have also installed VSCode and the tools you need to start doing more complex things with Office Web Add-ins.

JavaScript, HTML, Web, CSS, front-end, back-end, web server, ports, local hosts… some, if not all of these are new terms if you are just getting into this new world of the Web and Web add-ins. But it does not have to be that complicated. As I referred to earlier, the following posts should be your next two stops on your journey:

Getting started as an OfficeJS Developer

Start Developing in OfficeJS Today with ScriptLab

If you have any questions, please reach out to me.

easyEws v1.0.15 Released

In this latest release I have incorporated my first community pull. A big thank you to Vijay Samtani for adding the sendMailItem. This new addition allows you to create a message to multiple recipients and with zero to many attachments.

Here is a link to the repository: https://github.com/davecra/easyEWS

You can references it in your code:


<script type="text/javascript" src="node_modules/easyews/easyews.min.js"></script>

And you can allow pull it down from NPM:

npm install easyEws

Microsoft Office Development Environment Setup Script

If you are new to Office Web Add-in Development, then this post is for you. Even if you are not new and you need to setup a new development environment, this post is for you too.

Now, as far as manually setting up a new Office web add-in development environment, there are a few sources for helping you get started. There is the official guide here:

Set up your development environment

And I have also written one here:

How to-Configure VSCode for Office Development

NOTE: Mine is a tad more complicated, but essentially walks you through setting up for full fledged development, including source control with Git. That article was actually more for me when I wrote it.

BUT WAIT, THERE’S MORE…

However, after a co-worker and fellow Office Developer (Marty Andren) and I got to talking about this topic, I had an idea to create a script essentially automating the entire process. That was how the Microsoft Office Development Setup script, or MODES was born. The script is posted in the following repository:

https://github.com/davecra/modes

The MODES script is provided as-is for folks who need to setup an Office development environment. It is a simple run it and done. Once you have completed this script you will have:

NOTE: This also installs http-server, a simple local web server that I hope to use in a forthcoming blog post that expands on a previous post demonstrating how easy it is to build a Web Add-ins from scratch.

NOTE: The script installs everything as local user, so there is no need for administrative permissions.

On the MODES GitHub post, you can follow the README directions to download and run the PowerShell script. If you are unfamiliar with PowerShell, you can just follow the steps and should walk you through what you need to do fairly quickly and easily.

If the above script cannot be run on your system (as a script), you can try to perform the steps manually by Copy/Pasting the following GIST into a PowerShell window and pressing enter:


Add-Type assembly "system.io.compression.filesystem";$vscode_url = "https://aka.ms/win32-x64-user-stable";$vscode_output = $env:USERPROFILE + "\downloads\vscode.exe";$node_url = "https://nodejs.org/dist/latest/win-x64/node.exe";$node_output = $env:USERPROFILE + "\downloads\node.exe";$node_install_path = $env:USERPROFILE + "\node";$npm_url = "https://nodejs.org/dist/npm/npm-1.4.9.zip";$npm_output = $env:USERPROFILE + "\downloads\npm.zip"
Invoke-WebRequest Uri $vscode_url OutFile $vscode_output
Invoke-WebRequest Uri $node_url OutFile $node_output
Invoke-WebRequest Uri $npm_url OutFile $npm_output
Start-Process FilePath $vscode_output ArgumentList ('/VERYSILENT', '/MERGETASKS=!runcode') Wait
md $node_install_path
copy $node_output $node_install_path
[io.compression.zipfile]::ExtractToDirectory($npm_output, $node_install_path)
[Environment]::SetEnvironmentVariable("Path", [Environment]::GetEnvironmentVariable("Path", [EnvironmentVariableTarget]::User) + ";" + $node_install_path, [EnvironmentVariableTarget]::User)
$env:Path += ";" + $node_install_path
cd $node_install_path
Start-Process FilePath npm ArgumentList ('install', '-g', 'npm') WindowStyle Hidden Wait
Start-Process FilePath npm ArgumentList('install', '-g', 'yo', 'generator-office') WindowStyle Hidden Wait
Start-Process FilePath npm ArgumentList('install', '-g', 'http-server') WindowStyle Hidden Wait
$yo_file = $node_install_path + "\yo.ps1"
ren $yo_file "yo.ps1.old"

view raw

modes_gist.ps1

hosted with ❤ by GitHub

This GIST is a “minified” version of the script on GitHib (no comments, no output and some lines are combined). So if you run this GIST, you will need to do give it time to complete as there is no feedback.

 

 

easyEws v1.0.14 released

First off, there is no version 1.0.13. wlEmoticon-winkingsmile.png

For this latest release, I have added one handy new function getParentId(). This function uses the child message “InReplyTo” field to locate the parent message with the same corresponding “InternetMessageId” field. If found, you will get the Id of that parent message. If not found, you will get NULL.

As per usual, to get the latest you can link to the CDN: https://cdn.jsdelivr.net/gh/davecra/easyEws/easyEws.js

Or you can access it from NPM via this command:

npm -install easyews

NOTE: I was informed that I had forgot to update NPM to v1.0.12 per the last release. So I have fixed that and have updated this build on NPM.

easyEws v1.0.12 released

I have updated the easyEws library.

The changes I made in v1.0.11 for ResolveNames were only ES6 compliant and did not work in IE11. Specifically, I defined an ES6 class object versus a more compliant ES3+ function/class. The update was recoded and now functions in IE11.

Additionally, there was a reported issue with recipient aggregation in the getallRecipientsAsync() function. I was using a .forEach() on the recipient array which seemed to be causing problems with larger result sets. So I updated it to use a standard for loop.

Other than that there were no major changes to this version.

Office Scripts: The future is here

Microsoft has been working hard to make Office available on every platform. One of the biggest changes has been the ability to run Office the a browser or Office online. However, one drawback has been that automation in Office online has been limited to Web Add-ins which are aimed solidly at professional developers. However, Visual Basic for Applications (VBA) macros are only supported in Windows and Mac clients. They are not supported on the web platform, the iOS or Android platforms. And, well, they still are not.  wlEmoticon-disappointedsmile.png  However, something new has arrived which will allow you to create “macros” anywhere, on any platform (eventually).

Microsoft recently announced Office Scripts. It is an end-user approachable, web and collaborative supported scripting language. It is still in preview and only supported in Excel online, for now.

First, to access this you must have your administrator enable it in the Office 365 Portal:

turn_on_scripts

For citizen programmers this line from the above page says it all:

Scripts allow you to record and replay your Excel actions on different workbooks and worksheets. If you find yourself doing the same things over and over again, an Office Script can help you by reducing your whole workflow to a single button press.

So, I enabled it in my test tenant and created the quintessential “Hello Wold” Action Recording. When your administrator enables this on your tenant, you will get this new Automation tab:

AutomationTab

From there, I clicked on Record Actions, typed “Hello World!” in cell A1, and then stopped the macro. It then asked me to name it and give a description (optional), and then Save. From there it opened the Code Editor and here is what I see:


async function main(context: Excel.RequestContext) {
// Set range A1 on selectedSheet
let workbook = context.workbook;
let worksheets = workbook.worksheets;
let selectedSheet = worksheets.getActiveWorksheet();
selectedSheet.getRange("A1").values = [
["Hello World"]
];
}

view raw

HelloWorld.js

hosted with ❤ by GitHub

Once you open a new workbook, you can then click on the Code Editor button and you will see all your recorded scripts:

recordings

The future is here. Check it out now. You can learn more about Office Scripts from here: https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial.

 

What is Office 365, really?

In my day-to-day dealings with people and organizations, there is a common confusion on the term “Office 365.” And that confusion is fed by a few common myths that I have heard repeated by some very smart people in those organizations. So, first, let me dismiss the myths…

  • The first one is that Office 365 is online, in the browser applications only… NO! Office 365 is NOT just Excel Online, Word Online, PowerPoint Online, Outlook Online, and OneNote Online.
  • And the second myth is that the desktop applications are getting deprecated… NO! Microsoft is NOT doing away with the installed desktop applications on Windows and Mac in favor of web based versions.

So let’s get to the question:

If Microsoft is not replacing Office with web versions, then what the heck is all this Office 365 and cloud stuff then?

Primarily, Office 365 is the name of the subscription. You no longer buy the Office applications on disks and install them, then buy the next version, etc. With the subscription you always get the newest version (and much more). As you will read from the link above, there are lots of FAQ’s and other information that explain how this works.

So Excel Online, ET AL. is PART of the Office 365 subscription based platform. Office 365 still includes the traditional Microsoft Office desktop applications on the PC/Windows or the Mac. And they are not going anywhere. When you hear that your organization is moving to Office 365 it does not mean you will ONLY be accessing Office from a web browser.

Office 365 is really Office anywhere, on any device. For example, Office 365 also includes applications on iPhone, iPad, and Android. And it is not just the traditional applications. There are incredible tools such as Forms, Sway, Teams, and for developers the Graph API. It also shifts workloads like Exchange Server for mail and SharePoint server for document management from local IT servers to servers run by Microsoft in the cloud.

So when you hear Office 365, DO NOT THINK GOOGLE DOCS. wlEmoticon-smile.png The online versions of Office are only there to provide an additional avenues to access Office from anywhere, on any device. Per the Office 365 website:

Works across multiple devices

Get the fully installed Office apps on multiple PCs, Macs, tablets, and mobile devices (including Windows, iOS, and Android).

They are not as fully featured and as such you really need to consider when you might use them over using the traditional Microsoft Office desktop applications. Per this site (my emphasis added):

Office for the web (formerly Office Web Apps) opens Word, Excel, OneNote, and PowerPoint documents in your web browser. Office for the web makes it easier to work and share Office files from anywhere with an internet connection, from almost any device. Microsoft Office 365 customers with Word, Excel, OneNote, or PowerPoint can view, create, and edit files on the go. …

The following tables compare Office for the web feature capabilities to feature-rich Microsoft Office desktop apps.

So, if you like your Excel just the way it is, installed on your scientific workstation with a bazillion gigabytes of RAM, you can still run it there if you move to Office 365. What you get with the traditional Office applications is more frequent, seamless and automatic updates from the web. Per the Office 365 website:

Monthly Updates

Get the latest features and capabilities with fully installed and always up-to-date versions of Outlook, Word, Excel, PowerPoint for Windows or Mac, OneNote (features vary), Teams, and Access and Publisher (PC only).

Hopefully, this has helped clear up and dispel the myths. And when you go back to the smart person that has told you otherwise, and continues to insist and tell you otherwise, please send them here. wlEmoticon-hotsmile.png

Web Add-in Side Loader Tool

Whether you have been following the guidance for Centralized Deployment of Web Add-ins or you do not have Office 365 and you want to create a SharePoint catalog for deployment, but realize it has limitations:

App catalogs on SharePoint do not support add-in features that are implemented in the VersionOverrides node of the add-in manifest, such as add-in commands.

You essentially try all the options on this page and find that side loading is the only method that will work. So you give it a shot, it works and now you are wondering how to deploy that to a larger group of users.

That is where this new tool that a fellow co-worker (Marty Andren) and I created will come in handy: The Web Add-in Side Loader Tool. Here is the link to the GitHub where it and it’s documentation are located:

https://github.com/davecra/WebAddinSideloader

Please let me know if you have any questions or issues with this tool.

Extended MessageBox Library

Several years ago I created this class to assist with making a better, more useful Message Box windows in Add-ins and other projects. You can find it here in my GitHub repository:

Extended MessageBox Library

There were a few times I wanted to show the user a message, but also provide them with a link. Other times I found that I needed to present an error to the user, but wanted to hide more “technical” details from them, unless they clicked a More Info button. I also found cases where I needed to present an error if the user wanted to stop a potentially unattended process, but also wanted the message to dismiss after a period of time so the process could continue. Other times I wanted to present the user with a useful message, but an option to “Do not show this message again.”

After creating custom forms, over and over again, this C# library was born. I recently was writing a VSTO add-in that needed the later option – do not show again – and dusted off this old library, cleaned it up a bit (not not completely) and used it again. I figured, for posterity, I would share this with everyone since I have found it so useful over the years.

For the most part you use this exactly like you would the regular MessageBox object, with some extra goodies included.

For example, this will create a very simple Message box that will look fmailiar:

ExtendedMessageBox.Show(“Hello World”);

Produces this:

simple_msgbox

A more common scenario you might need with a checkbox, looks like this:


ExtendedDialogResult LobjResult = ExtendedMessageBox.Show("Hello World – Are you ok?",
"The Office Context",
MessageBoxButtons.OKCancel,
MessageBoxIcon.Information,
"Do not show this message again.");
if(LobjResult.Result == DialogResult.OK && LobjResult.IsChecked)
{
// do something
}

checkbox_msgbox

There are a lot more options and capabilities. I will not cover them all here, but they will be detailed in the README in the GitHub repository.

Please let me know if you have any questions or issues.

OfficeJS.dialogs Updated to v1.0.9

Like yesterday with easyEws, it has been a while since I have touched the OfficeJS.Dialogs library. I updated it yesterday with a quick fix and some content updates. I have had questions about using it from a CDN. The primary issue is that the displayDialogAsync() API does not support CDN. It just displays the HTML as raw text in the dialog. There are some ways I can provide this as a workaround, but it involves me support infrastructure. So, the better thing is to just wait for the API to support CDN’s.

If you have any questions about using this library, please let me know.