top of page
Search

How to connect SharePoint with Business Central

SharePoint integration with Business Central is one of the most common requirement. SharePoint Document Management System can be used to manage files for Business Central online. Sometimes for various reasons developer needs to handle files in SharePoint from Business Central AL Code programatically. This post explains how to integrate SharePoint with Business Central and handling Document Libraries and files using Microsoft Graph API.


The following topics are covered in this post:

  • Retrieve Document Libraries from a SharePoint site.

  • Retrieve folders and files from a Document Library.

  • Upload and download files to and from a Document Library.

  • Delete files and folders from a Document Library.


Prerequisits

Some of the topics are already covered in other posts. To avoid repetition, links are provided to the related posts.


Following are the prequisits:

  • An app registration in azure active directory

How to setup app registration in Azure Portal is explained in How to use Microsoft Graph API in Business Central post.

  • Generic OAuth2 Library

Using this library OAuth Access Token can be acquired from Azure AD. How to setup "OAuth 2.0 Application" is explained in Generic OAuth2 Library for Business Central post. Source code can be downloaded from GitHub,


API Functions

The following API functions are required to handle SharePoint Document Libraries.

  1. Get Access Token

  2. Fetch Drives (Document Libraries)

  3. Fetch Drive's Items (Folders and Files from a Document Library)

  4. Fetch Drive's Child Items (Folders and Files from a Folder in a Document Library)

  5. Upload a File

  6. Download a File

  7. Create a Folder

  8. Delete a Drive Item (File or Folder)


Get Access Token

The following code returns Access Token using "Generic OAuth2 Library". "OAuth 2.0 Application" table, "OAuth 2.0 App. Helper" codeunit objects are from "Generic OAuth2 Library".

procedure GetAccessToken(AppCode: Code[20]): Text
var
	OAuth20Application: Record "OAuth 2.0 Application";
	OAuth20AppHelper: Codeunit "OAuth 2.0 App. Helper";
	MessageText: Text;
begin
	OAuth20Application.Get(AppCode);
	if not OAuth20AppHelper.RequestAccessToken(OAuth20Application, MessageText) then
		Error(MessageText);

	exit(OAuth20AppHelper.GetAccessToken(OAuth20Application));
end;

Setup OAuth 2.0 Application

Setup "OAuth 2.0 Application" to acquire Access Token from Azure AD using Generic OAuth2 Library.


1) Search for "OAuth 2.0 Applications" in Business Central

2) Create a new "OAuth 2.0 Application" from the list

The above screenshot contains sample configuration to get Access Token from Azure AD using Generic OAuth2 Library.


Fetch Drives (Document Libraries)

The following code retrived Drives (Document Libraries) and saves it in "Online Drive" table.

var
	DrivesUrl: Label 'https://graph.microsoft.com/v1.0/drives', Locked = true;
	
procedure FetchDrives(AccessToken: Text; var Drive: Record "Online Drive"): Boolean
var
	JsonResponse: JsonObject;
	JToken: JsonToken;
begin
	if HttpGet(AccessToken, DrivesUrl, JsonResponse) then begin
		if JsonResponse.Get('value', JToken) then
			ReadDrives(JToken.AsArray(), Drive);

		exit(true);
	end;
end;

Online Drive (table 50115)

table 50115 "Online Drive"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; id; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(2; name; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(3; description; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(4; driveType; Text[80])
        {
            DataClassification = CustomerContent;
        }
        field(5; createdDateTime; DateTime)
        {
            DataClassification = CustomerContent;
        }
        field(6; lastModifiedDateTime; DateTime)
        {
            DataClassification = CustomerContent;
        }
        field(7; webUrl; Text[250])
        {
            DataClassification = CustomerContent;
        }
    }

    keys
    {
        key(PK; id)
        {
            Clustered = true;
        }
    }
}

HttpGet method

Following is a helper function to get json object response from the given Url using AccessToken.

local procedure HttpGet(AccessToken: Text; Url: Text; var JResponse: JsonObject): Boolean
var
	Client: HttpClient;
	Headers: HttpHeaders;
	RequestMessage: HttpRequestMessage;
	ResponseMessage: HttpResponseMessage;
	RequestContent: HttpContent;
	ResponseText: Text;
	IsSucces: Boolean;
begin
	Headers := Client.DefaultRequestHeaders();
	Headers.Add('Authorization', StrSubstNo('Bearer %1', AccessToken));

	RequestMessage.SetRequestUri(Url);
	RequestMessage.Method := 'GET';

	if Client.Send(RequestMessage, ResponseMessage) then
		if ResponseMessage.IsSuccessStatusCode() then begin
			if ResponseMessage.Content.ReadAs(ResponseText) then
				IsSucces := true;
		end else
			ResponseMessage.Content.ReadAs(ResponseText);

	JResponse.ReadFrom(ResponseText);
	exit(IsSucces);
end;


ReadDrives method

This function reads JsonArray and inserts data into "Online Drive" table.

local procedure ReadDrives(JDrives: JsonArray; var Drive: Record "Online Drive")
var
	JDriveItem: JsonToken;
	JDrive: JsonObject;
	JToken: JsonToken;
begin
	foreach JDriveItem in JDrives do begin
		JDrive := JDriveItem.AsObject();

		Drive.Init();
		if JDrive.Get('id', JToken) then
			Drive.Id := JToken.AsValue().AsText();
		if JDrive.Get('name', JToken) then
			Drive.Name := JToken.AsValue().AsText();
		if JDrive.Get('description', JToken) then
			Drive.description := JToken.AsValue().AsText();
		if JDrive.Get('driveType', JToken) then
			Drive.driveType := JToken.AsValue().AsText();
		if JDrive.Get('createdDateTime', JToken) then
			Drive.createdDateTime := JToken.AsValue().AsDateTime();
		if JDrive.Get('lastModifiedDateTime', JToken) then
			Drive.lastModifiedDateTime := JToken.AsValue().AsDateTime();
		if JDrive.Get('webUrl', JToken) then
			Drive.webUrl := JToken.AsValue().AsText();
		Drive.Insert();
	end;
end;

Fetch Drive's Items

The following code can fetch files and folders from a Drive (Document Library). DriveID is the value of the id property in Drive JsonObject (saved in "Online Drive" table).

var
	DrivesItemsUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/root/children', Comment = '%1 = Drive ID', Locked = true;
	
procedure FetchDrivesItems(AccessToken: Text; DriveID: Text; var DriveItem: Record "Online Drive Item"): Boolean
var
	JsonResponse: JsonObject;
	JToken: JsonToken;
	IsSucces: Boolean;
begin
	if HttpGet(AccessToken, StrSubstNo(DrivesItemsUrl, DriveID), JsonResponse) then begin
		if JsonResponse.Get('value', JToken) then
			ReadDriveItems(JToken.AsArray(), DriveID, '', DriveItem);

		exit(true);
	end;
end;

Online Drive Item (table 50115)

To store Files and Folder information

table 50116 "Online Drive Item"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; id; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(2; driveId; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(3; parentId; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(4; name; Text[250])
        {
            DataClassification = CustomerContent;
        }
        field(5; isFile; Boolean)
        {
            DataClassification = CustomerContent;
        }
        field(6; mimeType; Text[80])
        {
            DataClassification = CustomerContent;
        }
        field(7; size; BigInteger)
        {
            DataClassification = CustomerContent;
        }
        field(8; createdDateTime; DateTime)
        {
            DataClassification = CustomerContent;
        }
        field(9; webUrl; Text[250])
        {
            DataClassification = CustomerContent;
        }
    }

    keys
    {
        key(PK; id)
        {
            Clustered = true;
        }
    }
}


ReadDriveItems

Read JDriveItems JsonArray and saves in "Online Drive Item" table.

local procedure ReadDriveItems(
	JDriveItems: JsonArray;
	DriveID: Text;
	ParentID: Text;
	var DriveItem: Record "Online Drive Item")
var
	JToken: JsonToken;
begin
	foreach JToken in JDriveItems do
		ReadDriveItem(JToken.AsObject(), DriveID, ParentID, DriveItem);
end;

local procedure ReadDriveItem(
	JDriveItem: JsonObject;
	DriveID: Text;
	ParentID: Text;
	var DriveItem: Record "Online Drive Item")
var
	JFile: JsonObject;
	JToken: JsonToken;
begin

	DriveItem.Init();
	DriveItem.driveId := DriveID;
	DriveItem.parentId := ParentID;

	if JDriveItem.Get('id', JToken) then
		DriveItem.Id := JToken.AsValue().AsText();
	if JDriveItem.Get('name', JToken) then
		DriveItem.Name := JToken.AsValue().AsText();

	if JDriveItem.Get('size', JToken) then
		DriveItem.size := JToken.AsValue().AsBigInteger();

	if JDriveItem.Get('file', JToken) then begin
		DriveItem.IsFile := true;
		JFile := JToken.AsObject();
		if JFile.Get('mimeType', JToken) then
			DriveItem.mimeType := JToken.AsValue().AsText();
	end;

	if JDriveItem.Get('createdDateTime', JToken) then
		DriveItem.createdDateTime := JToken.AsValue().AsDateTime();
	if JDriveItem.Get('webUrl', JToken) then
		DriveItem.webUrl := JToken.AsValue().AsText();
	DriveItem.Insert();
end;

Fetch Drive's Child Items

Following is the code to read Folders and Files from a Folder in a Document Library. ItemID is the value of the id property in DriveItem JsonObject (saved in "Online Drive Item" table).


var
	DrivesChildItemsUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/items/%2/children', Comment = '%1 = Drive ID, %2 = Item ID', Locked = true;

procedure FetchDrivesChildItems(
	AccessToken: Text;
	DriveID: Text;
	ItemID: Text;
	var DriveItem: Record "Online Drive Item"): Boolean
var
	JsonResponse: JsonObject;
	JToken: JsonToken;
	IsSucces: Boolean;
begin
	if HttpGet(AccessToken, StrSubstNo(DrivesChildItemsUrl, DriveID, ItemID), JsonResponse) then begin
		if JsonResponse.Get('value', JToken) then
			ReadDriveItems(JToken.AsArray(), DriveID, ItemID, DriveItem);

		exit(true);
	end;
end;


Upload a File

The following code uploads a file into a Document Library and save newly created Drive Item (file) details in "Online Drive Item" table.


ParentID: Folder's Drive Item ID (should be blank for a file to be uploaded to the root drive)

FolderPath: Name of the target folder (ex: "/personal/documents" means file will be saved in documents folder which is a subfolder of personal folder)

FileName: Name of the file with extension (ex: "readme.pdf")

Stream: File Content


var
	UploadUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/items/root:/%2:/content', Comment = '%1 = Drive ID, %2 = File Name', Locked = true;

procedure UploadFile(
	AccessToken: Text;
	DriveID: Text;
	ParentID: Text;
	FolderPath: Text;
	FileName: Text;
	var Stream: InStream;
	var OnlineDriveItem: Record "Online Drive Item"): Boolean
var
	HttpClient: HttpClient;
	Headers: HttpHeaders;
	RequestMessage: HttpRequestMessage;
	RequestContent: HttpContent;
	ResponseMessage: HttpResponseMessage;
	JsonResponse: JsonObject;
	IsSucces: Boolean;
	ResponseText: Text;
begin
	Headers := HttpClient.DefaultRequestHeaders();
	Headers.Add('Authorization', StrSubstNo('Bearer %1', AccessToken));

	RequestMessage.SetRequestUri(
		StrSubstNo(
			UploadUrl,
			DriveID,
			StrSubstNo('%1/%2', FolderPath, FileName)));
	RequestMessage.Method := 'PUT';

	RequestContent.WriteFrom(Stream);
	RequestMessage.Content := RequestContent;

	if HttpClient.Send(RequestMessage, ResponseMessage) then
		if ResponseMessage.IsSuccessStatusCode() then begin
			if ResponseMessage.Content.ReadAs(ResponseText) then begin
				IsSucces := true;
				if JsonResponse.ReadFrom(ResponseText) then
					ReadDriveItem(JsonResponse, DriveID, ParentID, OnlineDriveItem);
			end;
		end else
			if ResponseMessage.Content.ReadAs(ResponseText) then
				JsonResponse.ReadFrom(ResponseText);

	exit(IsSucces);
end;

Download a File

The following code downloads a file from a Document Library. ItemID is the value of the id property in DriveItem JsonObject (saved in "Online Drive Item" table).

var
	DownloadUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/items/%2/content', Comment = '%1 = Drive ID, %2 = Item ID', Locked = true;

procedure DownloadFile(AccessToken: Text; DriveID: Text; ItemID: Text; var Stream: InStream): Boolean
var
	TempBlob: Codeunit "Temp Blob";
	OStream: OutStream;
	JsonResponse: JsonObject;
	Content: Text;
	NewDownloadUrl: Text;
begin
	NewDownloadUrl := StrSubstNo(DownloadUrl, DriveID, ItemID);
	if GetResponse(AccessToken, NewDownloadUrl, Stream) then
		exit(true);
end;

Create a Folder

The following code creates a new Folder in a Document Library.

ItemID: id of the Parent Folder (optional)

var
	CreateFolderUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/items/%2/children', Comment = '%1 = Drive ID, %2 = Item ID', Locked = true;
	CreateRootFolderUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/root/children', Comment = '%1 = Drive ID', Locked = true;

procedure CreateDriveFolder(
	AccessToken: Text;
	DriveID: Text;
	ItemID: Text;
	FolderName: Text;
	var OnlineDriveItem: Record "Online Drive Item"): Boolean
var
	HttpClient: HttpClient;
	Headers: HttpHeaders;
	ContentHeaders: HttpHeaders;
	RequestMessage: HttpRequestMessage;
	RequestContent: HttpContent;
	ResponseMessage: HttpResponseMessage;
	ResponseText: Text;
	JsonBody: JsonObject;
	RequestText: Text;
	EmptyObject: JsonObject;
	JsonResponse: JsonObject;
begin
	Headers := HttpClient.DefaultRequestHeaders();
	Headers.Add('Authorization', StrSubstNo('Bearer %1', AccessToken));
	if ItemID = '' then
		RequestMessage.SetRequestUri(StrSubstNo(CreateRootFolderUrl, DriveID))
	else
		RequestMessage.SetRequestUri(StrSubstNo(CreateFolderUrl, DriveID, ItemID));
	RequestMessage.Method := 'POST';

	// Body
	JsonBody.Add('name', FolderName);
	JsonBody.Add('folder', EmptyObject);
	JsonBody.WriteTo(RequestText);
	RequestContent.WriteFrom(RequestText);

	// Content Headers
	RequestContent.GetHeaders(ContentHeaders);
	ContentHeaders.Remove('Content-Type');
	ContentHeaders.Add('Content-Type', 'application/json');

	RequestMessage.Content := RequestContent;

	if HttpClient.Send(RequestMessage, ResponseMessage) then
		if ResponseMessage.IsSuccessStatusCode() then begin
			if ResponseMessage.Content.ReadAs(ResponseText) then begin
				if JsonResponse.ReadFrom(ResponseText) then
					ReadDriveItem(JsonResponse, DriveID, ItemID, OnlineDriveItem);

				exit(true);
			end;
		end;
end;


Delete a Drive Item

The following code deletes a folder or a file from a Document Library. ItemID is the id of the Drive Item (file or folder) saved in "Online Drive Item" table.


var
	DeleteUrl: Label 'https://graph.microsoft.com/v1.0/drives/%1/items/%2', Comment = '%1 = Drive ID, %2 = Item ID', Locked = true;

procedure DeleteDriveItem(AccessToken: Text; DriveID: Text; ItemID: Text): Boolean
var
	HttpClient: HttpClient;
	Headers: HttpHeaders;
	RequestMessage: HttpRequestMessage;
	ResponseMessage: HttpResponseMessage;
begin
	Headers := HttpClient.DefaultRequestHeaders();
	Headers.Add('Authorization', StrSubstNo('Bearer %1', AccessToken));

	RequestMessage.SetRequestUri(StrSubstNo(DeleteUrl, DriveID, ItemID));
	RequestMessage.Method := 'DELETE';

	if HttpClient.Send(RequestMessage, ResponseMessage) then
		if ResponseMessage.IsSuccessStatusCode() then
			exit(true);
end;

Conclusion

So this post has covered all basic operations that can be performed to handle documents in SharePoint within Business Central. Publishing, Version control etc. SharePoint features are not covered.


Tip: The same code with little changes can be used to integrate with OneDrive. Basically, Microsoft Graph API offers the same API for SharePoint and OneDrive to handle files.


Code in Action

Complete source code with working examples can be downloaded from GitHub. Issues and recommendations can be posted at GitHub Issue Tracker.


Happy Coding!!!


#MsDyn365 #MsDyn365BC #BusinessCentral #SharePoint #DynamicsNAV #MSGraphAPI

13,004 views6 comments
bottom of page