Power Automate Office Scripts Basics

title: Power Automate Office Scripts Basics
author: Lukasz Sokolowski

Power Automate Office Scripts Basics

Power Automate Office Scripts Basics Training Materials

Copyright Notice

This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.

Agenda

Single module context

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

Introduction

Office Scripts

Online version of VBA
Built to run in Excel online
- similar to GoogleDocs App Scripts, but based on TypeScript instead of Javascript
- TS is a subset of JS, so we can use JS as well
More here
- https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel

Accessing Scripts

In Excel online (Business version only)

an Automate tab in the ribbon bar
the Ribbon allows 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
- uses selections instead of references
- does show good notes and is a good way to learn

Triggering Scripts in Power Automate

Office Scripts

can be run by adding the Run script action under Excel Online (Business)
More here (connector docs)
- https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/
by default stored on our OneDrive (Documents/Office Scripts folder)
- can be shared with a file by clicking Share (same menu as adding a button)
- this attaches the script to the file, so anyone with the file can run the script
- unfortunately shared scripts can not be called by Power Automate
  - it can only call scripts stored on the connected OneDrive account

IntelliSense

Intellisense

auto complete for code
we type in the function we want
- Office Scripts will try and guess what we're typing by listing all possible options
speeding up our typing
can be used as a reference to find the function we're looking for

Variables, Types and Interfaces

Basic TypeScript knowledge is required

variables are declared with let or const
- need to declare type (must have value set against them)

let sString = "";
let iNumber = 0;
let bFlag = false;

we declare objects like: workbooks, worksheets, images, ranges and more to variables
- make them easier to use and update

function main(workbook: ExcelScript.Workbook) {
    let ws = workbook.getWorksheet("Sheet1");
    ws.setName("test");
}

variables are scoped locally
- declared in the function/loop is scoped to that function/loop only
- can't be read outside of it

Arrays, interfaces

2 options, an empty array and structured one

Empty arrays - for simple arrays with no objects within them
If we need an object - we should use an interface to set the structure

function main(workbook: ExcelScript.Workbooklet){ 
    let aSimple = [];           // [10, 20, 30, 40, 50]
    let aStructured: Schema[];
        /* [ { stringField:"test", numberField:1, booleanField: false }
           , { stringField:"test2", numberField:2, booleanField:true } ] */
}

interface Schema {
    stringField: string,
    numberField: number,
    booleanField: boolean
}

Types vs Interfaces

type is for an object

for structuring an object we're about to add to an array
usually is also called outside of the function
similar to an interface, but less general
- interface - reusable but generic, usually outlives the module
- type - reusable but more specific, usually stays within the module

type dataType = {
    data: JSONData[]
}

If

Fundamental action

Office Scripts leverage TypeScript/JavaScript
Logic is different to Excel
- equals is ==
  - === also matches type - 5 == "5" is true, 5 === "5" is false
- not equals is !=
- greater then and less then are standard ( >, <, >=, <= )
- we can also just pass a boolean in or an array to see if it is not empty
Examples

// full
if( rng[i][0] == 7 ){
    aNewRange.push( rng[i] );
} else {
    console.log( "Not a 7" );
}
// simple
let bFlag = true
if( bFlag ){
    console.log( "works as a charm" );
}
// ternary
invVoided ? 'voided' : 'stays'

Loops

for

for( i = 0; i < rng.length; i++ ){
    //do something
}

for ... in, for ... of

for ( let cell of row ) {
  if ( cell.toString().length > 0 ) {
    emptyRow = false;
  }
}

forEach

workbook.getWorksheets().forEach( ws => console.log( ws.getName() ) )

Get & Set

.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
we get our worksheet, get our range, then we set our range
- that can be a formula (setFormula) or value (setValue)
- can be one cell or a range (setValues)

Example

Copying a filtered list from one sheet to another

// First thought - the best approach would be to filter the excel data, then copy and paste
//// Better is to grab the whole range, filter it, then paste and set the range to the filtered values
// We 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
//// - we should avoid placing them in loops where ever possible
function main(workbook: ExcelScript.Workbook) {
    let ws = workbook.getWorksheet( "summary" );
    let i = 0;
    let aNewRange = [];

    let rng = ws.getUsedRange().getValues();  // gets values from range
    aNewRange.push( rng[0] );                 // adds header row

    for( i == 0; i < rng.length; i++ ){       // loops over rows in the array
      if( rng[i][0]==3 ){                     // if condition
        aNewRange.push( rng[i] );             // adds row to array
      }
    }
    // sets array to range
    workbook.getWorksheet( "Sheet2" )
        .getRange( "A1:e"+aNewRange.length )
        .setValues( aNewRange );
}

Basic Excel Actions

We can interact with the Excel workbook

let ws= workbook.addWorksheet("test");                          // Add worksheet called 'test'
ws.delete();                                                    // Delete worksheet

// Insert chart on sheet 'selectedSheet'
let chartName = selectedSheet.addChart(ExcelScript.ChartType.pie, selectedSheet.getRange("A1:C15")); 

// Add a new pivot table on 'sheet3'
let newPivotTable = workbook.addPivotTable("PivotTableName", ws.getRange("A1:C15"), wsPivot.getRange("A2:C16"))
newPivotTable.refresh();                                        // Refresh newPivotTable

// Apply values filter of 1 to range
ws.getAutoFilter().apply(ws.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: ["1"] });

// Add thin border to bottom of range
ws.getRange("A1:C4").getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.thin);
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 'A1:C4'

// Insert column F:F, move existing cells right
ws.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);
ws.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left);   // Delete column F:F
ws.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down);   // Insert at range 3:3, move existing cells down
ws.getRange("3:3").delete(ExcelScript.DeleteShiftDirection.up);     // Delete row 3:3

Send and Return Data

Additional functions

Office Scripts are TypeScript/JavaScript

most of JavaScript functions are available
few examples
- .sort() - Sort Array
- .filter() - Filter Array
- .includes() - Find in Array
  - Example - Includes
- .match() - Regexp Patterns
  - to test our regexps - https://regex101.com/

Power Automate Office Scripts Basics

Contents