Access Excel Online via a custom application in Node.js

Question

My app can access my Google Sheets via Google API and a service account without any problems. I try to do the same with Microsoft Excel On-Line.

I have a personal Microsoft account, one Excel file on OneDrive and admin access to Microsoft Azure. I can make queries with Microsoft Graph without any problems.

For my app, I went to Azure Portal -> Azure Active Directory -> App registrations. I created a new app, with clientId, tenantId, secret, etc.. Add the API permissions : User.ReadWrite.All, Files.Read.Write.All granted by admin only both types : Application and Delegated.

I wrote this code:

import {ConfidentialClientApplication} from '@azure/msal-node'
import fetch from 'node-fetch';

const config = {
    auth: {
        clientId: 'aed11b78-3498-4bc3-b7c9-e5d51cb1a79e',
        authority: "https://login.microsoftonline.com/72119209-f1d8-4705-a507-8d1e76935c64",
        clientSecret: 'DqG7Q~ibnMrBmREhhCQppx5Q.HOlxZg39IpwK'
   }
};
var client = new ConfidentialClientApplication(config);

const request= {
        scopes: ["https://graph.microsoft.com/.default"]
}

let url1 = 'https://graph.microsoft.com/v1.0/users'
let url2 = 'https://graph.microsoft.com/v1.0/drive/root/children'


let run = (async() => {
    let response = await client.acquireTokenByClientCredential(request)

    let query = await fetch(url1, {
        headers: {
            'Authorization': 'Bearer ' + response.accessToken
        }
    });
    
    let json  = await query.json()
    console.dir(json)

})()

(no worry I erased the credentials)

If I try the code with Url1 : 'https://graph.microsoft.com/v1.0/users' , no problem I got my users info.

value: [ { businessPhones: [], displayName: 'Pierre Roy', givenName: 'Pierre', jobTitle: null, mail: null, mobilePhone: null, officeLocation: null, preferredLanguage: 'en', surname: 'Roy', ...

However, if I try url2: 'https://graph.microsoft.com/v1.0/drive/root/children'

No luck, I got this reply :

{ error: { code: 'BadRequest', message: 'Tenant does not have a SPO license.', innerError: { date: '2021-10-22T02:01:58', 'request-id': '386808e2-13b0-458d-8131-e18653bfb1bd', 'client-request-id': '386808e2-13b0-458d-8131-e18653bfb1bd' } } }

Which is I guess some SharePoint access issues.. I just want to make a personal app and access my personal on-line Excel files programmatically for testing purpose. SharePoint is for enterprises purposes and useless for my purpose.

Any solution for this ? Do I need to have SharePoint and pay some monthly fees to access my little online Excel file programmatically ?

score 0 · Answer 1 · answered Oct 26 '21 at 13:44

{ error: { code: 'BadRequest', message: 'Tenant does not have a SPO license.', innerError: { date: '2021-10-22T02:01:58', 'request-id': '386808e2-13b0-458d-8131-e18653bfb1bd', 'client-request-id': '386808e2-13b0-458d-8131-e18653bfb1bd' } } }

Based on the error message, after referencing multiple Stack Over flow threads & git hub discussion understood that you can't use the Microsoft Graph API to access OneDrive without having SharePoint Business licenses.

if you are using a personal account in a registered Azure Active Directory(AAD) app, that type isn't Personal Microsoft accounts only or Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox) you will get this error. Also, you need to use the correct endpoint to avoid errors.

The main problem is our account type. As a personal account, there are some restrictions to access one drive files. These restrictions are:

You can only use Oauth2 Code Flow or Oauth2 Token Flow. Both are interactive approaches. [1][2]
Your application registered in AAD needs be Personal Microsoft accounts only or Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox) and each one have a different endpoint to acquire the access token (That you can saw clicking on endpoint button, near the delete app button in app page). [3]
Enable these delegated permissions to your application registered in AAD: Files.Read, Files.Read.All, Files.ReadWrite, and Files.ReadWrite.All.

Here is the reference SO thread where user is trying to access the one Drive API using postman

Access Excel Online via a custom application in Node.js

1 Answers1