Power Automate Office Scripts Basics

<slideshow style="nobleprog" headingmark="⌘" incmark="…" scaled="true" font="Trebuchet MS" >

title

Power Automate Office Scripts Basics

author

Lukasz Sokolowski

</slideshow>

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.

Introduction

Single module context

• 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

Intro Con't

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

// comment
let a: string = 'code'

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[]
}

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
  • todo