Forum Discussion
rayjack
Apr 03, 2019Copper Contributor
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
Sort By
- Dr_A_WellsCopper 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
}- RobElliottSilver 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_WellsCopper 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_GrossSilver Contributor
rayjackan easy way to do this is with a MS Flow. see https://support.office.com/en-us/article/Create-a-flow-for-a-list-or-library-in-SharePoint-Online-or-OneDrive-for-Business-a9c3e03b-0654-46af-a254-20252e580d01 to get started
- Rajkiran_SwainCopper ContributorYou can easily do it but need to do some customization using Timer Job .
- Dean_GrossSilver Contributoryou can not create Timer Jobs in SharePoint Online
- Paul TelkampCopper 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.