question

AlanDay-5585 avatar image
0 Votes"
AlanDay-5585 asked YiLu-MSFT commented

Create CSV file of all sites in a particular site collection in SharePoint online

I need to create a CSV list of all sites created under site collection .../sites/workspaces in our SharePoint Online tenancy. Is there a way to do this, preferably through either PowerShell or SharePoint Online Management Shell? I have tried numerous scripts that I have found through different forums without success.

office-sharepoint-onlineoffice-onedrive-client-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am currently doing some research on this issue, will let you know as soon as possible.

0 Votes 0 ·

1 Answer

YiLu-MSFT avatar image
0 Votes"
YiLu-MSFT answered YiLu-MSFT commented

Hi @AlanDay-5585
You could use this script:

 Connect-SPOService -Url https://contoso-admin.sharepoint.com
 $SiteURL = "https://crescent.sharepoint.com/sites/luyitest"
 $ExportFile ="c:\ListRpt.csv" 
 Try {
     #Get Credentials to connect
     $Cred= Get-Credential
     
     #Setup the context
     $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
     $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
       
     #Get the web
     $Web = $Ctx.web
     $Ctx.Load($Web)
         
     #get all subsites in sharepoint online powershell
     $Ctx.Load($Web.Webs)
     $Ctx.executeQuery()
     
 $SubsiteCollection = @()
 #Fetch each list item value to export to excel
  $web.Webs |  foreach {
     $ExportItem = New-Object PSObject
     $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_.Title
     $ExportItem | Add-Member -MemberType NoteProperty -Name "url" -value $_.url
       
     #Add the object with the above properties to the Array
     $SubsiteCollection += $ExportItem
  }
    
 #Export the result Array to CSV file
 $SubsiteCollection | Export-CSV $ExportFile -NoTypeInformation
     
 Write-host -f Green "List data Exported to CSV file successfully!"
 }
 Catch {
     write-host -f Red "Error:" $_.Exception.Message
 }


As a result, you will get a csv file like this:

205013-image.png



If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (9.3 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for that @YiLu-MSFT , tried the script, but ran into the following:
PS C:\Temp> C:\Temp\Subsites2.ps1
Import-Module : Could not load type 'Microsoft.SharePoint.Client.Publishing.PortalLaunch.PortalLaunchRedirectionType' from assembly
'Microsoft.SharePoint.Client.Publishing, Version=16.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.
At C:\Temp\Subsites2.ps1:1 char:1
+ Import-Module Microsoft.Online.SharePoint.Powershell
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Import-Module], TypeLoadException
+ FullyQualifiedErrorId : System.TypeLoadException,Microsoft.PowerShell.Commands.ImportModuleCommand


Connect-SPOService : The 'Connect-SPOService' command was found in the module 'Microsoft.Online.SharePoint.PowerShell', but the module could not be loaded. For more
information, run 'Import-Module Microsoft.Online.SharePoint.PowerShell'.
At C:\Temp\Subsites2.ps1:2 char:1
+ Connect-SPOService -Url https://sovereignhousingassociation-admin.sha ...
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Connect-SPOService:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CouldNotAutoloadMatchingModule


cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Error: Exception calling "ExecuteQuery" with "0" argument(s): "The sign-in name or password does not match one in the Microsoft account system."

I added the Import-Module into the script with no change, and credentials used have been verified as SharePoint admin on the tenancy

0 Votes 0 ·

Hi @AlanDay-5585
According to the error you met, it accurred when you use SharePoint Online command, please check whether you have installed SharePoint Online Management Shell or check the version of it.

0 Votes 0 ·

SharePoint Online Management Shell is version 16.0.22413.12000, which was installed two days ago. My colleague and I have done some remedial work on the server and the majority of the error is sorted, except for the last line, the Error:Exception referencing credentials. My account has basic authentication blocked, and this is causing the failure, as my colleague has basic authentication enabled and the script runs successfully. Could you please advise how to change the script to use interactive or MFA authentication?

0 Votes 0 ·

Hi @AlanDay-5585
Glad to hear that this answer could help you. As to use PowerShell to connect to SharePoint Online with MFA, you could refer to this article:
https://www.sharepointdiary.com/2019/08/connect-sharepoint-online-powershell-using-mfa.html


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




0 Votes 0 ·