In this blog we will see how to use Azure functions and generate report from data that is stored in a SharePoint list. The Azure function developed will essentially pull project status data from a SharePoint list and send out an email report on a weekly basis.
Creating the Azure Function
1. Logon to Azure Portal and click New --> Compute --> Function App
2. Give name for the Function App (e.g. WeeklyStatusReportFunction), select an Azure subscription and a Resource Group.
3. Azure Functions has two different hosting plans, Consumption plan and App Service plan. Select a hosting plan based on the requirement. In this example we would choose Consumption plan. Note: The hosting plan cannot be changed after creation of the function.
4. Each Function App requires a storage account to be specified as Functions internally uses the storage for managing triggers and logging. Create or link to a general-purpose Azure Storage account that supports Blob, Queue, and Table storage.
5. Click Create to create the Function.
7. Give name for the Function(e.g. WeeklyStatusReportFunction)
8. As we want the status report to be sent out every Friday at 8 PM, set the schedule as "0 00 20 * * 5". For more information on Azure Functions schedule syntax refer this article.
9. Click Create button to create the function.
Referencing SharePoint CSOM client libraries
As we would be using CSOM code to fetch project details from SharePoint list CSOM client libraries have to uploaded to the bin folder before they be can referenced from the function code.
1. Click Function app settings and then click Go to Kudu
2. In the Kudu interface navigate to site\wwwroot\<customfunction> folder. In our example this would be site\wwwroot\WeeklyStatusReportFunction
3. Create a folder named bin and navigate to the folder
4. Upload the Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll files to the folder.
Add User Name and Password values to App Settings
It is a good practice to refer username and password for connecting to SharePoint site from the App Settings instead of hard coding it within the function.
1. Click Function app settings and click Configure app settings button.
The Code
1. Refer SharePoint client libraries within the function code
#r "Microsoft.SharePoint.Client.Runtime.dll"
#r "Microsoft.SharePoint.Client.dll"
2. Within the run method of the function get the username and password for SharePoint connection from App settings
string userName = ConfigurationManager.AppSettings["o365username"];
string passwordString = ConfigurationManager.AppSettings["o365pwd"];
string SPSite = "https://xyz.sharepoint.com/sites/Site1/";
3. Connect to the SharePoint site
using (var ctx = new ClientContext(SPSite))
{
ctx.Credentials = new SharePointOnlineCredentials(userName, passWord);
Web web = ctx.Web;
ctx.Load(web);
ctx.ExecuteQuery();
}
4. Loop through the Project Tracker list and form a message string with the projects status
List myList = web.Lists.GetByTitle("Project Tracker");
CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
ListItemCollection collListItem = myList.GetItems(query);
ctx.Load(collListItem);
ctx.ExecuteQuery();
foreach (ListItem oListItem in collListItem)
{
string projectname = Convert.ToString(oListItem["Title"]);
string status= Convert.ToString(oListItem["Status"]);
mailmsg+="<tr><td>" + projectname + "</td><td>" + status + "</td></tr>";
}
5. Send mail using Office 365 SMTP
static void sendEmail(string mailmsg)
{
MailMessage msg = new MailMessage();
msg.To.Add(new MailAddress("xyz@spsite.onmicrosoft.com"));
msg.From = new MailAddress("xyz@spsite.onmicrosoft.com");
msg.Subject = "Weekly Project Status";
msg.Body = mailmsg;
msg.IsBodyHtml = true;
SmtpClient client = new SmtpClient();
client.UseDefaultCredentials = false;
client.Credentials = new System.Net.NetworkCredential(username,passwordString);
client.Port = 587;
client.Host = "smtp.office365.com";
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.EnableSsl = true;
client.Send(msg);
log.Info($"Weekly project status sent at: {DateTime.Now}");
}
Save and Run
1. Click Save to save the function. The function would be compiled and any compilation errors would be shown in the log window.
2. Clicking Run will allow you to execute the function immediately and log information will be logged to the log window.
