Export SharePoint List to Excel with PowerShell

This video takes a viewer request and makes it into a video. We take a SharePoint List and export it to a CSV file using PowerShell. Would work for both SharePoint Online and SharePoint On-Prem.

Video for installing PNP PowerShell

Video for Arrays with PowerShell

Video for ForEach

Video for creating SharePoint lists

Link to Download the PowerShell

[TomResing]

Moving list data to and from excel is something I've done more than a few times in the years with SharePoint. This is good alternative for anyone comfortable with or learning PowerShell.

[vinoddevops]

What a coincidence, Happy Thanks Giving!. Thank you for teaching powershell easy way.

[robertjones]

Thanks Shane - very helpful - I'm a beginner with powershell. I do a lot of excel stuff at work. I'm trying to figure out where to apply some PS magic.

[NancyRileyNuniverse]

Another awesome video, Shane!!. Keep doing what you are doing, you make a difference!!. Thanks so much... 😊

[hudzani]

Thanks a lot Shane for the great vid it really helps. Could you please assist whether i can export a column containing links to documents stored in Sharepoint without loosing the hyperlink to those documents. And another question is, when you export the list to csv, does the csv get spooled to the same server or can you send the csv to a different server or location. Thanks.

[sakshamrastogi]

Shane, thanks for this brilliant lesson however I have a query in this same thing that when I am doing the same of getting the items from the list using pnp then I try to get the field values, then I don't get some metadata columns for some specific lists. Instead it gives the column id. Let me know if you have any suggestions on this

[lahernandezs]

you did it again my friend! thanks a lot!

[mansimittal]

I have a question. I am getting 'error (429) too many requests' while using invoke - webrequest to hit a number of urls for the same site using powershell. Any idea to solve this?

[raredesign]

Great vid, but I have a question. In order to be performant, PnP PowerShell does not return all properties. After connecting to the list, I am trying to capture all fields inline, but I am having difficulty returning the Person/Group lookup DisplayName, UserName or LoginName. Email works though, so here is an example:

$items | ForEach-Object{New-Object PSObject -Property | ConvertTo-Json

This returns the array of email addresses, but I need an array of their names. Any idea how I can leverage Get-PnPUser within the expression?

Update: I found that LookupValue contains the name, but it would be nice to be able to access the other properties using Get-PnPUser in a statement such as yours.
I tried:
'People' = Get-PnPUser | but it didn't like it. Any idea?

[robertwleonard]

Loving your videos. I'm a dev at a school district that's really pushing O365 this year. I was curious if you had any tips on OneDrive/OneNote migration between tenants. I thought I could do it through a PS Graph API app (I could grab all the ID's, shared permissions, and all that), but when I got to the copying from one tenant to another step, I realized it doesn't work like that. I was curious if you had any suggestions on a better way to do this in PowerShell. Great work on the channel, brother.

[joakimmellqvist]

Very nice video. Thanks alot, helped me a bunch!

[chaitanyavarma]

Hi Shane Young. How to import csv file to Sharepoint list using powershell. can u make that video.

[luillyvasgas]

Any idea about how to fix the issue when the list is greater than 5000 item the scrip return an error,

[sureshg]

Hi Its nice video. But i have one question. when i export to excel ..My user dont want the options like open, save, then after enable prompt. direct he wants to open the data in excel ? is this possible? if yes how can i do that? any useful links please share it will be helpful?
Best
Chanty

[energysystemscatapultitdep]

If you use the
Find-Module -Name ImportExcel | Install-Module
Export-Excel $hashtable -AutoSize -StartRow 2 -TableName video

it will look better

[haydendyne]

Can you do a video on downloading an email attachment?