Dynamics 365 Business Central: How to import/read files from SharePoint (document library) to Business Central via AL (Graph API)

Dynamics 365 Business Central

Hi, Readers.
We discussed Dynamics 365 Business Central: How to use OAuth 2.0 in AL with SecretText (Using codeunit 501 OAuth2) last month. Today I would like to briefly introduce a requirement that is often asked, how to import/read files from SharePoint to Business Central via AL (Graph API).

SharePoint and OneDrive in Microsoft 365 are cloud-based services that help organizations share and manage content, knowledge, and applications to:

  • Empower teamwork
  • Quickly find information
  • Seamlessly collaborate across the organization

Last month I shared how to integrate files with OneDrive through Graph API.

In this post we will mainly discuss how to import/read files from SharePoint. We also use Microsoft Graph API this time.
Microsoft Graph API: Microsoft Graph is a RESTful web API that enables you to access Microsoft Cloud service resources. After you register your app and get authentication tokens for a user or service, you can make requests to the Microsoft Graph API.

Registering your application establishes a trust relationship between your app and the Microsoft identity platform. This part of the setup is the same as in Using OAuth to connect Business Central APIs and Web Service in Postman. Just the API permissions are different.

To configure application permissions for the app in the app registrations experience on the Microsoft Entra admin center, follow these steps:

  • On the application’s API permissions page, choose Add a permission.
  • Select Microsoft Graph > select Application permissions.
  • In the Select Permissions dialog, choose the permissions to configure to the app.

This time I only added Microsoft Graph -> Application -> Files.ReadWrite.All.

PS: Download driveItem content permission

Permission typeLeast privileged permissionsHigher privileged permissions
Delegated (work or school account)Files.ReadFiles.ReadWrite, Files.Read.All, Files.ReadWrite.All, Sites.Read.All, Sites.ReadWrite.All
Delegated (personal Microsoft account)Files.ReadFiles.ReadWrite, Files.Read.All, Files.ReadWrite.All
ApplicationFiles.Read.AllFiles.ReadWrite.All, Sites.Read.All, Sites.ReadWrite.All

Then we can test in Postman to see if it is accessible.

Enter the following info in Postman. (You can use variables instead to keep sensitive data secure)

Access Token URL: https://login.microsoftonline.com/d8f36038-1f93-4543-affc-5dc92b6ee871/oauth2/v2.0/token (Change it to your tenant ID)

Client ID: a80c03cf-6ffa-4b6e-b2c8-6005310d3d87

Client Secret: fME7Q~cAaSBhXMGZoHY3ei64nn1fxGpqF42mh

Scope: https://graph.microsoft.com/.default (Note that Scope is different from BC)

Cilent Authentication: Send client credentials in body

And my test SharePoint site:

https://2qcj3x.sharepoint.com/sites/BCShareFiles

Documents:

Business Central Folder:

Then we can get the GUID of the SharePoint Online site via adding the following _api/site/id at the end of the Site URL:

https://2qcj3x.sharepoint.com/sites/BCShareFiles/_api/site/id

SharePoint (site) id: 5b3b7cec-cbfe-4893-a638-c18a34c6a394

At this time we can get document libraries information.
Test Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drives

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives

Documents (drive) id:

 b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww

If we want to get the file information in the folder, we need to use the following endpoint.
Test Endpoint:

https://graph.microsoft.com/v1.0/drives/{driveId}/root:/{folderPath}:/children

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business%20Central:/children

Here we can get the file (item) id.
File (item) id: 01AA2EHNP46T7UJIJ6DZBYKJUOMSKBYWRZ

Finally, let’s read the contents of the file, 1988-S.jpg.

Test Endpoint:

https://graph.microsoft.com/v1.0/sites/{site-id}/drive/items/{item-id}/content

For example,

https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drive/items/01AA2EHNP46T7UJIJ6DZBYKJUOMSKBYWRZ/content

Very simple. More details: Working with SharePoint sites in Microsoft Graph

PS:
1. If you get the following error, please check the API permissions in App Registration.

        “code”: “AccessDenied”,
        “message”: “Either scp or roles claim need to be present in the token.”,

2. If you encounter a 401 Unauthorized error, please check whether the Scope is set to the graph API. (https://graph.microsoft.com/.default)

        “code”: “InvalidAuthenticationToken”,
        “message”: “Access token validation failure. Invalid audience.”,

3. If you use delegated permissions, use the following endpoint:

https://graph.microsoft.com/v1.0/me/drive/root/

4. When the file cannot be found, the following error message will be displayed:

        “code”: “itemNotFound”,
        “message”: “The resource could not be found.”

5. When opening the file in Postman, because it can only be opened in XML, HTML, Txt or Json format, the following situation may occur, but the test is successful.

OK, next let’s see if we can get these files from AL. Here are a few important points.
Get OAuth Token:

Using OAuth Token to access SharePoint:

Reading file contents:

This time I did three simple tests in total, I hope it can be of some help to everyone.

1. Read all file names from a specified folder in SharePoint.

It is generally used to test whether SharePoint can be accessed. In addition, I only took the file name, but the json content displayed in Postman can be taken.

Test video:

Source Code: GitHub (Please note that the source code is for reference only, you can improve it according to your own needs)

pageextension 50100 CustomerListExt extends "Customer List"
{
    actions
    {
        addafter("Sent Emails")
        {
            action(ImportFileFromSharePoint)
            {
                Caption = 'Import File From SharePoint';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = GetActionMessages;

                trigger OnAction()
                var
                    SharePointHandler: Codeunit SharePointHandler;
                begin
                    SharePointHandler.Run();
                end;
            }
        }
    }
}

codeunit 50120 SharePointHandler
{
    trigger OnRun()
    begin
        ImportFilesFromSharePointHandler();
    end;

    procedure ImportFilesFromSharePointHandler()
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        JsonResponse: JsonObject;
        JsonArray: JsonArray;
        JsonToken: JsonToken;
        JsonTokenLoop: JsonToken;
        JsonValue: JsonValue;
        JsonObjectLoop: JsonObject;
        AuthToken: SecretText;
        SharePointFolderUrl: Text;
        ResponseText: Text;
        FileName: Text;
    begin
        // Get OAuth token
        AuthToken := GetOAuthToken();

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

        // Define the SharePoint folder URL

        SharePointFolderUrl := 'https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business%20Central:/children';
        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(SharePointFolderUrl);
        HttpRequestMessage.Method := 'GET';
        HttpRequestMessage.GetHeaders(Headers);
        Headers.Add('Authorization', SecretStrSubstNo('Bearer %1', 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();
                            if FileName = '' then begin
                                FileName := JsonValue.AsText();
                            end else begin
                                FileName := FileName + '\' + JsonValue.AsText();
                            end;
                        end;
                    end;
                    Message(FileName);
                end;

            end else begin
                //Report errors!
                HttpResponseMessage.Content.ReadAs(ResponseText);
                Error('Failed to fetch files from SharePoint: %1 %2', HttpResponseMessage.HttpStatusCode(), ResponseText);
            end;
        end else
            Error('Failed to send HTTP request to SharePoint');
    end;

    procedure GetOAuthToken() AuthToken: SecretText
    var
        ClientID: Text;
        ClientSecret: Text;
        TenantID: Text;
        AccessTokenURL: Text;
        OAuth2: Codeunit OAuth2;
        Scopes: List of [Text];
    begin
        ClientID := 'b4fe1687-f1ab-4bfa-b494-0e2236ed50bd';
        ClientSecret := 'huL8Q~edsQZ4pwyxka3f7.WUkoKNcPuqlOXv0bww';
        TenantID := '7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62';
        AccessTokenURL := 'https://login.microsoftonline.com/' + TenantID + '/oauth2/v2.0/token';
        Scopes.Add('https://graph.microsoft.com/.default');
        if not OAuth2.AcquireTokenWithClientCredentials(ClientID, ClientSecret, AccessTokenURL, '', Scopes, AuthToken) then
            Error('Failed to get access token from response\%1', GetLastErrorText());
    end;
}

2. Read all files from the specified folder in SharePoint and add them to the Customer 10000’s Attachments

Test video:

Source Code: GitHub (Please note that the source code is for reference only, you can improve it according to your own needs)

pageextension 50100 CustomerListExt extends "Customer List"
{
    actions
    {
        addafter("Sent Emails")
        {
            action(ImportFileFromSharePoint)
            {
                Caption = 'Import File From SharePoint';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = GetActionMessages;

                trigger OnAction()
                var
                    SharePointHandler: Codeunit SharePointHandler;
                begin
                    SharePointHandler.Run();
                end;
            }
        }
    }
}

codeunit 50120 SharePointHandler
{
    trigger OnRun()
    begin
        ImportFilesFromSharePoint();
    end;

    procedure ImportFilesFromSharePoint()
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        JsonResponse: JsonObject;
        JsonArray: JsonArray;
        JsonToken: JsonToken;
        JsonTokenLoop: JsonToken;
        JsonTokenFileName: JsonToken;
        JsonTokenItemId: JsonToken;
        JsonValue: JsonValue;
        JsonObjectLoop: JsonObject;
        AuthToken: SecretText;
        SharePointFolderUrl: Text;
        ResponseText: Text;
        FileName: Text;
        FileContent: InStream;
        ItemId: Text;
    begin
        // Get OAuth token
        AuthToken := GetOAuthToken();

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

        // Define the SharePoint folder URL
        SharePointFolderUrl := 'https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drives/b!7Hw7W_7Lk0imOMGKNMajlK0n-8Wdev9FmPdhx03j5o95rz4xvtmtTIUW5qUH7Jww/root:/Business%20Central:/children';
        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(SharePointFolderUrl);
        HttpRequestMessage.Method := 'GET';
        HttpRequestMessage.GetHeaders(Headers);
        Headers.Add('Authorization', SecretStrSubstNo('Bearer %1', 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();
                        JsonObjectLoop.Get('name', JsonTokenFileName);
                        JsonValue := JsonTokenFileName.AsValue();
                        FileName := JsonValue.AsText();
                        JsonObjectLoop.Get('id', JsonTokenItemId);
                        JsonValue := JsonTokenItemId.AsValue();
                        ItemId := JsonValue.AsText();
                        DownloadAndProcessFile(FileName, ItemId, AuthToken);
                    end;
                end;

            end else begin
                //Report errors!
                HttpResponseMessage.Content.ReadAs(ResponseText);
                Error('Failed to fetch files from SharePoint: %1 %2', HttpResponseMessage.HttpStatusCode(), ResponseText);
            end;
        end else
            Error('Failed to send HTTP request to SharePoint');
    end;

    procedure GetOAuthToken() AuthToken: SecretText
    var
        ClientID: Text;
        ClientSecret: Text;
        TenantID: Text;
        AccessTokenURL: Text;
        OAuth2: Codeunit OAuth2;
        Scopes: List of [Text];
    begin
        ClientID := 'b4fe1687-f1ab-4bfa-b494-0e2236ed50bd';
        ClientSecret := 'huL8Q~edsQZ4pwyxka3f7.WUkoKNcPuqlOXv0bww';
        TenantID := '7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62';
        AccessTokenURL := 'https://login.microsoftonline.com/' + TenantID + '/oauth2/v2.0/token';
        Scopes.Add('https://graph.microsoft.com/.default');
        if not OAuth2.AcquireTokenWithClientCredentials(ClientID, ClientSecret, AccessTokenURL, '', Scopes, AuthToken) then
            Error('Failed to get access token from response\%1', GetLastErrorText());
    end;

    procedure DownloadAndProcessFile(FileName: Text; ItemId: Text; AuthToken: SecretText)
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        FileUrl: Text;
        FileContent: InStream;
    begin
        // Define the file URL
        FileUrl := 'https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drive/items/' + ItemId + '/content';

        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(FileUrl);
        HttpRequestMessage.Method := 'GET';
        HttpRequestMessage.GetHeaders(Headers);
        Headers.Add('Authorization', SecretStrSubstNo('Bearer %1', 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(FileName, 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(FileName: Text; FileContent: InStream)
    var
        Cust: Record Customer;
        DocAttach: Record "Document Attachment";
        FileExtension: Text;
        FileMgt: Codeunit "File Management";
    begin
        if Cust.Get(10000) then begin
            FileExtension := CopyStr(FileMgt.GetExtension(FileName), 1, MaxStrLen(FileExtension));
            FileName := CopyStr(FileMgt.GetFileNameWithoutExtension(FileName), 1, MaxStrLen(FileName));
            DocAttach.Init();
            DocAttach.Validate("Table ID", Database::Customer);
            DocAttach.Validate("No.", Cust."No.");
            DocAttach.Validate("File Name", FileName);
            DocAttach.Validate("File Extension", FileExtension);
            DocAttach."Document Reference ID".ImportStream(FileContent, FileName);
            DocAttach.Insert(true);
        end;
    end;
}

PS: Dynamics 365 Business Central: How to import files from a zip file (Bulk import attachments and Excel files)

3. Read the specified csv file from the specified folder of SharePoint and import the data into BC

Since this only requires reading one file, it is relatively simple. (However, if you only know the folder and don’t know the item id (file name,) or want to import data ​​from multiple files, continue to use the loop in the above method)

Item id: 01AA2EHNNW32ULFEPXI5DIS23SD4S2CEJK

Test video:

Source Code: GitHub (Please note that the source code is for reference only, you can improve it according to your own needs)

pageextension 50100 ItemListExt extends "Item List"
{
    actions
    {
        addafter(AdjustInventory)
        {
            action(ImportFileFromSharePoint)
            {
                Caption = 'Import File From SharePoint';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = GetActionMessages;

                trigger OnAction()
                var
                    SharePointHandler: Codeunit SharePointHandler;
                begin
                    SharePointHandler.Run();
                end;
            }
        }
    }
}

codeunit 50120 SharePointHandler
{
    trigger OnRun()
    begin
        ImportFilesFromSharePoint();
    end;

    var
        CSVBuffer: Record "CSV Buffer" temporary;

    procedure ImportFilesFromSharePoint()
    var
        HttpClient: HttpClient;
        HttpRequestMessage: HttpRequestMessage;
        HttpResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        JsonResponse: JsonObject;
        JsonArray: JsonArray;
        JsonToken: JsonToken;
        JsonTokenLoop: JsonToken;
        JsonValue: JsonValue;
        JsonObjectLoop: JsonObject;
        AuthToken: SecretText;
        SharePointFolderUrl: Text;
        ResponseText: Text;
        FileName: Text;
        FileContent: InStream;
        FileUrl: Text;
        ItemId: Text;
    begin
        // Get OAuth token
        AuthToken := GetOAuthToken();

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

        // Define the file name
        ItemId := '01AA2EHNNW32ULFEPXI5DIS23SD4S2CEJK';
        // Define the file URL
        FileUrl := 'https://graph.microsoft.com/v1.0/sites/5b3b7cec-cbfe-4893-a638-c18a34c6a394/drive/items/' + ItemId + '/content';

        // Initialize the HTTP request
        HttpRequestMessage.SetRequestUri(FileUrl);
        HttpRequestMessage.Method := 'GET';
        HttpRequestMessage.GetHeaders(Headers);
        Headers.Add('Authorization', SecretStrSubstNo('Bearer %1', 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 GetOAuthToken() AuthToken: SecretText
    var
        ClientID: Text;
        ClientSecret: Text;
        TenantID: Text;
        AccessTokenURL: Text;
        OAuth2: Codeunit OAuth2;
        Scopes: List of [Text];
    begin
        ClientID := 'b4fe1687-f1ab-4bfa-b494-0e2236ed50bd';
        ClientSecret := 'huL8Q~edsQZ4pwyxka3f7.WUkoKNcPuqlOXv0bww';
        TenantID := '7e47da45-7f7d-448a-bd3d-1f4aa2ec8f62';
        AccessTokenURL := 'https://login.microsoftonline.com/' + TenantID + '/oauth2/v2.0/token';
        Scopes.Add('https://graph.microsoft.com/.default');
        if not OAuth2.AcquireTokenWithClientCredentials(ClientID, ClientSecret, AccessTokenURL, '', Scopes, AuthToken) then
            Error('Failed to get access token from response\%1', GetLastErrorText());
    end;

    procedure ImportFileContent(FileContent: InStream)
    var
        Item: Record Item;
        LineNo: Integer;
    begin
        CSVBuffer.Reset();
        CSVBuffer.DeleteAll();
        CSVBuffer.LoadDataFromStream(FileContent, ',');
        for LineNo := 2 to CSVBuffer.GetNumberOfLines() do begin
            Item.Init();
            Item.Validate("No.", GetValueAtCell(LineNo, 1));
            Item.Insert(true);
            Item.Validate(Description, GetValueAtCell(LineNo, 2));
            case GetValueAtCell(LineNo, 3) of
                'Inventory':
                    Item.Validate(Type, Item.Type::"Inventory");
                'Service':
                    Item.Validate(Type, Item.Type::"Service");
                'Non-Inventory':
                    Item.Validate(Type, Item.Type::"Non-Inventory");
            end;
            Evaluate(Item.GTIN, GetValueAtCell(LineNo, 4));
            Evaluate(Item."Unit Price", GetValueAtCell(LineNo, 5));
            Item.Validate("Base Unit of Measure", GetValueAtCell(LineNo, 6));
            Item.Modify(true);
        end;
    end;

    local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): Text
    begin
        if CSVBuffer.Get(RowNo, ColNo) then
            exit(CSVBuffer.Value)
        else
            exit('');
    end;
}

PS: Dynamics 365 Business Central: How to import data from CSV (comma-separated values) file

At this point, I believe you should be able to understand how to read data from SharePoint. In fact, not only that, you can add the file name (item id) or URL as a setting so that users can modify it, which is more flexible. Alternatively, you can set these process in the Job Queue so that the system can run automatically. Give it a try!!!😁

END

Hope this will help.

Thanks.

ZHU

コメント

Copied title and URL