Access SharePoint List Using Custom Copilot – Copilot Studio

Introduction

When working with enterprise data, having a Co-pilot that can't access databases is like flying without a map—you're not getting the full potential. But what if your Co-pilot could tap into databases effortlessly? That's exactly what we'll explore in this blog. I'll show you how to use a SharePoint List as an example database, but the principles apply to any database you choose. By following the steps in this guide, you’ll learn how to create a flow that connects your Co-pilot to the data source of your choice, ensuring it’s fully equipped to handle all your enterprise data needs.

Overview

To continue with the example I created in Copilot for Air India customer support, we will take input from customers using Copilot and use those inputs to filter data from the SharePoint list. And return output to the copilot.

Step 1. Create Copilot Action Flow.

Flow my previous blogs to create a chatbot and take inputs from customers like To Location, From Location, and input date.

Then select an action to create a flow in Power Automate and give it a name search flight. Please check my " Connect Copilot and Power Automate flow " blog for this flow

Step 2. Setup a List.

Let’s set up a flow that will query the list and give us output as flight number. The SharePoint list shown in the image provides details of Air India flights with the following columns:

1. Title: This column lists the flight numbers (e.g., AI 100, AI 101). Each row represents a different flight.
2. DepartureCity: This column shows the city and airport code from where the flight departs (e.g., Delhi (DEL), Pune (PNQ)).
3. ArrivalCity: This column displays the destination city and its airport code (e.g., Mumbai (BOM), Chennai (MAA)).
4. DepartureTime: This column indicates the time the flight is scheduled to depart (e.g., 07:00 AM, 11:00 AM).
5. Arrival Time: This column shows the scheduled arrival time of the flight (e.g., 09:00 AM, 01:00 PM).
6. Flight Duration: This column provides the duration of the flight, typically shown in hours and minutes (e.g., 2h 00m).
7. DaysOfOperation: This column specifies the days on which the flight operates. For some flights, it states "Daily," meaning the flight operates every day. For others, specific days are mentioned (e.g., Mon, Wed, Fri or Tue, Thu, Sat).

This list is likely used to track and manage flight schedules, providing a quick reference for flight details, including operating days, departure and arrival times, and routes. We will use an ad database for flight searches.

Step 3. Create a Flow.

The "Run a flow from Copilot" step appears to be an integration where inputs from the user are collected to search for flights based on the specified locations and dates.

The flow is designed to take inputs related to searching for flights. It includes the following parameters.

• ToLocation: This input field is for specifying the destination location of the flight. The user is prompted to enter the desired arrival city or airport.
• FromLocation: This input field is used to specify the departure location. The user should enter the city or airport from which the flight will depart.
• BookingDate: This field is intended for entering the desired date of booking or travel. The user is asked to provide the date for which they want to find available flights.

Then we add a variable

The Power Automate step titled "Day" is part of the flow and follows the "Run a flow from Copilot" step. Here's an explanation of this step.

• Name: The variable is named var_Day. This name is used to reference this variable in subsequent steps within the flow.
• Type: The type is set to String, meaning the variable will store a text value.
• Value: ‘Expression - formatDateTime(triggerBody()?['text_2'],'ddd')’. The formatDateTime function typically takes a date input and a format string. For example, if extracting the day of the week, the format ddd (e.g., "Mon").

This step is used to extract and format the booking date obtained earlier in the flow to determine the specific day of the week.

Then we will make a call to the SharePoint list and use these parameters we have as input. Add action “Send an HTTP request to SharePoint”.

This action is used to retrieve data from a SharePoint list using the SharePoint REST API. Here's a detailed explanation of each component of this step.

• Site Address: The site address is set to Sandy - https://stwservices.sharepoint.com/sites/Sandy, which specifies the SharePoint site where the list is located.
• Method: The method used is GET, which is typically used to retrieve data from a specified resource, in this case, a SharePoint list.
• URI: The URI specifies the exact SharePoint list and filters to be used in the request.

./_api/web/lists/getbytitle('AirIndiaflights')/items?
$filter=substringof('Delhi', field_1) 
    and substringof('Mumbai', field_2) 
    and (substringof('Daily', field_6) or substringof('Mon', field_6)) 
    and (substringof('09:00', field_3)) 
&$orderby=field_3 asc
&$top=1

This URI performs the following.

• _api/web/lists/getbytitle('AirIndiaflights')/items: Accesses the items in the SharePoint list titled "AirIndiaflights."
• $filter=...: Applies multiple filters to retrieve specific items from the list:
• substringof('Delhi', field_1): Filters the list items where field_1 (likely representing the departure city) contains the substring "Delhi."
• substringof('Mumbai', field_2): Filters the items where field_2 (likely representing the arrival city) contains the substring "Mumbai."
• (substringof('Daily', field_6) or substringof('Mon', field_6)): Filters items where field_6 (likely representing days of operation) contains either "Daily" or "Mon" (Monday).
• substringof('09:00', field_3): Filters the items where field_3 (likely the departure time) contains "09:00."
• $orderby=field_3 asc: Orders the filtered items by field_3 (possibly departure time) in ascending order.
• $top=1: Limits the results to the top (first) item that matches the criteria.

Headers and Body

These sections are left empty, indicating that no additional headers or request body content are required for this GET request.

This action retrieves flight details from the SharePoint list "AirIndiaflights" based on specific conditions, such as departure and arrival cities, days of operation, and time.

Add the "Parse JSON" action in Power Automate. This action is used to parse a JSON response, typically from a previous step, such as an HTTP request, allowing you to extract and work with individual data elements within the JSON object.

The Content field is set to Body, which means the action is using the output from a previous step (Send an HTTP request to SharePoint) as the input for parsing. This is usually the body of a response from an HTTP request, such as data fetched from SharePoint or another API.

The Schema section defines the structure of the JSON data that will be parsed if you have a Postman response, which we can use to generate a scheme.

Then add Compose" action in Power Automate. The Compose action is commonly used to perform simple operations, format data, or hold values for later use in the flow.

We will extract flight value from HTTP SharePoint call. Using Expression.

body('Parse_JSON')?['d']?['results'][0]?['Title']

Then, add these actions as per the Image.

FlightNumber This variable is created to store the flight number extracted from the previous Compose step. Type string and value which is coming from Compose action.

In Respond to Copilot, which is by default, you will find in this flow if you are creating for Copilot Studio. Add output parameter and assign value FlightNumber.

Save flow, run, and test it.

We will add this action in Copilot and get the flight number from the SharePoint list.

Conclusion

This Power Automate flow demonstrates a streamlined approach to managing flight data by integrating data retrieval, parsing, and processing actions. Starting with an HTTP request to a SharePoint list, it filters and retrieves relevant flight details. The flow then uses JSON parsing to organize data, the Compose action to extract values, and variable assignment to handle specific data points like flight numbers. Finally, the flow responds to Copilot with the processed data, enhancing efficiency and automating routine tasks. This approach highlights Power Automate’s ability to simplify workflows, ensuring seamless data handling and integration.