Forum Discussion

rayjack's avatar
rayjack
Copper Contributor
Apr 03, 2019

Automatic Report from SharePoint List - Office 365

Hi, I am new to SharePointonline. I need to create a report (excel/ other format) which need to capture today's new data from a list and send to manager through email on daily basis. Could anyone help me on this. Thanks

- Ray

7 Replies

  • Dr_A_Wells's avatar
    Dr_A_Wells
    Copper Contributor

    rayjack I run a schedule task power shell script it would be something like this.. you need a way to see when the entry was created. In this example i am assuming the information is held in "$entrydate you also will need to install the pnp module. 

     

    #Config Variables

    #get the credentials to log in to office 365

    $Password = cat c:\Scripts\RunAs\xxxx.txt | ConvertTo-SecureString
    $Cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "user@domain.com",$Password


    $SiteURL="<the url of the sharepoint site>"
    $ListName= "<list name>"

    #Connect to PNP Online to office 365
    Connect-PnPOnline -Url $SiteURL -Credentials $cred
    $data=@()
    $FurtureDate = (Get-Date).adddays(+0)
    $strFutureDate = $FurtureDate.ToString().Substring(0,10)
    $dateFile = $strFutureDate -replace ("/","_")

    $file = "C:\Scripts\Report_$DATEFILE.csv"
    $logfile = "C:\Scripts\Summary.log"
    #Get All List Items 
    $ListItems = (Get-PnPListItem -List $ListName -Fields "field 1","EntryDate").FieldValues

    #Loop through each Item
    foreach($ListItem in $ListItems)
    {

    $field1 = $ListItem["field 1"]
    $EntryDate= $ListItem["EntryDate"]

    #find the new entries

    $EntryDate"

    if(($EntryDate.Date -eq $FurtureDate.Date) )
    {

    $objReport = New-object -typename psobject

    $objReport | Add-Member -type NoteProperty -Name field 1-Value $field1
    $objReport | Add-Member -type NoteProperty -Name field 2-Value $ffield2

    $Data += $objReport
    } # close if

    } # close for loop

     

    if ($data.Count -gt 0) # check that there is any new data to send
    {

    $data | Export-Csv -Path $FILE -NoTypeInformation
    $body = "Hi,<br> <br> Please find attached a csv file attached "


    Send-MailMessage -From "username@domain.com" -To "boss@domain.com" -Body $body -Subject "Report" -SmtpServer "smtp.office365.com" -Credential $cred -Port "587" -UseSsl -BodyAsHtml -Attachments $file -Priority High


    get-date | out-file -FilePath $logfile -Append
    "script ran on $strFutureDate" | out-file -FilePath $logfile -Append
    }

    • RobElliott's avatar
      RobElliott
      Silver Contributor

      Dr_A_Wells personally I think it's much easier to use a scheduled flow in Power Automate which would take less than 5 minutes to prepare and test.

       

      Rob
      Los Gallardos
      Microsoft Power Automate Community Super User

      • Dr_A_Wells's avatar
        Dr_A_Wells
        Copper Contributor

        RobElliott I have only started to use power automate recently. Mainly for approval process flows. For me that powershell script took only a few minutes because i was just grabbing together parts of other scripts i have already written. I would be less familiar doing string manipulation, and formatting the excel file. 

         

        There is a good module for excel 

         

        Get-Module -Name ImportExcel -ListAvailable

        Get-Module -Name ImportExcel

         

        There are often many ways to do the same thing.

         

        Alex

         

    • Dean_Gross's avatar
      Dean_Gross
      Silver Contributor
      you can not create Timer Jobs in SharePoint Online
  • Paul Telkamp's avatar
    Paul Telkamp
    Copper Contributor

    rayjack On the list / library you need to create a view. When creating the view you need to Filter by Modified or Created Column (Depending on which you want) and type "[Today]" in the value.

     

    If you export the list you will have to filter it yourself. If you want a separate export, you'll need to have some custom development or look in the app store.

Resources