Dynamics Community Forum Thread Details

I'm attempting to automatically import a file from a cloud storage folder, in this case Microsoft OneDrive, into a table in a Microsoft Business Central SaaS environment using an AL extension and Job Queue. At the moment I'm getting error: 'User's mysite not found'. I tried the same exercise in Postman and got the same error. Any idea where I'm going wrong?

I've copied a test CSV file into a folder on my OneDrive see below:

I've created an App Registration in Azure Portal / Entra as below:

... and I have the AL extension code below. I've commented where the error is reported.

codeunit 50120 "OneDriveFileImporter"
{

    trigger OnRun()
    begin
        ImportFilesFromOneDrive();
    end;

    procedure ImportFilesFromOneDrive()
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        JsonResponse: JsonObject;
        JsonArray: JsonArray;
        JsonToken: JsonToken;
        JsonTokenLoop: JsonToken;
        JsonValue: JsonValue;
        JsonObjectLoop: JsonObject;
        AuthToken: Text;
        OneDriveFolderUrl: Text;
        ResponseText: Text;
        FileContent: InStream;
        FileName: Text;
        txtTemp: Text;
        UserPrincipalName: Text;
    begin
        // Get OAuth token
        AuthToken := GetOAuthToken();

        if AuthToken = '' then
            Error('Failed to obtain access token.');

        // Define the user principal name (replace with the actual user principal name)
        UserPrincipalName := 'someone@somewhere.com';

        // Define the OneDrive folder URL

        // delegated permissions
        // OneDriveFolderUrl := 'https://graph.microsoft.com/v1.0/me/drive/root:/OneDriveFileImporter:/children';

        // application permissions
        OneDriveFolderUrl := 'https://graph.microsoft.com/v1.0/users/' + UserPrincipalName + '/drive/root:/OneDriveFileImporter:/children';

        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(OneDriveFolderUrl);
        HttpRequestMessage.Method := 'GET';
        HttpRequestMessage.GetHeaders(Headers);
        Headers.Add('Authorization', 'Bearer ' + AuthToken);

        // Send the HTTP request
        if HttpClient.Send(HttpRequestMessage, HttpResponseMessage) then begin
            // Log the status code for debugging
            // Message('HTTP Status Code: %1', HttpResponseMessage.HttpStatusCode());

            if HttpResponseMessage.IsSuccessStatusCode() then begin
                HttpResponseMessage.Content.ReadAs(ResponseText);
                JsonResponse.ReadFrom(ResponseText);

                if JsonResponse.Get('value', JsonToken) then begin
                    JsonArray := JsonToken.AsArray();

                    foreach JsonTokenLoop in JsonArray do begin
                        JsonObjectLoop := JsonTokenLoop.AsObject();
                        if JsonObjectLoop.Get('name', JsonTokenLoop) then begin
                            JsonValue := JsonTokenLoop.AsValue();
                            FileName := JsonValue.AsText();
                            DownloadAndProcessFile(FileName, AuthToken);
                        end;
                    end;
                end;

            end else begin
                // HERE'S WHERE THE ERROR IS REPORTED!!!
                HttpResponseMessage.Content.ReadAs(ResponseText);
                Error('Failed to fetch files from OneDrive: %1 %2', HttpResponseMessage.HttpStatusCode(), ResponseText);
            end;
        end else
            Error('Failed to send HTTP request to OneDrive');
    end;

    procedure GetOAuthToken() AuthToken: Text
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        HttpContent: HttpContent;
        Headers: HttpHeaders;
        ResponseText: Text;
        JsonToken: JsonToken;
        JsonResponse: JsonObject;
        JsonValue: JsonValue;
        ClientID: Text;
        ClientSecret: Text;
        TenantID: Text;
        TokenEndpoint: Text;
        Content: Text;
    begin
        // Get client ID, client secret, and tenant ID from setup
        ClientID := 'myclientid.....';
        ClientSecret := 'myclientsecret.....';
        TenantID := 'mytenantid.....';

        // Define the token endpoint
        TokenEndpoint := 'https://login.microsoftonline.com/' + TenantID + '/oauth2/v2.0/token';

        // Define the request content
        Content := 'grant_type=client_credentials&client_id=' + ClientID + '&client_secret=' + ClientSecret + '&scope=https://graph.microsoft.com/.default';

        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(TokenEndpoint);
        HttpRequestMessage.Method := 'POST';

        // Initialize the HTTP content
        HttpContent.WriteFrom(Content);
        HttpContent.GetHeaders(Headers);
        Headers.Remove('Content-Type');
        Headers.Add('Content-Type', 'application/x-www-form-urlencoded');
        HttpRequestMessage.Content := HttpContent;

        // Send the HTTP request
        if HttpClient.Send(HttpRequestMessage, HttpResponseMessage) then begin
            if HttpResponseMessage.IsSuccessStatusCode() then begin
                HttpResponseMessage.Content.ReadAs(ResponseText);
                JsonResponse.ReadFrom(ResponseText);
                if JsonResponse.Get('access_token', JsonToken) then begin
                    JsonValue := JsonToken.AsValue();
                    AuthToken := JsonValue.AsText();
                    Message(AuthToken);
                    exit(AuthToken);
                end else
                    Error('Failed to get access token from response');
            end else
                Error('Failed to get access token: %1', HttpResponseMessage.HttpStatusCode());
        end else
            Error('Failed to send HTTP request for access token');
    end;

    procedure DownloadAndProcessFile(FileName: Text; AuthToken: Text)
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        FileUrl: Text;
        FileContent: InStream;
    begin
        // haven't reached this pocedure yet.
    
        // Define the file URL
        FileUrl := 'https://graph.microsoft.com/v1.0/me/drive/root:/OneDriveFileImporter/' + FileName + ':/content';

        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(FileUrl);
        HttpRequestMessage.Method := 'GET';
        HttpRequestMessage.GetHeaders(Headers);
        Headers.Add('Authorization', 'Bearer ' + AuthToken);

        // Send the HTTP request
        if HttpClient.Send(HttpRequestMessage, HttpResponseMessage) then begin
            if HttpResponseMessage.IsSuccessStatusCode() then begin
                HttpResponseMessage.Content.ReadAs(FileContent);
                // Process the file content (e.g., import into a table)
                ImportFileContent(FileContent);
            end else
                Error('Failed to download file: %1', HttpResponseMessage.HttpStatusCode());
        end else
            Error('Failed to send HTTP request to download file');
    end;

    procedure ImportFileContent(FileContent: InStream)
    var
    // haven't reached this pocedure yet.       
    // Define your table and variables here
    // FileTable: Record "Your File Table";
    // other variables
    begin
        // Implement your file content import logic here
        // For example, reading CSV data and inserting into a table
        // ...
    end;
}

Categories:

Development