Get drive id for Doc Library selection

[TorreyFalconer]

Hey all, 

I am using the Excel action 'Create Worksheet' and I would like to make the 'Document Library' property dynamic rather than select it from the dropdown. However it does not accept the Document Library 'Display Name' or Id,  it only seems to accept the 'drive' property.

Does anybody know how to get the 'drive' of a document library? I tried sending an HTTP request to SharePoint but I must have something incorrect.

[annajhaveri]

@TorreyFalconer  are you also providing the dynamic file name, in the excel actions of Power Automate, we cant simply provide the library and filename as their actual names, we need to fetch the drive id and file id and use it in the Create Worksheet action, please see below steps on how to do it:

1. Add 'Send an HTTP request to SharePoint' action to the flow and configure it was follows:

Uri should be _api/v2.0/drive/root:/<filename>

here replace <filename> with a variable or the actual file name e.g. book1.xlsx

2. Then add Compose action and configure following expression in this compose action

outputs('Send_an_HTTP_request_to_SharePoint')?['body']['Id']

3. Now again add Compose action and configure following expression in this compose action

outputs('Send_an_HTTP_request_to_SharePoint')?['body']['parentReference']['driveId']

4. Then in Create Worksheet action configure it as follows, use compose action output in the Document library and file name parameters, as shown in screenshot below

[TorreyFalconer]

Thanks! 

I think this will work but I am still getting a 400 error at the moment with my HTTP request Uri. 
It just says that it got an unexpected response from the request. 

I have my Uri input: 

_api/v2.0/drive/root:/book1.xlsx

 I tried it various ways using a variable, the actual file name etc. (I even tried using the doc library name instead) but still get the same error.
Any thoughts?

[annajhaveri]

@TorreyFalconer  could you please let me know following:

1. Excel file name in which you want to create the worksheet? 

2. Document library in which file exists

3. Is file added directly under document library or under a folder, if under folder can you share the folder path

With these answers i can help you to figure out what will the Uri in your case

[TorreyFalconer]

Hey!

File Name: 2021-03_PersonCount.xlsx

Document Library: Meeting Attendance 
Not under a folder, directly in the document library!

Hope that helps!

[TorreyFalconer · Accepted solution]

UPDATE:
I was able to accomplish this with a work around. 
1) In my HTTP request to SharePoint I used the uri: _api/v2.0/drives

     It then listed all the drives and drive id's on that site

2) Then I used Parse JSON to get the values inside the output Body of the HTTP Request
3)  Then a Filter Array to find the document library I needed (I filtered by name)
4) Then finally I used a Compose with the Id from the Filter array. *Note - Many Ids may be listed from the output so if this helps anyone it is: @{items('Apply_to_each')?['id']} 

And of course I set this as a variable because that was a bit of a ridiculous process to get one id. 
So, a bit nuts, but it works if you are having the trouble with the _api/v2.0/drive/root:/<FileName> uri as I was.

I believe this is all much easier to achieve using the Microsoft Graph api if you are registered
But, I hope this helps anyone with the same issue. 

Thanks for your guidance and help @annajhaveri!

[annajhaveri]

@TorreyFalconer  the reason why below Uri input did not work for is because your document library name is Meeting Attendance , the below Uri works for default Documents library of the site. If not using default documents library then the solution that you provided is correct, which worked for your scenario

_api/v2.0/drive/root:/book1.xlsx

[TorreyFalconer]

I configured it exactly like that and it did not work. Glad I found a work around though! 

[annajhaveri]

@TorreyFalconer I just meant to explain why it didnt work for your scenario. I hope that makes sense.

[TorreyFalconer]

@annajhaveri it did! Thank you very much for the help!

[Anonymous]

Hi @annajhaveri  / @TorreyFalconer 

I tried both ways:

But both HTTP Get actions failed with "Unexpected response from the service
clientRequestId: 6afea342-387b-4c35-ba2b-28bc1b1837d0
serviceRequestId: d11be59f-a0f6-c000-47d7-b2029fb9b849"

So, I couldn't even proceed to the next step. How were you able to pass that action?

[Ms-PowerRanger2]

What is the syntax if the file is located in a folder and not directly under the document library.  The document library is TestLib and the folder is Output.

[metsshan]

I wanted to get the driveitemid from my "When a file is created" SharePoint trigger, and this thread helped me greatly.

Maybe my approach will help other travellers to this topic?

When using a Data Source Environment Variable, you will have the 'list' ID of your target library. Using the List ID and the "ID" from the Trigger (which is actually the List Item ID), you can use the Graph API to get all the list properties.

Graph API documentation tells us that List Items have a relationship with "driveItem", so we can simply expand that property to get both the "driveItem/id" and the "driveItem/parentReference/driveId", which are the two values @annajhaveri pointed out you need for your Excel activity (in my case, I only need the driveItem/id)!

Now, I would have thought using SharePoint HTTP request activity would work here, but I am getting an error trying to do anything with _api/v2.0/lists, I am always getting:

{"error":{"code":"itemNotFound","message":"Item not found"}}

But if v2.0 could be used, your SharePoint HTTP request would look like this:

_api/v2.0/lists/{List ID From Data Source Environment Variable}/items/{SharePoint File ID}?expand=driveitem(select=id,parentReference)&select=id,weburl,driveitem

To work around this, I am using "Office 365 Groups - Send an HTTP Request" to avoid any authentication overhead using the Graph API (you could also use Azure AD HTTP Request). The GET request URL looks like this:

https://graph.microsoft.com/v1.0/sites/{Site Address}:/lists/{List ID From Data Source Environment Variable}/items/{SharePoint File ID}?expand=driveitem(select=id,parentReference)&select=id,weburl,driveitem

You need the {Site Address} to be in the format "contoso.sharepoint.com:/sites/TestSite", which we can get by transforming the Data Source Environment Variable:

@{replace(replace({Site Address Data Source Environment Variable},'https://',''), '.com/', '.com:/')}

And, it works!

[OliverR-82]

Thank you for the elaborate write-up, @metsshan . However, I suspect that Microsoft made changes to the O365 Groups connector to no longer allow calling that endpoint.

The URI works fine directly in the Graph Explorer (https://developer.microsoft.com/en-us/graph/graph-explorer), but it fails if I call it in a Power Automate flow using the O365 Groups connector as described in your post. The error message it returns is:

URI path is not a valid Graph endpoint, path is neither absolute nor relative or resource/object is not supported for this connector. 

Can anyone else confirm if this approach still works today?

[metsshan]

Hi @OliverR-82 , still working as described in my post, no issues from any of my flows which use this daily.

[metsshan]

Hey @OliverR-82 , I know this was a few months ago but after a quick test on my end, and a bit of sleuthing I can see that the 'Send and HTTP request' activity has been deprecated and replaced by a V2 activity which only grants access to the /groups endpoint.

I don't believe there is a non-premium solution for this request now, and people will need to use 'HTTP Request' or 'HTTP with Microsoft Entra ID'. 

EDIT: Or in my case, I replaced with using Custom Connector as I did not like how users were always prompted for a service endpoint when Allowing/creating 'HTTP with Microsoft Entra ID' connections.

[DavidHorsky]

Update:
After trying a few things, _api/v2.0/drive no longer works. 
_api/v2.1/drives is the new endpoint