Power Platform Community Forum Thread Details

I have an excel file in a sharepoint folder that I need to make 100 or so copies of

I need to rename those file names to include an individual's name

I have another excel file with the names of those individuals

I then need to give each person permission to view their own file (ie give permission at each file level)

Is there a template I can start with that would help with these steps?

Categories:

Hi @CU13120130-0,

You can achieve your goal using Power Automate to automate the process of copying, renaming, and setting permissions for your Excel files. Here's how you can set up a flow:

Step-by-Step Solution

Prerequisites:

Ensure your original Excel file is stored in a SharePoint folder.
Have another Excel file with the names of individuals (let's call this the "Names List").
Verify that you have proper permissions to manage files and grant access on the SharePoint site.

Steps to Build the Flow

1. Set Up the Trigger:

Use a manual trigger or a trigger based on the "Names List" being updated.

2. Read the Names List:

Use the "List rows present in a table" action (Excel Online) to get all the names from your "Names List" Excel file.
Ensure the table in the Excel file has a column for names (e.g., Name).

3. Loop Through Each Name:

Add an "Apply to each" action to iterate through the rows retrieved from the "Names List."

4. Create a Copy of the Original File:

Inside the loop, use the "Copy file" action:
- Source File Path: Path of your original Excel file.
- Destination File Path: Construct a dynamic file name with the individual's name using the following expression:
  
  concat('/Shared Documents/TargetFolder/', items('Apply_to_each')?['Name'], '_FileName.xlsx')

5. Grant Permissions to Each File:

Use the "Grant access to an item or a folder" action:
- File Path: Use the dynamic file path created in Step 4.
- Recipient: Use the individual's email address. If the email address is not available in the "Names List," consider adding a column for it in the Excel file.
- Role: Assign appropriate permissions (e.g., "View only" or "Edit").

Example Flow Outline:

Trigger: When the flow is manually triggered or when the "Names List" Excel file is updated.
List rows present in a table: Read all names from the "Names List" file.
Apply to each (Name):
- Copy file: Create a copy of the original Excel file, renaming it with the individual's name.
- Grant access to an item or a folder: Assign file-level permissions to the individual.

Tips and Notes:

Error Handling: Add a "Scope" with error-handling actions to log any failures (e.g., if a file can't be created or permissions can't be set).
Large Lists: If you have more than 100 names, consider using batch processing or breaking the flow into smaller runs to avoid hitting throttling limits.
Folder Permissions Alternative: If assigning individual file permissions is too complex, you could group files into separate folders and assign permissions at the folder level instead.

Let me know if you’d like help setting up this flow in more detail or troubleshooting any part of the process. 😊

Best regards,
@Ninjasabi