Search share point for document

[Gwill1983]

Hi,

I have built a bot with several topics, but I am trying to implement a function where the user can request a process document and either get the file itself, or a share point link to access this.

I am really struggling to even get started on this.

does anybody know if this is possible?

[Expiscornovus]

Hi @Gwill1983,

You do not have to apologize 😀. I was just curious which approach you would prefer. This way we can focus on making that approach work for you.

With that other approach you won't have to deal with nested json in the response, so that's why I thought that other one was probably a bit easier.

[GlennBullion]

Sorry to revive a 5-month-old thread, but I'm in a similar situation, and could use some guidance.

Trying to build a "Document Search" bot. The ultimate goal is to ask the user "What are you looking for?" Give them a choice of "Manual, Drawing", etc.,  And the bot will call a flow to search that particular document library.

Would like to use the Sharepoint Search API, as one of the libraries is rather large, and that search seem to run well against it.

It looks like this solution though only returns 1 Search Result ( body('Select')[0]  .

How would I modify this to return multiple search results?

From what I can see online, dealing with the results from the Sharepoint Search API can be a hassle.

[Expiscornovus]

Hi @GlennBullion,

I think you are correct about the 1 Search Result comment. I think I tried to retrieve the nested array, but what it actually returned is only one result. Thanks for spotting that misconfiguration.

I would remove the index [0]. But you would probably also have to change the setup of the rest flow. This is because the cells array is nested within the rows array, which makes is slightly more challenging 😁

[GlennBullion]

I did get a working solution together. I'll post it tomorrow for thoughts.

[Gwill1983]

I never managed to get this working fully and it was well and truly filed into the ‘too difficult’ column! If you have managed to find a work around then I would be keen to see it! 

[GlennBullion]

Okay, will break this down for what I ended up doing. Keep in mind, I'm no expert. 🙂 But this worked for me, for my needs. All I'm looking to do is get some keywords from the user in the bot, pass it to flow to do a Sharepoint Search, then pass those results back to the bot. A lot of joy is in here, namely due to the fun of working with the search results, markdown language, etc.

Here we go.

Overview below, I'll go through the steps as well. (Forgive my naming. I try to rename many steps so that I can look at it later and know what I was doing).

The first few steps:

The Power Virtual Agents is the input from the bot, of course.

The "Compose Folder" (grayed out for privacy) statement is just the full Sharepoint path name of the folder (and subfolders) I want the Sharepoint Search API to search. You see it being used in the Send an HTTP Request step.

The Send an HTTP Request statement (Site Address grayed out for privacy) was discussed in previous messages. The only thing I added to it was the "Compose Folder" to limit the search location, a RowLimit of 100, and I also limited the returned properties to Title and Path, as they were the only two fields I cared about.

I ultimately used two select statements to get the Title and Path.

The first Select statement is again a lift from earlier in this thread. It grabs the "Cells" from the JSON search results.

The second Select statement grabs the Title and Path from the previous Select statement. Those two "Item" statements are, in order:

item()[0]['Value']

item()[1]['Value']

Since I only grabbed the Title and Path from the API above, Items 0 and 1 in the array turn out to be Title and Path.

Below is just setting up a String Variable. It will end up holding all of the search results.

Here is the Apply to Each that builds the search results.

The "Output" statement is from the second Select statement.

The "Append to varResults" is just an Append to String, and you can see how the Value is built. That is all Markdown language (which I had to look up quite a bit 🙂 ), in order to get it looking pretty for the bot. The "Title" goes in brackets. The Path goes in parenthesis and greater than/less than, which puts together a nice URL link. the "\n" I use for a new line (which I'll talk about below).

The Title and Path are expressions: items('Drawings_Loop')['Title'] and items('Drawings_Loop')['Path'] .

Below is the last stretch, a Compose statement to create a line break, and the Return to PVA.

The Compose Line Break statement is: uriComponentToString(replace(variables('varResults'),'\n','%0A%0A'))

What I found is that without that statement, the results that go back to the user don't have any line breaks. It's all just mashed together. So, this replaces all of the "\n" with two "spaces", which triggers a line break in Markdown. Makes the results look nice.

And the last statement is to return that to PVA. Here is a sample of my results.

This turned out well for me. I'm still fine tuning, but getting there.

Note some "weirdness" with this:

I can't figure out how to Sort the results alphabetically yet.  Adding a "sortlist" to the Search API statement for Title crashes the flow.  Not sure why.

This also searches inside the document, which I myself like. But I can see my users saying "We want just the title only". So, will dig into that later as well.

Thanks to Expiscornovus, (and all the other threads I went through 🙂 ), for getting me on the right track with this.

[SShAl249]

Hi @GlennBullion Thanks for sharing this method. I tried the it and it worked well except for the SharePoint sub folder links, its only working for the parent folder for me.(shows blank result when sub folder is selected) 

Below is the format of the link https://sharepoint/com/sites/site1/Shared Documents/General

It only works when I put https://sharepoint/com/sites/site1/ in the compose step.

Please can you help out with this. Thanks.

Also, is there a way to obtain Name of the file instead of the Title ?

Thanks

S

[GlennBullion]

That's how I search as well. My Compose statement is just the root of a document library. I've never tried a sub folder.

Just a guess, but you might be able to pass a subfolder in the "Send HTTP request to Sharepoint" step?

[SShAl249]

Do you mean in the site address section? I have tried it and it gives this error in the bot chat window.

Sorry, the bot can't talk for a while. It's something the bot's owner needs to address. Error code: 2003. Conversation ID: 3R592OiSpnLD1FvKynU2aN-uk. Time (UTC): 2/7/2023 6:10:24 AM.

The sub folder url is working in the browser, when added to 'site address' and 'uri' section, it doesn't work. Below screenshot shows how I have added the site address in both sections.

If I use the site address from the drop down (parent link) and keep the sub folder link in the compose section the error goes away although I get no results.

I need to restrict the search to a sub folder only. Please can you try at your end if it works.

[Cydman2023]

Hi @GlennBullion thanks for this concept and I am trying to mimic your solution here. I completed all nodes but I am getting this error. "The input parameter(s) of action 'Return_value(s)_to_Power_Virtual_Agents' contain an invalid reference to 'Compose_1'. Correct to include a valid reference to 'Compose_1' for the input parameter(s) of action 'Return_value(s)_to_Power_Virtual_Agents'."

Any idea? Any help you can extend would be highly appreciated because your requirements are exactly what I am looking for.

Please anyone could help me please? I am trying to fix this for almost 3days now 😄

Please see below my flow.

When I copy and paste "The Title and Path are expressions: items('Drawings_Loop')['Title'] and items('Drawings_Loop')['Path'] ." It shows different on my flow