Automatic Report from SharePoint List - Office 365

Copper Contributor

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

@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.

You can easily do it but need to do some customization using Timer Job .
you can not create Timer Jobs in SharePoint Online

@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
}

@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

@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