Office Scripts are an online version of VBA. Built to run in Excel online, it is similar to GoogleDocs App Scripts, but based on TypeScript instead of JavaScript (But as TypeScript is a subset of JavaScript, you can use JavaScript as well).
One of the most powerful things about Office Scripts is that they can be called by Power Automate, so they can run without any human interaction. If you want to know about Office Scripts from the Power Automate side I have done a sister article, that has same basics as this article, but the additional features/steps required in Power Automate.
In this article i'm going to cover:
- Accessing Scripts
- Triggering Scripts
- Intellisense
- Variables, Types and Interfaces
- Get & Set
- Basic Excel Actions
- If
- Loop
- API
- Additional Functions
Accessing Scripts
In Excel online you will see an Automate tab in the ribbon bar. The Ribbon allows you to record actions (just like vba), Create a blank New Script, Open existing Scripts and Automate a task with a Power Automate template.
The record function does not record every action, and uses selections instead of references, but it does show good notes and is a good way to learn.
Triggering Scripts
Office Scripts can be run by selecting them in the Automate tab, or by adding a button.
Office Scripts by default are stored on your OneDrive, in Documents/Office Scripts folder, though they can be shared with a file by clicking Share in the same menu as adding a button. This then attaches the script to the file, so anyone with the file can run the script.
Intellisense
Intellisense is auto complete for code, as you type in the function you want Office Scripts will try and guess what you are typing by listing all possible options. This is great not only for speeding up you typing but can be used as a reference to find the function you are looking for.
Variables, Types and Interfaces
This is where anyone with TypeScript knowledge can start jumping ahead. Variables are declared with 'let', and need to declare type (must have value set against them).
let sString="";
let iNumber=0;
let bFlag=false;
We can also declare objects, like workbooks, worksheets, images, ranges and more to variables, to make them easier to use and update.
function main(workbook: ExcelScript.Workbook) {
let ws=workbook.getWorksheet("Sheet1");
ws.setName("test");
}
Variables are scoped locally, so a variable declared in the function is scoped to the function, a variable declared inside a loop is scoped to the loop, and cant be read outside of the loop.
When declaring an array you have 2 options, an empty array and structured.
Empty arrays are for simple arrays with no objects within them, if you need an object then you should use an interface to set the structure.
function main(workbook: ExcelScript.Workbooklet){
aSimple=[]=[];
let aStructured:schema[];
}
interface schema {
stringField: string,
numberField: number,
booleanField: boolean
}
As you can see you declare the interface outside of the function, and it creates the schema for the array, so aStructure will look like this:
[
{stringField:"test",numberField:1,booleanField:false},
{stringField:"test2",numberField:2,booleanField:true}
]
where as aSimple would be something like:
[1,2,3,4,5,6,7,8]
A type is almost interchange with an interface, with the differences not really present in Office Scripts. So you can use a type instead of an interface, but most Microsoft documentation uses interface.
type dataType = {
data: JSONData[],
name: string
}
Get & Set
As you saw with getWorkSheet("Sheet1"), get is used to reference something, to either store as variable or to complete an action against e.g. setValue(). It can get not only parts of the workbook, but parameters to them, like worksheet name.
So you get your worksheet, get your range, then you set your range. That can be a formula (setFormula) or value (setValue), and can be one cell or a range (setValues).
So in below example we are going to copy a filtered list from one sheet to another.
function main(workbook: ExcelScript.Workbook) {
let ws=workbook.getWorksheet("summary");
let i=0;
let aNewRange=[]=[];
let rng=ws.getUsedRange().getValues();
aNewRange.push(rng[0]);
for(i==0;i<rng.length; i++){
if(rng[i][0]==3){
aNewRange.push(rng[i]);
}
}
workbook.getWorksheet("Sheet2").getRange("A1:e"+aNewRange.length).setValues(aNewRange);
}
You may think the best approach would be to filter the excel data, then copy and paste. But it is better to grab the whole range, filter it, then paste set the range to the filtered values.
let rng=ws.getUsedRange().getValues(); - gets values from range
aNewRange.push(rng[0]); - adds header row
for(i==0;i<rng.length; i++){ - loop over rows in the array
if(rng[i][0]==3){ - if condition
aNewRange.push(rng[i]); - add row to array
workbook.getWorksheet("Sheet2").getRange("A1:e"+aNewRange.length).setValues(aNewRange); - sets array to range
You could also set the range row by row in the loop, but this can have a big impact on performance. For any interactions with the Excel file uses api calls, so we should avoid placing them in loops where ever possible.
Basic Excel Actions
As you would expect, you can interact with the Excel workbook. The list below gives a few examples, based on a worksheets assigned to ws and wsPivot variables.
As you would expect, you can interact with the Excel workbook. The list below gives a few examples, based on a worksheets assigned to ws and wsPivot variables.
let ws= workbook.addWorksheet("test"); - Add worksheet called test
ws.delete(); - Delete worksheet
let chartName = selectedSheet.addChart(ExcelScript.ChartType.pie, selectedSheet.getRange("A1:C15")); - Insert chart on sheet selectedSheet
let newPivotTable = workbook.addPivotTable("PivotTableName", ws.getRange("A1:C15"), wsPivot.getRange("A2:C16")) - Add a new pivot table on sheet3
newPivotTable.refresh(); - Refresh newPivotTable
ws.getAutoFilter().apply(ws.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: ["1"] }); - Apply values filter of 1 to range
ws.getRange("A1:C4").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin); - Add thin border to bottom of range
ws.getRange("A1:C4").getFormat().getFill().setColor("FFFF00"); - Set fill color to FFFF00 for range
ws.getRange("A1:C4").removeDuplicates([0], false); - Remove duplicates from rangeG9:G39
ws.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right); - Insert column F:F, move existing cells right
ws.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left); - Delete column F:F
ws.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down); - Insert at range 39:39, move existing cells down
ws.getRange("3:3").delete(ExcelScript.DeleteShiftDirection.up); - Delete row 39:39
ws.getRange("A1:C4").getFormat().getFill().setColor("FFFF00"); - Set fill color to FFFF00 for range
ws.getRange("A1:C4").removeDuplicates([0], false); - Remove duplicates from range G9:G39
ws.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right); - Insert column F:F, move existing cells right
ws.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left); - Delete column F:F
ws.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down); - Insert at range 39:39, move existing cells down
ws.getRange("3:3").delete(ExcelScript.DeleteShiftDirection.up); - Delete row 39:39
If
If is a fundamental action in all coding, luckily Office Scripts leverage TypeScript/JavaScript so its nice and simple.
if(rng[i][0]==3){
aNewRange.push(rng[i]);
} else {
console.log("Not a 3");
}
if (condition){//if true do}else{//if false do). As you can see the logic is different to Excel, so equals is == (=== also matches type, so 1=="1" is true, 1==="1" is false). Not equals is !=, greater then and less then are standard (> , < >=, <=). You can also just pass a boolean in or an array to see if it is not empty.
let bFlag=true
if(bFlad){
console.log("its true");
}
Loops
As always there are a few ways to action a loop, the 2 I recommend are forEach and for. You have seen the for already.
for(i==0;i<rng.length; i++){
//do something
}
for(counter = start; till counter less then value; step by +1). In the example im starting at 0 (the first item in an array is 0 not 1) and im looping until i is the rng length (number of rows), and each loop im stepping +1 (so 1-- would step backwards, 10++ would increase 10 each loop).
To reference parts of an array you use the [], so array[0] is the first item in the array. Additionally if its a 2 dimensional array (like a table with rows and columns), you can use [][], so array[1][0] is second row, first column.
The other useful loop is the forEach loop, here you don't need to hand the counter/index of the array, you can reference the item. In the below example I have named the item ws, but you can change it for anything (generally I just use item).
workbook.getWorksheets().forEach(ws =>
console.log(ws.getName())
)
Above we are looping of all worksheets in the workbook. Then we are logging the worksheets name.
API
This is where Office Scripts can get more powerful, it can extend beyond the workbook and interact with API's (this can even be other workbooks with the graph api).
async function main(workbook: ExcelScript.Workbook) {
let iRows = 0;
let aTemp=[]=[];
const myHeaders = new Headers({'app-id': '############'});
const myInit = {
method: 'GET',
headers: myHeaders
};
let fetchResult = await fetch('https://dummyapi.io/data/v1/user?limit=10',myInit);
let oData: dataType = await fetchResult.json();
let json: JSONData[] = oData.data;
json.forEach(item =>
aTemp.push([item.id, item.title, item.firstName, item.lastName,item.picture])
);
iRows = json.length;
workbook.getWorksheet('Sheet1').getRange('A2:E' + (iRows+1)).setValues(aTemp);
}
interface JSONData {
id: number,
firstName: string,
lastName: string,
title: string,
picture: string
}
interface dataType = {
data: JSONData[]
}
To get an app id and use code sign up for free at https://dummyapi.io/
So the main change is now we are using async function instead of function Main. After that we are using standard JavaScript await fetch().
const myHeaders = new Headers() - create header, like content type, auth, etc
const myInit() - config call, GET/POST/DELETE/PATCH/PUT and add header
let fetchResult = await fetch() - store response in fetchResult
let oData: dataType = await fetchResult.json(); - wait for resposne, and store as oData (the response as a parent data object so we set oData to dataType)
let json: JSONData[] = oData.data; - set the data array in json variable
json.forEach(item =>aTemp.push([item.id, item.title, item.firstName, item.lastName,item.picture])); - Loop over every row and add to simple 2 dimensional array
One thing to be aware of is API's can't be called if Script is ran by Power Automate, and there is no way to secure you auth credentials.
Additional functions
As I said, Office Scripts are TypeScript/JavaScript, so most JavaScript functions also work, a couple of recommend ones are:
Sort Array
if(sortType=="Number"){
sortArray = data.sort((a, b) => {
if (direct=="asc"){
return a.ID - b.ID;
}else{
return b.ID - a.ID;
}
});
} else{
if (direct == "asc") {
sortArray = data.sort((a, b) => a.ToSort.localeCompare(b.ToSort));
}else{
sortArray = data.sort((a, b) => b.ToSort.localeCompare(a.ToSort));
}
};
Above we are showing the way to sort by Number vs String, and how to do Ascending or Descending (data is input - unsorted array, sortArray is output - sorted array).
Filter Array
let filteredArray=data.filter((item, index) =>
return item.Field > 100
)
Above we are filtering any row where the Field column is greater then 100 (data is input - unfiltered array, filteredArray is output - filtered array).
Regex
Regular expressions allow you to extract (find) strings from other strings, using patterns (so not just an exact match)
let regEx = new RegExp(rgex, flag);
let aMatches = inputString.match(regEx);
if (aMatches) {
console.log("Regex found matches");
}
For more info on regexs I recommend this website https://regex101.com/
Latest comments (0)