Using Azure Functions to generate a report from SharePoint list

Azure Functions is a serverless computer offering from Azure which allows developers to focus on solutions rather than the infrastructure. Azure functions provide the capability to execute code based on triggered events.

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.

6. After the deployment job for creating the Function App has succeeded, open the Function App. Click New Function and then from the templates select “TimeTrigger-CSharp” template.
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.

2. In the Application settings page , under the App setting section add two new Key-Value pairs for user name and password. For e.g. o365username and o365pwd.

3. Click Save

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.