Featured

[Automation]: Monitoring Gmail Inbox Using Python

Level of Difficulty: Beginner – Senior.

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:

  1. Ensure that your Gmail account is configured correctly
  2. Import the libraries
  3. Gather variable values
    1. Email address
    2. Password
    3. Label
    4. Search Criteria
  4. Define methods
    1. Get Body
    2. Search
    3. Get Emails
    4. Authenticate
  5. Authenticate
  6. Extract mails
  7. 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:

  1. Which Google account (or email address) do we want to monitor?
  2. What is the password to the above account?
  3. What label do we want to monitor?
  4. 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.

Featured

[Automation]: Working With The UiPath Automation Hub API And Postman (Then Getting The Code)

Level of Difficulty: Beginner – Senior.

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:

  1. Identifying the URL
  2. Compiling and populating the bearer token
  3. Adding the necessary headers
    1. x-ah-openapi-auth = openapi-token
    2. x-ah-openapi-app-key (only if you’ve assigned the app key when generating the token)
  4. Grabbing the results
  5. 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:

https://automation-hub.uipath.com/api/v1/openapi/automations

Pop this URL into Postman:

Compile and Populate The Bearer Token

First thing’s first. Make sure Open API is set up on your tenant. You can do that as follows:

  1. Navigate to the Admin Console within Automation Hub
  2. Hover over Platform Setup
  3. 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-cb237efb4bda is 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

Featured

[Power BI] Creating a Hamburger Menu for Filters in Power BI

Level of Difficulty: Beginner – Senior.


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:

  1. Create the report layout and set up the filters
  2. Add two hamburger menu images
  3. Create a “hide” bookmark
  4. Create a “show” bookmark
  5. Add the bookmarks to the actions of each image
  6. 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 hide bookmark 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 Bookmarks Pane. 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 report is available on GitHub, here.

If you got stuck or have feedback, please feel free to drop a comment below or reach out to jacqui.jm77@gmail.com for help!

Featured

[RPA]: Uploading Files to Azure Blob Storage in UiPath Processes

Level of Difficulty: Intermediate – Senior.

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:

  1. Create an App Registration
  2. Assign the correct API permissions
  3. Gather the necessary info
    1. Application ID
    2. Tenant ID
    3. Client Secret
  4. Create Azure Storage Resource
  5. Use the created resources in UiPath
    1. Add Azure Scope
    2. Add Get Storage Account Activity
    3. Add Get Storage Account Key Activity
    4. Add Get Blob Container
    5. 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:

  1. Go to Azure Active Directory
  2. Go to App Registrations
  3. Click Register an application
  4. Provide a name and select authentication
  5. 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:

  1. Inside the newly created App Registration, select API permissions
  2. Select Add a permission
  3. 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:

  1. Inside the newly created App Registration, select Overview
  2. Copy the Application (client) ID and the Directory (tenant) ID as it will be needed later
  3. Click on Client secrets, generate a secret
  4. Copy the secret and paste it somewhere for when you need it
  5. Click on Overview in the Azure Active Directory and copy the Tenant ID
  6. 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.

  1. Hit new resource
  2. Search for Storage account
  3. Click Storage account – blob, file, table, queue
  4. Click Create
  5. Select Subscription and resource group (create one if necessary)
  6. Select storage account name, location, account kind (BlobStorage) and Replication (Read-access geo-redundant storage (RA-GRS))

Create Container

Great! Storage account created – now to create a container within the storage account:

  1. Open newly created Storage Account resource
  2. Click on Containers
  3. Click Add Container
  4. Give it a name and access level

Storage account created, container created, but how does the App Registration tie in? Well, we still need to add it, so let’s do that.

Give the App Registration access to the Storage Account

In order to give the Registered App access to the newly created Storage Account, do the following:

  1. Go to the Storage Account
  2. Select Access Control (IAM)
  3. Select Add, Role Assignment
  4. Assign Role to Storage Blob Data Contributor
  5. Search for the App Registration in the Select field
  6. Now add another Role and assign the App Registration to the Contributor Role

Now that we have everything set up in Azure, let’s get started in UiPath.

Using Azure Storage in UiPath

In order to use the resources created on Azure, the following needs to be done:

  1. Create a new process and give it a name
  2. Add the UiPath.Azure.Activities library
  3. Add the Azure Scope to the sequence
  4. Enter the following details gathered from above:
    1. ClientID
    2. ClientSecret
    3. SubscriptionID
    4. TenantID
  5. Add Get Storage Account Activity inside of the Azure Scope, with the following properties
    1. StorageAccountName
    2. ResourceGroupName
    3. Output StorageAccount to a variable
  6. Add Get Storage Account Key Activity below, with the following properties
    1. StorageAccount
    2. Output StorageAccountKey to a variable
  7. Add Get Blob Container with the following properties
    1. ContainerName
    2. StorageAccount
    3. StorageAccountKey
    4. Output BlobContainer to a variable
  8. Add Upload Blob from File with the following properties
    1. BlobContainer
    2. BlobName
    3. ContentType
    4. FilePath
    5. Type
    6. Output Blob to a variable

Now Jeremy, Jackson and the rest of the team can go about their business without worrying about stolen laptops and lost files.

The above solution is available on Github

If you have any questions, issues or feedback, drop a comment below or reach out to jacqui.jm77@gmail.com

[Power BI] Connecting Power BI to an Excel File Stored on Microsoft Teams, SharePoint or OneDrive as a Data Source

Level of Difficulty: Beginner – Senior.


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:

  1. Ensure that the file is reachable from SharePoint or OneDrive by navigating to it’s location through a browser
  2. Get the link to the file
  3. Modify the link so that it contains the appropriate Site URL
  4. Use the modified link to connect a new data source to Power BI
  5. Navigate to your file
  6. 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

The link we need:
https://<domain>/personal/jacqui_muller/

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&gt;.sharepoint.com/teams/Reactit/Shared%20Documents/General/world_cities_with_timezone.csv?d=w303802fbc1f04af2a4b27fdd251757a9&csf=1&web=1&e=cUU5Iz

The link we need:
https://<domain&gt;.sharepoint.com/teams/Reactit/

Connect to Power BI

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

[Automation]: YouTube Playlist Monitoring Using Python

Level of Difficulty: Beginner – Senior.

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:

  1. Get a Developer Key
    1. Create Project
    2. Create Credentials
    3. Get API Key
  2. Create the Python Script
    1. Import Python Libraries
    2. Obtain Playlist ID
    3. Query Playlist Items
    4. 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:

!pip install google-api-python-client
!pip install google_auth_oauthlib

Import Libraries and Instantiate Variables

The following libraries should be imported:

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:

DEVELOPER_KEY = '<insert key>'
YOUTUBE_API_SERVICE_NAME = 'youtube'
YOUTUBE_API_VERSION = 'v3'

Obtain Playlist ID

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.

[RPA] The Use of Design Patterns in UiPath Processes

Level of Difficulty: Senior.

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

[Et al.] Intelligent Development – Developing Intelligently

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!

[Python] Using Python for SQL Schema and Table Validation and Logging Progress to Microsoft Teams

Level of Difficulty: Intermediate to Senior

So you’ve reached created a database, you’ve created schemas and tables. You’ve managed to create an automation Python script that communicates with the database, however, you’ve reached a crossroad where you need to validate your SQL tables. Some of the validation might include ensuring that the tables exist and were created correctly before referencing them in the script. In the case of a failure, a message should be sent to a Microsoft Teams channel.

If that’s the case, this post might have just the code to help!

What are the steps?

The steps that the script will be following are:

  1. Create a webhook to your Teams channel
  2. If a schema was provided
    1. Get all the tables in the schema
    2. Validate that all the tables have a primary key
  3. If a schema was not provided but tables were
    1. Validate that each table exists
    2. Validate that each table has a primary key

Create a Webhook to a Teams Channel

To create a webhook to your teams channel, click on the three dots next to the channel name and select connectors:

Search for “webhook” and select “Configure” on the Incoming Webhook connector:

Provide your webhook with a name and select “Create”:

Be sure to copy your webhook and then hit “Done”:

Create The Python Script That Does The Validation

Import Libraries

import pandas as pd

import pyodbc
from sqlalchemy import create_engine, event
import urllib

import pymsteams

Create a Connect to SQL Method

The purpose of this method is to allow the reusable method to be accessed each time SQL needs to be accessed.

def connect_to_sql(server, user, password, database, auth):
    
    driver = '{SQL Server}'
    
    if user == None:

        params = urllib.parse.quote_plus(r'DRIVER={};SERVER={};DATABASE={};'.format(driver, server, database))
        conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
        engine = create_engine(conn_str)
        
        return engine
    
    else:
        
        params = urllib.parse.quote_plus(r'DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver, server, database, user, password))
        conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
        engine = create_engine(conn_str)
        
        return engine

Create Send Teams Message Method

The method below enables the sending of a teams message to a channel. In order to configure this to work, add the webhook configured above to the method below.

def send_teams_message(text):
    
    webhook = '<add webhook>'
    
    # You must create the connectorcard object with the Microsoft Webhook URL
    myTeamsMessage = pymsteams.connectorcard(webhook)

    # Add text to the message.
    myTeamsMessage.text(text)

    # send the message.
    myTeamsMessage.send()

Create Validate Primary Keys Method

This method ensures that primary keys are validated and returns the status as well as the text to be sent to MS Teams.

def ValidatePrimaryKeys(engine):
    
    query = "select schema_name(tab.schema_id) as [schema_name], tab.[name] as table_name from sys.tables tab left outer join sys.indexes pk on tab.object_id = pk.object_id and pk.is_primary_key = 1 where pk.object_id is null order by schema_name(tab.schema_id), tab.[name]"

    df = pd.read_sql(query, engine)
    
    keysValid = True
    
    text = ""
    
    if len(df) > 0:

        text = 'Primary Key Validation Failed.\n\n'

        for index, element in df.iterrows():

            text += '{} does not have a primary key\n\n'.format(element['table_name'])
            
            keysValid = False

        # send_teams_message(text)
        
    return keysValid, text

Create Validate Tables Method

This method validates that the tables exist within the current database and returns the status as well as the text to be sent to MS Teams.

def ValidateTables(engine, tables):

    query = "select tab.[name] as table_name from sys.tables tab"

    df = pd.read_sql(query, engine)

    tables_split = []
    
    tables = tables.replace(' ','')

    if ';' in tables:

        tables_split = tables.split(';')

    elif ',' in tables:

        tables_split = tables.split(',')
    
    elif len(tables) != 0:
        
        tables_split = [tables]

    text = ""

    tablesValid = True

    for table in tables_split:

        if table not in df['table_name'].tolist() and (table != '' and table != None):

            text += 'Table not found in database: {}\n\n'.format(table)
            tablesValid = False

    if tablesValid:

        text = 'Table Validation Passed\n\n'

    else:

        text = 'Table Validation Failed\n\n' + text

    #send_teams_message(text)
    
    return tablesValid, text

Create Validate Schema Method

This method validates that the schema exists. Once the schema is validated, all tables in the schema are retrieved and their primary keys are validated.

def ValidateFromSchema(schemas, engine):
    
    text = ""
    tableText = ""
    
    schemas_split = []
    
    schemas = schemas.replace(' ','')

    if ';' in schemas:

        schemas_split = schemas.split(';')

    elif ',' in schemas:

        schemas_split = schemas.split(',')
    
    elif len(schemas) != 0:
        
        schemas_split = [schemas]
        
    isValid = True

    for schema in schemas_split:
        
        if (isValid):
        
            query = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '{}'".format(schema)

            df = pd.read_sql(query, engine)
            
            if (len(df) > 0):

                query = "select t.name as table_name from sys.tables t where schema_name(t.schema_id) = '{}'".format(schema)
                
                df = pd.read_sql(query, engine)
                
                tables = ",".join(list(df["table_name"]))
                
                validateTables = ValidateTables(engine, tables)
                
                isValid = validateTables[0]
                tableText += "{}\n\n".format(validateTables[1])
                
            else:
                
                isValid = False
                
                text += "Schema Validation Failed\n\n"
                text += "Schema not found in database: {}\n\n".format(schema)
                    
    if (isValid):
        
        text = "Schema Validation Passed\n\n"
        
        text = "{}\n\n{}".format(text, tableText)
        
    return isValid, text

Create Validate SQL Method (Equivalent to “main”)

This method acts as the main method and encapsulates all the methods in the correct order and executes the proceeding tasks.

def ValidateSQL(project, server, database, schemas, tables, auth, username, password):
    
    engine = connect_to_sql(server, username, password, database, auth)
    
    summaryText = None
    
    if (schemas != None):
        
        validateSchemas = ValidateFromSchema(schemas, engine)
        
        isValid = validateSchemas[0]
        text = validateSchemas[1]
    
    else:
        
        validateTables = ValidateTables(engine, tables)
    
        isValid = validateTables[0]
        text = validateTables[1]

    if isValid:
        
        summaryText = 'Primary Key Validation Passed\n\n'
        
        validatePrimaryKeys = ValidatePrimaryKeys(engine)
        
        isKeysValid = validatePrimaryKeys[0]
        
        pkText = validatePrimaryKeys[1]
        
        if isKeysValid:
            
            text += summaryText
            
        else:
            
            text += pkText
        
    else:
        
        summaryText = text
        
    text = "<strong><u>{}<u><strong>:\n\n{}".format(project, text)
                
    send_teams_message(text)
    
    return isValid

Calling the Validate SQL Method

The below is how you’d initialise the variables and use them to call the ValidateSQL method.

server = '<server>'
user = '<user>'
password = '<password>'
database = '<database>'
auth = 'SQL' # or Windows
schemas = '<schemas comma separated>'
tables = '<tables comma separated>'
project = '<project>'

ValidateSQL(project, server, database, schemas, tables, auth, user, password)

And that’s a wrap Pandalorians! The Github repo containing the script is available here. Did this help? Did you get stuck anywhere? Do you have any comments or feedback? Please pop it down below or reach out – jacqui.jm77@gmail.com

[Python] Automating ASP.NET Core Web API Creation That Communicates With Your Database in 60 Seconds or Less

Level of Difficulty: Intermediate

So you’ve created a database and now you need to make it available for third party access without actually giving people a username and password to the database. You’re familiar with how C# works and how beneficial an ASP.NET Core Web API would be in developing this solution. You have a crazy deadline though and you start wishing that some of the ground work could be automated, allowing you to enter a few parameters and the rest is automagic!

Well here’s a solution that might help and it’s all written in Python!

What are the steps?

The steps that the script will be following are:

  1. Create the project
  2. Scaffold the database
  3. Delete the default template content
  4. Create the controllers

Deep Dive

Let’s dive deeper into the steps listed above.

Create The Project

The project can be created using the dotnet CLI thus being executable from a Python script. This does mean that the dotnet CLI would need to be installed. If it is not yet installed, you can install it from here.

The project creation step consists of a few steps:

  1. Create a reusable “Execute Command” method
  2. Create an ASP.NET Core Web API project
  3. Create a solution (.sln) file
  4. Add the Web API Project to the solution

Initialise Variables

The following library is to be imported and the variables initialised so that the script will work properly:

import os

project_path = r"{}\Projects".format(os.getcwd()) # points to a folder named projects created in the same directory as this script
project_name = "TestScriptWebAPI" # the name of the project (and file that groups the project/solution files together)

start_directory = os.getcwd() # the directory of the script
start_time = datetime.now() # the time that process started

Execute Command

This method allows for any command to be executed, provided that the command and the appropriate file path are provided.

def ExecuteCommand(command, file_path):
    
    # if the file path exists and is not empty, change to the directory else return False and "File path not valid"
    if file_path != None and os.path.exists(file_path):

        os.chdir(file_path)

    else:

        return False, "File path not valid" # False depicts that the command did not run successfully due to the invalid file path

    command_output = os.popen(command).read() # command is executed

    return True, command_output # True depicts that the command was executed successfully, however, it might not be the desired out put which is why the command_output is also returned

Create an ASP.NET Core Web API Project, Solution and Linkage

This method is used to create the project, the solution and the linkage between the two.

def CreateWebAPI(project_name, project_path):
    
    # create the solution path if it doesn't exist yet
    solution_path = r"{}\{}".format(project_path, project_name)
    
    if (os.path.exists(solution_path) == False):
        
        os.mkdir(solution_path)

    # this is the command that will be run in order to create a new project.  Customising the project before creation would occur here
    command = "dotnet.exe new webapi --name {} --force".format(project_name)

    result = ExecuteCommand(command, project_path)[0]
    
    if result:
        
        print("Project successfully created")
        
    else:
        
        print("Project not created")

    # this is the command that will be run in order to create a new sln.  Customising the project before creation would occur here
    command = "dotnet.exe new sln --name {} --force".format(project_name)

    result = ExecuteCommand(command, solution_path)[0]
    
    if result:
        
        print("Solution successfully created")
        
    else:
        
        print("Solution not created")
            
    # this is the command used to add the project to the solution
    csproj_path = r"{0}\{1}\{1}.csproj".format(project_path, project_name)
    command = 'dotnet.exe sln add "{}"'.format(csproj_path)
    solution_path = r"{}\{}".format(project_path, project_name)
    
    result = ExecuteCommand(command, solution_path)[0]
    
    if result:
        
        print("Project successfully added to solution")
        
    else:
        
        print("Project not added to solution")

Now that the project has been created and added to the solution, the appropriate libraries can be installed so that the database can be scaffolded.

Scaffold Database

The database would need to already be created and validation on the database would already need to have happened. The validation would include ensuring that all tables contain primary keys.

Scaffolding the database consists of the following steps:

  1. Install the libraries
  2. Compile the command and connection string
  3. Scaffold the database


Install The Libraries

This method installs the desired packages.

def InstallPackages(solution_path):
    
    # Install SqlServer
    command = "dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 3.1.1"
    command_result = ExecuteCommand(command, solution_path)
    
    # Install EF Design
    command = "dotnet add package Microsoft.EntityFrameworkCore.Design -v 3.1.1"
    command_result = ExecuteCommand(command, solution_path)
    
    # Install EF Tools
    command = "dotnet add package Microsoft.EntityFrameworkCore.Tools -v 3.1.1"
    command_result = ExecuteCommand(command, solution_path)

This method compiles the command string from the database connection and tables.

def CompileCommandStrings(auth, server, database, tables, username, password):
    
    # the models are created in the Models folder
    # the context is created in the Data folder

    if auth == 'Windows':
        
        connection_string = text = '"ConnectionStrings": \n\t{"DefaultConnection": "Initial Catalog=' + database + ';Data Source=' + server + '; Trusted_Connection=true;"},\n"Logging"'
        command = 'dotnet.exe ef dbcontext scaffold "Initial Catalog={};Data Source={}; Trusted_Connection=true;" Microsoft.EntityFrameworkCore.SqlServer -o Models --context-dir Data'.format(database, server)

    if auth == 'SQL':

        connection_string = text = '"ConnectionStrings": {\n\t"DefaultConnection": "Password=' + password + ';Persist Security Info=True;User ID=' + username + ';Initial Catalog=' + database + ';Data Source=' + server + '"},\n"Logging"'
        command = 'dotnet.exe ef dbcontext scaffold "Password={};Persist Security Info=True;User ID={};Initial Catalog={};Data Source={}" Microsoft.EntityFrameworkCore.SqlServer -o Models --context-dir Data'.format(password, username, database, server)

    if tables != '':

        split_table = []

        tables.replace(' ','')

        if ',' in tables:

            split_table = tables.split(',')

        if ';' in tables:

            split_table = tables.split(';')

        for table in split_table:

            command += ' -t {}'.format(table)

    command += ' -f'
    
    return command, connection_string

This method is used to scaffold the database into the project.

def ScaffoldDatabase(auth, server, database, tables, username, password, project_path):
    
    solution_path = r"{}\{}".format(project_path, project_name)
    
    InstallPackages(solution_path)
    
    result = CompileCommandStrings(auth, server, database, tables, username, password)
    command = result[0]
    connection_string = result[1]
        
    command_result = ExecuteCommand(command, solution_path)
    
    print(command_result)

Delete Template Content

When spinning up a project, a WeatherForecast.cs model is created along with a controller. These default classes need to be deleted so that they don’t interfere with the project.

This method deletes the template model and controller files that have been created with the project:

def DeleteTemplateFiles(project_path, project_name):

    # delete the template WeatherForecast.cs Model class
    template_model = r"{}\{}\WeatherForecast.cs".format(project_path, project_name)

    if os.path.isfile(template_model):
        os.remove(template_model)

    # delete the template WeatherForecast.cs Controller class
    template_controller = r"{}\{}\Controllers\WeatherForecastController.cs".format(project_path, project_name)

    if os.path.isfile(template_controller):
        os.remove(template_controller)

Create The Controllers

Creating the controllers requires the following steps:

  1. Get the context name
  2. Get the model
  3. Compile the controller from the template
  4. Create the controllers


Get The Context Name

This method gets the context class name.

def GetContext(file_path):
    
    # the file path should be the path to where the context class was created    
    f = open(file_path, "r")

    context_name = None
    
    for line in f.readlines():

        if '_context' in str(line) and 'private readonly' in str(line):
            
            line = line.replace('  ', '')
            context_name = line.split(' ')[2]
            
    return context_name


Get The Model

This method gets the model class and returns the class name, attribute as well as the namespace.

def GetModel(file_path):

    # file path should depict the path to the Model folder
    f = open(file_path, "r")

    class_name = None
    attributes = []
    namespace = None

    # for each line in the model class, extract the class name, the attributes and the namespace
    for line in f.readlines():

        if 'namespace' in str(line):

            namespace = line.split(' ')[1].split('.')[0]

        if 'public' in str(line):

            line = line.replace('  ', '')

            split_line = line.split(' ')

            if split_line[2] == 'class':

                class_name = split_line[3].replace('\n','')

            else:

                attributes.append(split_line[2])
                                                
    return class_name, attributes, namespace


Compile The Controller From The Template

This method compiles the controller class from the controller template.

def CompileControllerTemplate(model, attributes, file_path, template_file_path, namespace, context_name, id):

    file = open(file_path, "w+")

    template_file = open(template_file_path, "r+")

    template = template_file.read()

    template = template.replace('FillNamespace', namespace)
    template = template.replace('FillContext', context_name)
    template = template.replace('FillModel', model)
    template = template.replace('fillModel', model[0].lower() + model[1:])
    template = template.replace('FillId', id)

    file.writelines(template)

    file.close()
    template_file.close()


Create Controllers

This method creates the controllers.

def CreateControllers(solution_path):

    # initialise the model_file_path
    model_file_path = r"{}\Models".format(solution_path)
    
    # for each model found in the model file, get the model info and use it to create the controller
    for file in os.listdir(model_file_path):

        if file != 'ErrorViewModel.cs':

            file_path = "{}\..\Data".format(model_file_path)

            for context in os.listdir(file_path):

                context_name = context.replace('.cs','')

            file_path = '{}\{}'.format(model_file_path, file)

            model_result = GetModel(file_path)

            model = model_result[0]
            attributes = model_result[1]
            id = attributes[0]
            namespace = model_result[2]            
            path = r'{}\Controllers'.format(solution_path)
            file_path = r"{}\{}Controller.cs".format(path, model)
                        
            template_file_path = r"{}\APIControllerTemplate.cs".format(start_directory)

            if os.path.exists(path) == False:

                os.mkdir(path)

            CompileControllerTemplate(model, attributes, file_path, template_file_path, namespace, context_name, id)

Now you can hit F5 and test your API functionality. Did you get stuck anywhere or do you have feedback? Please feel free to drop it below.

Future Enhancements

Future enhancements of this code include adding SQL validation prior to the web API project creation as well as the implementation of a design pattern through the use of templates, similar to the way it was done in order to create controllers.

The project code is available here.

[Automation]: Using Power Automate with SQL Server (Primary keys that aren’t int)

Level of Difficulty: Beginner – Senior.

Are you in a situation where you’re using a SQL database and you’ve already designed, created and gone through all 700 iterations of the review? Primary keys already created of type uniqueidentifier with default value of NEWID(). Then Fred comes with his clever idea of adding some sort of automation workflow on top of the database – you know, like Power Automate?

So you go down a tangent of getting info together around Power Automate triggers and actions, specific to SQL Server. You come across the two triggers: When an item is created (V2) and when an item is modified (V2).

You decide on using SQL Server “When an item is created (V2)” as a trigger and Office 365 Outlook “Send an Email (V2)” as an action.

You go into Power Automate and you connect it up to your database but when you hit test and “I’ll perform the trigger action”, you wait… And wait… And wait… ? … ? … And wait. Nothing’s happening?

You do more research and you find that most people use a primary key of type int and it works for them. Now you are running ahead with everything that needs to change in order to get this to work. You think of workarounds like creating views, using a different trigger, using a different automation type, heck… Even using a different workflow tool. But wait! That’s not necessary.

Here’s what to do:

  • Alter your table design in SQL Server Management Studio (SSMS)
  • Add another row, named something like “PowerAutomateID”
    • Make the type int
    • Untick “Allow Null”
    • Scroll down in the table properties and set Identity Specification to Yes
  • Save
  • Go test your flow

There’s no need to make the new column a Primary Key as the Power Automate trigger looks for the column that has Identity Specification set. I had a web form built on top of my database and none of the CRUD (create, read, update and delete) functionality had to be changed. It all continued to work properly, even the Power Automate process.

This is definitely something I wish I knew when I first went on this journey a few weeks ago.

If you hadn’t come across this in your research, you would most likely have loved to have landed on the Microsoft Docs that explain some of the limitations.

Still not working or may you found a better way? Drop a comment below or send an email through to jacqui.jm77@gmail.com