Unanswered

Get SharePoint list item history in bulk into excel

(0) Share

Report

Posted on by Community Power Pla...

Hi All,

I've got a flow that failed and now I'd need to extract the date of the status change to closed from a Sharepoint list. I have around 300 items to go through so would appreciate an automated solution.

My Sharepoint list contains tickets and for every closed ticket the closed date should be captured in the corresponding field. I am trying to create a flow which will go through each closed item and if the closed date is blank, extract the version history of the item (row, ticket) and find on which date the status was changed to 'Closed' (can be up to 2-3 months ago and is not the most recent action). Then I would like to populate the closed date for each ticket with the respective date (this part is optional, already having the actual closed date in an excel table is a big help).

I have started to create my flow based on this post Restore list items version history in bulk - Power Platform Community (microsoft.com) as it's very similar to my case.

However, I'm having 3 issues:

- One is with the 'Send an HTTP request V2' action as it won't accept my URI which I'm trying to set to

https://graph.microsoft.com/v1.0/sites/variables('SiteID')/lists/variables('ListID')/items

The example is using the regular request, however I could only find the V2 in my list of actions, not sure what difference that makes

- I'm trying to use the [value] from the first HTTP request to then cycle through the 'Apply to each', however I'm not able to extract the value as it should be using

body('Send_an_HTTP_request_V2')['value']

- In the 'Apply to each' I'm building the Uri for the HTTP request as below but it's not accepted either

_api/web/lists(guid'@{variables('ListID')}')/items(@{items('Apply_to_each')['ID']})/versions?$filter=('ClosedDate' ne null)&$select=Created,VersionLabel,VersionId,Status,ClosedDate

Any help on what to change would be appreciated, I don't have a lot of experience with HTTP requests and the like.

This is what my flow looks like for now:

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (0)

SamLed 2,338 Moderator on at

Like (0)

Report

Hi,

This will probably be a stupid question, but when the element gets closed status, is it intended to undergo further changes after?

If not, then modification date should be what you're looking for without having to dig into history.

[Edit] Ok, got the answer: (can be up to 2-3 months ago and is not the most recent action)
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No
SamLed 2,338 Moderator on at

Like (0)

Report

Hi,

Give a try with the following:
_api/web/lists/GetByTitle('[ListTitle]')/items(@{items('Apply_to_each')['ID']})/versions?$filter=(ClosedDate ne null)&$select=Created,VersionLabel,VersionId,Status,ClosedDate
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

There are two other PA scripts involved with the tickets.
- When a ticket status is changed to 'Closed' a script populates the 'Closed Date' for all tickets which are closed but don't have a closed date.
- Another PA script calculates the 'days open' for every open ticket and populates the data. Once a ticket has a closed date, the days open are not changed anymore.

Unfortunately the first script failed populating the closed that and therefore the second script kept updating the 'days open' over the past months and so the latest actions are updates of 'days open' and not the change in status.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi SamLed,

I am actually not able to get to that point in the flow as it fails already at the 'Send an HTTP request V2' point earlier in the flow. In it's current state PA is not liking the URI that I provided despite me following the convention it's asking for and matches the link to the SP list:

Was this reply helpful? Yes No
SamLed 2,338 Moderator on at

Like (0)

Report

Hi,

I don't get why in other topic he uses Graph endpoint to get items. Except there is a specific reason he didn't explained, I would rather use "Get items" action or _api/web/lists/GetByTitle('<listtitle>')/items using "Send an HTTP request to SharePoint" if you really want to use an HTTP call.
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
Hi,

I've now changed to using 'Get Item' from Sharepoint list, which seems to have fixed the issue.

However, the issue with my HTTP call is not going away. Even if I just use a simple GET request, it still doesn't like my URI so I suspect there's some bigger/deeper issue at hand. I've also tried to work without variables so that the list name matches what's available from the Site Address, but no luck.
_api/web/lists/getbytitle('variables('ListID')')/items({items('Apply_to_each')?['ID']}) _api/web/lists/getbytitle('variables('ListID')')/items(@{items('Apply_to_each')['ID']}) _api/web/lists/GetByTitle('mylistname')/items(@{items('Apply_to_each')?['ID']})
Neither is of these was accepted. I assume that if even such a simple request fails then there must be something wrong outside of the script. Maybe access issues?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

OK, I think I've identified the issue.
I need to have the URI as plain text and not as an expression, doesn't accept the latter.

It's not working yet, however it's good enough to save and now I can start debugging the deeper issues.

What seems to work for now is:

Was this reply helpful? Yes No
SamLed 2,338 Moderator on at

Like (1)

Report

Hi,

Remove quotes, it's a field name not a string value:
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Hi,
irrespective of that I'm having troubles connecting to the SP list. I've simplified the retrieval so that I'm just trying to retrieve some items (any item will do for now), however in both cases I'm getting a resource/list not found error. I've now tried to connect both via GetByTitle() and guid() (in case I got one of them wrong) and in both cases I'm getting an error message that the resource/list can not be found.
Cannot find resource for the request guid. List '****' does not exist at site with URL...

But that seems to be a topic that's beyond the original topic, even though I'd appreciate any help I can get.

Was this reply helpful? Yes No
SamLed 2,338 Moderator on at

Like (0)

Report

Hi,
Yeah, because when you use GetByTitle() you give it ListID variable, that should be the list title.
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No