I have created linked service for google sheet and using Data flow activity to fetch google sheet data. On source side, I want End Cell value to be dynamic instead of hard coded. It should fetch data till last non empty cell of the last column as well as row.

Hello @ Prajakta Did you try any Dataflow/Transformations? One solution Which i can suggest is as follows/ Step1: Use an external Application to read the last cell of the Google sheet, I have used the Azure Function, My ADF pipeline will call the Azure Function to get the Last Cell of the Google Sheet, and then it is passed as a parameter to the DataFlow which will start the Data Copy from Google Sheet to Destination. I have written my Azure Function in C#, if you need the code snippet, I can provide, or you may go with your choice of Web App language stack to fetch the Google sheet Columns last cell. Step2: In the DataFlow, please make sure to add the Global Param called 'paramEndCell' Make use of the Param in the Dataflow Source in the End Cell. $paramEndCell Go to the Pipeline and add the above Dataflow and add the Parameter from Pipeline Expression, as shown in the below image. The below response will capture the End Cell Location: such as D4 or D7 or D9, Dynamically @activity('Azure Function1').output.Response This is how my Azure function responds with Dynamic cell value: Your WebHook which uses OAuth REST call to read the GoogleSheet End Cell is also a great idea, which we can use in this scenario! I hope this is helpful with your initial query! Please let me know if you have further questions and happy to help!

Hi, did you manage to resolve the problem? Tutuapp

How to set End Cell value as dynamic in source inside mapping data flow of azure data factory to fetch google sheet data?

Accepted answer

QuantumCache 20,346

Hello @Prajakta

Did you try any Dataflow/Transformations?

One solution Which i can suggest is as follows/

Step1: Use an external Application to read the last cell of the Google sheet, I have used the Azure Function, My ADF pipeline will call the Azure Function to get the Last Cell of the Google Sheet, and then it is passed as a parameter to the DataFlow which will start the Data Copy from Google Sheet to Destination.

User's image

I have written my Azure Function in C#, if you need the code snippet, I can provide, or you may go with your choice of Web App language stack to fetch the Google sheet Columns last cell.

Step2: In the DataFlow, please make sure to add the Global Param called 'paramEndCell'

User's image

Make use of the Param in the Dataflow Source in the End Cell.

$paramEndCell
User's image

Go to the Pipeline and add the above Dataflow and add the Parameter from Pipeline Expression, as shown in the below image.

User's image

The below response will capture the End Cell Location: such as D4 or D7 or D9, Dynamically

@activity('Azure Function1').output.Response

This is how my Azure function responds with Dynamic cell value:
User's image

Your WebHook which uses OAuth REST call to read the GoogleSheet End Cell is also a great idea, which we can use in this scenario!

I hope this is helpful with your initial query!

Please let me know if you have further questions and happy to help!

Prajakta-8530 45 Reputation points

2023-06-22T09:11:46.6333333+00:00

Hi @SatishBoddu-MSFT, Thank you for the solution.
In case of rest api linked service using OAuth 2 client authentication type I am getting error for Token Endpoint, I tried multiple ways there.
Could you please also share your code snippet.

QuantumCache 20,346

Hello @Prajakta-8530,

Below is my Azure Function Code in C#. which use OAuth for accessing the Google Sheet via Service Account Credentials.

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading;
using System.Threading.Tasks;

public static class Function1
{
    static string ApplicationName = "Get Last Cell Google Sheets";
    static string SpreadsheetId = "1PxI"; // Replace with your spreadsheet id
    static string range = "Sheet1!D:D"; // Replace with your sheet name and column id.
    static string ColumnToRead = "D";
    static string jsonPath = "sab7d23fd4eb4.json"; // Path to the Service Account key file, if             //you need help with how to get Service Account File then refer //
//
    [FunctionName("GetLastCell")]
    public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequest req)
    {
        try
        {
            GoogleCredential credential;
            using (var stream = new FileStream(jsonPath, FileMode.Open, FileAccess.Read))
            {
                credential = GoogleCredential.FromStream(stream)
                    .CreateScoped(SheetsService.Scope.SpreadsheetsReadonly);
            }

            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            SpreadsheetsResource.ValuesResource.GetRequest request =
                service.Spreadsheets.Values.Get(SpreadsheetId, range);

            ValueRange response = await request.ExecuteAsync();
            IList<IList<object>> values = response.Values;
            string lastCell = "";

            if (values != null && values.Count > 0)
            {
                // Loop through the column in reverse to find the last non-empty cell
                for (int i = values.Count - 1; i >= 0; i--)
                {
                    if (values[i].Count > 0 && values[i][0] != null)
                    {
                        lastCell = ColumnToRead + (i + 1).ToString(); // cell number should start from 1 not from 0
                        break;
                    }
                }
            }

            return new OkObjectResult(lastCell);
        }
        catch (Exception ex)
        {
            // Log the exception message and return a bad request result.
            // Consider adding more detailed error handling here.
            return new BadRequestObjectResult($"Error reading from Google Sheets: {ex.Message}");
        }
    }
}

User's image If you need more help in this matter, then please do let me know!

QuantumCache 20,346 Reputation points

2023-06-22T20:53:25.3266667+00:00

References for Google Sheet Service Account
https://github.com/juampynr/google-spreadsheet-reader

https://developers.google.com/identity/protocols/oauth2/service-account#creatinganaccount
QuantumCache 20,346 Reputation points

2023-06-27T22:01:04.2266667+00:00

Hello @Prajakta-8530,

If the response is helpful, please click "Yes" . So that we can close this thread.
Prajakta-8530 45 Reputation points

2023-06-28T16:50:17.7766667+00:00

Hi @SatishBoddu-MSFT,

Yes, this is helpful but

I am trying another way where I am using web activity and there I am passing google sheet api get data link https://sheets.googleapis.com/v4/spreadsheets/[spreadsheetid]/values/Sheet1?dateTimeRenderOption=FORMATTED_STRING&majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE&key=[used_my_api_key], it is giving result as shown. After that i have used copy data activity to copy data to blob storage file there I am unable to get column values like Subject_Id, Subject_Name and Student_Name in mapping instead getting values shown in the picture 2.
QuantumCache 20,346 Reputation points

2023-06-29T04:16:36.72+00:00

Hello @Prajakta-8530,
I was not using the JSON as source format!! Since the DataFlow is fetching the GoogleSheet info in my scenario!

Ok, So i have used the ADF Data Flow to fetch the Data from the Google Sheet, so the fetched content is more simpler!!!

Example: Showing the Data Preview from Source
I started reading the Sheet from A2, so I Skip the Column names

Mapping: was simple as shown in the below image!
Prajakta-8530 45 Reputation points

2023-07-05T17:38:44.91+00:00

Hi @SatishBoddu-MSFT, Thank you for the solution.
QuantumCache 20,346 Reputation points

2023-07-05T21:06:53.97+00:00

Hello @Prajakta-8530

Thanks for working with me on this topic, this is really a good learning!

If the response is helpful, please click "Accept Answer" and Click 'Yes'. So that we can close this thread.

Suren Edilyan 0

Dear SatishBoddu-MSFT,

I am experiencing some complications in the Azure Function section. It works locally and reaches the end cell, but when I publish it, I encounter an HTTP ERROR 500. Through Application Insights, I noticed that it is related to Google API authentication.

Thank you...

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 Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using System.Collections.Generic;
using System.Threading;

namespace FunctionGetEndCellFromGoogleSheet;

public static class EndCellFromGoogleSheet
{
    static string applicationName = "NNN";
    static string spreadsheetId = "XXX";
    static string sheet = "Sheet1";

    static string googleClientId = "XXX";
    static string googleClientSecret = "XXX";

    static string[] scopes = new[] { SheetsService.Scope.Spreadsheets };
    static UserCredential credential = Login(googleClientId, googleClientSecret, scopes);

    static SheetsService service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
    {
        HttpClientInitializer = credential,
        ApplicationName = applicationName
    });

    [FunctionName("GetEndCellFromGoogleSheetFunction1")]
    public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)]
        HttpRequest req,
        ILogger log)
    {
        log.LogInformation("C# HTTP trigger function processed a request.");

        string responseMessage = GetGoogleSheetsLastFilledCell(service, sheet);

        return new OkObjectResult(responseMessage);
    }

    #region GetGoogleSheetsCell
    static string GetGoogleSheetsLastFilledCell(SheetsService service, string sheet)
    {
        string range = $"{sheet}!A1:Z";

        // Retrieve the values from the specified range
        SpreadsheetsResource.ValuesResource.GetRequest request =
            service.Spreadsheets.Values.Get(spreadsheetId, range);
        ValueRange response = request.Execute();
        IList<IList<object>> values = response.Values;

        if (values != null && values.Count > 0)
        {
            // Find the last non-empty cell in the range
            for (int row = values.Count - 1; row >= 0; row--)
            {
                for (int col = values[row].Count - 1; col >= 0; col--)
                {
                    if (!string.IsNullOrEmpty(values[row][col].ToString()))
                    {
                        char columnLetter = (char)('A' + col);
                        return $"{columnLetter}{row + 1}";
                    }
                }
            }
        }

        // If no data is found, return a default cell (e.g., A1)
        return "A1";
    }
    #endregion
    
    #region Login
    static UserCredential Login(string googleClientId, string googleClientSecret, string[] scopes)
    {
        ClientSecrets secrets = new ClientSecrets()
        {
            ClientId = googleClientId,
            ClientSecret = googleClientSecret
        };

        return GoogleWebAuthorizationBroker.AuthorizeAsync(secrets, scopes, "user", CancellationToken.None).Result;
    }
    #endregion
}

Suren Edilyan 0 Reputation points

2023-12-05T08:23:57.24+00:00

This link is not actual yet, Google change Json file structure...

https://github.com/juampynr/google-spreadsheet-reader

Share via

How to set End Cell value as dynamic in source inside mapping data flow of azure data factory to fetch google sheet data?

1 additional answer

Your answer