Building a Power Automate Workflow for Monitoring Red-Formatted Cells in SharePoint Lists

Introduction

In this guide, you will learn how to create a Power Automate workflow to monitor SharePoint lists for red-formatted cells, extract relevant data, and send email notifications.

Prerequisites

• Access to SharePoint Online.
• Access to Microsoft Power Automate.
• A SharePoint list with cells formatted in red.

Step-by-Step Instructions

Step 1: Create a Flow in Power Automate

1. Navigate to Power Automate:

   • Open your browser, go to Power Automate, and sign in.

2. Create a New Flow:

   • Click on Create on the left sidebar.
   • Select Automated flow.

3. Set Up Trigger:

   • In the Build an automated flow dialog, name your flow (e.g., "Monitor Red Cells in SharePoint List").
   • Under Choose your flow's trigger, search for When an item or a file is modified and select it.
   • Click Create.

4. Configure the Trigger:

   • Choose the Site Address and List Name from the available SharePoint sites and lists.

Step 2: Initialize Variables to Track Red Cells

1. Add a New Step:

   • Click on + New step.
   • Search for Initialize variable and select it.

2. Configure the Variable:

   • Set the Name to RedCellValues.
   • Set the Type to Array.

Step 3: Retrieve and Check Cell Formatting

1. Add a New Step:

   • Search for Get items and select it under the SharePoint connector.

2. Configure the Get items Action:

   • Set the Site Address and List Name to match your SharePoint list.

3. Add an Apply to Each Control:

   • Click on + New step.
   • Search for Apply to each and select it.

4. Configure the Apply to Each:

   • Set its output to the value of your Get items action (e.g., value).

5. Check for Red Formatting:

   • Inside the Apply to Each control, add a Condition.

6. Configure the Condition:

   • In the first box, select the relevant cell properties (field name likely containing the color information) from the output.
   • Set to equals and choose a red identified by its color code (e.g., #FF0000 for HTML red).

Step 4: Collect Red Cell Data

1. Add a New Step inside the If Yes branch of the Condition:

   • Search for Append to array variable.

2. Configure the Append to array variable Action:

   • Set the Name to RedCellValues.
   • Set the Value to extract the necessary data from the red cell (could be the content of the cell itself).

Step 5: Send Email Notification

1. Add a New Step after the Apply to Each loop:

   • Search for Send an email (V2) and select it from the Outlook connector.

2. Configure the Email Notification:

   • Set the To field to the intended recipients.
   • Set the Subject appropriately (e.g., "Alert: Red-Formatted Cells Detected").
   • In the Body, include the array variable RedCellValues. You might need to format this data as needed.

Final Workflow Summary

1. Trigger the flow when an item in the SharePoint list is modified.
2. Initialize an array variable to store the values of the red-formatted cells.
3. Retrieve the items from the SharePoint list and iterate through each item.
4. Check if the cell has red formatting.
5. Collect data from red-formatted cells.
6. Send an email with the details.

Conclusion

By following this step-by-step guide, you can set up a Power Automate workflow that monitors for red-formatted cells in a SharePoint list, extracts the relevant data, and sends email notifications to the desired recipients.

Building and Scheduling a Power Automate Flow

Here is a detailed step-by-step guide to building a Power Automate workflow for monitoring SharePoint lists for red-formatted cells, extracting relevant data, and sending email notifications.

Step 1: Create a New Flow

1. Navigate to https://flow.microsoft.com and sign in with your credentials.
2. Click on Create > Automated cloud flow.
3. Name your flow (e.g., "Monitor SharePoint Red Cells") and choose a trigger. Select "When an item is created or modified" from the SharePoint connector.

Step 2: Connect to the SharePoint List

1. Select your SharePoint site from the dropdown or enter the site URL.
2. Select the relevant list from the dropdown.

Step 3: Initialize Variables

1. Add a new step, then choose Initialize variable from the actions.
   • Name: redCells
   • Type: Array
   • Value: Leave blank

Step 4: Apply to Each - Iterate Over the List Items

1. Add a new step, then choose Get Items from the SharePoint connector.
   • Site Address: Your SharePoint site URL
   • List Name: Your SharePoint list
2. Add a new step, then choose Apply to each from the control actions.
   • Select the output from Get Items (value).

Step 5: Extract Cells with Red Formatting

1. Inside the Apply to each, add a new step, then choose Get item from the SharePoint connector.
   • Site Address: Your SharePoint site URL
   • List Name: Your SharePoint list
   • Id: ID (from the Apply to each context)
2. Add a condition to check if the cell background color is red (this assumes you have a column capturing cell color).
   • Condition: Choose your column that stores cell background color.
   • Operator: Equals
   • Value: #FF0000 (red color in hexadecimal)

Step 6: Append Red Data to Variable

1. If the condition is true, add an action to append to the array variable.
   • Variable: redCells
   • Value: @{triggerBody()?['ColumnForDataYouNeed']} (replace ColumnForDataYouNeed with the column name that stores your data)

Step 7: Send Email Notification

1. Outside the Apply to each loop, add a new step, then choose Send an email (V2) from the Office 365 Outlook connector.
2. Configure the email:
   • To: Your target email address
   • Subject: Red Cell Alert in SharePoint List
   • Body: You can use the redCells variable to include the relevant data. Use expressions like join(variables('redCells'), ', ') to format the data in the email body.

Step 8: Save and Test the Flow

1. Click on Save to save your flow.
2. Click on Test and follow the steps to ensure everything is working.
3. Monitor the run history to check for any errors and ensure emails are triggered appropriately.

Scheduling the Flow

Power Automate inherently triggers on SharePoint item creation or modification, but if you need to schedule the flow:

1. Navigate to the trigger of the flow.
2. Add a new step at the top and select the Recurrence trigger from the Schedule connector.
3. Configure the recurrence settings (e.g., every hour, daily, etc.).

This setup ensures the flow will run on a predefined schedule, checking for updated or newly created items.

Conclusion

This guide provides you with a comprehensive implementation for building and scheduling a Power Automate flow that monitors a SharePoint list for red-formatted cells, extracts relevant data, and sends email notifications. Apply these steps as they are provided to achieve your workflow automation goals.

Example Email Body Expression

join(variables('redCells'), ', ')

Use the provided Markdown snippet to format your email body dynamically using the redCells variable.

Configuring Actions for Data Extraction in Power Automate

In this section, we will focus on configuring actions within Power Automate to monitor a SharePoint list for cells formatted in red and extracting the relevant data. After extraction, we will set up the workflow to send email notifications.

Step-by-Step Configuration

1. Set Up a Trigger for Monitoring Changes

• Trigger: Use the "When an item is created or modified" trigger and select your SharePoint site and list (configured in earlier sections).

2. Initialize Variables

• Action: Initialize a variable for storing red-formatted cell values.

  {
    "type": "Initialize variable",
    "name": "RedFormattedCells",
    "dataType": "Array",
    "initialValue": []
  }

3. Apply a Condition to Identify Red-Formatted Cells

• Action: Add a condition to check for red formatting. In this example, we assume there's a known field RedFormattedColumn to identify cells.

  {
    "type": "Condition",
    "input": {
      "left": "@triggerOutputs()?['body/RedFormattedColumn']",
      "operator": "equals",
      "right": "Red"
    },
    "actions": {
      "ifTrue": [
        {
          "type": "Append to array variable",
          "name": "AppendRedCell",
          "data": "@triggerOutputs()?['body/FieldValue']"
        }
      ],
      "ifFalse": []
    }
  }

4. Extract Relevant Data

• Action: Use the "Get item" action to fetch details of the modified item.

  {
    "type": "Get item",
    "SharePointSiteURL": "[Your SharePoint URL]",
    "ListName": "[Your List Name]",
    "ID": "@triggerOutputs()?['body/ID']"
  }

5. Format the Email Body

• Action: Compose an email body with the extracted data.

  {
    "type": "Compose",
    "input": "Item ID: @triggerOutputs()?['body/ID']\nRed-formatted cell values: @variables('RedFormattedCells')"
  }

6. Send Email Notification

• Action: Configure the "Send an email" action to notify the relevant stakeholders.

  {
    "type": "Send an email",
    "recipients": ["recipient@example.com"],
    "subject": "Alert: Red Formatted Cells Detected in SharePoint List",
    "body": "@outputs('Compose')"
  }

Complete JSON Example

Here’s a JSON representation of the flow configuration, excluding the initial setup steps like creating the list integration:

{
  "Trigger": {
    "Name": "When an item is created or modified",
    "Parameters": {
      "Site Address": "[Your SharePoint URL]",
      "List Name": "[Your List Name]"
    }
  },
  "Actions": [
    {
      "InitializeVariable": {
        "Name": "RedFormattedCells",
        "Type": "Array",
        "Value": []
      }
    },
    {
      "Condition": {
        "LeftValue": "@triggerOutputs()?['body/RedFormattedColumn']",
        "Operator": "equals",
        "RightValue": "Red",
        "IfTrueActions": [
          {
            "AppendToArrayVariable": {
              "Name": "RedFormattedCells",
              "Value": "@triggerOutputs()?['body/FieldValue']"
            }
          }
        ],
        "IfFalseActions": []
      }
    },
    {
      "GetItem": {
        "Site Address": "[Your SharePoint URL]",
        "List Name": "[Your List Name]",
        "ID": "@triggerOutputs()?['body/ID']"
      }
    },
    {
      "ComposeEmail": {
        "Input": "Item ID: @triggerOutputs()?['body/ID']\nRed-formatted cell values: @variables('RedFormattedCells')"
      }
    },
    {
      "SendEmail": {
        "To": "recipient@example.com",
        "Subject": "Alert: Red Formatted Cells Detected in SharePoint List",
        "Body": "@outputs('ComposeEmail')"
      }
    }
  ]
}

Conclusion

This step-by-step guide demonstrates how to configure Power Automate to monitor a SharePoint list for red-formatted cells, extract relevant data, and send email notifications accordingly. By following these structured actions, you can implement an automated process that responds efficiently to the conditions set within your SharePoint environment.

Automating Email Notifications with Outlook using Power Automate

Overview

This guide will help you automate email notifications with Outlook when specific conditions are met in a SharePoint list. The automated workflow will monitor the SharePoint list for red-formatted cells, extract relevant data, and send email notifications using Power Automate.

Detailed Steps

Step 1: Trigger Configuration

1. Go to Power Automate and create a new Flow.

2. Select "When an item is created or modified" as the trigger and connect it to your SharePoint list.

   • Site Address: Choose your SharePoint site from the dropdown.
   • List Name: Select your SharePoint list.

Step 2: Condition to Identify Red-Formatted Cells

To detect red-formatted cells, we'll use a column in SharePoint to indicate items that need attention.

1. Add a new Condition action.

   • Expression:

     if empty(items('Apply_to_each')?['ColumnName']) or condition_to_check_red_format

   • For example, if the column "Status" equals "Urgent", which is marked by the red formatting:

     equals(items('Apply_to_each')?['Status'], 'Urgent')

Step 3: Extract Relevant Data

1. Within the Condition, in the If yes branch, add the Get item action to retrieve the item's details.

   • Site Address: Select your SharePoint site.
   • List Name: Select your SharePoint list.
   • Id: Use the dynamic content ID from the trigger.

Step 4: Send Email Notification

1. Add a Send an email (V2) action within the If yes branch to send an email notification using Outlook.

   • To: Add the recipient email address. You can use dynamic content from the SharePoint item if the email is stored there.
   • Subject: Construct an appropriate subject line. For example, "Alert: Urgent Item in SharePoint List".
   • Body: Use dynamic content from the retrieved item. For instance:

     Hello,
     The following SharePoint item needs your attention:
     
         
     Title: @{items('Apply_to_each')?['Title']}
         
     Description: @{items('Apply_to_each')?['Description']}
         
     Status: @{items('Apply_to_each')?['Status']}
     
     Regards,
     Your Team

Complete Workflow

Below is a summary of the workflow:

1. Trigger: ['When an item is created or modified']
2. Condition: Check for 'Urgent' status (indicated by red format).
3. Get item: Retrieve item details.
4. Send an email (V2): Compose and send an email notification with the retrieved item details.

This setup will ensure that whenever an item in your SharePoint list is marked with red formatting (e.g., status 'Urgent'), an automated email notification will be sent using Outlook.

By following these steps, you'll have a fully functional Power Automate workflow that monitors your SharePoint list and sends notifications when necessary conditions are met.

Testing, Validation, and Optimization Techniques for Power Automate Workflow

This section covers practical methods for ensuring the reliability and efficiency of your Power Automate workflow that monitors SharePoint lists and automates email notifications based on red-formatted cells.

Testing the Workflow

Unit Testing

1. Create Test Cases: Define a set of test cases that cover different scenarios, e.g., cells turning red, no cells turning red, and multiple cells turning red.
2. Use Sample Data: Prepare a SharePoint list with entries that can simulate these scenarios.
3. Run Workflow Manually: Execute the Power Automate flow manually using these sample data entries.
4. Check Output: Verify that emails are sent only for rows where cells are red-formatted and that the content is correct.

Runtime Logs

1. Enable Logging: Use the 'Compose' and 'Append to array variable' actions in Power Automate to log key steps in your workflow.
2. Log Details: Include details such as list item ID, column name, color state, time of change, and email success or failure status.

Error Handling

1. Configure Error Handling: For each critical action, especially for data extraction and sending emails, use the 'Configure run after' setting to specify what should happen if an action fails.
2. Fallback Mechanism: Implement a fallback mechanism that logs errors and notifies an administrator.

Validation Techniques

Validation Rules

1. Data Validation: As soon as data is extracted from the SharePoint list, use condition actions to validate the data.

   if (cellColor == 'red'):
       proceed with processing
   else:
       skip

2. Email Validation: Validate email addresses to ensure emails are sent to the correct recipients.

   if (isValidEmail(recipientEmail)):
       send email
   else:
       log error

End-to-End Testing

Perform full run-through tests to ensure the entire workflow works correctly from starting the flow to receiving email notifications.

trigger(flowStart)
extract(data)
validate(data)
if (dataValid):
    format(emailContent)
    send(email)

Optimization Techniques

Reduce API Calls

1. Batch Processing: If applicable, batch process SharePoint list items to minimize the number of API calls.

   for (items in batch):
       process(item)

2. Conditional Actions: Use conditions and switch cases to avoid unnecessary actions.

   switch(cellColor):
       case 'red':
           send notification
       case 'non-red':
           continue

Optimize Data Fetching

1. Use Filters: Apply OData filters to queries to fetch only the necessary data.

   filter=query?color eq 'red'

Minimize Workflow Duration

1. Parallelism: Where appropriate, use parallel branches in Power Automate to execute independent actions simultaneously.

2. Delay and Retry: Use delay functions and retry policies to handle transient errors gracefully and reduce impact on performance.

   if (actionFails):
       delay(retryInterval)
       retry(action)

3. Limit Scope: Fine-tune the scope of your flow to handle only relevant data changes.

By following these Testing, Validation, and Optimization Techniques, you can ensure that your Power Automate workflow for monitoring SharePoint lists is reliable, accurate, and efficient.

---

Remember, these techniques are geared towards practical application; ensuring that any issues are identified, handled gracefully, and workflows are optimized for better performance and durability.

Power Automate Workflow Implementation for Red-Formatted Cells

Step 7: Including RedCellValues in the Email Body

Objective: Include the array variable RedCellValues in the body of an email notification within Power Automate.

Prerequisites

• A SharePoint list is already configured and monitored.
• Relevant data with red formatting is identified and stored in the variable RedCellValues.

Implementation

1. Access the Power Automate Workflow: Open your existing Power Automate workflow where you scan the SharePoint list and gather the red-formatted cells data.

2. Add an Email Notification Action:

   • Navigate to the next step in your workflow where the email notification is to be triggered.
   • Click on + New step.
   • Search for Send an email (V2) and select it.

3. Configure the Email Action:

   • To: Enter the recipient's email address or your desired recipient list.
   • Subject: Provide your preferred subject line.

4. Insert RedCellValues into the Email Body: In the Body section, include the array variable RedCellValues as follows:

   Here is a pseudocode example to guide you on formatting the email body, considering the values are extracted from the array and concatenated into a string:

   // Convert RedCellValues array to a string with each value on a new line
   string emailBodyContent = "The following cells are red-formatted:\n";
   for each value in RedCellValues:
       emailBodyContent += value + "\n";
   
   // Use emailBodyContent in the email body
   Email Body: "Below are the red-formatted cells found in the SharePoint list:\n" + emailBodyContent

5. Example Configuration: Here is what it might look like in the Power Automate visual designer:

   • To: recipient@example.com
   • Subject: Alert: Red-Formatted Cells Detected in SharePoint List
   • Body:

   Below are the red-formatted cells found in the SharePoint list:
   @{
     join(outputs('RedCellValues'), '

') } ```

6. Save and Test the Workflow:
   • Save your flow.
   • Test the flow by triggering it based on your pre-defined conditions to ensure that the email contains the values from RedCellValues.

By following these steps, you will be able to dynamically include the values of the red-formatted cells in an email notification sent from Power Automate. This ensures stakeholders are informed with the precise data condition monitored from the SharePoint list.

Step-by-Step Guide for Monitoring SharePoint Lists with Power Automate

Introduction

This guide helps you build a Power Automate workflow to monitor SharePoint lists for red-formatted cells, extract relevant data, and send email notifications.

Prerequisites

• You have a SharePoint list created.
• You have access to Power Automate.

Workflow Overview

1. Trigger the Flow: Start the workflow when an item is created or modified in the SharePoint list.
2. Get List Items: Fetch items from the SharePoint list.
3. Check Formatting: Find items with red-formatted cells.
4. Extract Data: Retrieve relevant data from the formatted cells.
5. Send Email Notifications: Email the extracted data.

Detailed Steps

Step 1: Trigger the Flow

1. Open Power Automate and click on Create > Automated Flow.

2. For the trigger, select When an item is created or modified.

3. Provide the necessary information:

   • Site Address: Your SharePoint site URL.
   • List Name: Your specific SharePoint list name.

Step 2: Get List Items

1. Click on + New Step.

2. Select Get items from the SharePoint connector.

3. Configure:

   • Site Address: Your SharePoint site URL.
   • List Name: Your specific SharePoint list name.

Step 3: Check Formatting

1. Click on + New Step.

2. Select Apply to each to iterate through items.

3. Inside Apply to each, add an action Condition to check for red formatting. Since Power Automate doesn't natively support cell formatting, use a different flag in your SharePoint list indicating red cells:

   • If yes: Based on your specific flag condition.

4. Configuration for Condition:

   • Choose value: Select value from the SharePoint item representing red format.
   • Set the condition (e.g., equals to "Red").

Step 4: Extract Data

1. Inside the If yes branch of Condition, add action to extract data.

2. Add Compose action to prepare email content using dynamic content from the item.

Step 5: Send Email Notifications

1. Inside the If yes branch, add Send an email (V2) from the Outlook connector.

2. Configure:

   • To: Email recipient.
   • Subject: "Red Formatted Cells Detected".
   • Body: Use dynamic content and Compose output.

Summary

This workflow will monitor your SharePoint list for items flagged (indicating red-formatted cells), extract relevant details, and send email notifications. Implementing this guide step-by-step will help automate your process efficiently.

Follow each of these steps in Power Automate, ensuring to double-check dynamic content and conditions based on your list's specifics.

Part 9: Implementing the Filter Condition in Power Automate

In this section, you will implement a filter condition in Power Automate to monitor a SharePoint list. The criteria involve the following conditions:

• HSSE Risk level must be either 'High' or 'Medium'.
• Avetta Compliance Status must be either 'Red' or 'Amber'.

Steps

1. Create an Automated Flow:

   • Trigger: "When an item is created or modified" in the SharePoint list.

2. Add a Condition:

   • Navigate to the "New step" and choose the "Condition" action.

Here is the filter condition in Power Automate Format:

((HSSE_x0020_Risk eq 'High' or HSSE_x0020_Risk eq 'Medium')) and ((AvettaComplianceStatus eq 'Red' or AvettaComplianceStatus eq 'Amber'))

3. Implementing the Condition:

   • Add the first "Or" condition for HSSE_x0020_Risk:

     • Left side: Choose HSSE_x0020_Risk from Dynamic content.
     • Operator: equals.
     • Right side: Enter High.

   • Click on + Add.

     • Left side: Choose HSSE_x0020_Risk again.
     • Operator: equals.
     • Right side: Enter Medium.

   • Add the second "Or" condition for AvettaComplianceStatus:

     • Left side: Choose AvettaComplianceStatus from Dynamic content.
     • Operator: equals.
     • Right side: Enter Red.

   • Click on + Add.

     • Left side: Choose AvettaComplianceStatus again.
     • Operator: equals.
     • Right side: Enter Amber.

4. Group Conditions:

   • Group the first two conditions (HSSE_x0020_Risk) using the "Or" operator.
   • Group the second two conditions (AvettaComplianceStatus) using the "Or" operator.
   • Finally, group these two sets using the "And" operator.

5. Email Notification:

   • If the condition is true:
     • Action: Send an email.
     • Specify the recipient, subject, and body in the email.

Below is the schematic representation in the condition builder in Power Automate:

Condition Action:

( (HSSE_x0020_Risk eq 'High' or HSSE_x0020_Risk eq 'Medium') and (AvettaComplianceStatus eq 'Red' or AvettaComplianceStatus eq 'Amber') )

If true: - Send an email with the respective details.

Implementation

Insert the described conditions as shown in Power Automate's interface to filter items based on the criteria:

1. Select condition logic:

   • Logic split:
     • Group 1: (HSSE_x0020_Risk eq 'High' or HSSE_x0020_Risk eq 'Medium')
     • Group 2: (AvettaComplianceStatus eq 'Red' or AvettaComplianceStatus eq 'Amber')

2. Final Grouping:

   • Final grouping using "And" operator for the above groups.

3. Email Notification Action:

   If group 1 and group 2 are true, execute the "Send an email" action.

This detailed step ensures the workflow in Power Automate is correctly configured to monitor the specific SharePoint list conditions and send out an email notification when the criteria match.

Power Automate Workflow for Filtering Red-Formatted Cells

Objective

Create a Power Automate flow that retrieves items from a SharePoint list where any cell is formatted with the color #FF0000 (red) and sends an email notification with the relevant data.

Implementation Steps

1. Trigger:

   • Use the Recurrence trigger to periodically check the SharePoint list.
   • Set the interval according to your needs (e.g., daily, weekly).

2. Get Items from SharePoint:

   • Use the Get items action to retrieve all items from the target SharePoint list.

3. Initialize a Red Items Array:

   • Use Initialize variable to create an array variable (e.g., RedItemsArray) that will hold the items with red-formatted cells.

4. Apply to Each Item:

   • Add an Apply to each action to loop through each item retrieved from the SharePoint list.

5. Check for Red-Formatted Cells:

   • Inside the loop, use a Condition to check if any column in the current item is formatted with #FF0000.

   • Unfortunately, Power Automate does not natively support checking cell formatting directly, so this functionality typically requires custom columns in SharePoint that signal when a cell is formatted red.

   • Assuming there is a status column (StatusColor) in your SharePoint that indicates the cell color:

     If `StatusColor` is equal to `#FF0000`

   • If matched, use the Append to array variable action to add the item to the RedItemsArray.

6. Send Email Notification:

   • After the loop concludes, add a Condition to check if the RedItemsArray contains any items.
   • If true, use the Send an email (V2) action to send an email notification with the details of the red-formatted cells.

Sample JSON Structures

By using a custom column StatusColor that flags red cells in SharePoint, the sample pseudocode implementation inside Power Automate will look like this:

Recurrence Trigger:
  Frequency: Daily

Get items:
  Site Address: 
  List Name: 

Initialize Variable:
  Name: RedItemsArray
  Type: Array
  Value: []

Apply to each:
  From: value (output from Get items)
  Actions:
    Condition:
      Condition: @equals(items('Apply_to_each')?['StatusColor'], '#FF0000')
      If true:
        Append to array variable:
          Name: RedItemsArray
          Value: items('Apply_to_each')

Condition:
  Condition: @not(empty(variables('RedItemsArray')))
  If true:
    Send an email (V2):
      To: 
      Subject: Items with Red-Formatted Cells
      Body: |
        The following items contain red-formatted cells: 
        @{variables('RedItemsArray')}

Explanation

• Trigger: Defines when the flow runs.
• Get items: Retrieves all items from the SharePoint list.
• Initialize Variable: Prepares an array to hold items with red-formatted cells.
• Apply to each: Iterates through each item.
• Condition: Checks for red formatting in a designated 'StatusColor' column.
• Append to array variable: Adds the item to the array if it meets the condition.
• Condition: Verifies if the array contains any items.
• Send an email (V2): Sends an email listing all items with red-formatted cells.

You can now apply this Power Automate flow to monitor and handle your SharePoint list data accordingly.

Power Automate Workflow for Monitoring Red-Formatted Cells in SharePoint Lists

Step 1: Create a Flow in Power Automate

1. Go to Power Automate (flow.microsoft.com).
2. Click on Create and choose Automated flow.
3. Name your flow and select the When an item is created or modified trigger.
4. Select the SharePoint site address and list name.

Step 2: Initialize Variables

1. Add a new Initialize variable action.
   • Name: redCellsExist
   • Type: Boolean
   • Value: false

Step 3: Get List Item Details

1. Add a Get item action.
   • Site Address: Select your SharePoint site.
   • List Name: Select your SharePoint list.
   • Id: ID

Step 4: Parse HTML Content to Check Cell Formatting

1. Add a Compose action.

   • Input: Outputs('Get_item')?['body']?['MultiLineTextField']

     Replace MultiLineTextField with the actual field name containing your formatted content.

2. Add a Parse HTML step (since Power Automate doesn't have built-in HTML parsing, you might consider using an Azure Function or another service here).

Azure Function (Pseudocode):

function run(req) {
    let itemContent = req.body.content;
    // Regex to detect red cells
    let redCellPattern = /style="[^"]*background-color:\s*red[^"]*"/i;
    
    return {
        status: 200,
        body: redCellPattern.test(itemContent)
    };
}

Step 5: Set Variable Based on HTML Parsing Result

1. Add an HTTP request action to call the Azure Function.

   • URI: https://<yourfunctionapp>.azurewebsites.net/api/YourFunction
   • Method: POST
   • Body: { "content": @{outputs('Compose')} }

2. Add a Condition action to set the variable based on the function result.

   • Expression: @outputs('HTTP')?['body']
   • If true, set redCellsExist to true.

Step 6: Send Email if Red Cells are Found

1. Add a Condition action.

   • Condition: @equals(variables('redCellsExist'), true)

2. Under the if yes branch, add a Send an email (V2) action.

   • To: Specify recipient emails.
   • Subject: Red Cells Found in SharePoint List
   • Body: Describe the issue and include relevant details.

Step 7: Save and Test

1. Save the workflow.
2. Test the flow by creating or modifying an item in the SharePoint list that includes red-formatted cells.
3. Verify that the email notification is sent when red cells are detected.

Conclusion

This workflow leverages Power Automate in combination with an external Azure Function for HTML parsing to monitor SharePoint list items for cells formatted in red and send email notifications accordingly.

12. Add a Compose Action in Power Automate

In this step, we will instruct Power Automate to use the content of a specific field from a SharePoint list item. This content is typically stored in a "MultiLineTextField" in a SharePoint list.

Implementation:

1. Create a Compose Action

   • In your Power Automate flow, add a new action by clicking on the "+ New step" button.
   • Search for "Compose" and select the "Compose - Data Operations" action.

2. Configure the Compose Action

   • In the "Compose" action, you need to provide the input data. You will pull this data from the output of a previous step where you retrieved the SharePoint item. Let’s say, the step to get the SharePoint item is named "Get_item".

   • Replace MultiLineTextField with the actual field name from your SharePoint list. For example, if the field containing the formatted content is named Description, you should use it like this:

   Inputs: `Outputs('Get_item')?['body']?['Description']`
   

   • Your "Compose" action configuration should look something like this:

   {
       "inputs": "@outputs('Get_item')?['body']?['Description']"
   }
   

Explanation:

• Outputs('Get_item'): This refers to the output of a previous action named "Get_item".
• ['body']: This accesses the body of the response from the "Get_item" action.
• ['Description']: This accesses a specific field within the body called Description. Replace Description with the appropriate field name from your SharePoint list that contains the multi-line text or formatted content.

Application in Real Life:

1. Ensure your SharePoint list has a field that can store the content you need to work with in the flow.
2. Retrieve the SharePoint list item in a previous step within your Power Automate flow.
3. Use the configured "Compose" action as shown above to extract this specific field's content.
4. You can now use this content in subsequent steps of your Power Automate workflow, such as sending an email notification.

Following the above steps will allow you to dynamically pull the field content from your SharePoint list and use it within your Power Automate flow efficiently.

Part 13: Extracting Red Formatted Cells in Power Automate

Step 1: Create a Flow to Monitor SharePoint Lists

1. Trigger: Use the "For a selected row" action or a "Recurrence" trigger depending on your preferred scheduling.

Step 2: Get the Items from SharePoint List

1. Action: Add "Get items" action from SharePoint.
   • Site Address: <Your SharePoint Site>
   • List Name: <Your List Name>

Step 3: Initialize Variables

1. Action: "Initialize variable" to store the extracted red-formatted cell data.
   • Name: RedFormattedCells
   • Type: Array
   • Value: []

Step 4: Loop Through Each Item

1. Action: Add "Apply to each" action.
   • Input: Value from the output of "Get items" action.

"Apply_to_each": {
  "type": "foreach",
  "inputs": "Value from Get items"
}

Step 5: Process Each Column

1. Action: Inside the loop, use the "Get item" action to fetch detailed data for the current item.

   • Site Address: <Your Site Address>
   • List Name: <Your List Name>
   • Id: ID from current item.

2. Action: Add "Compose" action to evaluate each column's cell formatting. Use an expression or script if necessary to determine the cell's format.

"Compose": {
  "type": "compose",
  "inputs": {
    "scss": "{ 'background-color': 'red', 'details': itemFromGetItemAction }"
  }
}

Step 6: Conditional Check for Red Formatted Cells

1. Action: Add a "Condition" to check if the cell is red formatted.
   • Condition: evaluate the cell’s background color.

"Condition": {
  "conditions": [
    {
      "value": "@equals(triggerOutputs()?['body/BackgroundColor'], 'red')",
      "target": true
    }
  ]
}

Step 7: Append to Variable

1. Action: If true, append the cell data to the RedFormattedCells variable.

"Append_to_array_variable": {
  "name": "RedFormattedCells",
  "value": "Current item details"
}

Step 8: Send Email Notification

1. Action: Add "Send an email (V2)" action.
   • To: Email Address
   • Subject: "Red Formatted Cells Detected"
   • Body: Construct the body to include the contents of RedFormattedCells variable.

"Send_an_email_(V2)": {
  "To": "Email Address",
  "Subject": "Red Formatted Cells Detected",
  "Body": "@{variables('RedFormattedCells')}"
}

Flow End

This completes the flow to monitor SharePoint lists for red-formatted cells, extract relevant data, and send email notifications.

7. Save and Test: Save your flow and test it to ensure it works as expected.

This setup ensures that the Power Automate workflow will accurately extract red-formatted cells from all columns in the spreadsheet present in a SharePoint list and send relevant notifications.

Full Path in Power Automate

In Power Automate, the "Full Path" refers to the complete hierarchical path to a specific file or list item in a SharePoint site. This can be particularly useful when you need to identify or manipulate items in a SharePoint document library or list.

Practical Example: Monitoring SharePoint Lists for Red-Formatted Cells, Extracting Relevant Data, and Sending Email Notifications

Step-by-Step Implementation

Step 1: Setup the Trigger for Monitoring SharePoint Lists

1. Create a new flow.

2. Choose the trigger: "When an item or file is modified" from SharePoint.

   • Site Address: Select your SharePoint site.
   • Library Name: Choose the relevant list or document library you want to monitor.

Step 2: Retrieve the Full Path of the Modified Item

1. Initialize Variables:
   • Add an action: "Initialize variable".
   • Name: FullPath
   • Type: String
   • Value: triggerOutputs()?['body/{Path}']

Step 3: Get the List Item or File Properties

1. Add a new action: "Get file metadata" or "Get item"

   • Site Address: Same as above
   • File Identifier / Item ID: Use the identifier from the trigger action.

2. Add another action: "Get file properties" if you selected "Get file metadata".

   • Site Address: Same as above
   • Library Name: Same as above
   • File Identifier: Use the identifier from the previous action.

Step 4: Extract Relevant Data

1. Add Data Operations - Compose action:

   • Name: Compose Data
   • Inputs: @triggerOutputs()?['body/FieldNameRedFormattedCell']

   Replace FieldNameRedFormattedCell with the actual field name you are monitoring for red formatting.

2. Condition Action:

   • To check if the cell is red-formatted based on your criteria.
   • If the condition is met, proceed to the next step.

Step 5: Send Email Notifications

1. Add an action: "Send an email (V2)"
   • To: Recipient@example.com
   • Subject: Alert: Red-Formatted Cell Detected
   • Body:

     Dear User,
     A red-formatted cell was detected in your monitored SharePoint list.
     Full Path: @{variables('FullPath')}
     Relevant Data: @{outputs('Compose_Data')}
     Regards,
     Power Automate Bot

Testing the Flow

Once the workflow is built, modify an item or file in the SharePoint list with a red-formatted cell. Wait for the flow to trigger and verify that the email notification is sent with the correct details.

Conclusion

By leveraging the "Full Path" in your Power Automate workflow, you can efficiently monitor specific elements in SharePoint, extract necessary data, and ensure relevant stakeholders are promptly notified. This facilitates better data management and enhances collaborative efforts across teams.

Guide to Building a Power Automate Workflow for Monitoring SharePoint Lists for Red-Formatted Cells, Extracting Relevant Data, and Sending Email Notifications

Unit 15: Uploading Documents or Snippets for Analysis

Creating a Flow in Power Automate

Follow these steps to create and execute a flow that allows users to upload documents or snippets to SharePoint, which can then be analyzed and used to send email notifications.

Step 1: Create a New Flow

1. Navigate to Power Automate.
2. Click on "Create" and choose "Automated flow."

Step 2: Select a Trigger

1. Select the "When a file is created (properties only)" trigger from SharePoint.
2. Configure the trigger to target your specific SharePoint library where documents or snippets are uploaded.

Step 3: Retrieve File Content and Metadata

1. Add a new step, choose "Get file content," and configure it with:

   • Site Address: Your SharePoint site URL.
   • File Identifier: Use the dynamic content from the trigger step.

2. Add another step, choose "Get file metadata," and configure it with:

   • Site Address: Your SharePoint site URL.
   • File Identifier: Use the dynamic content from the trigger step.

Step 4: Analyze Document Content

To implement custom analysis, add a "Condition" or a combination of "Compose" and "Expression" actions, depending on the specifics of your analysis requirements. For instance:

1. Add a "Compose" action that might use a pre-defined JSON schema or specific expressions to parse the content.
   • Example:

   {
       "fileContent": "@triggerOutputs()?['body']?['$content']",
       "fileName": "@triggerOutputs()?['body']?['{fileName}']"
   }

Step 5: Process Red-Formatted Cells

Assuming each document contains a table/format with specific rules:

1. Add an "Apply to each" action to iterate over the parsed table data.
2. Inside the loop, add a "Condition" to check for red-formatted cells.
   • Example Condition Expression:

   {
       "expression": "@equals(item()?['CellColor'], 'Red')"
   }

Step 6: Collect Relevant Data

1. Use actions like "Initialize variable" and "Append to array variable" to collect data.
   • Initialize example:

   {
       "name": "RelevantData",
       "type": "Array",
       "value": []
   }

   • Append example:

   {
       "name": "RelevantData",
       "value": "@concat(variables('RelevantData'), item()?['RelevantInformation'])"
   }

Step 7: Send Email Notifications

1. Add a "Send an email (V2)" action.
2. Configure the email using the collected data:
   • To: Recipients' emails (static or dynamic).
   • Subject: "Notification: Red-Formatted Cells Detected."
   • Body: Include the content of the "RelevantData" variable.

Example Email Body

Hello,
The following red-formatted cells were detected in the uploaded document:

  @{body('Append_to_array_variable')}

Best regards,
SharePoint Workflow Team

Conclusion

This flow will monitor for newly-uploaded documents in SharePoint, analyze them for red-formatted cells, extract relevant data, and send email notifications. Modify the flow steps to suit your specific project requirements and data format rules.

Part 15: Building a Power Automate Workflow for Monitoring SharePoint Lists

Objective

To create a Power Automate workflow that monitors a SharePoint list for red-formatted cells, extracts relevant data, and sends email notifications.

Implementation

Step 1: Create a Flow in Power Automate

1. Log in to Power Automate.
2. Create a new Automated Flow using the "When an item is created or modified" trigger for the SharePoint list you want to monitor.

Step 2: Configure the Trigger

1. Set up the SharePoint Site and List.

   Site Address: [Your SharePoint Site URL] List Name: [Your List Name]

Step 3: Initialize Variables

1. Add an "Initialize Variable" action to store the necessary data.

   Name: formattedCell Type: String Value: ''

Step 4: Get List Item Data

1. Add a "Get item" action to retrieve the item details when a change is detected.

   Site Address: [Your SharePoint Site URL] List Name: [Your List Name] Id: [ID from the Trigger]

Step 5: Apply Conditional Logic to Check Formatting

1. Add a "Condition" action.

   • Under "Choose a value", select the relevant cell.
   • Use the expression to determine if the cell is red.

     contains(triggerBody()?['ColumnName'], '')

2. On "Yes" branch, update the variable with relevant data.

   Set variable: formattedCell Value: [Data you want to extract, e.g., triggerBody()?['ColumnName']]

Step 6: Send an Email Notification

1. Add a "Send an email (V2)" action.
   • Set up your email parameters:

     To: [Recipient Email Address] Subject: "Alert: Red-Formatted Cell in SharePoint List" Body: "A cell in the SharePoint list has been formatted red. Details: Variable formattedCell"

Step 7: Save and Test the Flow

1. Save your flow and make some changes to your SharePoint list to trigger the flow and ensure it’s working as expected.

NB: Existing Content

Ensure not to duplicate any unit already covered in your project. This provided workflow focuses strictly on monitoring red-formatted cells and sending email notifications as outlined in Part 15.

By following this detailed guide, you should be able to implement a Power Automate workflow that effectively monitors a SharePoint list for red-formatted cells, extracts relevant data, and sends email notifications when such a cell is detected.