Update a file in a subfolder of a document library

[LeeS]

Dear all,

I'm sure I am missing something here and the solution will be real easy and slap me in the face!

However, I'm struggling to wrap my head around how to update a file (in this case Excel) that is located in a sub folder of a Document Library.

The Scenario

Each day, I want to backup a SharePoint list to a particular Excel File. The SharePoint list is one of several lists that are team plans for different teams in a project. Each team plan needs to backup to an Excel file for the current month, and the daily backup is written to a dynamically newly created worksheet.

My difficulty is referencing the existing Excel file in the sub folder. The structure is like this:

• Backups (this is the Document Library name)
  • Team Plans Backups
    • Team 1
      • Team 1 Plans Backup - August 2023.xlsx
      • Team 1 Plans Backup - July 2023.xlsx
    • Team 2
      • Team 2 Plans Backup - August 2023.xlsx
      • Team 2 Plans Backup - July 2023.xlsx

The goal is to dynamically create a new worksheet each day in the file for the current team in the current month. I've tried this in a flat folder structure and I can get this working. This also works without a problem if all the files are located at the Document Library root level folder - Backups.

However, I cannot figure out a way to access the files in the sub-folders. I know how to dynamically create the file name to look for using a concatenate expression together with utcNow() function, but I'm struggling to look for that filename in a sub-folder.

Any and all help and advice would be most appreciated!

[David_MA]

With more context on why you need to access the old files if you are creating a new file each day, I might suggest something else, but would this work:

It appears you have a standard naming convention. If not, you'll need to adjust your filter query. But if the only thing that changes is the month and year, the following expression will generate that for the query:

formatDateTime(utcNow(),'MMMMM yyyy')

FileLeafRef references the file name in the Get files action.

The condition uses a length expression to see if one file is returned.

length(outputs('Get_files_(properties_only)')?['body/value'])

If one file is returned, the apply to each will get the properties of the file based on the ID.

It should not be possible to have two files with the same name in the same folder, so the Get files action should either return 1 or 0 (found or not found).

[LeeS]

Hi David_MA and many thanks for your reply.

Apologies, maybe I didn't make it clear enough: a file is not created each day, a worksheet in an existing file is created each day. There is one Excel file (workbook) per team that has worksheets for each day of the month and it is this file I was having trouble finding in the sub folders so that I could add the daily worksheet to it.

I will have a look through your solution above to see it that helps me move forward and come back as soon as possible.

Thanks again!

[David_MA]

If you know which file you need to update, then why can't you choose the file to update in the action? Why do you need to find it?

[FraserBeadle365]

Do you just need to include the sub-folder path along with the file name.

In SharePoint, the file path includes the sub-folders. So, if you want to access a file in a sub-folder, you need to include the sub-folder name in the file path.

For example, if you have a file named "Team 1 Plans Backup - August 2023.xlsx" in the sub-folder "Team 1", the file path would be "Team 1/Team 1 Plans Backup - August 2023.xlsx".

So, when you're dynamically creating the file name, you should also include the sub-folder name in the file path.

Here's how you can do it:

1. First, dynamically create the sub-folder name. This could be the team name or any other identifier you're using for the sub-folders.

2. Then, concatenate the sub-folder name, a slash ("/"), and the file name. This will give you the full file path that includes the sub-folder.

3. Finally, use this full file path to access the file in the sub-folder.

Remember, every challenge is an opportunity to learn something new.