david wyatt

Posted on Nov 1, 2022 • Edited on Nov 21, 2023

How to Master Power Automate Scripts

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).

https://learn.microsoft.com

So this may not sound like a Power Automate script, but what you can do is call Office Scripts from Power Automate. Additionally you don't need to actually use the Excel side of the action, so they can extend the functionality of Power Automate, not just around missing functionality with Excel. If you want to know about Office Scripts outside of Power Automate I have done a sister article purely for Excel. There are alot of overlaps with this article, but the additional features/steps required when running in Excel Online.

In this article I'm going to cover:

Accessing Scripts
Triggering Scripts in Power Automate
Intellisense
Variables, Types and Interfaces
Get & Set
Basic Excel Actions
If
Loop
Send and Return Data
Additional functions

Accessing Scripts

In Excel online you will see and 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 a good way to learn.

Triggering Scripts

Office Scripts can be run by adding the 'Run script' action under Excel Online (Business). More info on the connector can be found here

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. Unfortunately Power Automate can only call scripts stored on the connected OneDrive account, so shared scripts can not be called.

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 a head. 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 can't 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 for an object, so is similar to an interface but is for structuring an object you are about to add to an array. It is also called outside of the function,



type dataType = {
    data: JSONData[]
}

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 salue (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 its 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.

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 loggin the worksheets name.

Send and Return Data

This is where we can really unlock Office Scripts and turn them into Power Automate Scripts. We can pass multiple parameters in and one out (though this can be an object or array, so can be multiple parameters too). There are couple of limits to the connector, like max parameter size being 5Mb, read here for more info.

In the below example we are going to pass an array that we want to sort, so we are also including if the sort column is a number or String, and it's ascending or descending.



function main(workbook: ExcelScript.Workbook, direct:string, sortType: string, data: schema[]) {
  let sortArray: schema[] = [];

  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));
    }
  };
  let iRowCount=sortArray.length;

  let response: responseType = {
    data: sortArray,
    total:iRowCount
  };
  return sortArray;
}

interface schema {
  Date: string;
  ToSort: string;
  ID: number;
}

interface responseType = {
  data: schema[],
  total: number
}

function main(workbook: ExcelScript.Workbook, direct:string, sortType: string, data: schema[]) { - we add the inputs in the function
direct:string - input parameter direct
sortType: string - input parameter sortType
data: schema[] - input parameter array data with schema structure

This will then create the inputs in the Run Script action.

By default it asks for each individual row of the array, but you can switch to single input, where you can pass a whole array or type in an JSON array to pass.

To pass the data back we use the return function.

let response: responseType = {} - the object to send back using responseType structure
return sortArray; - return sortArray object to Power Automate.

This will then create a response from the Run Script action, with the same structure as the object passed.

You can just pass a string or number, by using



return "Your string or number or boolean to pass back";

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/

And here's the real power in Office Scripts, and why I think of them as Power Automate Scripts, you have almost fully fledged TypeScript to code with, and you don't need to actually use Excel. I always think not having a Regex expression/action in Power Automate is a big miss, but you can pass the string and Regex, run the script and return the array back to Power Automate. You don't use Excel, just the code in the script.