Answered

Unable to use Excel connector with dynamic SharePoint Sites and paths

(0) Share

Report

Posted on by cc-94

Hi Everyone,

I am currently trying to create a flow which will insert a row into an excel file, however the excel file can be in one of many SharePoint sites. In the flow we have the full path to the file in different variables. I'm running into an issue when I use the URL in the 'Location' input for the 'Add a row into a table' excel connector like below:

I get the following error:

The provided drive id appears to be malformed, or does not represent a valid drive.
clientRequestId: xxxxxxxxxxxxxxxxxxxx
serviceRequestId: xxxxxxxxxxxxxxxxxxx

So I noticed that on the failed run the location in the inputs is showing as a URL, however if I specify the Location and Document Library by using the drop down menu in the action I get some form of string ID in the inputs section like below:

Successful Run with specified SharePoint Site and Document Library

Failed Run with URL

Is this an issue anyone has faced and come up with a solution to in the past? Is there a way to get the Location 'ID' in the last screenshot using the URL and feeding that in?

Any help at all is greatly appreciated!

Kind regards,

Conor

Categories:

Using connectors

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

It is possible to solve, but you need to know where the data is stored. To prevent errors this might take extra steps to confirm if you are calling a MS Teams site or a SharePoint Site.

The best way to investigate this will be to use the Peek Code in the settings of the Add a Row Into a Table.

Peek Code will show you what PA is actually using. So in a dropdown you select a name, but in the Peek Code PA is actually using a guid.

For example, if I want to add a row into a table and select the Location dropdown "Group - CORP-Projects" that is a MS Teams location. Looking at the peek code it is actually using Source: "groups/24690f41-a456-43d2-c21a-14c282c36499"

This is the MS Graph endpoint for "groups/{groupId}"

However, if I select the Location dropdown "SharePoint Site - Finance" it is a SharePoint site. If I look at the Peek Code the Source:

"sites/sunmc.sharepoint.com,2ba59563-eced-4820-9cec-2407a5b2bc6c,fe20a969-6fdc-4695-abae-8fbcd3879410"

This is really a web address used in MS Graph that is "{hostName},{siteId},{webId}"

Similarly the long "b!Ypceo8PbK8PHmoIPq8b1VGq66PvN" text string is the DriveId for the Document Library.

MS Graph uses Drives to refer to Document Library, so the Drive-Id is the unique identifier for the drive when using MS Graph calls.

The file will have a Drive Item ID that will look something like "01H4AJ6XHOSZ7JAY27MNJIFG4MWTQIN77Y" which is the unique identifier of the excel file as a DriveItem within the Drive(Document Library).

So it is possible to get what you need with various HTTP requests and then put them directly into the Location of the action. But you have to know what all you are looking for and how to correctly put it into the field.

If you look into MS Graph Explorer and resources it might help give a better idea of what you are looking for.

Was this reply helpful? Yes No
cc-94 4 on at

Like (0)

Report
Copy link

Link copied!

Thank you very much for your reply. I had read elsewhere about the Graph API, but it does seem fairly tedious to have to grab these details using various HTTP request. It especially seems limiting when all the SharePoint actions are able to connect to any file simply using the URL and file path, seems strange that Excel actions can't work in this way.

I decided to do a workaround by using the copy to SharePoint action and copying it to a temporary folder which I can specify the Drive ID, Item ID etc. I can then use the insert row excel action, after that I use copy SharePoint action again to copy it back to the original folder. This is giving me the desired result but is less than ideal.

Was this reply helpful? Yes No