I am ultimately trying add data to an Excel file in my OneDrive using Graph API with a local node.js app running on my PC, not a website, and I'm aiming to simply call 'node index.js' in console to do this, without having to authorise my login (perhaps this is impossible?)
I'm starting off with these endpoints to get simple data first but can't get them to work (base URL https://graph.microsoft.com):
/v1.0/me//v1.0/users/{My-UserID}//v1.0/me/drive/root:/TestFile.xlsx:/workbook/worksheets/v1.0/users/{My-UserID}/drive/root:/TestFile.xlsx:/workbook/worksheets
/v1.0/me/ and /v1.0/users/{My-UserID}/ returns 'Resource \'{My-UserID}\' does not exist or one of its queried reference-property objects are not present.'
/v1.0/me/drive/root:/TestFile.xlsx:/workbook/worksheets and /v1.0/users/{My-UserID}/drive/root:/TestFile.xlsx:/workbook/worksheets returns 'Tenant does not have a SPO license.'.
How I got my token
I got the access token using the adal-node library (https://www.npmjs.com/package/adal-node), using the 'Server to Server via Client Credentials' flow.
How I called the graph API
I am calling the API manually without the the Microsoft Graph SDK using axios.
How I registered my app in Azure portal
- I went to 'Azure Active Directory'.
- Clicked 'App registrations'.
- Clicked 'New registration'.
a) Chose an app name.
b) Selected 'Accounts in this organizational directory only (Default Directory only - Single tenant)' in 'Supported account types'.
c) Left 'Redirect URI (optional)' empty
- Clicked 'Register'.
- Copied 'Application (client) ID' and used it as the 'ClientID' constant in my app below.
- Copied 'Directory (tenant) ID' and used it as the 'Tenant' constant in my app below.
- Clicked 'Certificates & secrets'.
- Clicked 'New client secret'.
a) Left 'Description' empty.
b) Chose 'Never' under 'Expires'.
c) Clicked 'Add'.
- Copied the generated secret and used it as the 'ClientSecret' constant in my app below.
- Clicked 'API permissions'.
- Clicked 'Add a permission'.
- Clicked 'Microsoft Graph'.
- Clicked 'Application permissions'.
- Checked 'Files.Read.All'.
- Clicked 'Add permissions'.
- Clicked 'Grant admin consent for Default Directory', clicked 'Yes'.
I have also tried adding delegated permissions checking all permissions available and application permissions for 'Directory.Read.All' and 'Group.Read.All'.
What I've tried
I have tried using Graph Explorer (https://developer.microsoft.com/en-us/graph/graph-explorer) and can see that both /v1.0/me/ and /v1.0/me/drive/root:/TestFile.xlsx:/workbook/worksheets returns correct data.
I have also tried replacing /me/ with /users/{My-UserID}/ and Graph Explorer returns the same correct data but my node.js app still returns the same errors.
I used a JWT decoder to check my retrieved token and seems like
oidcorrectly equals to {My-UserID}.rolesincluded 'Files.Read.All' and 'User.Read.All' which is as I set it up in my app registration in Azure portal.appidcorrectly equals to {My-ApplicationID}.tidcorrectly equals to {My-TenantID}.audequals 'https://graph.microsoft.com'
I also tried obtaining the token manually by calling POST to https://login.microsoftonline.com/{My-TenantID}/oauth2/v2.0/token with header:
'Content-Type': 'application/x-www-form-urlencoded'
and data:
client_id=${My-ClientID}&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret={My-ClientSecret}&grant_type=client_credentials
and got this error: 'AADSTS7000215: Invalid client secret is provided'. <-- Edit: I found out why I'm getting this error. I had to escape special characters like : / + using %{ascii-code-in-hex}.
Other things to note
I have an Office 365 home subscription and a pay-as-you-go Azure account using the same email address as my OneDrive that has the Excel file that I want to add data to.
Edit: I found out that if you use Graph Explorer and call /v1.0/users/{any-number} you will get back your own profile. This means that {My-UserID} could have not been my correct user ID all along.
const Tenant = {My-TenantID};
const ClientID = {My-ApplicationID};
const ClientSecret = {My-ClientSecret};
function GetAccessToken()
{
const AuthenticationContext = require('adal-node').AuthenticationContext;
const authorityUrl = `https://login.microsoftonline.com/${Tenant}`;
const applicationId = ClientID;
const clientSecret = ClientSecret;
const resource = 'https://graph.microsoft.com';
const context = new AuthenticationContext(authorityUrl);
return new Promise((resolve, reject) =>
{
context.acquireTokenWithClientCredentials(resource, applicationId, clientSecret, (error, tokenResponse) =>
{
if (error)
{
console.log('Get token error:');
console.dir(error.stack, {depth: null});
reject(error);
}
else
{
console.log('Get token success:');
console.dir(tokenResponse, {depth: null});
resolve(tokenResponse.accessToken);
}
});
});
}
async function GetData()
{
const axios = require('axios');
const AccessToken = await GetAccessToken();
axios(
{
method: 'GET',
url: 'https://graph.microsoft.com/v1.0/me/', //I have also tried /v1.0/users/{My-UserID}/
headers:
{
'Authorization': `Bearer ${AccessToken}`,
}
})
.then(result =>
{
console.log('Get data success:');
console.dir(result.data, {depth: null});
})
.catch(error =>
{
console.log('Get data failed:');
if (error.response.data != undefined)
{
console.dir(error.response.data, {depth: null});
}
else
{
console.dir(error, {depth: null});
}
})
}
GetData();
Update 1
I think I've figured out why /v1.0/me nor /v1.0/users/{My-UserID} does not work. If we authenticate without a user (the flow I'm opting for here), the only users the application will be able to access are the ones in Azure Active Directory (AAD). Now, if you login to Azure using your hotmail account and go to 'Azure Active Directory' then 'Users' you will see your account there but I think that that account is actually a different account to your 'common' Microsoft account ('common' Microsoft account being the one you signed up with hotmail). Yes, it seems that Microsoft creates a separate 'AAD' identity and this is a different user than your 'common' identity user.
{My-UserID} is actually the user ID for the 'common' identity. So..this means that if I call /v1.0/users/{My-UserID}, my application will not recognise that user ID, because that user ID does not belong in the AAD. So what I tried is copying the 'Object ID' from the user in AAD, and used that instead: /v1.0/users/{AAD-ObjectID} then viola, I see my profile.
Edit: {My-UserID} is actually NOT my 'common' identity's user ID. It is some other ID that I don't know yet.
I then tried:
- creating a new application but instead of choosing 'Accounts in this organizational directory only (Default Directory only - Single tenant)' in 'Supported account types', choose 'Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox)' instead
- followed the 'Get access on behalf of a user' auth flow
and can confirm that with this flow, /v1.0/me and v1.0/users/{My-UserID} does work. Edit: Graph API actually returns my profile no matter what ID you specify, so you can specify v1.0/users/123 and it will still return your profile!
So I thought, hmm, how about instead of granting admin consent from Azure portal, I grant admin consent using the https://login.microsoftonline.com/common/adminconsent endpoint. Doing this, and getting access token from /common/oauth2/v2.0/token then
/v1.0/mereturns 'Current authenticated context is not valid for this request.'/v1.0/users/{My-UserID}returns 'The identity of the calling application could not be established' error./v1.0/users/{AAD-ObjectID}returns 'The identity of the calling application could not be established' error.
If I get access token from /{My-TenantID}/oauth2/v2.0/token then
/v1.0/meand/v1.0/users/{My-UserID}returns original 'Request_ResourceNotFound' error./v1.0/users/{AAD-ObjectID}correctly returns my profile.
I then tried /v1.0/users/{AAD-ObjectID}/drive/root:/TestFile.xlsx:/workbook/worksheets but am still getting 'Tenant does not have a SPO license.' I suspect this is because my AAD identity does not have the necessary license to manipulate OneDrive items.
A workaround for adding data to Excel without going down the 'Get access on behalf of user' auth flow that I found works is using Logic App instead. So in a Logic App, I set up an HTTP request endpoint which when triggered will add data to Excel and get my Node.js app to call this endpoint instead of calling Graph API directly.
In conclusion
I think this is probably something that Microsoft have to fix on their end - the AAD identity shouldn't be a separate identity but the same identity as my 'common' identity so that I can access my user info using /v1.0/users/{My-UserID} and manipulate my OneDrive files using /v1.0/users/{My-UserID}/drive.
Update 2
I have found a way to call Graph API directly without using Logic App!
So for those who have followed my problem, there are 2 ways call a Graph API:
- Call on behalf of a user
- Call without a user
The key point I figured out is 'call on behalf of a user' is like saying, you, the user, wants to use Graph API through your app, which is actually what I want because I want to call Graph API to edit my Excel file. 'Call without a user' is like saying, the app itself is calling Graph API - not the user using the app to call Graph API, so it won't have access to user data, unless that user is in the active directory. It's important to note (from my previous answer) here that the user you see in AAD that has the same name as you is not actually 'you', it is the 'AAD' version of you. So even though you see your name in the AAD, 'Call without a user' will not be able to access your OneDrive items. You'll need 'Call on behalf of a user' for that.
Now if we need to do 'Call on behalf of a user', I thought I would have to go through the 'get authorization code --> get access token' every time to access my OneDrive files, but I just found out that you can also refresh the token so you can keep accessing the OneDrive files without having to get a new authorization code. The most important thing for this step is to make sure to include offline_access to the scope parameter when getting the authorization code and getting the token, otherwise refreshToken will not be returned when you ask for the token and you will not be able to refresh the token. You will also need to add offline_access delegated permission in your app registration as well.
So with this, I can just get my node app to save the refreshToken and refresh the token every time it runs and viola, I can edit my Excel file whenever I want!
