Creating your first UI Flow

In this article we will look how to simply copy data from a cell in excel into a word document. It is short an simple, but nicely highlights the concept and the ability to automate moving data from one place to another.

Introduction
Robot Process Automation (RPA) and Microsoft’s new UI Flow capability are allowing Microsoft 365 users to automate processes which were previously though to be out of reach.

To create UI Flows you must have access to Microsoft 365 and a Power Automate license. If you don’t then you can always sign up for the free Office 365 developer account and you can have your own me.sharepoint.com tenant allocated for learning and exploration.

Installing Power Automate Desktop
From within Power Automate you can find the UI Flows under the New icon at the top

Select Power Automate Desktop

Select a name and Launch App – If you do not have it installed you will be prompted to download and install it

A simple test excel sheet
I created an excel file locally and left a simple message inside the message is in cell d4. The file is located at C:\Users\mroden\Desktop\temp\RPATest\RPATest.xslx

Power Automate Desktop
Back in the desktop tool I am going to start to build the robot. There are many different ways to achieve the same thing. being efficient is one thing that is important for robots, but that’s for another day. These are the steps we want to automate the moving of text in an excel cell to a word document.

  • Open the Excel file
  • Get the current Sheet
  • Go to Cell D4
  • Copy the Cell
  • Open Word
  • Execute a Paste.

So let’s start. Within Power Automate Desktop we are able to find the open to “Launch Excel” and from the resulting dialog select the path to the excel file we want to open.

We then add steps to Read from Excel worksheet – selecting and copying cell D4.

NOTE – the variable produced is “ExcelData” we will use this later once word is opened.

We are going to be good robot writers and now close the excel sheet again so that we don’t leave copies of excel all over the place.

Next we will open word and paste the ExcelData variable into the new book. We will do this by running a program and opening a test word document (blank) file – RPATest.docx.

Once open we then have to click into the window to make it active.

We do this using a Click UI action. We have to have the word document first open so that we can direct the Desktop app to pick where to click.

We select to add a new UI element and then the mouse highlights different aspects of the applications open. We mouse over the Word document and select CTRL-Click to record

The final step is to then send the text of the copied Excel cell back to the Word document. We can do this using SendKeys, and then selecting the ExcelData variable to be sent.

The final bot is finished and while it is simple it demonstrates how we can integrate multiple applications without having to write any code.

Running the robot…..we get this…. 🙂 You will see from the video that the variables are being stored and shown on the screen.

Conclusion
In this article we have seen how to install Power Automate Desktop and create out first simple bot. It’s takes a little time to get used to the interface but is pretty simple and worth having a go at yourself.

NOTE – This article is written in October 2020 and the content is likely to change over time as the platform evolves. It is written to help people wanting to get started with the preview version of Power Automate Desktop

Power Automate – UI flows

Power Automate acts like the glue holding together the interconnected web of Microsoft 365 and the broader partner eco-system. With the ability to trigger a “Flow” from hundreds of events on the platform, to perform thousands of potential actions, Power Automate is a compelling investment for companies looking seriously at no/low-code application integration.

Microsoft have extended Power Automate beyond the platform and onto the desktop with the addition of the UI Flows capability. Robot Process Automation (RPA) is the new kid on the block for companies trying to squeeze even more automation and productivity out of what’s left of their business processes.

For companies who are automating their business processes on the Microsoft 365 platform, adding RPA to the mix makes a lot of sense. Automating modern API based platforms to work together has been the theme of cloud platform delivery for years now. But it has never been able to achieve full integration with the manual repetitive processes which can consume many companies.

UI Flows are only in preview, but already Microsoft has released a significant capability for building Robots, the Power Automate Desktop. Using this tool anyone can start to build automation into their daily business processes.

My hope it to be able to start to write about Power Automate Desktop, learn about it’s features and create some compelling examples for our customers so that when it is finally released we will be able to provide the quality consulting that I know our customers have come to love about working with PSC………

Robot Process Automation

Robot Process Automation (RPA) as a technology group enables the automation of repetitive manual human tasks.

Imagine how much easier Excel Macros make an accounting process whereby a multi-stage process can be automated with the push of a button. Now imagine a multi-stage process where a human has to take data from Excel, or worse a paper document, and move it to another application such as a web site. RPA can do that for you.

– RPA can do that –

In an idea world where every system has a beautifully architected API integration layer, every system would be able to easily and quickly talk to every other system. In reality this is far from the case and a sleek and effective integration is either impossible or prohibitively expensive. RPA can do that for you.

Examples
Many companies have to go through a process of converting paper based data to electronic format. Whether it is incoming invoices, logistics documentation or even waste disposal profiles, there are thousands of people who’s role is to type data from paper into a website.

Through a relatively simple process of connecting the dots, a Robot can be configured to be responsible for:

  • Collecting the data from an Automated OCR process
  • Open a web browser and log into the ERP system
  • Map the data from the form to fields on a web form
  • Submit the web form

Reducing Risk
RPA Robots aren’t error prone. Robots will do exactly what is expected of them. In an business, mistakes cost money and in especially heavily regulated environments the consequences of putting the wrong value in fields can be significant.

Increasing productivity for everyone
Robots are also quicker than people, reducing the time taken for process completion. This then also frees up the people to be able to work in other areas of a business and bring their experience to bear in other ways.

There are many vendors in the RPA space, and Microsoft has entered the foray by extending their Microsoft 365 Power Automate platform to include UI-Flows. https://flow.microsoft.com/en-us/ui-flows/ I am going to write about Power Automate and ui-flows specifically as I believe it will be a significant game changer for our client in the future.

Using Chrome dev tools to get SharePoint API calls in JSON

When working with Power Automate I wanted an easy way to get the JSON version of an API call.

When looking at a normal API link in a webpage you get XML

https://xomino.sharepoint.com/sites/SPFest2019/_api/web/lists/getbytitle('MarkyList')/items

To be able to see the output in JSON we can use Chrome Dev Tools to call the same page and pass in the apporpriate header to turn it into JSON

fetch(location.href, {
  method: 'GET',
  headers: {
    'Accept': "application/json;odata=verbose"
  }
})
.then(res => res.text())
.then(console.log)

And from this you can get the output quickly from the console

Removing commas from number fields in SharePoint online view items

In this article I will show (and reference) a neat piece of formatting code I found to change a year of 2020 from 2,020 to 2020 in a list view

The Problem

Simply put, if you have a number in a list in SharePoint it will add number formatting by default. A view with a Year number value of 2020 will display as 2,020

The solution

Using Colulumn formatting you can superceed the default formatting by replacing the value displayed in the field with a text value of the same thing

I found the solution on stackexchange – https://sharepoint.stackexchange.com/a/269817/48559

Select the option to format the column

Add the following into the formatting box (advanced mode)

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField > 0,'', '')"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

And there you have it

Speaking: The Microsoft Graph – The API to rule all APIs

I am speaking this coming week at the #NWCJS meetup @PSCGroup

The Microsoft Graph – The API to rule all APIs

The Microsoft Graph sits on top of the Office365 SAAS platform. Over many years Microsoft has been building up the capabilities of “The Graph” to make it insanely powerful and allow enterprises to programmatically manage their entire platform.

From the simplest tasks of sending an email, to accessing a table in excel from an API! From accessing the security logs of the SAAS platform to the fact that they made all this available to play with through Postman. There is nothing not to love about the Microsoft Graph and what it means to developers. Did I mention the Delta query capability for tracking changes since you last asked? Or the batching capability? 😲

Come and see how the Graph is arguably the API to rule all APIs.

Changing your SharePoint online root site to a communication site

In this article we will look at how to change the root site of your SharePoint online tenant.

Introduction

By default your SharePoint Online default site is unlikely to be the site you want users to go to.

Invoke-SPOSiteSwap

Using the Invoke-SPOSiteSwap command inside of the SharePoint Online Management Shell we are able ot change the root site.

Download and install the management shell

And then use your version of the following script to change your tenant root site. You will have to have admin access to the tenant to be able to do this

$targetSite = “https://yourTenant.sharepoint.com”
$soureSite = “https://yourTenant.sharepoint.com/sites/yourSite”
$archiveSite = “https://yourTenant.sharepoint.com/sites/archivedRoot”
Connect-SPOService -Url https://yourTenant-admin.sharepoint.com
Invoke-SPOSiteSwap -SourceUrl $soureSite -TargetUrl $targetSite -ArchiveUrl $archiveSite

Basic Introduction: Automatic Testing of SharePoint online using Cypress.io

In this article we will look at the Automated Testing tool Cypress.io and look at the basic requirements for being able to set it up to test against a SharePoint online list. There are some interesting nuances to look at but the basic pronciples hold true.

Introduction

Cypress.io is a front end automated testing tool which has been around for a couple of years but has received significant interest from the open source community. It is an electron based testing tool with significant advantages over using the long term front end testing defacto tool Selenium. There are some disadvantages as well (being Chrome only right now) but as modern web browers get closer and closer to parity, that is less of an issue than say 5 years ago. After talking to my react development friends at the Northwest JavaScript meetup I resolved to try and make it work.

There are other recommendations for automated testing including a great article from Elio Struyf which uses a combination of puppeteer and jest – but again I wanted to try Cypress.io. It was challenging !

Update: Sheer coincidence, Elio also published an article on Cypress today!

Authentication and SharePoint online

The Cypress.io engine is based on electron – which means it runs in node. This is very advantageous, in that it has the ability to use open source npm libraries as part of its run time. In this case it allowed me to use the node-sp-auth to be able to generate the necessary headers to access a SharePoint Online site as an authenticated user. For the sake of this example I had the username and password in the config – you probably want to look at encrypting that or soemthing 😉

Cypress tasks

Cypress.io has the ability to create custom tasks which will execute some node code. So I created a custom login capability using the node-sp-auth library. This code should be added to the index.js file in the root of the cypress code.

const spauth = require("node-sp-auth");

let getLogin = async () => {
  const username = "me@mydomain.org";
  const password = "********";
  const pageUrl = "https://mydomain.sharepoint.com/sites/HelloWorld";

  // Connect to SharePoint
  const data = await spauth.getAuth(pageUrl, {
    username: username,
    password: password
  });
  return data;
};

module.exports = (on, config) => {
  // `on` is used to hook into various events Cypress emits
  // `config` is the resolved Cypress config
  on("task", {
    // deconstruct the individual properties
    getLogin() {
      return getLogin();
    }
  });
};

 

Running the test

This code can then be referenced in the Cypress.io code to create the login headers necessary for authentication, before each of the tests are run. The waits are in here because I have to make sure the DOM is ready. It is a little clunky and I need to figure out how to do it better………

describe("Sample SharePoint List test", function() {
  beforeEach(() => {
    cy.task("getLogin").then(token => {
      cy.visit({
        method: "GET",
        url: "https://mydomain.sharepoint.com/sites/HelloWorld/Lists/CyList",
        headers: token.headers
      });
    });

    cy.wait(2000)
      .get(".od-TopBar-item")
      .wait(1000)
      .find('[name="New"]')
      .wait(1000)
      .click();
  });

  it("Should have a title of Hello World", function() {
    cy.title().should("contain", "Hello World");
  });

  it("Should have a validated Title value", function() {
    cy.get('[aria-label="New item form panel"]')
      .find(".od-TopBar-item")
      .wait(1000)
      .find('[name = "Save"]')
      .wait(1000)
      .click();
    cy.get('[data-automation-id="error-message"]').should(
      "contain",
      "You can't leave this blank."
    );
    cy.get('[aria-label="New item form panel"]')
      .find(".od-TopBar-item")
      .find('[name = "Cancel"]')
      .click();
  });

  it("Should submit the form with a minimum Title field", function() {
    cy.viewport(550, 750);
    cy.get(".ReactFieldEditor")
      .eq(0)
      .type("sample text");
    cy.get('[aria-label="New item form panel"]')
      .find(".od-TopBar-item")
      .find('[name = "Save"]')
      .click();
  });
});

Here’s how it looks

Running the test – we can see that cypress runs through each of the tests, waiting for the DOM to be ready…..(see below) and ultimately automated a Funcational test of the list. One of the REALLY cool things about Cypress is the ability to move back through the test and see what was executed at the time of the test. This allows the web developer to see the DOM state and everything, should their test fail!!

 

What I found about SharePoint

The modern SharePoint Framework, and more specifically the React JavaScript library on which it is based, uses Web Components to render the DOM. What this means is that the webpage itself renders aynchronously and at times under no control of my own, will re-renter itself as the page decides. This means that during the automated test, I came across multiple instances of where Cypress failed because the DOM elements were no longer attached. This is going to need some work on my part to figure this out……

Conclusion

You can use Cypress.io to functional test SharePoint online and SPFx – but there is clearly a lot more to learn 🙂