Share
Report
674I'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; }
All responses (
Answers (
