Power Automate Office Scripts Basics

From Training Material
Jump to navigation Jump to search


<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

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

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
  • Office Scripts are only available for Excel (no support in Outlook, Word, Access, etc)
    • for both - Online and Windows versions of excel
  • More here

Intro Con't

Office Scripts VS VBA macros
Functionality Office Scripts VBA macros
automating solutions through an easy-to-use action recorder 1 1
allow edits of above recordings 1 1
small programs in Excel by not coders 1 1
developed for desktop solutions 0 1
designed for secure, cross-platform, cloud-based solutions 1 0
can interact with a user's desktop 0 1
connect with technologies, such as COM and OLE 0 1
have convenient way to call out to the internet 1 0
use a universal runtime for JavaScript 1 0
can make calls to web services 1 0

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

Office Scripts become Power Automate Scripts

Example

Passing an array that we want to sort

  • also including if the sort column is a Number or String
  • if ascending or descending

Additional functions

Office Scripts are TypeScript/JavaScript

Troubleshooting

Retrieved from "https://training-course-material.com/index.php?title=Power_Automate_Office_Scripts_Basics&oldid=88454"