Automating Data Processing with Power Automate: Updating a SharePoint List from Excel

The purpose of this flow is to process data from an Excel sheet and update relevant rows in a SharePoint list. The flow is specifically designed to add users to the SharePoint list, ensuring that they exist within the organization. A key aspect of this process involves using the format ‘i:0#.f|membership|’ to add users successfully:

concat(‘i:0#.f|membership|’, outputs(‘Get_user_profile_(V2)’)?[‘body/mail’])

Additionally, if any rows are not updated, an email notification is sent to the admin.

Step-by-Step Breakdown

1. Trigger: Manually Trigger a Flow
   • Type: Request (Button Trigger)
   • Description: This action manually starts the flow when a user presses a button in Power Automate. It requires the user to provide specific inputs such as location and date. These inputs can be referenced later in the workflow to filter data or apply conditions. This step ensures that the flow is executed with relevant contextual information.
   • Setup: Request (Button Trigger)
   • Create a new flow in Power Automate.
   • Select “Instant Cloud Flow” and choose “Manually Trigger a Flow.”
   • Add input fields if necessary.
2. Initialize Variables
   • List of Con: Stores a list of Required Users extracted from the Excel file. This helps keep track of users who need to be added or updated in SharePoint.
   • List of Users: Stores a list of users retrieved from the Excel sheet. This variable is used to compare existing records in SharePoint and identify new users who need to be added.
   • Setup:
   • Add two “Initialize Variable” steps.
   • Set the type to “Array” to accommodate multiple entries.
3. Retrieve Data from Excel
   • Action: List Rows Present in a Table
   • Description: This action retrieves all rows from a predefined table in an Excel sheet stored in a SharePoint document library. The flow filters out rows where the “Faculty” column is empty, ensuring that only relevant data is processed. This prevents errors and improves efficiency by only working with complete records.
   • Setup:
   • Use Excel Online (Business) → List rows present in a table.
   • Configure site URL, document library, file, and table.
   • Apply filters to exclude unnecessary data.
4. Retrieve Data from SharePoint
   • Action: Get Items
   • Description: This step retrieves existing items from a SharePoint list where the users’ information is stored. It allows for comparison between current data in SharePoint and new data from the Excel sheet. By fetching this data in advance, the flow can determine whether a user should be updated or added as a new entry.
   • Setup:
   • Use SharePoint → Get items.
   • Specify the site URL and list name.
5. Process Each Excel Row
   • Action: Apply to Each
   • Description: This loop processes each row retrieved from the Excel table. It iterates through all records and applies necessary updates or additions to the SharePoint list. This ensures that each entry is assessed and handled appropriately.
   • Setup:
   • Output set to values retrieved in step 3.
   • Add conditional logic inside the loop as needed.
6. Match Records in SharePoint
   • Action: Filter Array
   • Description: Filters the retrieved SharePoint list items to find records where the Title matches the Course Code from the Excel row. This helps determine if an entry already exists in SharePoint and needs updating, or if it is a new record that needs to be created.
   • Setup:
   • Use Filter array.
   • Define the condition to compare Title and Course Code.
7. Extract Required User Information
   • Actions:
   • “Set Variable” → Initializes “List of Con” to store Required Users.
   • “Condition” → Checks if the “Required User(s)” field is not empty.
   • “Get User Profile” → Searches user profiles for each Required User.
   • “Append to Array Variable” → Updates “List of Con” with retrieved profiles.
8. Check and Parse User Profiles
   • Actions:
   • “Condition” → Verifies if “List of Con” is not empty.
   • “Compose” → Converts “List of Con” to a structured format.
   • “Parse JSON” → Parses the composed data for further use.
9. Update SharePoint List
   • Actions:
   • “Condition” → Ensures matched SharePoint records exist.
   • “Apply to Each” → Iterates through filtered array.
   • “Update Item” → Updates existing SharePoint list items.
   • “Create Item” → Adds new records if no match exists.
10. Notify Admin of Unprocessed Users
    • Actions:
    • “Compose” → Prepares a list of unprocessed users.
    • “Send an Email (V2)” → Notifies the admin if users couldn’t be updated.

Conclusion

By implementing this automated flow, organizations can efficiently manage user data updates in SharePoint without manual intervention. This approach reduces errors, saves time, and ensures that all records are up to date. With Power Automate, businesses can streamline data processing and enhance operational efficiency.