Microsoft Excel (OneDrive for Business / SharePoint via OAuth2)

This guide walks you through using a Microsoft Excel workbook stored in OneDrive for Business or SharePoint Online as a scheduled, refreshable data source in DashboardFox via the Microsoft Graph API and OAuth2.

Unlike the Google Sheets public-API integration, Microsoft does not offer an API-key-with-public-link pattern. The only supported path for programmatic access to Excel data is OAuth2 with Microsoft Graph, which requires a paid Microsoft 365 Business subscription and an app registration in Microsoft Entra ID (formerly Azure AD).

If your workbook is in a personal Microsoft account (outlook.com, hotmail.com, M365 Personal/Family), the Microsoft Graph Workbook API is not supported on that storage. See our Microsoft Excel (Personal OneDrive) guide instead for the file-import workflow.

Prerequisites

Before starting, confirm you have:

• A Microsoft 365 Business subscription (Business Basic, Business Standard, Business Premium, or Enterprise). M365 Personal/Family is not sufficient.

• The workbook stored in either OneDrive for Business (a user's business OneDrive) or SharePoint Online (a team site document library). For new integrations, we strongly recommend SharePoint Online — see Part 2 for why.

• Global Administrator access to the Microsoft Entra ID tenant (or someone who can register apps and grant admin consent on your behalf).

• DashboardFox admin access for the integration setup.

Part 1. Register an App in Microsoft Entra ID

Microsoft Entra ID is the identity service that issues OAuth2 access tokens. You'll register a "client app" identity for DashboardFox, then DashboardFox uses that identity to call the Graph API.

1. Open the Microsoft Entra Admin Center

• Navigate to https://entra.microsoft.com.

• Sign in with your Global Administrator account.

2. Create a New App Registration

• In the left menu, expand Applications → App registrations.

• Click + New registration at the top.

• Fill in the form:

  Field	Value
  Name	DashboardFox Excel Connector (or similar — this appears on the user consent screen)
  Supported account types	Select "Accounts in this organizational directory only — Single tenant" for a single-customer deployment. Choose multi-tenant only if you're publishing this integration to multiple tenants.
  Redirect URI — Platform	Web (dropdown)
  Redirect URI — URL	Your DashboardFox OAuth callback URL, in the form https://yourcompany.dashboardfox.app/apiconnection.html

• Click Register.

3. Copy the App Identifiers

After registration, you land on the app's Overview page. Two values you'll need:

• Application (client) ID — the GUID near the top. Copy and save it.

• Directory (tenant) ID — also a GUID. Copy and save it.

4. Create a Client Secret

The Application ID is public; the client secret is the password-equivalent the app uses to prove its identity.

• In the left menu of your app, click Certificates & secrets.

• Click the Client secrets tab → + New client secret.

• Description: DashboardFox integration (or similar — for your reference).

• Expires: choose 24 months for production; renew before expiration to avoid a lapse.

• Click Add.

• IMPORTANT: Copy the Value column immediately. Microsoft will hide this value as soon as you navigate away, and there is no way to retrieve it again. If lost, create a new secret.

5. Configure API Permissions

This grants the app permission to read Excel files via the Microsoft Graph API.

• In the left menu, click API permissions.

• Click + Add a permission.

• Click Microsoft Graph.

• Click Delegated permissions.

• Search for and check the following:

  • Files.Read.All — allows the app to read files the signed-in user can access

  • offline_access — required to receive a refresh token for ongoing scheduled fetches

• Click Add permissions.

• (Optional but recommended) Click Grant admin consent for [your tenant] at the top, then confirm. This pre-consents the permissions tenant-wide so users don't see a consent prompt.

6. Verify Authentication Settings

• In the left menu, click Authentication.

• Confirm the redirect URI is correct (matches your DashboardFox URL exactly).

• Under Implicit grant and hybrid flows, both checkboxes should be unchecked.

• Under Advanced settings → Allow public client flows, leave as No.

You now have four values to save: Application (client) ID, Directory (tenant) ID, client secret value, and redirect URI.

Part 2. Prepare Your Workbook in SharePoint

1. Choose Where to Store the Workbook

You have two options:

Recommendation: Create a dedicated SharePoint team site (e.g., "BI Data" or "Reporting") and store your workbook in its Documents library.

To create a new site: https://admin.microsoft.com → SharePoint admin center → Create site → Team site → Standard team. The "Standard team" template is the cleanest choice — it gives you a Documents library without unnecessary Teams/Outlook integrations.

2. Prepare the Workbook for API Consumption

The Microsoft Graph API returns the raw underlying values of cells, not the formatted display. To produce clean, easy-to-cast data downstream, follow these formatting rules:

• Dates: Format date columns as Custom → yyyy-mm-dd. The API will return "2024-01-01" instead of Excel's internal serial number.

• Numbers / Currency / Revenue: Format as plain Number with no thousands separator and no currency symbol. If you want a $ symbol in your reports, add it via formatting in DashboardFox, not in Excel.

• Percentages: Format as plain Number. Store the underlying decimal (e.g., 0.05) rather than 5%.

• Negatives: Use plain minus-sign format, not accounting parentheses.

• Worksheet names: Use friendly names like Sales_Orders instead of Sheet1. Avoid spaces; use underscores. The API is case-sensitive on worksheet names.

• Column headers: Row 1 should contain headers; row 2+ should contain data. No title rows, summary rows, or merged cells.

• No empty columns or rows within your data block.

These rules match the prep philosophy of our Excel/CSV Import lesson — set types correctly in Excel, save downstream pain.

3. Get the SharePoint Site ID

The Microsoft Graph API addresses files by site ID + driveItem ID. You need to look up the site ID once.

• Open Microsoft Graph Explorer.

• Click Sign in to Graph Explorer and sign in with an account that has access to the SharePoint site.

• In the Modify permissions panel, ensure Sites.Read.All and Files.Read.All are consented. If they're not, click Consent next to each and complete the consent dialog.

• In the URL bar, run:

https://graph.microsoft.com/v1.0/sites/{tenant}.sharepoint.com:/sites/{SiteName}

Replace `{tenant}` with your tenant name and `{SiteName}` with the path-segment name of your site (e.g., `BIData`).

• In the response, find the id field — it's a compound string like:

yourtenant.sharepoint.com,GUID1,GUID2

• Copy the entire compound string (including the commas).

4. Get the Workbook DriveItem ID

• In a browser, open the Excel file from the SharePoint site (you can right-click → Open in Browser, or click the file directly).

• Look at the browser URL bar. You'll see something like:

https://yourtenant.sharepoint.com/:x:/r/sites/SiteName/_layouts/15/Doc.aspx?sourcedoc=%7BABC12345-...-1234567890AB%7D&file=...

• Copy the GUID between sourcedoc=%7B and %7D (excluding the URL-encoded braces). That GUID is the workbook's DriveItem ID.

You now have:

• Site ID (the compound string with commas)

• DriveItem ID (the GUID from the URL)

Part 3. Configure the OAuth2 API in DashboardFox

1. Create the API Container

• Log in to DashboardFox as an Admin.

• Navigate to Settings → Integrations → Create App → APIs.

• Fill in the form:

  Field	Value
  API Name	Office 365 Excel (or a name that identifies this Microsoft connection)
  API Description	Optional. Useful for documenting which tenant/site this connects to.
  API Connection	Select OAuth2 from the dropdown.

• Click Continue.

You'll land on the Register New API screen with sections for API Connection List, API Authorization List, Api Refresh Token, and Api Endpoints.

2. Configure the API Connection (Form 1 — Authorization Request)

This is the URL where Microsoft prompts the user to sign in and consent.

• Under API Authorization List, click Add Details.

• At the top of the form, set:

  Field	Value
  API Request URL — name	request_url
  API Request URL — value	https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/authorize (substitute your Directory ID GUID for {TENANT_ID})

• Click Add New Parameter for each of the following rows:

  Name	Value
  redirect_uri	Your DashboardFox callback URL — exactly matching what's registered in Entra (e.g., https://yourcompany.dashboardfox.app/apiconnection.html)
  client_id	The Application (client) ID GUID from Entra
  response_type	code
  prompt	consent
  scope	https://graph.microsoft.com/Files.Read.All offline_access (note: literal space between the two scopes, not %20)

• Click Next Step.

3. Configure the API Authorization (Form 2 — Token Exchange)

This is where DashboardFox exchanges the auth code Microsoft returns for an access token and refresh token.

• Set the top of the form:

  Field	Value
  Method	POST
  API Authorization Request URL — name	request_url
  API Authorization Request URL — value	https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/token

• Click Add New Parameter for each row:

  Name	Value
  client_id	Your Application (client) ID
  client_secret	Your client secret value
  grant_type	authorization_code
  code	(leave blank — DashboardFox fills this at runtime)
  redirect_uri	Same URL as Form 1
  scope	https://graph.microsoft.com/Files.Read.All offline_access

• Click Save.

Important: The redirect_uri in Form 2 must be named exactly redirect_uri (not request_uri). Microsoft rejects the token exchange if it sees an unexpected parameter name.

4. Configure the Refresh Token (Form 3)

This is the URL DashboardFox calls to get a fresh access token when the current one expires.

• Under Api Refresh Token, click Add Details.

• Set:

  Field	Value
  Method	POST
  Request URL — name	request_url
  Request URL — value	https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/token (same as Form 2)

• Click Add New Parameter for each:

  Name	Value	Is Saved
  grant_type	refresh_token	unchecked
  client_id	Your Application (client) ID	unchecked
  client_secret	Your client secret value	unchecked
  refresh_token	(leave value blank — DashboardFox populates after first auth)	checked ✓
  scope	https://graph.microsoft.com/Files.Read.All offline_access	unchecked

• Click Save.

5. Authenticate

• Back on the Register New API screen, click the Authenticate button (next to Add Details under the Authorization List section).

• A popup opens to Microsoft sign-in.

• Sign in with the admin or service account you want this integration to act as.

• Review the consent screen — Microsoft will list the permissions requested (Files.Read.All and offline_access). Click Accept.

• The popup redirects back to your DashboardFox callback page. A green success message confirms the access token and refresh token were stored.

• Close the popup.

If something goes wrong, see the Troubleshooting section below.

Part 4. Add Endpoints (One per Worksheet)

For each worksheet you want to import, you'll create a separate endpoint. Each endpoint becomes its own table in DashboardFox.

1. Add the First Endpoint

• Under Api Endpoints, click Add Endpoint.

• Fill in the form:

  Field	Value
  Endpoint Name	The worksheet name, e.g., Sales_Orders
  Response By Name	value (Microsoft Graph returns the workbook data under a values array — DashboardFox normalizes this)
  Method	GET
  Request URL — name	request_url
  Request URL — value	The full Microsoft Graph workbook URL — see template below

The endpoint URL template:

https://graph.microsoft.com/v1.0/sites/{SITE_ID}/drive/items/{DRIVEITEM_ID}/workbook/worksheets/{WORKSHEET_NAME}/usedRange?$select=text

Substitute:

• {SITE_ID} — the compound site ID with commas you copied from Graph Explorer

• {DRIVEITEM_ID} — the GUID from the workbook's URL

• {WORKSHEET_NAME} — the exact (case-sensitive) name of the worksheet, e.g., Sales_Orders

Why ?$select=text instead of ?$select=values: Microsoft Graph returns two parallel arrays — values (raw cell values, where dates appear as Excel serial numbers like 45292) and text (formatted display strings like 2024-01-01). Using text produces strings that cast cleanly back to typed values in App Builder, provided you've followed the Excel prep rules in Part 2.

2. Add the Authorization Header

This is what tells DashboardFox to attach the OAuth Bearer token to each request.

• In the endpoint form, scroll to the parameter section.

• Click Add New Parameter with these settings:

  Field	Value
  Name	Authorization
  Param Type (dropdown)	Is Auth Header
  Value	access_token
  Authentication (dropdown)	Authentication
  Is Query Param	unchecked

• Click Save.

3. Fetch the Data

• On the endpoint row, open the Actions menu (the three-dot menu on the right) and click Connect.

• A status indicator shows the fetch in progress. Keep the browser tab open while it runs.

• For workbooks under a few thousand rows this completes in seconds. Larger workbooks can take several minutes — for example, 48,000 rows takes ~3–5 minutes depending on network and server load.

• A green success bar confirms completion.

4. Preview the Data

• From the Actions menu, click Preview.

• A dropdown appears with the table names DashboardFox created from the workbook response. For Excel workbooks via the Graph API, you'll typically see two related tables per endpoint:

  • api_{apiId}_{endpointId}_value_parent — the wrapper/metadata table (one row)

  • api_{apiId}_{endpointId}_value_listdatatable — the actual data rows (this is the table you'll use in App Builder)

• Select the _listdatatable table and click Load.

• Verify the data looks correct: row count, column count, header row, data types.

• If something is wrong (wrong worksheet name, missing data, etc.), edit the endpoint, then use Purge & Connect from the Actions menu to rebuild the tables from scratch.

5. Note the Table Names for App Builder

The table name pattern is:

api_{apiId}_{endpointId}_value_listdatatable

You'll reference these names in App Builder when you build your app's semantic layer.

6. Repeat for Each Worksheet

For every worksheet in your workbook, repeat steps 1–5 with a new endpoint, changing only the Endpoint Name and the {WORKSHEET_NAME} segment of the URL.

Part 5. Schedule Automated Refreshes (Optional)

For ongoing automatic updates from the workbook:

• From the endpoint's Actions menu, click Schedule.

• Configure the cadence — hourly, daily, weekly, etc.

• Save.

DashboardFox will use the stored refresh token to obtain a new access token automatically when needed, then re-fetch the workbook data on schedule. No manual re-authentication required as long as the refresh token remains valid (refresh tokens for Microsoft Entra typically last 90 days, but get extended each time they're used).

Each fetch replaces the previous data — the table is purged and repopulated, so no stale rows accumulate.

Building Reports on the Imported Data

Once your endpoints have loaded successfully:

1. Register the app for users in Settings → Security → Apps — find the auto-created app, click Edit, and assign yourself the App Builder role.

2. Go to App Builder and find your new app.

3. Drag the _listdatatable tables from your endpoints into the semantic layer.

4. Apply data type casts on date/numeric columns (see Excel Prep Best Practices below).

5. Build reports following the lessons in our Academy.

Excel Prep Best Practices — App Builder Cast Formulas

When your Excel cells are formatted per the guidance in Part 2 Step 2, columns import as text strings that cast cleanly. Use these formula patterns in App Builder:

If you chose to keep Excel's currency or percentage formatting (with $, ,, or % symbols in the data), use these instead:

Limitations

• Personal Microsoft accounts are not supported. The Microsoft Graph Workbook API does not work on consumer OneDrive. Workbook must be in OneDrive for Business or SharePoint Online.

• Initial fetch of large workbooks is slow. Microsoft Graph returns the entire workbook range in a single response (no pagination on usedRange). For workbooks over ~10,000 rows, the initial fetch may take several minutes. Subsequent scheduled refreshes follow the same pattern but happen unattended.

• Worksheet name changes break the endpoint. If you rename a worksheet in Excel, you must update the endpoint URL to match. Same for the workbook filename if it's referenced by path rather than DriveItem ID.

• Workbook moves can break the endpoint. If you move the workbook to a different SharePoint site or library, the DriveItem ID changes — you'll need to look up the new ID and update the endpoint URL.

• Client secret expiration. Secrets in Entra ID have a max lifetime of 24 months. Track the expiration date and rotate before it lapses, or the integration will stop fetching.

• Refresh token expiration. Refresh tokens may expire after 90 days of inactivity. Active integrations have their tokens auto-extended, but a long pause could require re-authentication.

Troubleshooting

For unresolved issues, contact team@dashboardfox.com.

Related Articles

• Microsoft Excel (Personal OneDrive) — file-import workflow for personal Microsoft accounts

• Google Sheets (Public API) — the simpler API-key-based pattern for Google

• Google Sheets (OAuth2) — the equivalent OAuth2 flow for Google Sheets, for private / Workspace-managed spreadsheets

• Excel/CSV Import Lesson — Excel data preparation best practices

• App Builder Lessons — building reports on imported data