Unanswered

Export lists which are older than 3 months to Excel and delete those lists

(0) Share

Report

Posted on by 4tN0x

I have a tricky task for a flow. We regularly create lists on our SharePoint. If these lists are older than 3 months, they should be exported to Excel, the export named with the name of the list, saved in the SharePoint document library and the list then deleted. Does anyone have any ideas? Searches have not really brought me any further. I'm not getting anywhere with the "older than 3 months" point in particular.

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (1)

Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report
Hi @4tN0x,

Can you be a bit more specific when you are talking about the older than 3 months requirement?

Is it:

- The list has been created more than 3 months ago?

- List items haven't been modified/deleted by any user more than 3 months ago?

You could use the lists method of the REST API.

Here are a couple of examples.

Based on Created date

_api/web/lists?$filter=Created le datetime'@{addDays(utcNow(), -90)}'

Based on LastItemUserModifiedDate

_api/web/lists?$filter=LastItemUserModifiedDate le datetime'@{addDays(utcNow(), -90)}'

Based on LastItemDeleteDate

_api/web/lists?$filter=LastItemDeletedDate le datetime'@{addDays(utcNow(), -90)}'

Was this reply helpful? Yes No
4tN0x 13 on at

Like (0)

Report

Hi @Expiscornovus,
thanks for the ideas. I am a complete newcomer to flows. 😉
I meant that the lists were made more than three months ago.

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report
Hi @4tN0x,

Thanks for clarifying. In that case you could use that first example. I would also add some additional filtering to make sure we don't retrieve system related libraries or lists.

Let's first start with the export part. Try something like below.

1. Use a Send an HTTP request to SharePoint action to retrieve the lists.

In the filter we are checking the BaseTemplate 100 and that it is not hidden in the interface.

_api/web/lists?$filter=LastItemUserModifiedDate le datetime'@{addDays(utcNow(), -90)}' and BaseTemplate eq 100 and Hidden eq false

2. In an apply to each loop through the lists found

body('Send_an_HTTP_request_to_SharePoint_-_Created')['value']

3. With in the loop use another Send an HTTP request to SharePoint action to retrieve the items.

_api/web/lists/getbytitle('@{item()?['Title']}')/items

4. Use a Create CSV table with the output of the second HTTP action:

body('Send_an_HTTP_request_to_SharePoint_-_List_Items')['value']

5. Use a Create File to for example create a csv in a document library and use the output of the Create CSV table action in the file content

@{concat('Export',item()?['Title'],utcNow('ddMMyyyyHHmmss'))}.csv

Was this reply helpful? Yes No
4tN0x 13 on at

Like (0)

Report

@Expiscornovus, thank you very much.
I'm getting some errors within the action "Send_an_HTTP_request_to_SharePoint_-_List_Items" now, but I think they are related to the naming of the lists ...

For example:
{"odata.error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"web/lists/getbytitle('Sev2|INC2027139|Preu\u00dfsenElektra:Severallocationscan'taccessOutlook')/items\" is not valid."}}}

Most of the lists have been exported to my new SharePoint Library.
Would you be so kind as to give me a tip on how I can delete the exported lists afterwards?

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report
Hi @4tN0x,

You could use another REST API POST request for this with the guid of the list. You can retrieve that guid by using the item()?['id']

Below is an example within that apply to each loop

Uri

_api/web/lists(guid'@{item()?['Id']}')

Headers

{ "Accept": "application/json;odata=verbose", "Content-Type": "application/json;odata=verbose", "If-Match": "*", "X-HTTP-Method": "DELETE" }

In case of your error you with the renaming of the list you can actually also avoid that by using the same approach only using a GET with the standard Headers or use the getbyid method instead 😁

Was this reply helpful? Yes No
4tN0x 13 on at

Like (0)

Report

Hi @Expiscornovus

Cool, it seems to work. I have one more question: Is it possible to export the list to an Excel spreadsheet instead of a CSV file to ensure better readability directly?

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report

Hi @4tN0x,

There are several approaches for this.

A. Use the approach @takolota took in his cookbook, looping through the json array and add a new row to a table per item.

We already have the array with json. So, you would only need an additional nested apply to each which uses body('Send_an_HTTP_request_to_SharePoint_-_List_Items')['value']

Within that apply to each you can use the add new row to table action.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

B. Desktop flow; which opens up the csv and writes it into an Excel
https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/convert-csv-excel

C. Office script; which takes the data from a range of cells and copies it into a blank workbook

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv

Was this reply helpful? Yes No