Automatic Report from SharePoint List - Office 365

%3CLINGO-SUB%20id%3D%22lingo-sub-393855%22%20slang%3D%22en-US%22%3EAutomatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393855%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20new%20to%20SharePointonline.%20I%20need%20to%20create%20a%20report%20(excel%2F%20other%20format)%20which%20need%20to%20capture%20today's%20new%20data%20from%20a%20list%20and%20send%20to%20manager%20through%20email%20on%20daily%20basis.%20Could%20anyone%20help%20me%20on%20this.%20Thanks%3C%2FP%3E%3CP%3E-%20Ray%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401310%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401310%22%20slang%3D%22en-US%22%3Eyou%20can%20not%20create%20Timer%20Jobs%20in%20SharePoint%20Online%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401309%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401309%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313427%22%20target%3D%22_blank%22%3E%40rayjack%3C%2FA%3Ean%20easy%20way%20to%20do%20this%20is%20with%20a%20MS%20Flow.%20see%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-a-flow-for-a-list-or-library-in-SharePoint-Online-or-OneDrive-for-Business-a9c3e03b-0654-46af-a254-20252e580d01%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-a-flow-for-a-list-or-library-in-SharePoint-Online-or-OneDrive-for-Business-a9c3e03b-0654-46af-a254-20252e580d01%3C%2FA%3E%20to%20get%20started%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401297%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401297%22%20slang%3D%22en-US%22%3EYou%20can%20easily%20do%20it%20but%20need%20to%20do%20some%20customization%20using%20Timer%20Job%20.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394053%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313427%22%20target%3D%22_blank%22%3E%40rayjack%3C%2FA%3E%26nbsp%3BOn%20the%20list%20%2F%20library%20you%20need%20to%20create%20a%20view.%20When%20creating%20the%20view%20you%20need%20to%20Filter%20by%20Modified%20or%20Created%20Column%20(Depending%20on%20which%20you%20want)%20and%20type%20%22%5BToday%5D%22%20in%20the%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20export%20the%20list%20you%20will%20have%20to%20filter%20it%20yourself.%20If%20you%20want%20a%20separate%20export%2C%20you'll%20need%20to%20have%20some%20custom%20development%20or%20look%20in%20the%20app%20store.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405272%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313427%22%20target%3D%22_blank%22%3E%40rayjack%3C%2FA%3E%26nbsp%3BI%20run%20a%20schedule%20task%20power%20shell%20script%20it%20would%20be%20something%20like%20this..%20you%20need%20a%20way%20to%20see%20when%20the%20entry%20was%20created.%20In%20this%20example%20i%20am%20assuming%20the%20information%20is%20held%20in%20%22%24entrydate%20you%20also%20will%20need%20to%20install%20the%20pnp%20module.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23Config%20Variables%3C%2FP%3E%3CP%3E%23get%20the%20credentials%20to%20log%20in%20to%20office%20365%3C%2FP%3E%3CP%3E%24Password%20%3D%20cat%20c%3A%5CScripts%5CRunAs%5Cxxxx.txt%20%7C%20ConvertTo-SecureString%3CBR%20%2F%3E%24Cred%20%3D%20new-object%20-typename%20System.Management.Automation.PSCredential%20-argumentlist%20%22user%40domain.com%22%2C%24Password%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%24SiteURL%3D%22%3CTHE%20url%3D%22%22%20of%3D%22%22%20the%3D%22%22%20sharepoint%3D%22%22%20site%3D%22%22%3E%22%3CBR%20%2F%3E%24ListName%3D%20%22%3CLIST%20name%3D%22%22%3E%22%3CBR%20%2F%3E%3CBR%20%2F%3E%23Connect%20to%20PNP%20Online%20to%20office%20365%3CBR%20%2F%3EConnect-PnPOnline%20-Url%20%24SiteURL%20-Credentials%20%24cred%3CBR%20%2F%3E%24data%3D%40()%3CBR%20%2F%3E%24FurtureDate%20%3D%20(Get-Date).adddays(%2B0)%3CBR%20%2F%3E%24strFutureDate%20%3D%20%24FurtureDate.ToString().Substring(0%2C10)%3CBR%20%2F%3E%24dateFile%20%3D%20%24strFutureDate%20-replace%20(%22%2F%22%2C%22_%22)%3C%2FLIST%3E%3C%2FTHE%3E%3C%2FP%3E%3CP%3E%24file%20%3D%20%22C%3A%5CScripts%5CReport_%24DATEFILE.csv%22%3CBR%20%2F%3E%24logfile%20%3D%20%22C%3A%5CScripts%5CSummary.log%22%3CBR%20%2F%3E%23Get%20All%20List%20Items%26nbsp%3B%3CBR%20%2F%3E%24ListItems%20%3D%20(Get-PnPListItem%20-List%20%24ListName%20-Fields%20%22field%201%22%2C%22EntryDate%22).FieldValues%3CBR%20%2F%3E%3CBR%20%2F%3E%23Loop%20through%20each%20Item%3CBR%20%2F%3Eforeach(%24ListItem%20in%20%24ListItems)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%3CBR%20%2F%3E%24field1%20%3D%20%24ListItem%5B%22field%201%22%5D%3CBR%20%2F%3E%24EntryDate%3D%20%24ListItem%5B%22EntryDate%22%5D%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%23find%20the%20new%20entries%3C%2FP%3E%3CP%3E%24EntryDate%22%3C%2FP%3E%3CP%3Eif((%24EntryDate.Date%20-eq%20%24FurtureDate.Date)%20)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%3CBR%20%2F%3E%24objReport%20%3D%20New-object%20-typename%20psobject%3C%2FP%3E%3CP%3E%24objReport%20%7C%20Add-Member%20-type%20NoteProperty%20-Name%20field%201-Value%20%24field1%3CBR%20%2F%3E%24objReport%20%7C%20Add-Member%20-type%20NoteProperty%20-Name%20field%202-Value%20%24ffield2%3CBR%20%2F%3E%3CBR%20%2F%3E%24Data%20%2B%3D%20%24objReport%3CBR%20%2F%3E%7D%20%23%20close%20if%3C%2FP%3E%3CP%3E%7D%20%23%20close%20for%20loop%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20(%24data.Count%20-gt%200)%20%23%20check%20that%20there%20is%20any%20new%20data%20to%20send%3CBR%20%2F%3E%7B%3C%2FP%3E%3CP%3E%24data%20%7C%20Export-Csv%20-Path%20%24FILE%20-NoTypeInformation%3CBR%20%2F%3E%24body%20%3D%20%22Hi%2C%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Please%20find%20attached%20a%20csv%20file%20attached%20%22%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESend-MailMessage%20-From%20%22username%40domain.com%22%20-To%20%22boss%40domain.com%22%20-Body%20%24body%20-Subject%20%22Report%22%20-SmtpServer%20%22smtp.office365.com%22%20-Credential%20%24cred%20-Port%20%22587%22%20-UseSsl%20-BodyAsHtml%20-Attachments%20%24file%20-Priority%20High%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Eget-date%20%7C%20out-file%20-FilePath%20%24logfile%20-Append%3CBR%20%2F%3E%22script%20ran%20on%20%24strFutureDate%22%20%7C%20out-file%20-FilePath%20%24logfile%20-Append%3CBR%20%2F%3E%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405407%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F285286%22%20target%3D%22_blank%22%3E%40Dr_A_Wells%3C%2FA%3E%26nbsp%3Bpersonally%20I%20think%20it's%20much%20easier%20to%20use%20a%20scheduled%20flow%20in%20Power%20Automate%20which%20would%20take%20less%20than%205%20minutes%20to%20prepare%20and%20test.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405968%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Report%20from%20SharePoint%20List%20-%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3BI%20have%20only%20started%20to%20use%20power%20automate%20recently.%20Mainly%20for%20approval%20process%20flows.%20For%20me%20that%20powershell%20script%20took%20only%20a%20few%20minutes%20because%20i%20was%20just%20grabbing%20together%20parts%20of%20other%20scripts%20i%20have%20already%20written.%20I%20would%20be%20less%20familiar%20doing%20string%20manipulation%2C%20and%20formatting%20the%20excel%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20good%20module%20for%20excel%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGet-Module%20-Name%20ImportExcel%20-ListAvailable%3C%2FP%3E%3CP%3EGet-Module%20-Name%20ImportExcel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20often%20many%20ways%20to%20do%20the%20same%20thing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor

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
Highlighted

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

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

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

Highlighted

@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

Highlighted

@Rob Elliott 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