Automating File Uploads to Copilot Studio

Objective

The purpose of this task was to design and implement a Power Automate flow that retrieves files from a SharePoint site based on specific product names and uploads them directly into the knowledge source of Copilot Studio. This R&D effort aimed to automate and streamline the file management process, ensuring efficient file handling and integration into the bot's knowledge source.

Here is the Power Automate Flow Link - Power Automate Flow

Name of the Flow – Restricted_Search

Task Overview

Objective of the Flow

The flow automates the retrieval and upload of files related to specific product names from a SharePoint location to the knowledge source of Copilot Studio. The task involved:

1. Fetching files associated with a predefined list of product names.
2. Processing each file to extract relevant data dynamically.
3. Uploading the processed files directly into the bot's knowledge source.

Key Requirements

• Retrieve files from a specified SharePoint folder.
• Filter files based on predefined product names.
• Upload the files seamlessly into the Copilot Studio knowledge source.
• Ensure dynamic handling of file metadata, such as filenames and content.

Steps to Achieve the Task

1. Initialize Variable

• Action: Initialize a variable using the "Initialize Variable" action in Power Automate.
• Configuration:
  • Type: Array
  • Value: A predefined list of product names (e.g., ["MVR", "MVR Activity Files", "MVR DB 360", etc])
• Purpose: This variable provides the input for the subsequent loop, ensuring each product name is processed.

2. Apply to Each Loop (Outer Loop)

• Action: Apply to Each loop with the initialized array as input.
• Purpose: Iterates through each product name in the array to retrieve relevant files.

3. Get Files Properties Only

• Action: Use the "Get Files Properties Only" action.
• Configuration:
  • Site Address: SharePoint home site.
  • Library Name: Full path to the folder containing the files.
  • Filter Query: Products eq '<ProductName>' (dynamically set for each product name in the array).
• Purpose: Retrieves metadata for files whose Products property matches the product name in the array.

4. Apply to Each Loop (Inner Loop)

• Action: Apply to Each loop with the output (body/value) from the "Get Files Properties Only" action.
• Purpose: Processes each file retrieved in the previous step.

5. Compose Action

• Action: Use the "Compose" action.
• Configuration: Extract the file name with its extension dynamically using properties from the "Get Files Properties Only" action.
  • Example: @{items('Apply_to_each_1')?[‘FilenameWithExtentsion’]}
• Purpose: Dynamically retrieves the full filename (e.g., File1.pdf).

6. Get File Content

• Action: Use the "Get File Content" action.
• Configuration:
  • Site Address: Same SharePoint site as before.
  • File Identifier: Use the dynamic Identifier property from the "Get Files Properties Only" action.
• Purpose: Retrieves the binary content of the files identified in the previous steps.

7. Add a New Row to Selected Environment

• Action: Use the "Add a New Row" action.
• Configuration:
  • Environment: Select the target environment.
  • Schema Name: Dynamically constructed using the filename, sanitized to remove invalid characters, and appended with a random number for uniqueness.
  • Bot ID: Provide the specific Bot ID.
• Purpose: Creates a new entry in the bot's knowledge source to associate the file with the bot.

8. Upload a File or an Image to Selected Environment

• Action: Use the "Upload a File or an Image" action.
• Configuration:
  • Content Name: Use the body extracted from the Compose action.
  • Row ID: Use the Row ID generated in the "Add a New Row" action.
  • File Content: Use the file content retrieved from the "Get File Content" action.
• Purpose: Uploads the file directly into the bot's knowledge source in Copilot Studio.

Outcome

The flow successfully automates the following:

• Retrieves files based on product names from SharePoint.
• Dynamically processes file metadata (e.g., filenames).
• Uploads files directly into the knowledge source of the AR HUB Bot in Copilot Studio.

By implementing this flow, we:

1. Eliminated manual file handling.
2. Ensured consistent and accurate uploads.
3. Streamlined the integration process between SharePoint and Copilot Studio.

Conclusion

This R&D task demonstrates the feasibility and effectiveness of automating file uploads to the Copilot Studio knowledge source using Power Automate. The process is robust, scalable, and adaptable for similar use cases involving other bots or knowledge sources.

For further improvements, metadata tagging or additional filtering criteria can be added to enhance the flow’s capabilities.