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

Copyright © 2004-2025 by NobleProg Limited All rights reserved.

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

Additional functions

Office Scripts are TypeScript/JavaScript

• most of JavaScript functions are available
• few examples:
  • .sort() - Sort Array
    • Example - Sort
  • .filter() - Filter Array
    • Examples - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter
  • .includes() - Find in Array
    • Example - Includes
  • .matchAll() - Search in String
    • Example - matchAll
  • .match(), .search(), .test(), .replace(), .exec() - Regexp Patterns
    • Example - Test
    • References - https://www.w3schools.com/jsref/jsref_obj_regexp.asp
    • to test our regexps - https://regex101.com/
• more functions/constructs here - https://www.w3schools.com/js/js_es6.asp