Check in / Check out excel file on SharePoint

[Magistralis]

Good day beautiful people,

I was using this post as a guide Solved: Re: I have to Re: Checking Out / Checking In a Sha... - Power Platform Community (microsoft.... but I cannot overcome error which I have received.

My flow should work like this:

1. SharePoint list updated with item - trigger

2. Send email to the team.

So far works ok. Below what is not working:

It stops on "Check out file" saying that file cannot be found. As you can imagine I have deleted Site Address and Limit Entries on "Get files" and my name on "Filter array" as well as Site Address on Check out file. In my Power Automate everything is filled up.

The fx is:

first(body('Filter_array'))?['ID']

Any ideas why this file cannot be found?

[Pstork1]

You might need to add a clause filtering out folders in your filter array.  They always show first in the array and you may be getting a folder instead of a file.  Otherwise, this works for me

first(body('Filter_array'))?['ID']

[Magistralis]

Good day @Pstork1,

I have followed your idea and instead of using formula to identify file, I hardcoded it's ID (I know, I know). Now my issue is, that Check out step is completed but the file cannot be modified because it is not checked out.

I will try different approach to it and I would love to hear some pro tips regarding this:

1. Should I create one flow for checking new files, emails and file modification or better to create one file for checking and emails and then for checking and file modification?

2. What is the best approach to modify the file? I wanted to use "add new row" but ideal solution is to "press" refresh all button (company locked automated scripts in excel online).

My ideal solution would check if there were any changes in SharePoint list (about 30 columns, not all of them will be changed at once) and then refresh file or add new row to it. I feel that refresh file would be safer (file is connected to the SP list anyway). 

[Pstork1]

Could you explain a bit more of what your overall goal is and why you feel it is necessary to checkout the files?

[Magistralis]

In my company we have a process to monitor people who are joining company, leavers, people who are moving from one position to another and so on. Every single change.

I have currently created a SharePoint list with columns assigned to the type of change and later on created app in PowerApps which is used to add new records to this list.

I also have created 2 flows: send emails automatically once item is changed or created and update excel file.

My idea of 2nd flow looks like this:

1. Once item is created, add record to the table.

2. Repeat for whole month.

3. By the end of a month, get 2 tables from this file and send them as a body of a email to company supervisors for verification.

The biggest problem is that quite often people responsible for updating SP List's data are making mistakes; incorrect 1st day date, misspelled the surname and so on. I want this file to be editable by other team members but if I want to update rows (as it is currently working) with Power Automate it must be checked out.

[Pstork1 · Accepted solution]

Is the Excel file stored in SharePoint or OneDrive?  If it is and you teach them to use Excel Online instead of the desktop client then you should be able to make changes to the file without checking it out.  Both SharePoint and OneDrive support co-authoring.

[Magistralis]

That's new for me. I thought if file is checked in you can't make any changes to it. It solved my problem, thanks!