Getting attachments from SharePoint List and automatically attaching them to a DevOps work item

[Adam12345]

Hi,

I have been trying for the past 6 months to try and get attachments added from a Sharepoint list into a new RFQ work item.

The reason I want this, is to try and integrate attachments from SharePoint.

I just want attachments that are added on a SharePoint list to automatically attach onto a work item. 

Does anyone know any ideas on how to get this done? Im bust for ideas!

[MattWeston365]

Hi @Adam12345, unfortunately this isn't something which can be done directly using native actions currently in Flow. However, there is a really useful action called "Send a HTTP Request to DevOps" which means that we can use the REST APIs to achieve what we want.

High level approach is:

1. Get the work item from DevOps
2. Get the attachments from the list item
3. Get the content
4. Upload it to Dev Ops
5. Associate the attachment with the work item

Steps 1 - 3 are all straight forward with Flow, although if you need any pointers with these please shout and I'll be happy to ellaborate.

Let's focus on the first HTTP Request which is going to upload the attachment to DevOps, which in my overview is titled "Send an HTTP REquest to Azure DevOps - Upload Attachment."

This works in a similar way to the HTTP Connector for SharePoint, whereby you don't need to worry about generating tokens as the connection will handle the authentication for you. All you need to do is get your API call right.

Full documentation for this end point can be found here: https://docs.microsoft.com/en-us/rest/api/azure/devops/wit/attachments/create?view=azure-devops-rest...

The most important part here is the URI for the endpoint. It will basically take the form of:

/{project}/_apis/wit/attachments?fileName={fileName}&uploadType={uploadType}&areaPath={areaPath}&api-version=5.0

fileName - the DisplayName dynamic content taken from the Get Attachments action

areaPath - the Area Path dynamic content taken from the Get Work Item actions

I will need to use the output of this REST call in my next one, so I need to use the Parse JSON action to transform the output into something useful. It will only return two pieces of information, so you can use this schema to parse it:

{
    "type": "object",
    "properties": {
        "id": {
            "type": "string"
        },
        "url": {
            "type": "string"
        }
    }
}

Once the file is uploaded, we need to associate it to the work item so that it shows up in attachments. Again, I'm going to use the "Send an HTTP Request to Azure DevOps" to do this. The difference this time, is rather than being a post, it is going to be a PATCH as I'm updating an already existing work item.

This time the relative URI is much more simple, as all I'm doing is providing the workitem ID (from my workitem action earlier). The majority of the work here takes place in the body of the call. Full documentation for this call can be found here:
https://docs.microsoft.com/en-us/rest/api/azure/devops/wit/work%20items/update?view=azure-devops-res...

The only part of the body which will need to change is the url which will come from the Parse JSON step. That is the URL where your attachment has been uploaded to within DevOps. 

This has worked for me by attaching a txt file onto a List Item, and watching that flow through into my workitem, and I don't see any reason why it shouldn't work for more complex file types.

Let us know how you get on, and if you need any more help please shout.

If you have found this post useful, please give it a thumbs up. If it has answered your question, please accept it as the solution so others can benefit.

@MattWeston365

[deepakkoduri]

Thanks for the detailed information 🙂

When we try to move the SharePoint list item attachments apart from text files all the other files are getting uploaded as corrupted files to the DevOps work Items.  

Please help.

[MattWeston365]

Hi @deepakkoduri , I've stayed in contact with the OP as we did a couple of screen shares to get this built. He raised this same issue to me outside of the community so it's something I'm working on at the moment.

The issue is related to the way in which a Word document (or anything more complex than a txt file) is encoded within Flow as it is always encoded as a base64 string. The DevOps API actually wants a byte array (regardless of what the documentation suggests) and I've got this working in PowerShell.

So I'm working on a solution which will combine my findings in PowerShell with what is available through Flow. I'll report back once I've got the end-to-end solution working.

[deepakkoduri]

Thanks a lot @MattWeston365. Will wait for you reply.

May I know by when can we have this fix ready for planning a workaround.

[MattWeston365]

Hi @deepakkoduri and @Adam12345 

I've now got a solution to this. Give me a day or so as this is going to be a blog style solution since I've had to revert to Azure Automation to actually get this working as I want. The good news though is that it will now take the attachments from a list item and put them into Azure, and the documents don't corrupt.

I had a call with Adam not so long back, and I told him I don't do failure, so I'm finally there! Back in touch shortly.

[MattWeston365]

@deepakkoduri , @Adam12345 

Sorry it's taken me longer than I anticipated to get this to you. Here's a blog outlining my solution to your issue:

https://blog.mattweston365.com/2019/06/uploading-attachments-from-sharepoint.html

I'll be making an associated video shortly, but if you have any questions in the meantime please shout.

[deepakkoduri]

Thanks @MattWeston365.  I had used Azure Function to handle this.  For Uploading and tagging the attachment I'm using Azure Function which is being called in the Flow with the required parameters.

[kthaththathreya]

@deepakkoduri : Could you please send me the flow you have created after the issue got resolved. I am facing similar issue and the solution might be helpful.

Thanks in advance!

[deepakkoduri]

Hi @kthaththathreya , As mentioned I'm calling the Azure Function from the Flows to perform the following 

1. Upload the attachment to the Azure DevOps Instance
2. Update the upload attachment reference to DevOps Work Item

Below is the Azure Function Code. I hope this will help

namespace DevOpsSharePointIntegration
{
    public static class AddAttachmentToWorkItem
    {
        [FunctionName("AddAttachmentToWorkItem")]
        public static async Task<string> Run([HttpTrigger(AuthorizationLevel.Function, "POST", Route = null)]HttpRequestMessage req, TraceWriter log)
        {
            log.Info("Reading the request body in HTTP Triggers");
            string responseBody=string.Empty, 
                responsePatchBody = string.Empty;
            // Arrange.
            try
            {
                var configuration = new HttpConfiguration();
                req.Properties[System.Web.Http.Hosting.HttpPropertyKeys.HttpConfigurationKey] = configuration;

                // Read body
                FileData file = await req.Content.ReadAsAsync<FileData>();
                
                byte[] bytes = Convert.FromBase64String(file.data);

                using (HttpClient client = new HttpClient())
                {
                    client.DefaultRequestHeaders.Accept.Add(
                        new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/octet-stream"));

                    client.DefaultRequestHeaders.Authorization = req.Headers.Authorization;

                    ByteArrayContent byteContent = new ByteArrayContent(bytes);

                    string urlPost = "https://dev.azure.com/" + file.organization + "/" + file.project + "/_apis/wit/attachments?fileName=" + file.fileName + "&uploadType=Simple&areaPath=" + file.areaPath + "&api-version=5.0";
                    string requestPatchUrl = "https://dev.azure.com/" + file.organization + "/" + file.project + "/_apis/wit/WorkItems/"+file.workItemID+"?api-version=5.0";

                    using (HttpResponseMessage response = await client.PostAsync(
                                urlPost, byteContent))
                    {
                        response.EnsureSuccessStatusCode();
                        responseBody = await response.Content.ReadAsStringAsync();
                        FileDataOutput fileDataInfo = JsonConvert.DeserializeObject<FileDataOutput>(responseBody);

                        string jsonInputData = "[ { \"op\": \"add\", \"path\": \"/relations/-\", \"value\": { \"rel\": \"AttachedFile\", \"url\": \""+ fileDataInfo.url + "\", \"attributes\": { \"comment\": \"Uploaded from SharePoint List\" } }, } ]";

                        HttpRequestMessage requestPatch = new HttpRequestMessage
                        {
                            Method = new HttpMethod("PATCH"),
                            RequestUri = new Uri(requestPatchUrl)
                        };
                        requestPatch.Content = new StringContent(jsonInputData,
                                    Encoding.UTF8,
                                    "application/json-patch+json");//CONTENT-TYPE header
                        client.DefaultRequestHeaders.Accept.Remove(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/octet-stream"));
                        client.DefaultRequestHeaders.Accept.Add(
                        new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));

                        using (HttpResponseMessage responsePatch = await client.SendAsync(requestPatch))
                        {
                            responsePatch.EnsureSuccessStatusCode();
                            responsePatchBody = await responsePatch.Content.ReadAsStringAsync();
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                responsePatchBody = ex.Message + "  : " + ex.StackTrace;
            }
            
            return responsePatchBody;
        }
        
        public class FileData
        {
            public string data { get; set; }
            public string organization { get; set; }
            public string project { get; set; }
            public string fileName { get; set; }
            public string areaPath { get; set; }
            public string workItemID { get; set; }
        }
        public class FileDataOutput
        {
            public string id { get; set; }
            public string url { get; set; }
        }
    }
}