In order to find a flow ID (which you’ll need to export the logs, using the next query), you’ll need to dissect the URL. Navigate to the desired flow on the web version of Power Automate and retrieve the URL. This is what you’d like to navigate to, to get the URL break down below:
From the above, you would like to copy the portion that would depict the Flow ID of the flow that you are looking at. Then, execute the following command in PowerShell, replacing <FlowID> with the ID you’ve retrieved:
Open Power BI Desktop > Get Data > txt or csv. Then select the FlowExport.csv that was just generated from PowerShell. Then hit Transform Data and apply the following steps:
Remove the top row (which contains TYPE System.Management.Automation.PSCustomObject)
Use First Row as Headers
Change StartTime column to Date/Time
Split Internal by semi-colon, for each occurrence of the delimiter
Remove all Internal columns EXCEPT Internal.2 (which should start with ‘id=/’)
This post will assist in automating the creation of projects, repositories and work items in Azure DevOps through UiPath by using the Azure DevOps API.
There are multiple Azure DevOps libraries available on the UiPath Marketplace that make the API calls for you but the aim of this post is to enable developers through understanding how the Azure DevOps API works so that they can make calls that other existing libraries might not cater for.
Login to Azure DevOps, which you can navigate to from here.
In the top right hand corner of the screen, under User Settings you can access the Personal access tokens option.
Azure DevOps Personal Access Token Generation
Select New Token:
New Personal Access Token
Give your access token a name, select an organisation if you’d like to only grant permission to a specific organisation and set the expiration date of the token. Ensure that you update the personal access token once it has expired.
New Azure DevOps Personal Access Token
Once you’ve clicked Create, copy the personal access token somewhere safe as we will need it when making the API call.
Create API Body Templates
The templates may be stored in a ‘Templates‘ folder within the UiPath solution. We will need two templates, one for projects and one for repositories. These templates hold the JSON template that is needed for the API request.
The ‘new_project_template.json‘ file should contain the following:
In UiPath we will need to replace ‘<comment>‘, ‘<story_id>‘ and ‘<story_state>‘.
UiPath ‘Create Project’ Workflow
Create a folder in the UiPath solution folder named Projects and within the folder, create a ‘CreateProject.xaml‘ workflow:
Let’s start by adding a ‘Read Text File‘ activity and assigning a few variables:
The NewProjectURL above is assigned the value of “https://dev.azure.com/” + Organisation + “/_apis/projects?api-version=6.0”. The Organisation variable should correspond to your Organisation name in Azure DevOps.
Next, add a Multiple Assign activity to replace the project name and project description placeholder with the appropriate names:
Make sure that the UiPath.Web.Activities library is installed before trying to add the HTTP Request Activity:
The properties of the HTTP Request activity should include the following:
Only the Personal Access Token is used for authentication as a password when using Basic Authentication meaning that the username can be assigned to literally anything and it will still authenticate, as long as the personal access token is valid.
Add the following added as headers:
Now add a ‘Deserialise JSON’ activity:
The ‘Create Project’ sequence should look like this:
UiPath ‘Create Repository’ Workflow
Create a folder in the UiPath solution folder named Repositories and within the folder, create a ‘CreateRepository.xaml‘ workflow:
Let’s start by adding a ‘Read Text File‘ activity and assigning a few variables:
The NewRepositoryURL above is assigned the value of “https://dev.azure.com/” + Organisation + “/” + ProjectID + “/_apis/git/repositories?api-version=6.0”. The Organisation variable should correspond to your Organisation name in Azure DevOps and the Project ID should correspond to the ID of the project in Azure DevOps.
Next, add a Multiple Assign activity to replace the repository name and project ID placeholder with the appropriate names:
Make sure that the UiPath.Web.Activities library is installed before trying to add the HTTP Request Activity:
The properties of the HTTP Request activity should include the following:
Only the Personal Access Token is used for authentication as a password when using Basic Authentication meaning that the username can be assigned to literally anything and it will still authenticate, as long as the personal access token is valid.
Add the following added as headers:
Now add a ‘Deserialise JSON’ activity:
The ‘Create Repository’ sequence should look like this:
The process of creating the Work Items workflows is very similar to the two mentioned above. Therefore, I’ll go through the next two workflows in a little less detail.
UiPath ‘Create Work Item’ Workflow
Create a folder in the UiPath solution folder named Work Items and within the folder, create a ‘CreateWorkItem.xaml‘ workflow:
Let’s start by adding a ‘Read Text File‘ activity and assigning a few variables:
The NewWorkItemURL above is assigned the value of “https://dev.azure.com/” + Organisation + “/” + ProjectID + “/_apis/wit/workitems/$” + Type + “?api-version=6.0”. The Organisation variable should correspond to your Organisation name in Azure DevOps and the Project ID should correspond to the ID of the project in Azure DevOps.
Next, add a Multiple Assign activity to replace the story name, story state and project name placeholder with the appropriate names:
Similar to the Create Project and Create Repository sections above, add a HTTP request activity with the following properties:
Please note that the body format is application/json-patch+json.
Only the Personal Access Token is used for authentication as a password when using Basic Authentication meaning that the username can be assigned to literally anything and it will still authenticate, as long as the personal access token is valid.
Add the following added as headers:
Now add a ‘Deserialise JSON’ activity:
The ‘Create Work Items’ sequence should look like this:
UiPath ‘Link Work Item’ Workflow
Create a workflow named folder ‘LinkWorkItem.xaml‘ in the folder named Work Items. and add a ‘Read Text File‘ activity and assigning a few variables:
The LinkWorkItemURL above is assigned the value of “https://dev.azure.com/” + Organisation + “/” + ProjectID + “/_apis/wit/workitems/” + TaskID + “?api-version=5.0”. The Organisation variable should correspond to your Organisation name in Azure DevOps and the Project ID should correspond to the ID of the project in Azure DevOps. The Task ID should be the ID of the Task you would like to link to the story.
Next, add a Multiple Assign activity to replace the story name, story state and organisation name placeholder with the appropriate names:
Similar to the Create Project and Create Repository sections above, add a HTTP request activity with the following properties:
Please note that the body format is application/json-patch+json.
Only the Personal Access Token is used for authentication as a password when using Basic Authentication meaning that the username can be assigned to literally anything and it will still authenticate, as long as the personal access token is valid.
Add the following added as headers:
Now add a ‘Deserialise JSON’ activity:
The ‘Link Work Items’ sequence should look like this:
This post will assist in understanding what SQL user-defined table types (UDTTs) are and how they can be used within UiPath as a part of a Data Exchange Architecture (DEA). Following explanations of UDTTs and DEA approaches, we will do a practical implementation walkthrough.
User-Defined Table Types (UDTTs)
User-defined table types are table schema definitions that may be created by users in order to store temporary data. In essence, this means that you can send a table through to a stored procedure as a parameter, the same way you would an integer or string value, without having to write the data to a table first. The benefit of this is that you don’t need to store data on a permanent medium in order to perform SQL queries on the data (like joins or upsert merges, for example).
Data Exchange Architecture (DEA)
A Data Exchange Architecture component, in this context, refers to the use of Extract Transform Load (ETL) or Extract Load Transform (ELT) in order to maintain a constant and usable state of reliable data at any point in time. This can be implemented in many different ways, depending on the existing data architecture within your environment.
One way of implementing ETL would be to create three database schemas, each responsible for its own phase of ETL:
Landing: (Extract)
All raw data is landed in the relevant tables within this schema. No changes are to be made to this data (possibly done through a truncate and load process)
Staging: (Transform)
Data from landing tables are used to populate staging tables and data is cleaned up (possibly done either using truncate and load or using upsert merge statements)
Loading: (Load)
Data from staging tables are transformed according to the business rules and populated into the tables within this schema (usually done using upsert merge statements)
Another common approach is to combine Landing and Staging into one schema (usually Staging). The idea behind this approach is that the Staging tables should be the intermediate storage between data sources and data targets.
Use-Case
Let’s work through a practical example together. We have two datasets: an Excel file (which is a few hunderd records large) containing Azure Devops project informationand a SQL table (which is a few thousand records large) containing additional project information, like who the project manager is, what the project budget is, etc. For the sake of context, not all of the projects are recorded in the Azure Devops dataset as not all projects are currently active. The SQL table contains historical project data too.
We DO want to:
We would like to join the data that exists within in Excel with the data in SQL in order to enrich our dataset needed in our UiPath process.
We DO NOT want to:
We do not want to query the Excel and SQL data into UiPath datatables and do the joins in UiPath due to the way that the UiPath “Join Datatables” activity iterates through each record in each datatable to find the join. Considering the amount of data that we are working with, the process would perform poorly in terms of time. SQL is really good at processing data quickly, so it would be a good idea to parse the Excel data into SQL but we don’t want to create a table for it if we don’t need to.
So we…
We do stuff… In SQL and in UiPath.
In SQL
We use UDTTs and stored procedures. We can create a table schema definition for the Excel file in SQL (ID, name, description and URL) as a type. The UDTT create statement would look something like:
CREATE TYPE [Project_UDTT] AS TABLE
(
[id] [uniqueidentifier] NOT NULL,
[name] [varchar](255) NOT NULL,
[description] [varchar](max) NULL,
[url] [varchar](max) NOT NULL
);
GO
Next, we would need a stored procedure to parse the UDTT through and retrieve the joined results, which would look something like:
CREATE PROCEDURE [JoinProjectInformation_SP]
(
@Projects [Project_UDTT] READONLY
)
AS
BEGIN
SET NOCOUNT ON;
SELECT p.* -- all columns in @Projects
,pi.* -- all columns in ProjectInformation
FROM @Projects p
JOIN ProjectInformation pi
ON p.id = pi.ProjectID
END;
GO
Now we go do more cool stuff in UiPath.
In UiPath
We will need to add an Execute Query activity (Execute Non-Query would work more or less the same, if that’s the sort of thing that you’re into) to the desired sequence. If you can’t find the Execute Query activity, make sure that you have the UiPath.Database.Activities package installed.
User-Defined Table Types (UDTTs) in UiPath
First, we need add the stored procedure name (“JoinProjectInformation_SP”) to the SQL input argument as well as change the CommandType to StoredProcedure. Assign the output Datatable argument to something along the lines of dtJoinedProjects.
Next, we need to add the results (of Datatable type) from “Get Projects” as a paramter to the stored procedure. We do that by configuring Parameters:
Ensure that the Parameter Name is the same as it is declared in the SQL stored procedure but without the “@” prefix. Once you’re done, select OK and run the workflow.
Did this work for you? Is there something you’d like to add? Drop a comment below or reach out to me jacqui.jm77@gmail.com.
Many automation solutions make use of the functionality provided by mail services as it serves as an important element that allows for communication between humans and the automation process. There are many benefits provided by using Google Mail (Gmail), one of which is cost – for that reason, this post will focus on providing a step-by-step guide of how to monitor emails coming into your Gmail inbox, with the ability to monitor specific labels.
It is also important to note that there are tools and platforms that make it much easier to perform these actions but as developers, we know that life cannot always be “easy”. This post aims at empowering the “not easy” solutions.
What are the steps?
The steps that we will be following are:
Ensure that your Gmail account is configured correctly
Import the libraries
Gather variable values
Email address
Password
Label
Search Criteria
Define methods
Get Body
Search
Get Emails
Authenticate
Authenticate
Extract mails
Extract relevant information from results
Deep Dive
Let’s dive deeper into the steps listed above.
Ensure that your Gmail account is configured correctly
My first few attempts at this left me pulling my hair out with an “Invalid Credentials” error. Upon much Googling and further investigation, I found that it is caused by a Google Account setting. This is quite easily fixable.
In order to interact with my account, I had to allow less secure apps (you can access that setting here):
Allowing less secure apps to communicate with my Gmail account
If you are still experiencing problems, here is a more extensive list of troubleshooting tips.
Import the libraries
Now let’s move over to Python and start scripting!
First, let’s import the libraries that we’ll need:
import imaplib, email
Gather variable values
In order to access the mails from the Gmail account we will need to know the answers to the following questions:
Which Google account (or email address) do we want to monitor?
What is the password to the above account?
What label do we want to monitor?
What is the search criteria?
The best way to find out is to ask and luckily we can do that through code:
imap_url = 'imap.gmail.com' # This is static. We don't ask the questions we know the answer to
user = input("Please enter your email address: ")
password = input("Please enter your password: ")
label = input("Please enter the label that you'd like to search: ") # Example: Inbox or Social
search_criteria = input("Please enter the subject search criteria: ")
Define Methods
It becomes easier to break some of the reusable elements up into methods (or functions) so that larger implementations of this solution are equipped to be easily scalable. Stephen Covey teaches us that starting with the end in mind is one of the habits of highly effective people – some might even call it proactive design thinking. The point is that it is good to think ahead when developing a solution.
Enough rambling, here are the functions:
# Retrieves email content
def get_body(message):
if message.is_multipart():
return get_body(message.get_payload(0))
else:
return message.get_payload(None, True)
# Search mailbox (or label) for a key value pair
def search(key, value, con):
result, data = con.search(None, key, '"{}"'.format(value))
return data
# Retrieve the list of emails that meet the search criteria
def get_emails(result_bytes):
messages = [] # all the email data are pushed inside an array
for num in result_bytes[0].split():
typ, data = con.fetch(num, '(RFC822)')
messages.aplend(data)
return messages
# Authenticate
def authenticate(imap_url, user, password, label):
# SSL connnection with Gmail
con = imaplib.IMAP4_SSL(imap_url)
# Authenticate the user through login
con.login(user, password)
# Search for mails under this label
con.select(label)
Authenticate
Before we can extract mails, we first need to call the authenticate method that we had just created and pass through the answers to the questions we asked further up:
authenticate(imap_url, user, password, label)
Extract mails
Next, we need to call the search and get_mails methods to extract the mails:
# Retrieve mails
search_results = search('Subject', search_criteria, con)
messages = get_emails(searhc_results)
# Uncomment to view the mail results
#print(message
Extract relevant information from results
Now, let’s work through the results and extract the subject using string manipulation. Feel free to add a “print(subject)” statement underneath the assignment of “subject” for debugging purposes:
for message in messages[::-1]:
for content in message:
if type(content) is tuple:
# Encoding set as utf-8
decoded_content = str(content[1], 'utf-8')
data = str(decoded_content)
# Extracting the subject from the mail content
subject = data.split('Subject: ')[1].split('Mime-Version')[0]
# Handling errors related to unicodenecode
try:
indexstart = data.find("ltr")
data2 = data[indexstart + 5: len(data)]
indexend = data2.find("</div>")
# Uncomment to see what the content looks like
#print(data2[0: indexend])
except UnicodeEncodeError as e:
pass
Did this work for you? Feel free to drop a comment below or reach out to me through email, jacqui.jm77@gmail.com.
The full Python script is available on Github here.
Does your organisation use Automation Hub to capture and consolidate automation ideas and collateral? Have you ever wanted to interact with the data you have in Automation Hub in an automated manner? Well UiPath makes that easier now with the Automation Hub API – no more front-end automations needed to access your data.
Here’s how it works. If you’re looking for specific queries that aren’t covered in this blog post, checkout this Postman collection.
What are the steps?
The steps that we will be following are:
Identifying the URL
Compiling and populating the bearer token
Adding the necessary headers
x-ah-openapi-auth = openapi-token
x-ah-openapi-app-key (only if you’ve assigned the app key when generating the token)
Grabbing the results
Generating the code for reuse in an automation
Deep Dive
Let’s dive deeper into the steps listed above.
Identify the URL
In order to identify which URL (or API Call) would achieve the task at hand, take a look at the different API Calls available here.
For the sake of this post, we are going to list all automations in our instance. Thus, we will be using the following API call:
First thing’s first. Make sure Open API is set up on your tenant. You can do that as follows:
Navigate to the Admin Console within Automation Hub
Hover over Platform Setup
Select Open API
Next, you’re going to want to hit Generate Token and enter the necessary details:
You’re also going to want to take note of your tenant ID because that’s what we are going to use to compile the Bearer Token:
The Bearer Token is made up by concatenating your tenant ID and your generated token separated by “/”.
An example is: 46b6c342-3ab4-11e9-9c19-37a5980a67e8/ce91aa04-fc61-49e9-bec5-cb237efb4bda where:
46b6c342-3ab4-11e9-9c19-37a5980a67e8 is the unique Tenant ID
ce91aa04-fc61-49e9-bec5-cb237efb4bdais the specific token generated for the user account
Add the Bearer Token under the “Authorization” tab in Postman with the Type set to “Bearer Token“:
Add Headers
If you have set up app key as an extra security measure when you generated the token, you’ll need to add “x-ah-openapi-app-key” to your headers and assign it to the value you created.
Regardless of whether you populated the app key or not, you’ll need to add “x-ah-openapi-auth” to your headers and assign it to “openapi-token“:
Grab The Response
Once you’ve hit send in Postman, you make a sacrifice to the universe and wait for your results which should look something liiiiikkkkeeee this:
Generate Code For Automation
Now that you’re getting results, you’ll most likely want to get this automated. Well then let’s get the code (for whatever language we want) from Postman.
Click on code in the top(ish) right-hand corner in Postman:
Select your language then copy and paste the code:
Did you get stuck anywhere? Was this helpful?
If you have any questions, issues or feedback, drop a comment below or reach out to jacqui.jm77@gmail.com
Often we create Power BI reports that require some sort of filtering mechanism but filters take up a lot of real-estate that could be used for other visuals instead. What if we could hide and show a filter pane using a “hamburger” menu mechanism?
We can and here’s how.
What are the steps?
The steps to using a hamburger menu to hide and show a filter pane in Power BI are:
Create the report layout and set up the filters
Add two hamburger menu images
Create a “hide” bookmark
Create a “show” bookmark
Add the bookmarks to the actions of each image
Align the two images
Create the report layout and set up the filters
To create the filters panel, add a rectangle (Home > Insert > Shapes) before adding and aligning the filter visuals on top of the rectangle.
An example would be something like this:
Add two hamburger menu images
Next we would want to add two hamburger menu icons (preferably SVG or PNG images with transparent backgrounds) next to one another (Home > Insert > Image).
Create a hide bookmark
In order to create a bookmark, you would need to ensure that the bookmark pane and selection pane are visible. You can do this by navigating to the View tab and ensuring that the Bookmarks Pane and the Selection Pane are both checked. This should allow you to see the following:
To create a hidebookmark you would need to hide all of the filters, the rectangle block and one of the hamburger menu images using the Selection Pane. To hide a visual (or an element), you can either click on the eye icon next to the visual in the selection pane or you can click on the element on the report and select hide on the selection pane.
Once all necessary visuals have been hidden, you should see this:
Next, you are going to want to bookmark the view that you are currently looking at by selecting “Add” in the BookmarksPane. This will result in “Bookmark 1” being created:
You can then rename the bookmark to “Hide Filters” by double clicking on the bookmark or by selecting the three dots next to the bookmark name (on the right) and selecting Rename:
Create a show bookmark
To create a “Show” bookmark, we are going to ensure that all of our filters are visible again:
Next we are going to hide the hamburger image that was visible in the “Hide” bookmark:
Then select “Add” in the Bookmark Pane and rename the bookmark to “Show Filters“:
Adding the bookmarks to the actions of each image
Now we need to add these bookmarks as actions to the correct hamburger image. Let’s start with the image that’s still visible. When we click that image, we are expecting our filters to Hide. So we want to link this image to the “Hide Filters” bookmark.
To do this, click on the image, navigate to the Format Image pane, ensure that Action is On (if it is Off, click on the round dot and it will turn on), expand Action, change Type to “Bookmark” and select the “Hide Filters” bookmark:
If you hover over the visible image, there should be a tool tip that appears:
If you hold Ctrl and click on the image, it will apply the bookmark and the filters (with its hamburger menu image) should disappear.
Now let’s repeat these steps for the image that is currently visible and assign the “Show Filters” bookmark to its Action:
Now you can lay the one hamburger image on top of the other the other so that they appear to be one image (you may need both images to be visible for this). Reorganise your report layout and play around with the other fancy things that you can do with bookmarks!
Just a note: It is possible to keep values of filters between bookmarks. It would require manipulation of bookmark properties. For this scenario, the data option would need to be deselected:
This post will assist in using Azure Blob Storage to store files used in UiPath processes.
A funny thing happened the other day… Jackson broke the news to the team that his laptop was stolen. After some panic, he lets everyone know that at least the latest solution was checked into source control (and is available on the Orchestrator). That brings some relief, until Barbra asks if the look up files are in source control too. With a worried look on his face, Jackson says, “No… Neither are any of the reference files that are referenced in the queue item.”
After much excitement, Jeremy (the boss man), mandates a central storage repository to be implemented so that this doesn’t happen in the future and so that local development isn’t relied upon. After some investigation, the team decided that Azure Storage would be a great way to go as it fits in with the general architectural direction that the company is heading in. Here’s the question though, how?
What are the steps?
The steps that we will be following are:
Create an App Registration
Assign the correct API permissions
Gather the necessary info
Application ID
Tenant ID
Client Secret
Create Azure Storage Resource
Use the created resources in UiPath
Add Azure Scope
Add Get Storage Account Activity
Add Get Storage Account Key Activity
Add Get Blob Container
Add Upload Blob
Deep Dive
Let’s dive deeper into the steps listed above.
Create an App Registration
Once you have logged into Azure (https://portal.azure.com/), the first thing you want to do is create an App in App Registration and you can do that by doing the following:
Go to Azure Active Directory
Go to App Registrations
Click Register an application
Provide a name and select authentication
Hit Register
Next, you want to add the correct API permissions.
Assigning The Correct API Permissions
You will need to do the following to assign the correct API permissions:
Inside the newly created App Registration, select API permissions
Select Add a permission
Add Azure Storage, user_impersonation
Now that you’ve got that set up, you want to get the Application ID, Tenant ID and Client Secret.
Gather The Necessary Info
In order to use the app that has just been registered, you’ll need to collect certain info. The info can be accessed as follows:
Inside the newly created App Registration, select Overview
Copy the Application (client) ID and the Directory (tenant) ID as it will be needed later
Click on Client secrets, generate a secret
Copy the secret and paste it somewhere for when you need it
Click on Overview in the Azure Active Directory and copy the Tenant ID
The Subscription ID will be visible in the Overview section of the Storage Account (once it has been created)
Now you should be ready to create your storage account.
Creating Azure Storage Resource
Hit Home in the top left hand corner and navigate to Resources.
Hit new resource
Search for Storage account
Click Storage account – blob, file, table, queue
Click Create
Select Subscription and resource group (create one if necessary)
UiPath Insights is an RPA analytics solution that enables your business to track, measure, and manage the performance of your entire automation program. UiPath Insights can be used with a cloud orchestrator or a stand-alone orchestrator. If you have a stand-alone orchestrator, you can follow this guide to set up UiPath Insights. This post is specifically focused on setting up UiPath Insights on a cloud orchestrator.
What are the steps?
The steps that we will be following are:
Set up Insights (Licensing and Permissions)
Replicate Existing Dashboards from UiPath Templates (if needed)
You’ll need a license to use this product. If you don’t have an enterprise license, you may request a trial enterprise license by logging onto UiPath Cloud Platform, navigating to Admin > Licenses > Request Enterprise Trial
If you do have a license, you’ll need to ensure that Insights is enabled for your tenant. You can do this by navigating to Admin > Tenants then click on the three dots next to the appropriate tenant and select Tenant Settings:
Ensure that Insights are checked:
You should now see the Insights tab in the navigation pane on the left. If you don’t see it, try logging out and logging back in again. It sometimes takes a hot minute and a tad of patience:
Click on the Insights tab as seen above. You’ll need to ensure that you have Designer permissions by clicking on the three dots in the top right corner of the screen, next to the tenant name before selecting User Management:
Click on the edit pencil icon next to the relevant users and ensure that the Designer role is checked:
Navigate back to Insights, where you should see UiPath Templates. Let’s start by replicating these dashboards into your My Dashboards by clicking on the copy icon, as seen below:
Give the dashboard a new title and click Save:
The replicated dashboard will open up and you can now customise it as needed, like adding a ‘Process Name‘ filter. To add the filter, you’ll need to edit the dashboard:
Then click on Filters and select Add Filter:
Expand the Jobs dataset and select Process Name:
From here, you can hit the Add Button:
The filter will now show up on your dashboard and you can save your changes by hitting the Save button:
If you navigate back to Insights > My Dashboards, you should now see your dashboard:
You may replicate this to a tenant level (the report will then show up in the Insights > Tenant tab) by clicking on the copy icon on the dashboard card and selecting Share with Tenant:
Creating a New Dashboard
Now let’s create a Human Time Saved dashboard. ‘Human Time Saved’ refers to the time that a human did not have to spend executing this process. In theory, for the duration of the process run (of an unattended process), the human did not have to touch the process, resulting in Human Time Saved equating to the duration of the process run.
Let’s start by creating a new dashboard:
Name it appropriately and hit Save. You’ll then be prompted to hit Edit Dashboard:
Let’s start editing the dashboard by adding a tile:
For Annual Human Time Saving(per Process), we will information from the Jobs dataset, so in this case, click on Jobs:
Give the Tile a name in the top left corner then expand the Jobs dataset:
Expand Creation Date and select Year. Then Click on Process Name and Total Run Time in Seconds, before hitting Run:
Select the Visualization to change the way that the data is presented:
Now let’s add a filter to make sure that we are only calculating human time saved for Unattended processes. To do this, let’s filter Robot Type by Unattended. Hover Over Robot Type and click on Filter by Field:
You can now hit Save:
You should be returned to your dashboard where you can continue adding tiles and before clicking Save:
Setting up Alerts
Insights allows us the ability to trigger an alert when a ‘pre-defined’ event occurs, based on some condition. For instance, if the Annual Human Time Saving (per Process) goes above 5 seconds, I would like to receive an email. We can set that up on the dashboard by clicking on the Alert icon on a specific tile (visible when hovering over the tile), on the existing dashboard:
Enter the relevant information and hit Save Alert:
You should now see that an alert has been created on the tile when you hover over it:
When the alert condition is met (including the time trigger), the email will be sent and will look something like this:
Need help? Feel free to drop a comment below or reach out to me via email, jacqui.jm77@gmail.com
Have you ever wanted to assign a variable based on the value of another variable but… Without the use of an IF statement block? Here’s how to achieve that.
Let’s take an example… We have a boolean variable, named UnicornsExist which can be set by the user through an input dialog. Based on the value of UnicornsExist, we will print either “This is optimal” if the value is true or “This is not optimal” if the value is false.
Your sequence should look something like this:
UiPath Shorthand If Statement
The syntax of the shorthand if statement is as follows:
IF (<Condition>, <When True>, <When False>)
So in the context of the example outlined above, the if statement would be the following:
IF (UnicornsExist, "This is optimal", "This is not optimal")
The code to this solution is available on GitHub, here.
Power BI reports are a quick and easy way to visualise existing data. Often, we want to visualise data from flat files that aren’t necessarily stored on our local computer but rather, files that are modified by other contributors. By storing a file on OneDrive or SharePoint (or indirectly, on Microsoft Teams), the file may be shared with other collaborators and allows for easy data refresh on a Power BI report.
What are the steps?
The steps of connecting a Power BI report to an Excel workbook stored on SharePoint or OneDrive are as follows:
Ensure that the file is reachable from SharePoint or OneDrive by navigating to it’s location through a browser
Get the link to the file
Modify the link so that it contains the appropriate Site URL
Use the modified link to connect a new data source to Power BI
Navigate to your file
Transform and load the data
Navigate to Excel file to retrieve and modify the link
There are two ways to access the file and either way is dependent on where you’ve stored the file: OneDrive or SharePoint. If your document is stored on Microsoft Teams, it is accessible via SharePoint which means that if you follow the SharePoint navigation steps, you’ll get the same results.
OneDrive
In many instances, files stored on OneDrive are stored on the background instance of SharePoint so when you get the file link, it’ll have SharePoint in the URL. For this reason, even if the file is stored on OneDrive, we still use the SharePoint folder connector on Power BI.
Once you’ve retrieved the link to the file, make sure you grab the first part of the link that makes up the Site URL.
Original link: https://<domain>/:x:/r/personal/jacqui_muller/Documents/Datasets/world_cities_with_timezone.csv
Please note the highlighted :x:/r/ in the original link which needs to be removed when modifying the link into its required site URL form.
SharePoint
The document that I’m trying to access is currently stored in Microsoft Teams. I opted to open the document in SharePoint which has rendered this view for me to get the link from.
Original link: https://<domain>.sharepoint.com/teams/Reactit/Shared%20Documents/General/world_cities_with_timezone.csv?d=w303802fbc1f04af2a4b27fdd251757a9&csf=1&web=1&e=cUU5Iz
Open a new instance of Power BI Desktop and select “Get Data“. From there, select the “SharePoint Folder” connector option and then click on “Connect“:
Now paste the modified URL obtained during the step followed in the previous section into the “Site URL” textbox and hit OK:
You may be prompted to sign in. Use the Microsoft Account option to log in using your account that has access to the file.
Once you’ve been authorised, you should see all of your files in the site appear (as seen below). In order to select the appropriate file as a data source, click on “Transform Data“:
A Power Query window should pop up, allowing you to select the Binary Content of the file you’d like to use. Click on Binary in the Content column, corresponding to the file you’d like to use:
Once you are able to see all of your data in the Power Query Window, along with the new navigation steps that were added in the Query Settings window, you may click on Close & Apply to start working with your data:
Did this work for you or did you run into any issues trying to connect to your file? Drop a comment below or reach out for assistance – jacqui.jm77@gmail.com
There are many different workflow and automation suites/platforms available out there (some of which include IFTTT, Power Automate and Tonkean) that allow users to interact with their YouTube connector. Most of these workflows classify the functions within the connectors as either a Trigger or an Action. A trigger would be seen as an event that “kicks off” the workflow/process, whereas an action would be an event (or a set of events) that should be executed once the workflow/process has been triggered.
Many of these workflows make use of APIs to get their triggers and actions functioning. There is one small problem though… They don’t always have the predefined triggers or actions that we might be looking to use. Platforms like IFTTT and Power Automate do not yet have a “When an item is added to a Playlist” trigger. Not a train smash though… In this post, we work through how to monitor a YouTube playlist for the addition of new items using Python and the YouTube Data API.
What are the steps?
The steps that we will be following are:
Get a Developer Key
Create Project
Create Credentials
Get API Key
Create the Python Script
Import Python Libraries
Obtain Playlist ID
Query Playlist Items
Process New Items
Deep Dive
Let’s dive deeper into the steps listed above.
Please note: This will require a YouTube Playlist to be created if it doesn’t already exist.
Get a Developer Key
In order to use the YouTube Data API, a developer key needs to be obtained through this portal.
Create Project
You’ll first need to create a project by either clicking on “Create Project” if you have the option, or by clicking on “Select a Project” proceeded by “New Project”:
Create Credentials
Once you’ve selected an option to create a new project, you’ll be prompted to enter a name. Thereafter, you may click “Create”:
After the redirect, you should be focused on “Credentials” where you can add a new API key by selecting the “Create Credentials” option:
Get Key
Next, copy the API key as we will need it to get the Python script working properly:
Create Python Script
Install Libraries
Now, let’s switch to Python and install the correct libraries before we can import them:
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from oauth2client.tools import argparser
import pandas as pd
import numpy as np
import requests
import json
Next, let’s instantiate our first three variables needed to work with the YouTube Data API:
To get familiar with how the Google documentation works, let’s explore how to get a list of playlists, here, using the “Try it” function:
The documentation explains what parameters are required and which are optional. For the sake of getting a list of my own playlists, I added the following values before selecting “Execute”:
You should receive a 200 response with information regarding your playlists:
By selecting “Show Code” shown above, you should be able to select “Python” to see the Python Code if you wanted to add it to the automation script:
Once you have the ID of the playlist that you’d like to monitor, assign it to a variable:
playlist_id = '<insert id>'
Query Playlist Items
There is a max result limit of 50 results per call to the API which means that the results will need to be paged if there are more than 50 items in a playlist (multiple calls will need to be made to get all the results, 50 at a time). The response will contain a page token if there is a next page.
Now, let’s create a method that allows for paging through results:
# Get all items in specified playlist
def get_playlist_items(page_token):
# Auth with YouTube service
youtube = build(YOUTUBE_API_SERVICE_NAME, YOUTUBE_API_VERSION,
developerKey=DEVELOPER_KEY)
# Call the playlistItems.list method to retrieve results matching the specified query term.
request = youtube.playlistItems().list(
part="snippet,contentDetails",
pageToken=page_token,
maxResults=50,
playlistId=playlist_id
)
response = request.execute()
return response
Process New Items
In the true spirit of automation workflows/processes, if the trigger is “new items found in a playlist”, then we need actions to execute once that is found to be true. We can encapsulate these actions into a “Process New” method:
# process any items that were not found in the previous set of results
def process_new(df_old, df_new):
df_diff = df_new.set_index('title').drop(df_old['title'], errors='ignore').reset_index(drop=False)
print(len(df_diff))
for index, element in df_diff.iterrows():
print("New Item Added: " + str(element['title']).encode('utf-8'))
Let’s tie the top two methods together through a “main” method code snippet. Make sure you have an “items.xlsx” file that records all of the items that are in the playlist:
isEnd = False
page_token = None
df = pd.DataFrame() # instantiate blank dataframe
df_history = pd.read_excel('items.xlsx', headers=False) # read history before querying new results so that the new records may be identified
while not isEnd:
playlist_items = get_playlist_items(page_token)
current_count = playlist_items['pageInfo']['totalResults']
# if there is a page token, use it for the next call or assign it back to None
if 'nextPageToken' in playlist_items.keys():
page_token = playlist_items['nextPageToken']
else:
isEnd = True
page_token = None
# write playlist item information to the dataframe
for item in playlist_items['items']:
temp_df = pd.DataFrame.from_dict(item)
temp_df = temp_df[['snippet']].transpose()
df = df.append(temp_df)
df.to_excel('items.xlsx') # write the dataframe to excel
process_new(df_history, df) # process the new items
Did this work for you? Feel free to drop a comment below or reach out to me through email, jacqui.jm77@gmail.com.
The full Python script is available on Github here.
This post will assist in understanding the practical use of design patterns and how they can be applied to the development of processes within UiPath.
What’re Design Patterns?
In a nutshell, a design pattern can be described as a template (or format) that is used to structure code in order to solve a problem in a systematic way. There are multiple different types of design patterns which include a variety of behavioral, structural and creative patterns. These are explained in depth here.
Design patterns are often used to assist in implementing OOP (Object-Orientated Programming) in order to make a solution more modular and reusable. Design patterns can also be used to maintain consistency across solutions so that multiple developers have a clear understanding of how the solution flows from one step to the next.
It is very important to keep design principles (like SOLID) in mind when deciding when and which design pattern should be used within a solution. SOLID is explained nicely here.
Why’s This Important?
When working with Robotic Process Automation (RPA) solutions, it is important to remember that the solution is assumed to have a short lifespan that could be anything from 6 months to 24 months long before facing re-evaluation. Once the value of an RPA solution is re-evaluated and certain aspects need to be enhanced or upgraded, it is easier to do so if the design pattern caters for foreseeable changes by design.
For example, an RPA process that handles reading Excel files that contain stock take information and storing it into an on-premise SQL server already exists. The solution has been re-evaluated after 12 months and the audit has found that new scanners will be used for stock taking rather than manually filling in Excel spreadsheets. The automation needs to be enhanced to accept sensor data from the scanner instead. If all logic that connects to the Excel files is referenced in multiple sequences/workflows logic lived all over the place, the enhancement could create some havoc. By adding a data access/acquisition layer to the solution, only one sequence/workflow will connect to the Excel file and only that sequence/workflow would be affected. All data access to the file is then also modularised.
What’d Such an Implementation Look Like?
Let’s identify some of the possible groupings of functionality:
Initialisation/ Preparation
Data Access/ Acquisition
Domain/ Business Logic
Modular/ Helper Components
Tests
Output
The above-mentioned groups can be further split, as seen below:
RPA Design Pattern
As seen above, the idea behind the proposed design pattern is to ensure the appropriate grouping of functionality. Applying a design pattern like this could be as simple as implementing a similar file structure that groups the relevant workflows.
An example of this is illustrated in A UiPath process, as seen below:
RPA Design Pattern in UiPath
A template of the above UiPath process is available for download here.
Design patterns can be modified as needed. The most important aspect of planning the implementation of a design pattern is to determine how the functionality should be split, modularisedand reused (potentially even between projects) while remaining considerate of future operational support.
If you have any questions, issues or feedback, drop a comment below or reach out to jacqui.jm77@gmail.com
Along with the emergence of the Fourth Industrial Revolution (4IR) we are seeing an increased usage of rebranded terms, that now contain the word “Intelligent”.
We find ourselves surrounded by concepts like Intelligent Automation, Intelligent Digital Workplaces, Intelligent Customer Experience, Intelligent Infrastructure and many more. How much longer until we are faced with the new and improved concept of Intelligent Development as a rebrand of DevOps?
What is “Intelligent Development”?
Intelligent Development, in the context of software development, would refer to concepts, designs and approaches that developers could explore in order to create solutions that would continue to develop, maintain and even enhance themselves.
A large portion of the concepts that may form the basis of Intelligent Development would most likely be adopted from modern DevOps elements. By applying concepts like critical and design thinking to the planning of a solution, the solution may be developed in a manner that is sophisticated enough to cater for foreseeable use cases (like further repetitive or predictable development, maintenance and enhancements). Thus, it can be argued that if architects and developers work together to develop intelligently, Intelligent Development would be no more than a “fancy” term to encapsulate a concept that is much more powerful than the catch-phrase depicts.
An example of a high-level implementation of Intelligent Development is depicted in the figure below:
Developer Pre-work and Intelligent Development
In the figure above, the existing concept of using source control repositories and pipelines is expanded upon to accommodate for consistent (or standard) elements of a solution. With this approach, the developer is also allowed the opportunity to review the changes before accepting the pull request and kicking off the publishing process – this ensures that the appropriate level of visibility and responsibility are still maintained. Let’s consider an example.
There is a need for a Web API that can be expanded to onboard new data sets (with consistent data source types, ie different SQL Server Databases with SQL Server as the consistent data source type) over time. Each time that a new data set is introduced, the solution would need to be enhanced to accommodate for the change. What if the solution developed itself? But how?
Let’s consider the steps that would be involved in making the above possible:
A source control repository would need to be created and a standard branching strategy would need to be defined
The Web API project would need to be created and stored in the repository
The business logic would need to be defined
A pipeline would need to be created to control environmental specific (dev, qa, prod) publishes
Documentation would need to be created and stored somewhere that is centrally accessible
A reusable mechanism to clone a repository, push code and create pull requests would need to exist
A lead developer would need to take responsibility for reviewing changes and approving the pull request so that the pipeline may be kicked off
Most of the above is feasible for automation, considering a few reusable elements that are mentioned in my previous posts as well as my GitHub repo:
What About The Moral Standing of Self-Developing Solutions?
In the space of Automation, the focus on repurposing certain job roles is significant. Automating repetitive tasks has many benefits but the fear of job loss definitely is not one of them.
As developers, we should understand that not even we are safe from the capability that technology has to offer. We, too, need to repurpose our skills towards more meaningful, complex and cognitive tasks. In retrospect, being able to concentrate on research and development instead of supporting an existing solution is the dream that many developers would want to work towards. The ability to pivot towards practically implementing self-developing solutions is what separates good developers from great developers.
Personally, I believe that the benefits of developing intelligently outweigh the shortfalls by far. It has become a new way of approaching problems for me. By remaining conscious of Intelligent Development while architecting and developing solutions, I am enabling myself to work on more innovative projects and supporting less legacy solutions. My hope is that as a collective of developers we strive towards the same goal of continuously adding intelligence to our development process – Let’s take this opportunity to be better together!
