Organizing Google Sheet’s End Cell Coordinate Dynamic Detection and Transferring with help of Azure Function to ADF (Azure Data Factory) Data Flows Activity for Incremental Data Refreshing Purposes.
I’m thrilled to share with you a comprehensive guide on Azure Functions, — a powerful serverless computing service offered by Microsoft Azure. Whether you’re a developer looking to streamline your workflows or an enthusiast eager to explore the world of cloud computing, this guide is your roadmap to success.
So, grab a cup of coffee, sit back, and let’s dive into the world of Azure Functions!
Prerequisites
- Now, navigate to the Azure portal and proceed to create a Function App. Ensure to select the appropriate Subscription and choose the same Resource Group where you previously created the Azure Data Factory and SQL DB. Here, I’m going to create an Azure Function using .NET and Windows (although you may opt for your preferred framework). Other settings on the current page will remain unchanged and click “Next: Storage >”.
2. On the “Storage” page, select “Create new” and provide a name for your storage account. Then click “Next: Networking >”. Ensure that “Enable public access” turned on, and proceed by clicking “Next: Monitoring >”.
3. In this step, I will select “No” for “Enable Application Insights” and proceed by clicking “Review + create”, skipping other sections. Then, in the “Review + create” section, I will click “Create” to begin the resource creation process.
4. After completing “Step 3”, please ensure that within your resource group, you have three new resources added.
5. Now, I will proceed to open Visual Studio and create a new Azure Functions App.
*If you are unable to find the Azure Function project type in your Visual Studio, please open the Vusal Studio Installer app and ensure that “Azure development” is enabled.
6. Please select suitable names for both the Project and Solution.
7. While creating the Azure Function App, ensure that the Function type is set to “Http trigger” and the Authorization level is set to “Anonymous”.
8. To add a NuGet package, right-click on “Packages” under the “Dependencies” folder in your Project. Then, select “Manage NuGet Packages…”. This action will open a new window. On the left side, navigate to “Browse”, search for “Google Sheet”, and choose “Google.Apis.Sheets.v4”. Finally, click on “Install” located on the right side of the window.
*You may also notice updates under “Updates”, particularly for packages associated with Azure Function, which may require updating.
9. Rename “Function1.cs" to “EndCellFromGoogleSheet” and add the following code inside it. After running the project, Windows may prompt you to allow public network access for this project; you should grant permission at that point.
using Google.Apis.Services;
using Google.Apis.Sheets.v4;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;
namespace EndCellFromGoogleSheet;
public class EndCellFromGoogleSheet(ILogger<EndCellFromGoogleSheet> logger)
{
private readonly ILogger<EndCellFromGoogleSheet> _logger = logger;
private const string SpreadsheetId = "1r-BBUDsvTj5AFCJipTlfVgZvAGqLwyR8lDhFzvEwedU";
private const string Sheet = "Sheet1";
[Function("EndCellFromGoogleSheet")]
public async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest req)
{
_logger.LogInformation("C# HTTP trigger function processed a request.");
var responseMessage = await Run();
return new OkObjectResult(responseMessage);
}
private static async Task<string> Run()
{
var service = new SheetsService(new BaseClientService.Initializer()
{
ApplicationName = "Discovery Sample",
ApiKey = "AI---------------------"
});
return await Task.FromResult(GetGoogleSheetsLastFilledCell(service, Sheet));
}
private static string GetGoogleSheetsLastFilledCell(SheetsService service, string sheet)
{
var range = $"{sheet}!A1:Z";
// Retrieve the values from the specified range
var request = service.Spreadsheets.Values.Get(SpreadsheetId, range);
var response = request.Execute();
var values = response.Values;
if (values is not { Count: > 0 }) return "A1";
// Find the last non-empty cell in the range
for (var row = values.Count - 1; row >= 0; row--)
{
for (var col = values[row].Count - 1; col >= 0; col--)
{
if (string.IsNullOrEmpty(values[row][col].ToString())) continue;
var columnLetter = (char)('A' + col);
return $"{columnLetter}{row + 1}";
}
}
// If no data is found, return a default cell (e.g., A1)
return "A1";
}
}10. Within the Console window, you’ll find a clickable link. Clicking on it will prompt your browser to display the End Cell of your Google Sheet.
11. To publish the Azure Function, right-click on the project and select “Publish”.
12. Then, select “Azure” and proceed by clicking “Next”.
13. Select “Azure Function App (Windows)” and then click “Next”.
14. Here, you may need to sign in to Visual Studio using the same account as your Azure account. Choose the corresponding Subscription and locate the Azure Function App under the resource groups. Then, press “Finish”. Once you see the green confirmation message, close the popup window.
15. After closing the popup, you’ll be directed to the Publish window, where you can review your data and proceed to click “Publish”.
16. Now, return to the Azure Portal, and within your Function App resource, you can locate your latest publish.
17. Navigate to “App keys” and copy default key.
18. Return to Azure Data Factory Studio and add an Azure Function as a Linked service by following these steps:
- Click on “Manage” on the left side of the window.
- Click on “Linked service”.
- Click on “+ New”.
- Choose the “Compute” tab.
- Locate “Azure Function”.
- Press “Continue”.
19. A popup menu will appear where you should either choose or fill in the fields as follows:
- Name: Fill as you preferred while adhering to identifier naming rules.
- Description: You may skip this if desired.
- Connect via integration runtime: Keep the default selection.
- Azure Function App selection method: Keep “From Azure subscription”.
- Azure subscription: Select the appropriate subscription.
- Azure Function App url: Locate the Function App created in the previous steps.
- Function Key: Paste the key that you copied in “Step 17”.
- Authentication method: Keep it as “Anonymous”.
Then, proceed to click “Create”.
20. Now, return to the pipeline section and add an Azure Function activity into your current pipeline, following the guidelines outlined in the prerequisites article. After that select the Settings of Azure Function activity and follow these steps:
- Azure Function linked service: Choose the Function app Linked service, which we created in the previous step,
- Function name: Fill it as you name it (you can find it in “Step 16”).,
- Method: Select “GET”.
21. Now, navigate inside the Data Flow by clicking the top-right arrow on the Data Flow activity, ensuring that “Data flow debug” is enabled. Here, at the bottom, you can find the “Parameters” section where we need to add a new one.
22. Return to the pipeline and select “Data flow”. At the bottom, locate “Parameters”, where you will find the new one added in the previous step. Click on the textbox beneath the “Value” sign and select “Pipeline expression”.
23. In the previous step, a new popup window will appear, where you’ll need to input the following code into the text area, as demonstrated in the image, and then click “OK”.
@activity('Azure Function1').output.Response
24. You can now debug the pipeline and publish it after successfully testing.
25. After all we can check Azure SQL Database.
If you have any questions, thoughts, or need further clarification on any aspect discussed in this article, please don’t hesitate to get in touch with me. Your feedback and inquiries are valuable. Feel free to reach out to me via LinkedIn as well, where we can connect and discuss in more detail. Let’s explore and delve deeper into the topics together, ensuring your understanding and success.
