none
Extract all documents and metadata in SharePoint Online

Réponses

  • Definitely not going over my head. I have written the below script in powershell using CSOM to fetch all files from folders and sub folder in the current site collection, web and sub webs. I have displayed the list of file metadata (no of files returned in thousands) to the CSV spreadsheet.

    Please enter the User login, Site URL. When you execute the script it will ask you for password to connect to office365.

    #Specify tenant admin and site URL
    $User = ""
    $SiteURL = ""
    #Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
    Add-Type -Path {C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll}
    Add-Type -Path {C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll}

    $Password = Read-Host -Prompt 'Enter password' -AsSecureString

    #Constant Variables
    $OutputFile = "C:\Data.csv"   #The CSV Output file that is created, change for your purposes

    #Bind to site collection
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
    $Context.Credentials = $Creds

    $ImportFile = Import-Csv $OutputFile -Delimiter ',' -Header WebName, DocumentLibraryName, Sensitivity, Type, FileName, FileURL, CreatedBy, ModifiedBy, CreatedAt, ModifiedAt
    $exportCollection = @()

    $Context.Load($Context.Web)
    $Context.ExecuteQuery()
     
    foreach($OuterWeb in $Context.Web)
    {
        $Context.Load($OuterWeb.Webs)
        $Context.ExecuteQuery()

        $OuterLists = $OuterWeb.Lists
        $Context.Load($OuterLists)
        $Context.ExecuteQuery()

        foreach($List in $OuterLists)
        {
            if($List.IsApplicationList -eq $false -and $List.BaseType -eq "DocumentLibrary" -and $List.Hidden -eq $false -and $List.Title -ne "Form Templates" -and $List.Title -ne "Images" -and $List.Title -ne "Style Library" -and $List.Title -ne "Web Part Gallery" -and $List.Title -ne "Pages")
            {

                 #function begins
                        function Recurse($RootFolder,$List) {
                            $Context.Load($RootFolder)
                            $Context.Load($RootFolder.Folders)
                            $Context.ExecuteQuery()

                            $Context.Load($RootFolder.Files)
                            $Context.ExecuteQuery()
                            $resultCollection = @()
                            foreach($file in $RootFolder.Files)
                            {
                                $resultCollection += $file
                            }

                            foreach($folder in $RootFolder.Folders)
                            {
                                if($Folder.Name -ne "Forms")
                                {     
                                   Recurse $folder $List 
                                }
                             }      
                                Return $resultCollection
                            }
                        # Function ends

                        $Context.Load($List.RootFolder)
                        $Context.Load($List.RootFolder.Folders)
                        $Context.ExecuteQuery()

                        if($List.RootFolder.ItemCount -gt 0)
                        {
                            $Files = Recurse $List.RootFolder $List
                            foreach($file in $Files)
                            {
                                if($file -ne $Null)
                                {
                                Write-Host $Web.Title, $List.Title, $file.Name
                                $Context.Load($file)
                                $Context.ExecuteQuery()
                                }
                                $ImportFile = "" | Select WebName, DocumentLibraryName, Sensitivity, Type, FileName, FileURL, CreatedBy, ModifiedBy, CreatedAt, ModifiedAt
                                if(-not($file.Name -eq $Null))
                                {
                                if($file.Author -ne $Null)
                                {
                                    $CreatedUser = $file.Author
                                    $Context.Load($CreatedUser)
                                    $Context.ExecuteQuery()
                                    $CreatedUserId = $CreatedUser.LoginName
                                }
                                if($file.ModifiedBy -ne $Null)
                                {
                                    $ModifiedUser = $file.ModifiedBy
                                    $Context.Load($ModifiedUser)
                                    $Context.ExecuteQuery()
                                    $ModifiedUserId = $ModifiedUser.LoginName
                                }
                       
                                if(-Not($file.Name.EndsWith(".webpart") -or $file.Name.EndsWith(".wsp") -or $file.Name.EndsWith(".js") -or $file.Name.EndsWith(".dwp") -or $file.Name.EndsWith(".stp")))
                                {
                                    if($Web.Title -ne $null)
                                    {
                                        $ImportFile.WebName = $Web.Title
                                    }
                          
                                    if($List.Title -ne $null)
                                    {
                                        $ImportFile.DocumentLibraryName = $List.Title
                                        if($List.title.Contains("Classified"))
                                        {
                                          $ImportFile.Sensitivity = "Classified"
                                        }
                                        else
                                        {
                                          $ImportFile.Sensitivity = "UnClassified"
                                        }

                                    }
                                    if($file.Name -ne $Null)
                                    {
                                        $ImportFile.FileName = $file.Name
                                    }
                                    if($file.ServerRelativeUrl -ne $Null)
                                    {
                                        $ImportFile.FileURL = $file.ServerRelativeUrl
                                        $var = $file.ServerRelativeUrl
                                        $Length = $var.LastIndexOf("/")
                                        $subString1 = $var.Substring(0,$Length)
                                        $SubLength = $subString1.LastIndexOf("/")
                                        $var = $subString1.Substring($SubLength+1, ($Length-$SubLength)-1)
                                        $ImportFile.Type = $var
                                    }
                                    if($CreatedUserId -ne $Null)
                                    {
                                        $ImportFile.CreatedBy = $CreatedUserId
                                    }
                                    if($ModifiedUserId -ne $Null)
                                    {
                                         $ImportFile.ModifiedBy = $ModifiedUserId
                                    }
                                    if($file.TimeCreated -ne $Null)
                                    {
                                        $ImportFile.CreatedAt = $file.TimeCreated
                                    }
                                    if($file.TimeLastModified -ne $Null)
                                    {
                                        $ImportFile.ModifiedAt = $file.TimeLastModified
                                    }   
                                    $exportCollection += $ImportFile   
                                    }
                             }
                                    }
                               
                        }
               
              
            }
        }

        foreach($Web in $OuterWeb.Webs)
        {

                $Lists = $Web.Lists
                $Context.Load($Lists)
                $Context.ExecuteQuery()

                foreach($List in $Lists)
                {
                 if($List.IsApplicationList -eq $false -and $List.BaseType -eq "DocumentLibrary" -and $List.Hidden -eq $false -and $List.Title -ne "Form Templates" -and $List.Title -ne "Images" -and $List.Title -ne "Style Library" -and $List.Title -ne "Web Part Gallery"  -and $List.Title -ne "Pages")
                    {
                        #function begins
                        function Recurse($RootFolder,$List) {
                            $Context.Load($RootFolder)
                            $Context.Load($RootFolder.Folders)
                            $Context.ExecuteQuery()

                            $Context.Load($RootFolder.Files)
                            $Context.ExecuteQuery()
                            $resultCollection = @()
                            foreach($file in $RootFolder.Files)
                            {
                                $resultCollection += $file
                            }

                            foreach($folder in $RootFolder.Folders)
                            {
                                if($Folder.Name -ne "Forms")
                                {     
                                   Recurse $folder $List 
                                }
                             }      
                                Return $resultCollection
                            }
                        # Function ends

                        $Context.Load($List.RootFolder)
                        $Context.Load($List.RootFolder.Folders)
                        $Context.ExecuteQuery()

                        if($List.RootFolder.ItemCount -gt 0)
                        {
                            $Files = Recurse $List.RootFolder $List
                            foreach($file in $Files)
                            {
                                if($file -ne $Null)
                                {
                                Write-Host $Web.Title, $List.Title, $file.Name
                                $Context.Load($file)
                                $Context.ExecuteQuery()
                                }
                                $ImportFile = "" | Select WebName, DocumentLibraryName, Sensitivity, Type, FileName, FileURL, CreatedBy, ModifiedBy, CreatedAt, ModifiedAt
                                if(-not($file.Name -eq $Null))
                                {
                               
                                if($file.Author -ne $Null)
                                {
                                    $CreatedUser = $file.Author
                                    $Context.Load($CreatedUser)
                                    $Context.ExecuteQuery()
                                    $CreatedUserId = $CreatedUser.LoginName
                                }
                                if($file.ModifiedBy -ne $Null)
                                {
                                    $ModifiedUser = $file.ModifiedBy
                                    $Context.Load($ModifiedUser)
                                    $Context.ExecuteQuery()
                                    $ModifiedUserId = $ModifiedUser.LoginName
                                }
                       
                                if(-Not($file.Name.EndsWith(".webpart") -or $file.Name.EndsWith(".wsp") -or $file.Name.EndsWith(".js") -or $file.Name.EndsWith(".dwp") -or $file.Name.EndsWith(".stp")))
                                {
                                    if($Web.Title -ne $null)
                                    {
                                        $ImportFile.WebName = $Web.Title
                                    }
                          
                                    if($List.Title -ne $null)
                                    {
                                        $ImportFile.DocumentLibraryName = $List.Title
                                         if($List.title.Contains("Classified"))
                                        {
                                          $ImportFile.Sensitivity = "Classified"
                                        }
                                        else
                                        {
                                          $ImportFile.Sensitivity = "UnClassified"
                                        }
                                    }
                                    if($file.Name -ne $Null)
                                    {
                                        $ImportFile.FileName = $file.Name
                                    }
                                    if($file.ServerRelativeUrl -ne $Null)
                                    {
                                        $ImportFile.FileURL = $file.ServerRelativeUrl
                                        $var = $file.ServerRelativeUrl
                                        $Length = $var.LastIndexOf("/")
                                        $subString1 = $var.Substring(0,$Length)
                                        $SubLength = $subString1.LastIndexOf("/")
                                        $var = $subString1.Substring($SubLength+1, ($Length-$SubLength)-1)
                                        $ImportFile.Type = $var
                                    }
                                    if($CreatedUserId -ne $Null)
                                    {
                                        $ImportFile.CreatedBy = $CreatedUserId
                                    }
                                    if($ModifiedUserId -ne $Null)
                                    {
                                         $ImportFile.ModifiedBy = $ModifiedUserId
                                    }
                                    if($file.TimeCreated -ne $Null)
                                    {
                                        $ImportFile.CreatedAt = $file.TimeCreated
                                    }
                                    if($file.TimeLastModified -ne $Null)
                                    {
                                        $ImportFile.ModifiedAt = $file.TimeLastModified
                                    }   
                                    $exportCollection += $ImportFile   
                                    }
                             }
                                    }
                               
                        }
                     }
            }
        }
     }    
        $exportCollection | Export-Csv -Path $OutputFile -Force -NoTypeInformation

     

     

    • Marqué comme réponse Aammiitt22 mardi 29 avril 2014 07:10
    mardi 29 avril 2014 07:06

Toutes les réponses

  • You can get all that by leveraging the CSOM, list metadata, collect metadata and download them. Check out the core clients section at http://msdn.microsoft.com/en-us/library/office/jj193041(v=office.15).aspx

    You will have to create a custom program or PowerShell script to do it though.


    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com

    jeudi 17 avril 2014 06:40
    Modérateur
  • Hi Margriet,

    I have found the below link,

    http://www.mysharepointadventures.com/2013/09/powershell-script-to-generate-report-on-all-documents-in-site-collection-client-side/

    Is there a way I can do it from the UI in SharePoint Online instead of using Powershell? This is for the semi IT guys who do not have much idea on running these scripts and they prefer working with UI.

    Thanks,

    Amit

    jeudi 17 avril 2014 07:02
  • You can't use the majority of scripts, that one included, with SharePoint Online. Doing this manually will be a bit slow but you can browse to each list and library and export a list of the items there to excel.

    Margriet's link is a good one but it does sound like it's going to be way over your head.

    What exactly are you trying to achieve?

    jeudi 17 avril 2014 07:44
  • First you can use SharePointOnlineCredentials class to connect to your tenant of SharePoint Online

    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.sharepointonlinecredentials.aspx

    Then you can leverage the managed .NET CSOM or REST apis to fetch the list of documents

    http://msdn.microsoft.com/en-us/library/office/jj193041(v=office.15).aspx

    Then you can leverage ListItem.GetFieldValuesAsText property in the Microsoft.SharePoint.Client namespace to get the list of tags associated with the documents.


    Please mark the replies as answers if they help or unmark if not.

    samedi 19 avril 2014 06:17
  • Hi,

    Should I be using the office 365 powershell or SharePoint Online Powershell to run the powershell script against SharePoint Online to get the list of all documents and metadata.

    Are the below set of steps correct?

    Step 1: Start Windows PowerShell as an Administrator

    Step 2: Create a Windows PowerShell Credentials Object

    Step 3: Connect to Office 365

    Step 4: Connect to SharePoint Online

    Step5: Run the script using CSOM to fetch the list of documents and metadata.

    Thanks,

    Amit

    mardi 22 avril 2014 06:54
  • Definitely not going over my head. I have written the below script in powershell using CSOM to fetch all files from folders and sub folder in the current site collection, web and sub webs. I have displayed the list of file metadata (no of files returned in thousands) to the CSV spreadsheet.

    Please enter the User login, Site URL. When you execute the script it will ask you for password to connect to office365.

    #Specify tenant admin and site URL
    $User = ""
    $SiteURL = ""
    #Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
    Add-Type -Path {C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll}
    Add-Type -Path {C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll}

    $Password = Read-Host -Prompt 'Enter password' -AsSecureString

    #Constant Variables
    $OutputFile = "C:\Data.csv"   #The CSV Output file that is created, change for your purposes

    #Bind to site collection
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
    $Context.Credentials = $Creds

    $ImportFile = Import-Csv $OutputFile -Delimiter ',' -Header WebName, DocumentLibraryName, Sensitivity, Type, FileName, FileURL, CreatedBy, ModifiedBy, CreatedAt, ModifiedAt
    $exportCollection = @()

    $Context.Load($Context.Web)
    $Context.ExecuteQuery()
     
    foreach($OuterWeb in $Context.Web)
    {
        $Context.Load($OuterWeb.Webs)
        $Context.ExecuteQuery()

        $OuterLists = $OuterWeb.Lists
        $Context.Load($OuterLists)
        $Context.ExecuteQuery()

        foreach($List in $OuterLists)
        {
            if($List.IsApplicationList -eq $false -and $List.BaseType -eq "DocumentLibrary" -and $List.Hidden -eq $false -and $List.Title -ne "Form Templates" -and $List.Title -ne "Images" -and $List.Title -ne "Style Library" -and $List.Title -ne "Web Part Gallery" -and $List.Title -ne "Pages")
            {

                 #function begins
                        function Recurse($RootFolder,$List) {
                            $Context.Load($RootFolder)
                            $Context.Load($RootFolder.Folders)
                            $Context.ExecuteQuery()

                            $Context.Load($RootFolder.Files)
                            $Context.ExecuteQuery()
                            $resultCollection = @()
                            foreach($file in $RootFolder.Files)
                            {
                                $resultCollection += $file
                            }

                            foreach($folder in $RootFolder.Folders)
                            {
                                if($Folder.Name -ne "Forms")
                                {     
                                   Recurse $folder $List 
                                }
                             }      
                                Return $resultCollection
                            }
                        # Function ends

                        $Context.Load($List.RootFolder)
                        $Context.Load($List.RootFolder.Folders)
                        $Context.ExecuteQuery()

                        if($List.RootFolder.ItemCount -gt 0)
                        {
                            $Files = Recurse $List.RootFolder $List
                            foreach($file in $Files)
                            {
                                if($file -ne $Null)
                                {
                                Write-Host $Web.Title, $List.Title, $file.Name
                                $Context.Load($file)
                                $Context.ExecuteQuery()
                                }
                                $ImportFile = "" | Select WebName, DocumentLibraryName, Sensitivity, Type, FileName, FileURL, CreatedBy, ModifiedBy, CreatedAt, ModifiedAt
                                if(-not($file.Name -eq $Null))
                                {
                                if($file.Author -ne $Null)
                                {
                                    $CreatedUser = $file.Author
                                    $Context.Load($CreatedUser)
                                    $Context.ExecuteQuery()
                                    $CreatedUserId = $CreatedUser.LoginName
                                }
                                if($file.ModifiedBy -ne $Null)
                                {
                                    $ModifiedUser = $file.ModifiedBy
                                    $Context.Load($ModifiedUser)
                                    $Context.ExecuteQuery()
                                    $ModifiedUserId = $ModifiedUser.LoginName
                                }
                       
                                if(-Not($file.Name.EndsWith(".webpart") -or $file.Name.EndsWith(".wsp") -or $file.Name.EndsWith(".js") -or $file.Name.EndsWith(".dwp") -or $file.Name.EndsWith(".stp")))
                                {
                                    if($Web.Title -ne $null)
                                    {
                                        $ImportFile.WebName = $Web.Title
                                    }
                          
                                    if($List.Title -ne $null)
                                    {
                                        $ImportFile.DocumentLibraryName = $List.Title
                                        if($List.title.Contains("Classified"))
                                        {
                                          $ImportFile.Sensitivity = "Classified"
                                        }
                                        else
                                        {
                                          $ImportFile.Sensitivity = "UnClassified"
                                        }

                                    }
                                    if($file.Name -ne $Null)
                                    {
                                        $ImportFile.FileName = $file.Name
                                    }
                                    if($file.ServerRelativeUrl -ne $Null)
                                    {
                                        $ImportFile.FileURL = $file.ServerRelativeUrl
                                        $var = $file.ServerRelativeUrl
                                        $Length = $var.LastIndexOf("/")
                                        $subString1 = $var.Substring(0,$Length)
                                        $SubLength = $subString1.LastIndexOf("/")
                                        $var = $subString1.Substring($SubLength+1, ($Length-$SubLength)-1)
                                        $ImportFile.Type = $var
                                    }
                                    if($CreatedUserId -ne $Null)
                                    {
                                        $ImportFile.CreatedBy = $CreatedUserId
                                    }
                                    if($ModifiedUserId -ne $Null)
                                    {
                                         $ImportFile.ModifiedBy = $ModifiedUserId
                                    }
                                    if($file.TimeCreated -ne $Null)
                                    {
                                        $ImportFile.CreatedAt = $file.TimeCreated
                                    }
                                    if($file.TimeLastModified -ne $Null)
                                    {
                                        $ImportFile.ModifiedAt = $file.TimeLastModified
                                    }   
                                    $exportCollection += $ImportFile   
                                    }
                             }
                                    }
                               
                        }
               
              
            }
        }

        foreach($Web in $OuterWeb.Webs)
        {

                $Lists = $Web.Lists
                $Context.Load($Lists)
                $Context.ExecuteQuery()

                foreach($List in $Lists)
                {
                 if($List.IsApplicationList -eq $false -and $List.BaseType -eq "DocumentLibrary" -and $List.Hidden -eq $false -and $List.Title -ne "Form Templates" -and $List.Title -ne "Images" -and $List.Title -ne "Style Library" -and $List.Title -ne "Web Part Gallery"  -and $List.Title -ne "Pages")
                    {
                        #function begins
                        function Recurse($RootFolder,$List) {
                            $Context.Load($RootFolder)
                            $Context.Load($RootFolder.Folders)
                            $Context.ExecuteQuery()

                            $Context.Load($RootFolder.Files)
                            $Context.ExecuteQuery()
                            $resultCollection = @()
                            foreach($file in $RootFolder.Files)
                            {
                                $resultCollection += $file
                            }

                            foreach($folder in $RootFolder.Folders)
                            {
                                if($Folder.Name -ne "Forms")
                                {     
                                   Recurse $folder $List 
                                }
                             }      
                                Return $resultCollection
                            }
                        # Function ends

                        $Context.Load($List.RootFolder)
                        $Context.Load($List.RootFolder.Folders)
                        $Context.ExecuteQuery()

                        if($List.RootFolder.ItemCount -gt 0)
                        {
                            $Files = Recurse $List.RootFolder $List
                            foreach($file in $Files)
                            {
                                if($file -ne $Null)
                                {
                                Write-Host $Web.Title, $List.Title, $file.Name
                                $Context.Load($file)
                                $Context.ExecuteQuery()
                                }
                                $ImportFile = "" | Select WebName, DocumentLibraryName, Sensitivity, Type, FileName, FileURL, CreatedBy, ModifiedBy, CreatedAt, ModifiedAt
                                if(-not($file.Name -eq $Null))
                                {
                               
                                if($file.Author -ne $Null)
                                {
                                    $CreatedUser = $file.Author
                                    $Context.Load($CreatedUser)
                                    $Context.ExecuteQuery()
                                    $CreatedUserId = $CreatedUser.LoginName
                                }
                                if($file.ModifiedBy -ne $Null)
                                {
                                    $ModifiedUser = $file.ModifiedBy
                                    $Context.Load($ModifiedUser)
                                    $Context.ExecuteQuery()
                                    $ModifiedUserId = $ModifiedUser.LoginName
                                }
                       
                                if(-Not($file.Name.EndsWith(".webpart") -or $file.Name.EndsWith(".wsp") -or $file.Name.EndsWith(".js") -or $file.Name.EndsWith(".dwp") -or $file.Name.EndsWith(".stp")))
                                {
                                    if($Web.Title -ne $null)
                                    {
                                        $ImportFile.WebName = $Web.Title
                                    }
                          
                                    if($List.Title -ne $null)
                                    {
                                        $ImportFile.DocumentLibraryName = $List.Title
                                         if($List.title.Contains("Classified"))
                                        {
                                          $ImportFile.Sensitivity = "Classified"
                                        }
                                        else
                                        {
                                          $ImportFile.Sensitivity = "UnClassified"
                                        }
                                    }
                                    if($file.Name -ne $Null)
                                    {
                                        $ImportFile.FileName = $file.Name
                                    }
                                    if($file.ServerRelativeUrl -ne $Null)
                                    {
                                        $ImportFile.FileURL = $file.ServerRelativeUrl
                                        $var = $file.ServerRelativeUrl
                                        $Length = $var.LastIndexOf("/")
                                        $subString1 = $var.Substring(0,$Length)
                                        $SubLength = $subString1.LastIndexOf("/")
                                        $var = $subString1.Substring($SubLength+1, ($Length-$SubLength)-1)
                                        $ImportFile.Type = $var
                                    }
                                    if($CreatedUserId -ne $Null)
                                    {
                                        $ImportFile.CreatedBy = $CreatedUserId
                                    }
                                    if($ModifiedUserId -ne $Null)
                                    {
                                         $ImportFile.ModifiedBy = $ModifiedUserId
                                    }
                                    if($file.TimeCreated -ne $Null)
                                    {
                                        $ImportFile.CreatedAt = $file.TimeCreated
                                    }
                                    if($file.TimeLastModified -ne $Null)
                                    {
                                        $ImportFile.ModifiedAt = $file.TimeLastModified
                                    }   
                                    $exportCollection += $ImportFile   
                                    }
                             }
                                    }
                               
                        }
                     }
            }
        }
     }    
        $exportCollection | Export-Csv -Path $OutputFile -Force -NoTypeInformation

     

     

    • Marqué comme réponse Aammiitt22 mardi 29 avril 2014 07:10
    mardi 29 avril 2014 07:06
  • This is really awesome... Thank you so much for sharing this script.. Its working fine..

     
    vendredi 18 mai 2018 05:28