Microsoft Excel API

Connect Microsoft Excel Online (OneDrive/SharePoint) as live data sources. OAuth2 integration with Microsoft Graph API for real-time Excel workbook access.

---

Base URL

https://app.datapublisher.io/api/microsoft

---

Authentication

All endpoints require JWT authentication via Authorization: Bearer header.

---

Overview

The Microsoft Excel API allows you to:

Connect Excel files from OneDrive/SharePoint via OAuth2

Read workbook data in real-time via Microsoft Graph

List Excel files from OneDrive

Extract worksheet data with headers and rows

Auto-refresh expired tokens

Import data for document generation

Supported:

Excel Online (OneDrive for Business)

SharePoint Online

Personal OneDrive

Required Microsoft Scopes:

Files.Read.All - Read Excel files

offline_access - Refresh tokens

User.Read - Get user info

---

OAuth2 Connection Flow

Step 1: Start Authorization

Initiate Microsoft OAuth2 flow to connect Excel.

Endpoint: GET /auth/start

Headers:

Authorization: Bearer

Response (200 OK):

{ "success": true, "authUrl": "https://login.microsoftonline.com/common/oauth2/v2.0/authorize?client_id=...&redirect_uri=...&response_type=code&scope=Files.Read.All%20offline_access%20User.Read&state=abc123", "message": "Redirect user to this URL to authorize Microsoft access"

}

Usage:

Call API to get authUrl

Open authUrl in browser dialog

User logs into Microsoft

User grants Excel access

Microsoft redirects to callback

---

Step 2: OAuth2 Callback (Automatic)

Microsoft redirects here after authorization. Handled automatically by DataPublisher.

Endpoint: GET /auth/callback

Process:

Receives authorization code

Validates state (CSRF protection)

Exchanges code for access + refresh tokens

Gets user's Microsoft account email

Encrypts and stores tokens

Shows success page that auto-closes

---

Workbook Operations

List Excel Workbooks

Get all Excel files from connected OneDrive/SharePoint.

Endpoint: GET /workbooks

Headers:

Authorization: Bearer

Query Parameters:

search (optional): Filter by file name

Response (200 OK):

{ "success": true, "workbooks": [ { "id": "01AZJL5PN6Y2GOVW7725BZO354PWSELRRZ", "name": "Sales Report 2026.xlsx", "webUrl": "https://onedrive.live.com/...", "createdDateTime": "2026-01-15T10:00:00Z", "lastModifiedDateTime": "2026-02-12T09:30:00Z", "size": 245678, "createdBy": "user@company.com" }, { "id": "01AZJL5PNUJRXZM33ZVBCJYP3BQYGZK5HQ", "name": "Customer Database.xlsx", "webUrl": "https://companyname-my.sharepoint.com/...", "createdDateTime": "2026-02-01T08:00:00Z", "lastModifiedDateTime": "2026-02-12T10:15:00Z", "size": 1245678, "createdBy": "team@company.com" } ], "count": 2

}

---

Get Workbook Metadata

Retrieve detailed information about a specific workbook.

Endpoint: GET /workbooks/:workbookId

Headers:

Authorization: Bearer

Path Parameters:

workbookId: OneDrive file ID

Response (200 OK):

{ "success": true, "workbook": { "id": "01AZJL5PN6Y2GOVW7725BZO354PWSELRRZ", "name": "Sales Report 2026.xlsx", "webUrl": "https://onedrive.live.com/...", "worksheets": [ { "id": "Sheet1", "name": "Sales Data", "position": 0, "visibility": "visible" }, { "id": "Sheet2", "name": "Summary", "position": 1, "visibility": "visible" } ], "size": 245678, "createdDateTime": "2026-01-15T10:00:00Z", "lastModifiedDateTime": "2026-02-12T09:30:00Z" }

}

---

Get Worksheet Data

Retrieve data from a specific worksheet.

Endpoint: GET /workbooks/:workbookId/worksheets/:worksheetName/data

Headers:

Authorization: Bearer

Path Parameters:

workbookId: OneDrive file ID

worksheetName: Worksheet name (e.g., "Sheet1", "Sales Data")

Query Parameters:

range (optional): A1 notation (e.g., "A1:E100")

limit (optional): Max rows (default: 1000)

offset (optional): Skip rows (default: 0)

Response (200 OK):

{ "success": true, "workbookId": "01AZJL5PN6Y2GOVW7725BZO354PWSELRRZ", "workbookName": "Sales Report 2026.xlsx", "worksheetName": "Sales Data", "range": "A1:L500", "headers": [ "OrderID", "CustomerName", "ProductName", "Quantity", "UnitPrice", "TotalAmount", "OrderDate", "ShipDate", "Status", "Region", "SalesRep", "Notes" ], "data": [ { "OrderID": "10001", "CustomerName": "Acme Corp", "ProductName": "Widget Pro", "Quantity": "50", "UnitPrice": "99.99", "TotalAmount": "4999.50", "OrderDate": "2026-01-15", "ShipDate": "2026-01-20", "Status": "Shipped", "Region": "West", "SalesRep": "John Doe", "Notes": "Express delivery" } ], "rowCount": 500, "columnCount": 12

}

---

Import Worksheet to CSV

Import Excel worksheet data as CSV file in DataPublisher.

Endpoint: POST /workbooks/:workbookId/worksheets/:worksheetName/import

Headers:

Authorization: Bearer

Content-Type: application/json

Request Body:

{
  "fileName": "sales-data.csv",

  "description": "Imported from Excel Online",

  "tags": ["sales", "excel"],

  "range": "A1:L500"

}

Response (200 OK):

{ "success": true, "fileId": "csv-file-uuid", "fileName": "sales-data.csv", "rowCount": 499, "columnCount": 12, "message": "Excel worksheet imported successfully"

}

---

Connection Management

Get Connection Status

Check Microsoft Excel connection status.

Endpoint: GET /connection/status

Headers:

Authorization: Bearer

Response (200 OK):

{ "success": true, "connected": true, "email": "user@company.com", "tokenExpiresAt": "2026-02-13T10:00:00Z", "connectedAt": "2026-01-15T09:00:00Z"

}

---

Disconnect Microsoft Excel

Remove Excel connection and delete tokens.

Endpoint: DELETE /connection

Headers:

Authorization: Bearer

Response (200 OK):

{ "success": true, "message": "Microsoft Excel disconnected successfully"

}

---

Extracting Workbook ID from URL

OneDrive URL format:

https://onedrive.live.com/?id=WORKBOOK_ID

SharePoint URL format:

https://companyname-my.sharepoint.com/personal/user_company_com/_layouts/15/Doc.aspx?sourcedoc={WORKBOOK_ID}

API Helper:

Use the list workbooks endpoint - it returns file IDs for you.

---

Token Management

Automatic Token Refresh

Access tokens expire after 1 hour. Refresh tokens valid for ~90 days.

Auto-Refresh Process:

Before Graph API call, check token expiry

If expired, use refresh_token to get new access_token

Update database

Continue with original request

No manual intervention needed!

---

Supported File Formats

.xlsx (Excel 2007+)

.xlsm (Excel with macros)

.xlsb (Excel binary)

Not supported:

.xls (Excel 97-2003) - Must be converted to .xlsx

Local files - Must be in OneDrive or SharePoint

---

Rate Limiting

Microsoft Graph API: Per Microsoft's throttling policies

DataPublisher API: 1000 requests per 15 minutes

---

Common Error Codes

| Status Code | Description |

|-------------|-------------|

| 400 | Invalid workbook ID or worksheet name |

| 401 | Not connected or token expired |

| 403 | Insufficient permissions or file access denied |

| 404 | Workbook or worksheet not found |

| 429 | Microsoft Graph throttling |

| 500 | Microsoft API error |

---

Usage Examples

JavaScript

// Connect Excel
async function connectExcel() {

  const response = await fetch('https://app.datapublisher.io/api/microsoft/auth/start', {

    headers: { 'Authorization': Bearer ${jwtToken} }

  });

  const { authUrl } = await response.json();

  window.open(authUrl, '_blank');

}


// List workbooks

async function listWorkbooks() {

  const response = await fetch('https://app.datapublisher.io/api/microsoft/workbooks', {

    headers: { 'Authorization': Bearer ${jwtToken} }

  });

  const { workbooks } = await response.json();

  return workbooks;

}


// Get worksheet data

async function getWorksheetData(workbookId, worksheetName) {

  const url = https://app.datapublisher.io/api/microsoft/workbooks/${workbookId}/worksheets/${encodeURIComponent(worksheetName)}/data?limit=100;

  const response = await fetch(url, {

    headers: { 'Authorization': Bearer ${jwtToken} }

  });

  return await response.json();

}


// Import worksheet

async function importWorksheet(workbookId, worksheetName) {

  const response = await fetch(

    https://app.datapublisher.io/api/microsoft/workbooks/${workbookId}/worksheets/${encodeURIComponent(worksheetName)}/import,
    {

      method: 'POST',

      headers: {

        'Authorization': Bearer ${jwtToken},

        'Content-Type': 'application/json'

      },

      body: JSON.stringify({

        fileName: ${worksheetName}.csv,

        description: 'Imported from Excel'

      })

    }

  );

  return await response.json();

}

---

Best Practices

File Location: Store Excel files in OneDrive/SharePoint

Permissions: Ensure user has read access to workbooks

Worksheet Names: URL-encode names with spaces

Large Workbooks: Use pagination for sheets with >1000 rows

Data Types: All data returned as strings

Formulas: Only values returned, not formulas

Token Refresh: Auto-refreshes - no action needed

Caching: Cache frequently accessed data

---

Microsoft Excel API

Base URL

Authentication

Overview

OAuth2 Connection Flow

Step 1: Start Authorization

Step 2: OAuth2 Callback (Automatic)

Workbook Operations

List Excel Workbooks

Get Workbook Metadata

Get Worksheet Data

Import Worksheet to CSV

Connection Management

Get Connection Status

Disconnect Microsoft Excel

Extracting Workbook ID from URL

Token Management

Automatic Token Refresh

Supported File Formats

Rate Limiting

Common Error Codes

Usage Examples

JavaScript

Best Practices

Related Documentation