[Azure] From Function to SharePoint List Item

January 2, 2017January 1, 2017 Jasper Siegmund Technical

This article describes how to insert an item into a SharePoint list using an Azure Function written in C#. Might seem like a trivial task, but there are some caveats you might want to take notice of before you start.

Graph vs SharePoint REST API

To begin with, we need to choose how we’re going to insert items in SharePoint lists. The Microsoft Graph should be your consideration for every API action in Office365, but the SharePoint endpoints are still in beta at this moment. For production systems, I would always recommend waiting a bit until the technology is out of beta.

So if we consider Graph as “not an option”, we need to look at what SharePoint offers itself, instead. There’s multiple ways to Rome, but the REST API surely is one of the most logical ones, so we’ll go with that. Added benefit is that with the REST API you can pretty much do anything, so this article can be used as the foundation for any Function that does something in SharePoint.

Authentication

The next thing to consider is how to go about authentication. In case of an Azure Function, there’s no user interaction possible. So we need to authenticate without the need for a UI where a user enters credentials or delegates access (OAuth) in any way. This is known as an app-only (or add-in only) action in SharePoint and it requires configuration and getting an access token.

Important: in all of the examples for Microsoft Graph or other Azure AD based authentication, the access token comes from Azure AD. SharePoint Online does not support these tokens (yet, hopefully) so you can’t use such a token!

Ok, so how are we going to get this access token? Having a client ID and secret (read on…), you can get one from the access control token endpoint. Here’s the code you’ll need:

public static async Task<string> GetAuthTokenForSharePoint()
{
    HttpClient client = new HttpClient();

    string clientId = System.Configuration.ConfigurationManager.AppSettings["SP_CLIENT_ID"];
    string clientSecret = System.Configuration.ConfigurationManager.AppSettings["SP_CLIENT_SECRET"];
    string tenantId = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_ID"];
    string spTenantUrl = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_URL"];
    string spPrinciple = "00000003-0000-0ff1-ce00-000000000000";
    string spAuthUrl = "https://accounts.accesscontrol.windows.net/" + tenantId + "/tokens/OAuth/2";

    KeyValuePair<string, string>[] body = new KeyValuePair<string, string>[]
    {
        new KeyValuePair<string, string>("grant_type", "client_credentials"),
        new KeyValuePair<string, string>("client_id", $"{clientId}@{tenantId}"),
        new KeyValuePair<string, string>("resource", $"{spPrinciple}/{spTenantUrl}@{tenantId}".Replace("https://", "")),
        new KeyValuePair<string, string>("client_secret", clientSecret)
    };

    var content = new FormUrlEncodedContent(body);
    var contentLength = content.ToString().Length;

    string token = "";

    using (HttpResponseMessage response = await client.PostAsync(spAuthUrl, content))
    {
        if (response.Content != null)
        {
            string responseString = await response.Content.ReadAsStringAsync();
            JObject data = JObject.Parse(responseString);
            token = data.Value<string>("access_token");
        }
    }

    return token;
}

public static async Task<string> GetAuthTokenForSharePoint()

{

HttpClient client = new HttpClient();

string clientId = System.Configuration.ConfigurationManager.AppSettings["SP_CLIENT_ID"];

string clientSecret = System.Configuration.ConfigurationManager.AppSettings["SP_CLIENT_SECRET"];

string tenantId = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_ID"];

string spTenantUrl = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_URL"];

string spPrinciple = "00000003-0000-0ff1-ce00-000000000000";

string spAuthUrl = "https://accounts.accesscontrol.windows.net/" + tenantId + "/tokens/OAuth/2";

KeyValuePair<string, string>[] body = new KeyValuePair<string, string>[]

{

new KeyValuePair<string, string>("grant_type", "client_credentials"),

new KeyValuePair<string, string>("client_id", $"{clientId}@{tenantId}"),

new KeyValuePair<string, string>("resource", $"{spPrinciple}/{spTenantUrl}@{tenantId}".Replace("https://", "")),

new KeyValuePair<string, string>("client_secret", clientSecret)

};

var content = new FormUrlEncodedContent(body);

var contentLength = content.ToString().Length;

string token = "";

using (HttpResponseMessage response = await client.PostAsync(spAuthUrl, content))

{

if (response.Content != null)

{

string responseString = await response.Content.ReadAsStringAsync();

JObject data = JObject.Parse(responseString);

token = data.Value<string>("access_token");

}

return token;

}

As you see there are some variables involved:

clientId: this is the GUID of the application you’ve registered in SharePoint.
clientSecret: the secret key of that same application
tenantId: in SharePoint this is also known as the “realm”; it’s actually the GUID of the Azure AD instance you’re SharePoint Online tenant is linked to. There’s more than one ways to find out what it is; I always head over to the old management portal (manage.windowsazure.com) and open up your Azure Active Directory instance. The GUID will be part of the URL.
tenantUrl: the is the URL to the SharePoint environment, so https://<<tenantname>>.sharepoint.com
spPrinciple: this is a static GUID value: “00000003-0000-0ff1-ce00-000000000000”
siteUrl: the URL to the SharePoint Online tenant
spAuthUrl: points to accounts.accesscontrol.windows.net, which will be the token endpoint for the authentication request

I’ve configured most of these as app settings of my Azure Function, but of course you can also store them somewhere else or hardcode them for trial & error purposes.

Digest

Having only the token is not enough. SharePoint REST API calls also require passing in a digest, although the documentation here says this is not required when using add-in authentication. Despite that, I found that I’m getting errors nonetheless when leaving it out. The only way around seems to be involving certificates, which makes things much more complicated. So I prefer just getting the digest, which requires a second http post request:

public static async Task<string> GetDigestForSharePoint(string siteUrl, string token)
{
    HttpClient client = new HttpClient();

    client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);
    client.DefaultRequestHeaders.Add("accept", "application/json;odata=verbose");
    StringContent content = new StringContent("");

    string spTenantUrl = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_URL"];
    string digest = "";

    using (HttpResponseMessage response = await client.PostAsync($"{spTenantUrl}{siteUrl}/_api/contextinfo", content))
    {        
        if (response.IsSuccessStatusCode)
        {
            string contentJson = response.Content.ReadAsStringAsync().Result;
            JObject val = JObject.Parse(contentJson);
            JToken d = val["d"];
            JToken wi = d["GetContextWebInformation"];
            digest = wi.Value<string>("FormDigestValue");
        }
    }

    return digest;
}

public static async Task<string> GetDigestForSharePoint(string siteUrl, string token)

{

HttpClient client = new HttpClient();

client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);

client.DefaultRequestHeaders.Add("accept", "application/json;odata=verbose");

StringContent content = new StringContent("");

string spTenantUrl = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_URL"];

string digest = "";

using (HttpResponseMessage response = await client.PostAsync($"{spTenantUrl}{siteUrl}/_api/contextinfo", content))

{

if (response.IsSuccessStatusCode)

{

string contentJson = response.Content.ReadAsStringAsync().Result;

JObject val = JObject.Parse(contentJson);

JToken d = val["d"];

JToken wi = d["GetContextWebInformation"];

digest = wi.Value<string>("FormDigestValue");

}

return digest;

}

So this is basically an empty post to /_api/contextinfo, which will return you a response with the digest. Note that you need the authentication token from the previous method before you can actually do this, otherwise your call will just be rejected. You should also be aware that both token and digest have expiration dates, but that’s only relevant should your function take a longer time to complete. Mine runs in about one second so no need to be bothered about that.

Register your SharePoint add-in

If you have already done development with the SharePoint add-in model, this should be familiar. In order to do anything with the SharePoint API’s, you’ll need to have an add-in registered. You can do so by heading over to <siteurl>/_layouts/15/AppInv.aspx. The MSDN article here has some detailed information on how to do this. Note that the domain and redirect URL in this case are not that important, since we won’t be hosting any UI for this add-in.

When creating a new add-in registration, ensure that you give the add-in the correct permissions. Of course this depends on what your Azure Function will exactly do. For testing purposes, use the following XAML:

<AppPermissionRequests AllowAppOnlyPolicy="true">
    <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" />
</AppPermissionRequests>

</AppPermissionRequests>

This will give the Azure Function control over everything in that specific site collection.

The client ID and client secret you get from this process are the ones you need to specify in the authentication calls above. Should anything fail, use Fiddler to inspect the contents of your requests and responses, those should give you some more detail on what’s going on. Also, http://jwt.calebb.net/ offers a quick way to decode the token you’ve been given, very useful to see what’s really in there!

Completing the Function

Now we have everything in place to actually go and do stuff! The last and final method we’ll need in our function is the one that creates a SharePoint list item:

public static async Task CreateSharePointListItem(string siteUrl, string listName, string itemTitle, TraceWriter log)
{
    try
    {
        var token = await GetAuthTokenForSharePoint();
        var digest = await GetDigestForSharePoint(siteUrl, token);

        HttpClient client = new HttpClient();
        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);
        client.DefaultRequestHeaders.Add("accept", "application/json;odata=verbose");
        client.DefaultRequestHeaders.Add("X-RequestDigest", digest);
        client.DefaultRequestHeaders.Add("X-HTTP-Method", "POST");

        HttpContent content = new StringContent($"{{ '__metadata': {{ 'type': 'SP.ListItem' }}, 'Title': '{itemTitle}'}}");
        content.Headers.ContentType = new MediaTypeHeaderValue("application/json");
        content.Headers.ContentType.Parameters.Add(new NameValueHeaderValue("odata", "verbose"));

        string spTenantUrl = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_URL"];

        using (HttpResponseMessage response = await client.PostAsync($"{spTenantUrl}{siteUrl}/_api/web/Lists/GetByTitle('{listName}')/items", content))
        {
            if (!response.IsSuccessStatusCode)
                log.Error($"The REST call to SharePoint failed: {response.StatusCode.ToString()}.");
        }
    }
    catch (Exception ex)
    {
        log.Error($"Could not write SharePoint list item: {ex}");
    }
}

public static async Task CreateSharePointListItem(string siteUrl, string listName, string itemTitle, TraceWriter log)

{

try

{

var token = await GetAuthTokenForSharePoint();

var digest = await GetDigestForSharePoint(siteUrl, token);

HttpClient client = new HttpClient();

client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);

client.DefaultRequestHeaders.Add("accept", "application/json;odata=verbose");

client.DefaultRequestHeaders.Add("X-RequestDigest", digest);

client.DefaultRequestHeaders.Add("X-HTTP-Method", "POST");

HttpContent content = new StringContent($"{{ '__metadata': {{ 'type': 'SP.ListItem' }}, 'Title': '{itemTitle}'}}");

content.Headers.ContentType = new MediaTypeHeaderValue("application/json");

content.Headers.ContentType.Parameters.Add(new NameValueHeaderValue("odata", "verbose"));

string spTenantUrl = System.Configuration.ConfigurationManager.AppSettings["SP_TENANT_URL"];

using (HttpResponseMessage response = await client.PostAsync($"{spTenantUrl}{siteUrl}/_api/web/Lists/GetByTitle('{listName}')/items", content))

{

if (!response.IsSuccessStatusCode)

log.Error($"The REST call to SharePoint failed: {response.StatusCode.ToString()}.");

}

catch (Exception ex)

{

log.Error($"Could not write SharePoint list item: {ex}");

}

So that’s http request #3. We first need to get the authentication token and digest from the methods seen above. Then you’re all set to fire off any REST call to SharePoint. This can be used to create items, query lists, create sites, you name it. The only thing you need to do now is call the right URL and pass in the required content. In the above example, I create a simple list item with the Title field specified. Not rocket science of course, but at least it shows the principle.

Ah wait, I forgot one thing:

#r "Newtonsoft.Json"

using System;
using System.Net.Http;
using System.Net.Http.Headers;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

public static async void Run(string myQueueItem, TraceWriter log)
{
    await CreateSharePointListItem("/sites/processes","Leave Requests", "Test item", log);
}

#r "Newtonsoft.Json"

using System;

using System.Net.Http;

using System.Net.Http.Headers;

using Newtonsoft.Json;

using Newtonsoft.Json.Linq;

public static async void Run(string myQueueItem, TraceWriter log)

{

await CreateSharePointListItem("/sites/processes","Leave Requests", "Test item", log);

}

That’s the first lines of the function.

Conclusion

That’s all the bits and pieces you’ll need. Now it’s up to you to give a bit more meaning to your function. You could, for example, read an item from an Azure queue, translate it to the correct fields and then save it to SharePoint. My next post will reveal a bit more about what I’m going to do with this function. You might be interested in that one too (spoiler: it’s about bots!), so keep an eye open; coming soon!

10 thoughts to “[Azure] From Function to SharePoint List Item”

M says:

August 7, 2017 at 3:17 pm

This was an enormous help. Thanks for posting such clear instructions with code samples (that worked flawlessly) 🙂

Reply
Vaibhav Godbole says:

January 22, 2019 at 3:18 pm

Hi,

This post is great. It helped me a lot achieving one of the functionality in my work till a very close point.

I am basically trying to get search suggestions by accessing the REST API (/_api/search/suggest?querytext=%27%27&showpeoplenamesuggestions=false).

I have followed your post and have exactly done accordingly. The only issue I am facing is that I am not getting actual suggestions results.

I am using code as below:

HttpResponseMessage response = client.GetAsync(endpointUrl).Result;
response.EnsureSuccessStatusCode();
var responseContent = response.Content.ReadAsStringAsync().Result;

I am not getting desired result in ‘responseContent’.

I am getting result as below:

{{
“d”: {
“suggest”: {
“__metadata”: {
“type”: “Microsoft.SharePoint.Client.Search.Query.QuerySuggestionResults”
},
“PeopleNames”: {
“__metadata”: {
“type”: “Collection(Edm.String)”
},
“results”: []
},
“PersonalResults”: {
“__metadata”: {
“type”: “Collection(Microsoft.SharePoint.Client.Search.Query.PersonalResultSuggestion)”
},
“results”: []
},
“PopularResults”: {
“__metadata”: {
“type”: “Collection(Microsoft.SharePoint.Client.Search.Query.PersonalResultSuggestion)”
},
“results”: []
},
“Queries”: {
“__metadata”: {
“type”: “Collection(Microsoft.SharePoint.Client.Search.Query.QuerySuggestionQuery)”
},
“results”: []
}
}
}
}}

If you notice the last part, the ‘result’ array is coming blank. When I try to access the REST API URL, I am getting the result, but not when I run from code.

Can anyone please help here?

Reply
1. Jasper Siegmund says:
  
  January 22, 2019 at 5:41 pm
  
  Thanks for the comment, good to hear this post helped you out! By the looks of it, you’re trying to perform a SharePoint search query but it comes up blank. This is kinda hard to debug from a distance. In general the REST endpoint from search you give you the exact same results as the UI does, as it uses the same engine under the covers. If this is not the case, I’d suspect either the context (user, site, search scope) is different or the query itself is somehow different. There’s a good search query tool originally made by Mikael Svenson which might help you out. I believe it’s still available here: https://github.com/SharePoint/PnP-Tools/tree/master/Solutions/SharePoint.Search.QueryTool. Hope that helps!
  
  Reply
  1. Vaibhav Godbole says:
    
    January 23, 2019 at 11:53 am
    
    Thanks Jasper,
    
    I am trying to retrieve the search suggestions by using its REST API by passing the keyword.
    I also tried accessing this API from postman (GET request), by passing the headers values that I got from my code (Authorization token, X-Request Digest, accept) , but did not get result in that too.
    However, as stated above, when I try to put the REST API in browser (which gets invoked by my id), I get the result in browser.
    
    Does that mean that it will not give results because we are not passing any user credentials (are they mandatory for SP online search REST APIs, and App only token will not work in this case) ?
    
    Reply
    1. Jasper Siegmund says:
      
      January 24, 2019 at 8:11 am
      
      Yeah that’s most likely your issue. App only tokens do not contain any user context, so results will depend on what the app is able to access. If you’re firing the query from the browser, it’ll be using your security context which I’m guessing is probably administrator or at least privileged.
      
      If you’re building a Function to do this, your best option is probably to ensure the registered app you’re using is granted sufficient permissions to access the site(s) and content that you need. That should enable it to retrieve content without having any user context.
      
      Reply
AlokS says:

June 30, 2019 at 2:31 pm

Please provide a solution, getting the below error while status: 204, list item is not created.

Could not write SharePoint list item: System.InvalidOperationException: An invalid request URI was provided. The request URI must either be an absolute URI or BaseAddress must be set.
at System.Net.Http.HttpClient.PrepareRequestMessage(HttpRequestMessage request)
at System.Net.Http.HttpClient.SendAsync(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.PostAsync(Uri requestUri, HttpContent content, CancellationToken cancellationToken)
at Submission#0.d__4.MoveNext() in D:\home\site\wwwroot\HttpTrigger1\run.csx:line 91
— End of stack trace from previous location where exception was thrown —
at Submission#0.d__2.MoveNext() in D:\home\site\wwwroot\HttpTrigger1\run.csx:line 22

Reply
AlokS says:

July 1, 2019 at 11:02 am

Hi Jasper,
Require your help, i have copied the code and replaced with the values where required.
But on executing it i get an error, below is the details of that error.

2019-07-01T10:00:02.289 [Error] Could not write SharePoint list item: System.InvalidOperationException: An invalid request URI was provided. The request URI must either be an absolute URI or BaseAddress must be set.
at System.Net.Http.HttpClient.PrepareRequestMessage(HttpRequestMessage request)
at System.Net.Http.HttpClient.SendAsync(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.PostAsync(Uri requestUri, HttpContent content, CancellationToken cancellationToken)
at Submission#0.d__2.MoveNext() in D:\home\site\wwwroot\HttpTrigger1\run.csx:line 54
— End of stack trace from previous location where exception was thrown —
at Submission#0.d__3.MoveNext() in D:\home\site\wwwroot\HttpTrigger1\run.csx:line 79

Reply
1. Jasper Siegmund says:
  
  July 2, 2019 at 6:24 pm
  
  Hello AlokS,
  
  thanks for commenting! I’m not doing a lot of SharePoint related work any more so I must admit I am a little rusty. Can you maybe provide a little code snippet of where this is failing? It sounds as if the URL of the list or the site you’re passing in might not be in the correct format. Cheers!
  
  Reply
AlokS says:

July 5, 2019 at 11:52 am

After manipulating script in certain portions just to check what is okay and what is not, it appears I am able to get the token, but NOT sure about the digest.

My requirement is to create a new row with value to ListItem: ‘ProcessFlag’ | Value: ‘Processing’ (I am hopeful that this can be achieved using some tweaks to your script and also that creating a new row is possible with adding only one value to one column(ListItem), for this I have marked all the columns as “NOT REQUIRED”, ) Please guide on what changes I have to make on HTTP Request and how to handle the query.

Thank you Jasper!, waiting for your reply

Reply
1. Jasper Siegmund says:
  
  July 5, 2019 at 12:33 pm
  
  I can advise to use Fiddler to check what is going over the line. Nowadays (this is an older post of mine), SharePoint supports Azure AD tokens as well which means you should be able to use the Azure AD endpoints instead of the SharePoint token provider. Here is an article that in details cover how to do app-only authentication (which you need for your Function) for SharePoint Online: https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azuread
  
  Reply