How To Trigger ADF Pipelines Using an Excel Button and Azure Function

Introduction:

Here, we’ll explore how to trigger an ADF pipeline from a button action in Excel Workbook. This button action in excel will call an Azure function which will trigger a pipeline in Azure Data Factory (ADF).

Steps to be Followed for Creating an ADF Trigger from Excel Button:

Step 1: Login and Registration.

(i) Login into Azure Portal.

(ii) Register a New Application.

(iii) Go to Azure Active Directory > App registrations > New registration.

Fill in the necessary details:

Name: Provide a name for your application (e.g. PipelineExecutorApp).

Supported account types: Choose the appropriate account type. For most cases, you can select Accounts in this organizational directory only (Default Directory only — Single tenant).

Redirect URI: You can leave this blank for now.

Click Register

Step 2: Application (Client) ID and Directory (Tenant) ID

After registration, you will be redirected to the application overview page.

Note down the Application (client) ID and Directory (tenant) ID as you will need them in your code.

Step 3: Add API Permissions

In the application’s overview page, go to API permissions > Add a permission.

Select Azure Service Management > Delegated permissions > select user_impersonation.

Click Add permissions.

Step 4: Grant Admin Consent.

Click Grant admin consent for <Your Organization> to grant the permissions.

Step 5: Create a Client Secret

Add a Client Secret

In the application page, go to Certificates & secrets > Client secrets > New client secret.

Provide a description (e.g., PipelineExecutorSecret) and choose an expiry period.

Click Add.

Copy the Client Secret:

After the client secret is created, copy the value immediately. This will be your authenticationKey.

Note: This secret value will not be shown again, so make sure to store it securely.

Step 6: Assign Role to the Application

Navigate to Subscriptions in the Azure portal.

Select the subscription you want to grant access to.

Go to Access control (IAM) > Add > Add role assignment.

In the Role dropdown, select Contributor.

In the Select field, search for your registered application (PipelineExecutorApp) and select it.

Click Save.

Step 7: Create a Azure Function App

Create Azure function using the following code and replace all the tenant id, client id, subscription id, secret id, authentication key , Resource Group name, ADF name and pipeline name with your values.

Save and publish the function app.

Note: The Function App code is attached below

Step 8: Create a new pipeline as you need or use an existing pipeline.

After the pipeline creation Publish the pipeline.

Excel Action Plan: Follow These Steps

Step 1: Press Alt + F11 to open the VBA editor.

Step 2: Insert a new module by right-clicking on any existing module or the project, then selecting Insert > Module.

Step 3: Add VBA code to call the Azure Function

Note: Replace your function app URL in this code

Code:

Sub TriggerAdfPipeline()
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
Dim url As String
url = "Add your function App URL"
Dim json As String
json = "{""param1"": ""value1"", ""param2"": ""value2""}"
With http
.Open "POST", url, False
.setRequestHeader "Content-Type", "application/json"
.send json
End With
MsgBox "Response: " & http.responseText
End Sub

Step 4: Create a Button in Excel

Go back to Excel.

Go to the “Developer” tab (enable it from Options if not visible).

Click “Insert” and choose “Button (Form Control)”.

Draw the button on the sheet, and when prompted, assign the TriggerAdfPipeline macro to it.

Step 5: Test the Setup

Click the button you created in Excel.

It should call the VBA function, which in turn calls the Azure Function to trigger your ADF pipeline.

Go to the Azure Portal and check the Data Factory monitoring section to verify that the pipeline has been triggered.

Function App Code:

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Rest;
using Microsoft.Azure.Management.DataFactory;
using Microsoft.Azure.Management.DataFactory.Models;
using Newtonsoft.Json.Linq;
using System.Collections.Generic;
namespace PipelineExecutor
{
public static class ExecutePipeline
{
[FunctionName("ExecutePipeline")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string tenantId = "Your Tenant-ID";
string applicationId = "Your Application-ID";
string authenticationKey = "Your Authentication-Key";
string subscriptionId = "Your Subscription-ID";
string resourceGroup = "Your Resource Group";
string factoryName = "Your Factory Name";
string pipelineName = "Your Pipeline Name";
// Check for missing values
if (string.IsNullOrEmpty(tenantId) ||
string.IsNullOrEmpty(applicationId) ||
string.IsNullOrEmpty(authenticationKey) ||
string.IsNullOrEmpty(subscriptionId) ||
string.IsNullOrEmpty(factoryName) ||
string.IsNullOrEmpty(pipelineName))
{
return new BadRequestObjectResult("Invalid request body, value missing.");
}
try
{
// Create a data factory management client
var context = new AuthenticationContext("https://login.windows.net/" + tenantId);
ClientCredential cc = new ClientCredential(applicationId, authenticationKey);
AuthenticationResult result = await context.AcquireTokenAsync("https://management.azure.com/", cc);
ServiceClientCredentials cred = new TokenCredentials(result.AccessToken);
var client = new DataFactoryManagementClient(cred)
{
SubscriptionId = subscriptionId
};
CreateRunResponse runResponse;
PipelineRun pipelineRun;
runResponse = client.Pipelines.CreateRunWithHttpMessagesAsync(
resourceGroup, factoryName, pipelineName).Result.Body;
log.LogInformation("Pipeline run ID: " + runResponse.RunId);
// Wait and check for pipeline result
log.LogInformation("Checking pipeline run status…");
while (true)
{
pipelineRun = await client.PipelineRuns.GetAsync(resourceGroup, factoryName, runResponse.RunId).ConfigureAwait(false);
log.LogInformation("Status: " + pipelineRun.Status);
if (pipelineRun.Status == "InProgress" || pipelineRun.Status == "Queued")
{
await Task.Delay(15000); // wait for 15 seconds before checking the status again
}
else
{
break;
}
}
// Final return detail
string outputString = "{ \"PipelineName\": \"" + pipelineName + "\", \"RunIdUsed\": \"" + pipelineRun.RunId + "\", \"Status\": \"" + pipelineRun.Status + "\" }";
JObject outputJson = JObject.Parse(outputString);
return new OkObjectResult(outputJson);
}
catch (AdalException ex)
{
log.LogError($"An error occurred while acquiring the token: {ex.Message}");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
catch (HttpOperationException ex)
{
log.LogError($"An error occurred while calling the Data Factory API: {ex.Response.Content}");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
catch (Exception ex)
{
log.LogError($"An unexpected error occurred: {ex.Message}");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
}
}
}

Use Case Scenario:

To update data in an Excel workbook by triggering an Azure Data Factory (ADF) pipeline, create a custom button within the Excel file. This button will initiate the ADF pipeline execution directly from Excel.

Conclusion:

Triggering Azure Data Factory pipelines with a simple Excel button via an Azure Function makes complex data workflows accessible to everyone. This seamless integration boosts productivity and leverages Azure’s power without leaving Excel. Enhance security, error handling, and monitoring to perfect this user-friendly solution!

About Us

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe, and we’re quickly building a brand that is well-known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies