Skip to content

Power Automate Guide

Introduction

Welcome to the documentation for Power Automate integration within the Skill Collector application. In this guide, we will explore how Power Automate enhances the functionality of Skill Collector by automating key processes such as email invitations, data collection from Forms, and updating the database with generated hashes.

Skill Collector is a powerful platform that enables the collection of essential skill information from users, which is then utilized by educational institutions to shape their curriculum according to industry demands. With Power Automate, the process becomes even more efficient and streamlined, allowing for seamless communication, data management, and database updates.

One of the key functionalities of Power Automate within Skill Collector is the collection of data from responses submitted through Forms. The integration allows for the seamless transfer of data from Forms to an Excel sheet, where the information is stored for further analysis and processing. This eliminates the need for manual data entry and ensures accurate and up-to-date information within the Skill Collector database. ( Flow # 1 Form Response Processing Flow)

Additionally, Power Automate plays a crucial role in updating the Skill Collector database with the hashes generated in the Excel sheet. By automating this process, Power Automate ensures that the database remains synchronized and reflects the latest data and user information. (Flow # 2 Updating Database with Hashes)

Furthermore, Power Automate send email invitations to users, inviting them to participate in skill surveys. This automated process ensures that users receive timely notifications and can easily access the Skill Collector application to provide their valuable input. (Flow # 3 Sending Emails and Notifications)

By leveraging the power of Power Automate, Skill Collector optimizes the user experience, enhances data accuracy, and streamlines the overall data management process.

Flow # 1 (Form Response Processing Flow)

Workflow Design

The workflow design and logic behind the Power Automate flows used in the app are aimed at ensuring smooth data collection, validation, and updating processes. Let's break down the sequence of actions, triggers, conditions, and connectors involved in each flow.

Flow # 1 Form Response Processing Flow:

Flow # 1 Logic:

Microsoft Forms Template:

Descriptions

The flow triggers every time a new response is submitted in the Forms.

The Forms are restricted to specific individuals who are responsible for providing valid information. They are instructed to enter a valid email address containing the "@" symbol. This ensures that only legitimate email addresses are collected for further processing.

Actions:

  1. Get Response Details: Retrieve the details of the submitted response, including the responder's information.

  2. Check for Duplicated Emails: Verify if the email address provided in the form response already exists in the Excel sheet.

  3. Conditional Statement:

  • If Email Exists: Update Row in Excel: Update the corresponding row in the Excel sheet with the Name and Company values from the form response.

  • If Email Does Not Exist: Add New Row to Excel: Add a new row to the Excel sheet with the Name, Company details from the form response.

  1. Send Notification to Process Owner: Notify the process owner about the successful processing of the form response.

  2. Hash Creation and Verification Flow:

  • Manually create a hash for each row.

  • Check for Duplicated Hashes: Validate if there are any duplicated hash values in the Excel sheet. Conditional formatting in Excel table will highlight the duplicated value in the table, making them easily identifiable for the process owner.

  • Process owner is responsible for validating if the hashes are ready to be send.

By following this workflow design and incorporating checks for duplicated emails and hashes, the Power Automate flows facilitate efficient data processing, accurate updating of the Excel sheet, and effective communication with the process owner throughout the process.

Flow # 2 (Updating Database with Hashes)

Workflow Design

This flow is responsible for updating the database with the generated hashes from the Excel sheet. It ensures that the Skill Collector app has an accurate and up-to-date record of the generated hashes.

Flow # 2 Updating Database with Hashes:

Flow # 2 Logic

Descriptions

Trigger: HTTP Request - When the Send button is clicked

This flow is triggered when the Send button in the Excel sheet is clicked, initiating an HTTP request.

  1. Retrieve Hash and Related Information. The flow retrieves the generated hash from the Excel sheet.

  2. Compose JSON File: The retrieved hash is then composed into a JSON file with the desired structure, such as:

   [
    {
    "hash": "234233435"
    },
    {
      "hash": "34532"
    },
    {
      "hash": "1141"
    },
    {
      "hash": "111"
    },
    {
      "hash": "54853"
    },
    {
      "hash": "67843015"
    },
    {
      "hash": "24342545alena"
    }
    ]

  1. Connect to the Database API: The flow establishes a connection to the database API using the appropriate API connector. This connector should be configured with the necessary authentication and connection details to access the database.

  2. Authenticate User: The flow uses the API connector to send a login request to the database API, including the user credentials. The API endpoint should be configured to handle user authentication.

  3. Condition - Check Login Status: After authenticating the user, the flow checks the response from the API to determine if the login was successful or not. If the login is successful, the flow continues to the next step. If the login fails, the flow sends a notification to Teams indicating the login failure.

  1. Update Database with Hash: If the login is successful, the flow proceeds to send a POST request to the database API, including the JSON file with the generated hashes as the payload. The API endpoint should be configured to receive the JSON file and update the database accordingly.

  2. Condition - Check Update Status: After updating the database with the hashes, the flow checks the response from the API to determine if the update was successful or not. If the update is successful, the flow continues to the next step. If an error occurs during the update, the flow sends a notification to Teams indicating the error.

  1. Notification to Process Owner: After the database is successfully updated with the new hashes, or in case of login or update errors, the process owner receives a notification in Teams. This notification serves as an acknowledgment of the login status and database update status, providing confirmation of the process completion or error.

  1. After successfully updating the database with the generated hashes, the flow triggers the next flow to send emails based on the updated information.

By adding the user login to the database API and incorporating error notifications, the flow ensures secure access to the database and provides real-time updates on the login and update status. This enhances the overall reliability and visibility of the data management process within the Skill Collector app.

Flow # 3 (Sending Emails and Notifications)

Workflow Design

This flow is triggered when an HTTP request is received from Flow #2. The purpose of this flow is to send emails containing the generated hash and a link to the Skill Collector app.

Flow # 3 Sending Emails and Notifications:

Flow # 3 Logic

Descriptions

Trigger: HTTP Request

This flow is triggered when an HTTP request is received. The request is sent by clicking the "SEND" button in the Excel sheet.

  1. When the HTTP request is received, the flow starts.

  2. List rows where the Send column is set to True (checkbox is ticked).

  3. The flow checks the value in the "Send" column of the Excel sheet.

  • If no people are selected (Send column is false for all rows), the flow sends a notification to the Teams chat, informing that no emails were sent.

  • If the value is true, the flow proceeds.
  1. Check if the Hash column is not empty.
  • If the 'Hash' column is not empty:
    1. Send an email to the person with the link and a hash.
    2. Notification to Process Owner. After the email is sent, the process owner receives a notification specifying to whom the emails were sent. This notification provides an overview of the recipients and ensures transparency in the email communication process.
  • If the 'Hash' column is empty:
    1. Send a notification to the Teams channel indicating that the email failed to be sent to the person because the hash is not created.

This flow ensures that emails are sent to the intended recipients who have the 'Send' column set to 'True' and a non-empty 'Hash' column. For any cases where the 'Hash' column is empty, a notification is sent to the Teams channel indicating the failure to send the email.

Note: Make sure to configure the necessary email and Teams connectors in Power Automate to enable the sending of emails and notifications to the respective recipients and channels.

Integration with Other Systems

Power Automate plays a crucial role in integrating various systems and services within the Skill Collector app. It enables seamless data transfer and communication between different components, ensuring a smooth and efficient workflow. The following are the key integrations within the app:

  1. Integration with Forms: Power Automate integrates with Forms to capture responses. The responses collected through Forms are automatically processed by Power Automate to extract relevant data such as name, email, and company.

  2. Integration with Excel: Power Automate integrates with Excel to store and manage the collected data efficiently. When a new response is received from Forms, Power Automate updates the Excel sheet by adding a new row or updating existing rows with the responder's information. It also performs checks for duplicate emails, ensuring that each entry in the Excel sheet is unique.

  3. Integration with Email: Power Automate leverages email services to send personalized invitations and notifications to the users of the Skill Collector app. Additionally, notifications are sent to the Process Owner when new responses are received or when the emails are sent to the users.

  4. Integration with Teams: Power Automate integrates with Teams to provide real-time notifications and updates to the process Owner. Notifications about new responses, email sending status, and other important events are sent to a designated Teams channel using a Power Automate bot.

  5. Integration with the PostgreSQL Database: Power Automate facilitates the integration with the database used by the Skill Collector app. When the Send button is clicked in the Excel sheet, Power Automate updates the database with the generated hashes, ensuring the app has an up-to-date record of user information.

The integration of Power Automate with other systems in the Skill Collector app enhances its functionality, automates data management tasks, and ensures a reliable and efficient user experience.

Security and Permissions

When using Power Automate within the app, there are several security considerations and permissions to be aware of.

  1. Access Control Measures: Power Automate ensures data confidentiality and integrity through various access control measures. These include:

  • Authentication: Users accessing the Skill Collector app and initiating the flow through the HTTP request are required to authenticate with their Office 365 credentials. This helps verify their identity and ensures that only authorized individuals can trigger the flow.

  • Role-Based Access Control: Within the Office 365 environment, appropriate role-based access control measures should be implemented. This ensures that only users with the necessary permissions can access and modify the Excel sheet or interact with the database.

  • API Connection Security: When establishing the API connection to the database, it's essential to configure appropriate security measures. This may involve using secure protocols (such as HTTPS), implementing authentication mechanisms (such as API keys or OAuth), and enforcing data encryption during transit.

  1. Data Confidentiality: The Skill Collector app and Power Automate prioritize data confidentiality by securely handling the generated hashes and related information. This includes:
  • Compliance Standards: Compliance with relevant data protection regulations, such as GDPR (General Data Protection Regulation) or industry-specific standards, should be considered. Adhering to these standards helps maintain data confidentiality and protects user privacy.

  1. Premium Subscription: The use of Power Automate in this scenario may require a premium subscription. It's important to ensure that the appropriate subscription level is in place to access the necessary features and capabilities required for this automation process.

  2. Ongoing Monitoring and Maintenance: Regular monitoring and maintenance of the Skill Collector app, Power Automate flows, and associated security measures are crucial. This helps identify and address any potential vulnerabilities or unauthorized access attempts promptly.