Office Scripts VS Script Lab

Question

I am confused about Office Scripts and Script Lab.

Both can run javascript in Excel, but it seems the code can't be shared in them.

For Office Scripts, some code like

function main(workbook: ExcelScript.Workbook) {
  // Set fill color to FFC000 for range Sheet1!A2:C2
  let selectedSheet = workbook.getActiveWorksheet();
  selectedSheet.getRange("A2:C2").getFormat().getFill().setColor("FFC000");
}

For Script lab, the code is

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.tables.add("B2:E5", true);
    await context.sync();
});

The workbook are different in ExcelScript.Workbook and context.workbook

Answer 1

They’re extremely different but there is an element of perceived cross over.

The script lab is in place to help you with the process of building Office JS add-ins but it's not the complete solution. You need to build the add-ins using the SDK's Microsoft provide through an IDE like VS or VS code. It's the cross platform mechanism for building add-ins that work on Excel for web, Windows and Apple platforms.

Office Scripts provides a mechanism for writing Typescript functions that are then able to be executed from PowerAutomate flows.

Also, with Office JS, you can create a fully functioning action pane with HTML/CSS, etc. that your user can interact with. The current extent that Office Scripts provide is a button with script behind. It's really more of an interface mechanism that the user wouldn't typically interact with. They're really powerful when you consider you can mix and match the inputs and outputs with other actions in PowerAutomate.

This is a direct quote from the documentation.

Office Add-ins are cross-platform. They work across Windows desktop, Mac, iOS, and web platforms and provide the same experience on each. Any exception to this is noted in the documentation of the individual API.

Office Scripts are currently only supported by for Excel on the web. All recording, editing, and script management is done on the web platform.

While the Office JavaScript APIs for Office Add-ins and the Office Scripts APIs share some functionality, they are different platforms. The Office Scripts APIs are an optimized, synchronous subset of the Excel JavaScript API model. The major difference is usage of the load/sync paradigm with add-ins. Additionally, add-ins offer APIs for events and a broader set of functionality outside of Excel, known as the Common APIs.

An add-in compared to a flow function are very different from a usage perspective and so is the development process. Also, you host your add-in on a web server somewhere when you build it using Office JS whereas with Office Scripts, it's all done for you. The scripts are stored in your OneDrive and the platform has the application model for execution, you don't reference and use that SDK in a self contained project like you do for Office JS.

Some resources to reference ...

https://learn.microsoft.com/en-us/office/dev/scripts/resources/vba-differences

https://learn.microsoft.com/en-us/office/dev/add-ins/overview/explore-with-script-lab

Answer 2

There are two different APIs: Office.js and Office Scripts. Office.js has APIs available on platforms other than Excel (e.g. Word, OneNote, etc.). Office Scripts is currently only available for Excel.

ScriptLab is an add-in meant for exploring the Office.js APIs. While you can try to use it for automation, it's not meant to be a development environment. You can however experiment with the API, create custom functions, etc. You can later create more sophisticated add-ins using an Office Add-in creator like Yeoman generator for Office. Add-ins would then be deployed to a server where they could be utilized on any platform supported (PC, Mac, Web, etc.)

Office.js is more suited towards traditional developers. To develop add-ins, in addition to TypeScript, you may also need knowledge of HTML / CSS. In terms of JavaScript / TypeScript, you also need to know about promises or asynchronous concepts

Office Scripts is a simplified API. It is actually built on top of Office.js. It does not require knowledge of HTML / CSS, promises, asynchronous concepts, etc. It also does not require deployment to a web server. Because it's simplified, the APIs actually end up being different. But overall the APIs aren't too different. If you're looking to do relatively simple automation and don't want to deal with the overhead of developing add-ins, Office Scripts are a good choice. It's also a good choice if you want to do simple scripting to integrate with PowerAutomate.