How to use Excel Office Scripts in Power Automate

Office Scripts

Office Scripts in Excel is a new feature in Office365 that allows you to Automate your day-to-day tasks using power automate

• You can record your Excel actions with the Action Recorder, which creates a script. 
• You can also create and edit scripts with the Code Editor. Your scripts can then be shared across your organization so your coworkers can also automate their workflows.
• TypeScript is a superset of JavaScript.
• Every script runs the code in a single main method.

Connecting Office Scripts to Power Automate

Connectors are the bridges between Power Automate and applications. The Excel Online (Business) connector gives your flows access to Excel workbooks. The "Run script" action lets you call any Office Script accessible through the selected workbook. You can also give your scripts input parameters so data can be provided by the flow, or have your script return information for later steps in the flow.

The Excel Online (Business) connector has been a valuable part of the Power Automate ecosystem for some time now. It has a wide variety of applications

Prerequisites

In order to run Office Scripts via Power Automate, the following must be true:

• Your organization must have enabled Office Scripts via the switch in the Office Admin Portal
• Your organization must have enabled Power Automate and the Excel Online (Business) connector

Create an Office Script

1. Go to the Automate tab and select All Scripts.
2. Select New Script.
3. Create script
4. Rename the script to . Press the script name to change it.
5. Save the script by pressing Save Script.

Example

Create an automated workflow with Power Automate

I have created a flow, to create separate files based on the excel header column.

Calling the created office script in power automate.

Please find below some handy scripts.

Format a Table

Below script used to format a table with excel data

function main(workbook: ExcelScript.Workbook, sheetName: string) 

{ 

  tableRowRange: workbook.getWorksheet(sheetName)   

  workbook.getWorksheet(sheetName).addTable(workbook.getWorksheet(sheetName).getRange('A1').getSurroundingRegion(), true);  

  workbook.setIsDirty(true) 

}

Get Column Names

Below script used to get excel header column details

function main(workbook: ExcelScript.Workbook, rangeAddress: string): string {

  let sh = workbook.getFirstWorksheet();

  let rg = sh.getRange(rangeAddress)

  return JSON.stringify(rg.getValues());

} 

Format a table without a header columns

function main(workbook: ExcelScript.Workbook, sheetName: string, ) 

{ 

  tableRowRange: workbook.getWorksheet(sheetName)  

 workbook.getWorksheet(sheetName).addTable(workbook.getWorksheet(sheetName).getRange('A1:AY70'), false); 

// 'A1:AY70' refers to columnrow

  workbook.setIsDirty(true) 

}

Delete empty row 

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let table = selectedSheet.getTables()[0];
  let tableDataRange = table.getRangeBetweenHeaderAndTotal();
  let dataValues = tableDataRange.getValues();

  let removed = 0;
  // Important that you go from bottom to top to account for removing correct rows. 
  for (let i = dataValues.length-1; i >=0 ; i--) {
    let [event, date] = dataValues[i];
    if (event === '' || date === '') {
      // tableDataRange.getRow(i).getFormat().getFill().setColor('Yellow');      
      table.deleteRowsAt(i);
      removed++;
    }
  }
  console.log(`Removed ${removed} rows.`)
}

https://powerusers.microsoft.com/t5/General-Power-Automate/Deleting-mostly-empty-rows-from-an-Excel-table/td-p/747378