Return data from office scripts and integrate systems with Power Automate

jean.dosher@gmail.com

December 19, 2023

Business case of systems integration with Power Automate and a help of office scripts to make it error proof, effective and fancy.

Context

Let’s suppose that you need to integrate a legacy system with your SharePoint List and the only source of data is an excel spreadsheet (😖). Power Automate handles this very well, but unfortunately there’s room for issues of all kinds.

If the file doesn’t have data structure consistency like columns out of order, data need to be formatted as a table or even wrong columns names. In all these cases (and there´s more), the automation execution breaks.

Another circumstance for this example is that we need to obtain certain data inside a cell, like an ID correlated to SharePoint item ID -in this condition, we will verify if the item already exists and if there’s an update in that item.

All these can be treated/manipulated with office scripts! In this article I’ll focus on a short office script explanation, solutions for the presented case and script’s integration with Power Automate.

I’ll show a bit of the Flow’s Scopes, but there’s some handling to get more than 256 excel items of a spreadsheet and the mapping after that (maybe someday I’ll post the full solution), but for now, you will have to do your homework for that!

Those are the SharePoint list that will receive data and the spreadsheet that will be the source (in this case without formatting, but there’s a Flow action that break the table formatting)

Spreadsheet of the import (without formatting)

Office Scripts

Office Scripts are a way to automate tasks in Excel. They allow you to record, edit, and perform custom automation actions in your spreadsheets. You can use Office Scripts to automate repetitive tasks, transform data, create reports, etc. The scripts are written in TypeScript, a JavaScript-based programming language (you will need some familiarity with it) that adds features like static types and classes. You can use the code editor in Excel to create, edit, and run Office scripts. Basically, Office scripts can access and manipulate Excel objects such as workbooks, worksheets, tables, charts, and more.

Implementation

Let’s divide to conquer! Before any file manipulation, let Flow check if the file is in the correct expected extension (.xlsx), and then solve each issue with a separate action:

Break table formatting and enforce its formatting

Here is assumed that sometimes user makes mistakes and don’t keep a pattern in table formatting:

For that, we’ll use our first simple “Run Script” action:

This function will check if there’s a table in the spreadsheet and convert it do range 0 (removing the table)

function main(workbook: ExcelScript.Workbook) {

  //let selectedSheet = workbook.getActiveWorksheet();       

  let tabela = workbook.getTables()[0];

  // Check if the table is not empty
  if (tabela && tabela.getRange().getValues().length > 1) {
    tabela.convertToRange();

  }
}

After cleaning the table formatting, you’ll need to reformat it again using the native Excel Action in Power Automate called “Create Table” and obtain that table with “Get tables action” (to be used later in “List rows” action):

“Create Table action” to enforce the expected table formatting

Get ID with Script

Here’s where the magic happens, in this case a better explanation is needed. Our source file will also be responsible for updating the corresponding item in the SharePoint List, here’s the trick; the item ID in the list (SharePoint) is in the Ship Description field -ok, you’re asking yourself why this is not in a separate column? The answer is, because of the legacy system that is generating the source file 😵!

With Power Automate expressions you can try to manipulate the string to get what’s inside the bracket, but if there are two pairs of brackets in random positions? I’m sure you can write a huge expression to handle that, but if need to debug it?

The smartest and secure way is using office script with regex, this one is a bit handy:

Regex

Regex, short for regular expression, is a sequence of characters that defines a search pattern for text. Regex can be used to find, replace, or validate text based on some rules.

Regex is a subject that requires an article itself, but fortunately chat GPT can be extremely helpful writing regex expressions!

Implementation:

script:

function main(workbook: ExcelScript.Workbook) {

    // Get active Worksheet
    let currentWorksheet = workbook.getActiveWorksheet();
  
    // Get used range 
    let interval = currentWorksheet.getUsedRange().getLastRow().getRowIndex() + 1;
  
    const definedRange = `I2:I${interval}`;
    const definedRangeID = `J2:J${interval}`;
  
    // Get range from the defined range.
    let range = currentWorksheet.getRange(definedRange);
    let rangeID = currentWorksheet.getRange(definedRangeID);
  
    // Get the size boundaries of the range.
    let rows = range.getRowCount();
    let cols = range.getColumnCount();
  
    // Regex for 3 cases [1], [ 1], [1 ]
    let regex = /\[(\d+)\]/;
    let regexFinal = new RegExp("\\[(\\d+ )\\]", "g");
    let regexFront = new RegExp("\\[(\\s\\d+)\\]", "g");
  
    // Iterate over the range.
    for (let row = 0; row < rows; row++) {
      for (let col = 0; col < cols; col++) {
  
        let cellValue = range.getCell(row, col).getValue();
        let cellValueID = rangeID.getCell(row, col).getValue();
  
        console.log(cellValue);
  
        function getNumberInsideBrackets(text: string) {
  
          // Run regex and get non-null result
          let result1 = text.match(regex);
          let result2 = regexFinal.exec(cellValue);
          let result3 = regexFront.exec(cellValue);
          let result = result1 ?? result2 ?? result3;
  
          // Check if ID value exists; if not, write non-null regex in the column
          if (result != null) {
            if (cellValueID != null) {
              rangeID.getCell(row, col).setValue(result[1]);
              console.log(`Result: "${result[1]}"`);
            }
          }
        }
  
        getNumberInsideBrackets(cellValue);
      }
    }
}

Running this script will populate the ID column of the spreadsheet:

Obtain columns names

To obtain the columns names to compare it with the expected columns; in the first versions of the Flow I used a “List Rows” action, and with that treat the first line as a string and split it to get the columns names, but depending of what we have in the columns this splitting can be difficult (needing a huge expression with a lot of ifs and replaces) and lead to failure

Example of the first returned item from “List Rows” action

Once more, using an office script to get an array of the headers:

And compare it with the expected array of columns:

Now, this script has a particularity, we need an output from it (the array). To return data from script, you need to add the return type syntax to the main function:

function main(workbook: ExcelScript.Workbook): string[] {

  function getColumnNames(): string[] {

    // Get active worksheet
    let currentWorksheet = workbook.getActiveWorksheet();

    // Get the used range of the worksheet
    let usedRange = currentWorksheet.getUsedRange();
    let lastColumnIndex = (currentWorksheet.getUsedRange().getLastColumn().getColumnIndex())
    //console.log(lastColumnIndex);

    // Get the column names
    let columnNames = usedRange.getResizedRange(1, usedRange.getColumnCount()).getValues();

    let columnList: string[] = [];

    // Log each column name
    for (let i = 0; i < lastColumnIndex + 1; i++) {
      columnList.push(String(columnNames[0][i]));
    }

    return columnList;
  }

  // Execute the function and return
  return getColumnNames();
}

With all that in hand you can check for wrong columns names, columns order or absence of columns and send feedback to user that uploaded the file to the SharePoint library, reducing any possibility of errors!

Conclusion

In this particular presented case, the office scripts were the tool that solved the problem, sometimes you will be tempted to use it to simple Tasks; but there’s a tradeoff here that need to be weight (how much time you will spend writing and testing your code? There’s a native action that does the same?)

Office Scripts offers even more for a wide range of challenges: you can pass parameters for the main script function (this is awesome) or even make external API calls, here’s where your imagination goes and you really need to take a look at the documentation to expand your knowledge of this tool!