Apr 03 2019 07:20 AM - edited Apr 03 2019 09:31 AM
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
Apr 03 2019 11:27 AM
@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.
Apr 06 2019 12:07 PM
Apr 06 2019 12:41 PM
@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... to get started
Apr 06 2019 12:42 PM
May 20 2020 03:22 AM
@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
}
May 20 2020 04:32 AM
@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
May 20 2020 08:20 AM
@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