I ended up doing exactly what I wanted using React and NodeJS using the Microsoft Graph API. You can try example queries here https://developer.microsoft.com/en-us/graph/graph-explorer, some of which allow you to fetch and perform CRUD operations if needed or just simply fetch on a sharepoint excel file.
The only thing you need to use the queries directly in your React/NodeJS application is a token from a Microsoft authentication session. In order to use Microsoft's authentication session you will need to generate a private and public key in sharepoint.
Steps for registering your application and getting the key needed for client authentication...
- https://portal.azure.com/#home < azure active directory
- Add > App registration
- Accounts in any organizational directory (Any Azure AD directory - Multitenant)
- Single-page application: http://localhost:3000 http://localhost:3080
- Get application (client) ID
- Authentication > add a platform > Single page application
- redirect url: http://localhost:3000 && http://localhost:3080
Once this is done you are all set on sharepoint's end. All of this was just to get the client ID key for your app.
Now you will need to add this key to your react app. This can be done by going to your index.js and formatting it like so, to let the application recognize your app as a trusted authentication client.
You will need the npm packages "@microsoft/mgt-msal2-provider" & "@microsoft/mgt-element"
import React from 'react';
import ReactDOM from 'react-dom/client';
import './index.css';
import App from './App';
import { Providers } from '@microsoft/mgt-element';
import { Msal2Provider } from '@microsoft/mgt-msal2-provider';
Providers.globalProvider = new Msal2Provider({
clientId: 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
});
const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(
<App />
);
Now your app is recognized as a trustworthy client and you can now test your authentication. Install the package "@microsoft/mgt-react" to access an easy authentication login component.
import { Login } from '@microsoft/mgt-react';
function App() {
return (
<Login />
)
}
If everything was done correctly it should show a microsoft login component and you should be able to login to a microsoft account. This will give you a token which you can use to run Sharepoint queries using the microsoft graph api.
Example on how to get token
import { Providers } from "@microsoft/mgt";
function Test() {
const accessToken = await Providers.globalProvider.getAccessToken({ scopes: ['User.Read', 'Files.ReadWrite'] })
console.log(accessToken) // Returns token!
}
With your token you can now run queries to your sharepoint excel documents and much more.
Example query with authentication token...
const XLSX_DOC = '1SLFSDA93LD30AKD3L3098KFLZBODFMS3K';
const XLSX_SHEET = 'Sheet1';
const SHAREPOINT_URL = `https://graph.microsoft.com/v1.0/me/drive/items/${XLSX_DOC}/workbook/worksheets/${XLSX_SHEET}/usedRange`;
const accessToken = req.params.token;
const bearer = `Bearer ${accessToken}`;
let headers = new Headers();
headers.append("Authorization", bearer);
const options = {
method: "GET",
headers: headers
};
const response = await fetch(SHAREPOINT_URL, options);
const data = await response.json();
console.log(data); // Sharepoint excel data!
To get the sharepoint doc, you will need to go to the Microsoft graph explorer and input the query "https://graph.microsoft.com/v1.0/me/drive/root/children".
This will return the list of sharepoint excel files you have, find the file such as "Book.xlsx", whatever the file name is, and copy the ID, done.
To get the sharepoint sheet, you just put the name of the sheet.
There is a lot more you can do once you get to this point. You can explore other things you can do in this wiki https://learn.microsoft.com/en-US/graph/api/overview?view=graph-rest-1.0. If you have any questions feel free to reply here or message me directly.
