Unlocking one drive file through API

[kim7]

When I try to delete an Excel file I get an error:

"The resource you are attempting to access is locked"

The Excel file can be deleted after 7 minutes but I have found a solution here:

http://www.cleverworkarounds.com/2021/02/21/how-to-clear-annoying-excel-file-locks-in-power-automate...

It is possible to call the following API

_api/web/Lists/GetByTitle('DocLib')/GetItemById(ID)/recycle

and unlock the file located on One Drive?

I tried to use it but this API needs a "document library" parameter

which I do not understand (I'm a total beginner with Microsoft Power Automate)

Do you think it is possible to use this API to unlock the file that is located

on my One Drive?

My One Drive files can be listed using the following URL

https://ZZZZ-my.sharepoint.com/personal/UUUU/_layouts/15/onedrive.aspx

ZZZZZ is my compnay name
UUUUU is my user name

What is the DocLib parameter I should use?

Kind regards,

Mark

[Pstork1]

If you look in the lower left-hand corner while in the document library in OneDrive you should see a link to return to classic OneDrive.  If you lick on that you should see the document library name included in the URL.  Normally it would be Documents.  Something like this

[kim7]

Thank you

Can I retrieve the item ID from the documentURL? How this ID looks like?

With this call:

_api/web/Lists/GetByTitle('Documents')/GetItemById('1F9Y48R4-BX3G-4D32-12XC-76F8D1126F5')/recycle

I get the folIowing error:

"Input string was not in a correct format"

Thank you for your help,

Mark

[Pstork1]

That's the document GUID.  The Item ID its looking for is the SharePoint Integer ID for the item in the document library.  How are you retrieving the document metadata?  The ID should be included.

[kim7]

With this API i need to unlock the file that was created using the "Create File" action.

"Create File" action returns among other parameters:

Id (a very long string)
Name
Path
Media type
ETag
File locator

From my tests it looks like the parameter I'm looking for is the integer number

When I put an integer number (for example 6)

_api/web/Lists/GetByTitle('Documents')/GetItemById(6)/recycle

... the http request does not return an error. But how can I get this ID number?

Thank you,

Mark

[kim7]

With this API i need to unlock the file that was created using the "Create File" action.

"Create File" action returns among other parameters:

Id (a very long string)
Name
Path
Media type
ETag
File locator

From my tests it looks like the parameter I'm looking for is the integer number

When I put an integer number (for example 6)

_api/web/Lists/GetByTitle('Documents')/GetItemById(6)/recycle

... the http request does not return an error. But how can I get this ID number?

Thank you,

Mark

[Pstork1]

Unfortunately, the OneDrive Create File action doesn't return the ID you need.  But if you use the SharePoint Create File and paste in the Site name of the OneDrive you can create the file and it will return the ItemID, which is the number you need.  

[kim7]

Thank you,

But are you sure that SharePoint Create File also returns the ID that is returned by the OneDrive Create File action? In my opinion SharePoint Create File returns the ID that I need to the further Sharepoint API but I don't get the ID that is returned by the OneDrive Create File (which I also need).

Anyway, I managed to convert the ID that is returned by the OneDrive Create File into the ID that is returned by the SharePoint Create File. I will post here the solution soon.

Thank you,

Mark

[Pstork1]

I tested it and yes, creating a OneDrive file using the Create a File from the SharePoint connector does work to create the file and does return the itemID that you need.  Behind the scenes OneDrive is SharePoint so the connector will work for both.  The ItemID is the List Item ID that is assigned to the file.  You still also get the file identifier which you'll need for file operations.  But the REST call you are making operates at the list level, so it needs the list ID, not the file identifier.

[kim7]

Thank you,

I took SharePoint Create File and created a file successfully. The action returned the following parameters:

ItemId (what I was looking for from the beginning)

Id

Name

DisplayName

Path

LastModified

Size

MediaType

IsFolder

ETag

FileLocator (with subparameters dataset and id)

Then I called Delete File action for each returned parameter. None of the parameters let me deletes the file.

The most promising was FileLocator/id but it also fails.

For each parameter I get the message "The file id XXX is not valid"

I examed the file I had created with the Get file metada action and the ID that was returned

does not match with any parameter that was returned by the SharePoint Create File action.

Thank you,

Mark

[Glorenzon]

Please past the final solution/image of the action you have compiled.

Thanks

[kim7]

I have managed to delete a file in OneDrive using SharePoint API

So add "Send a HTTP request to SharePoint"

Site address: put here the address you see in your task bar when browsing your OneDrive, for instance:

https:// ....  .sharepoint.com/personal/   ....... 

Method: POST

URI: _api/web/Lists/GetByTitle('Documents')/GetItemById(  variables('sSharePointId')  )/recycle

variables('sSharePointId') - I get this variable in my previous step using GetSharePointFile service

Headers: key:Prefer, value: bypass-shared-lock

Let me know if this was helpful.

Regards,

Mark

[Glorenzon]

Hi and thank you but i don't have the id for 

GetItemById( variables('sSharePointId')

where i find it?

[kim7]

If you know a file's name and you want to get it's SharePoint ID you must call GetSharePointFile service

Site addess: same as previous ( https:// .... .sharepoint.com/personal/ ....... )
Method: GET

URI: _api/web/GetFileByServerRelativeUrl('/personal/ ....... /Documents/ {variables('sFileName')}')/listItemAllFields?$select=Id

Header: Key = Accept, Headers: application/json;odata=nometadata

And in the next step put the value that is returned from this service into sSharePointId (using SetVariable service)

Good luck

[Glorenzon]

Don't work or i don't understand it correct...i sent you a private message yesterday...

[kim7]

Let's start from the beginning. I assume you have a file on your OneDrive that you want to delete. Is this correct?

[Glorenzon]

yes that's right, I save the excel file to run an automate excel script. after execution I have to send this file by email and then delete it so that next time I can start the run again. This script unfortunately blocks the deletion. last year I managed to get it working, then something happened and now it no longer works.

[kim7]

Can you fetch the SharePoint ID of this file using SharePoint HTTP request?

[Glorenzon]

See attachment

[kim7]

Disable your second HTTP request, let's focus on the first one.

Instead of {variables('ERD_LISTA_LANCI.xlsx')} put only ERD_LISTA_LANCI.xlsx
Then check if this request returns the file ID