Microsoft Excel Grid Integration

The Microsoft Excel Grid integration allows users to view, search, add, inline-edit, delete, and download rows from any Excel workbook hosted in Microsoft OneDrive or SharePoint directly inside the AppColab Grid UI—in real time via the Microsoft Graph API, without duplicating records in Salesforce storage.

What’s Deployed

One-Time Setup Guide

Connecting Microsoft Excel requires authorizing Salesforce to communicate with Microsoft Graph API.

Step 1: Configure Microsoft Entra ID (Azure AD)

1. Sign in to the Microsoft Entra Admin Center or Azure Portal.
2. Navigate to Identity → Applications → App registrations → click New registration:
   • Name: AppColab Salesforce Integration
   • Supported account types: Select Accounts in any organizational directory (Multitenant) and personal Microsoft accounts (or your specific tenant).
   • Redirect URI: Select Web and paste your Salesforce Callback URL (found in Salesforce Setup → Auth. Providers → Microsoft_OAuth → Callback URL).
   • Click Register.
3. Under API Permissions → click Add a permission:
   • Select Microsoft Graph → Delegated permissions.
   • Add:
     • Files.ReadWrite (read and write user OneDrive/SharePoint files)
     • offline_access (allow maintaining access to data via refresh token)
   • Click Add permissions.
4. Under Certificates & secrets → click New client secret:
   • Description: Salesforce Integration Secret
   • Copy the secret Value immediately.

Step 2: Configure Salesforce Auth Provider & Named Credential

2.1 Verify Auth. Provider (Microsoft_OAuth)

1. In Salesforce Setup, enter Auth. Providers in the Quick Find box.
2. Ensure Microsoft_OAuth is configured with:
   • Authorize Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
   • Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
   • Default Scopes: Files.ReadWrite offline_access
   • Consumer Key: Paste your Azure Application (client) ID.
   • Consumer Secret: Paste your Azure Client Secret Value.

2.2 Verify Named Credential (Excel)

1. In Salesforce Setup, navigate to Named Credentials.
2. Under the Named Credentials tab, verify Excel:
   • URL: https://graph.microsoft.com
   • Identity Type: Named Principal
   • Authentication Protocol: OAuth 2.0
   • Authentication Provider: Microsoft_OAuth
   • Scope: Files.ReadWrite offline_access
   • Generate Authorization Header: Checked
3. Save and authenticate the principal with your Microsoft account (Status should display Authorized).

Step 3: Assign Permission Set Access

1. Setup → Permission Sets → click Integration Admin (or Appcolab_Grid_Admin).
2. Ensure access to the Excel Named Credential and Apex classes (ExcelClient, ExcelGridDataProvider) is granted.
3. Click Manage Assignments and assign the permission set to active grid users.

Creating an Excel Grid Definition

1. In Salesforce, open the AppColab Grid app → navigate to the Grid Definitions tab → click New Grid Definition.
2. Fill in:
   • Grid Identifier: IT_Hardware_Provisioning_Grid
   • Grid Label: IT Hardware Provisioning
   • Is Active?: Checked
   • Data Provider Class: ExcelGridDataProvider
   • Pagination Type: Server-Side
   • Page Size: 10
   • Data Provider Configuration:

     {
       "filePath": "/IT_Hardware_Provisioning.xlsx",
       "sheetName": "Hardware",
       "headerRow": 1,
       "parentMatchColumn": "Employee Email",
       "parentMatchField": "Email",
       "autoPopulateParent": true
     }
     

3. Click Save.
4. Open the Grid Browser or place the grid on a record page to view live data!

Provider Configuration Options

Scope Excel Rows to a Salesforce Record

To display only rows related to the current Salesforce record:

1. Add a column in your Excel sheet matching parentMatchColumn (e.g. Employee Email).
2. Populate that column with the corresponding parent record value (e.g. employee email address).
3. In Lightning App Builder:
   • Drag Appcolab Grid onto the parent record page (e.g. Contact Record Page).
   • Set Grid Definition Name to IT_Hardware_Provisioning_Grid.
   • Save and activate.

When opened on an employee’s Contact record, AppColab Grid queries Microsoft Graph API and filters rows where Employee Email matches Contact.Email.

Supported Grid Features

Testing via Anonymous Apex

You can test the Microsoft Excel integration directly in the Developer Console (Debug → Open Execute Anonymous Window):

ExcelGridDataProvider provider = new ExcelGridDataProvider();
appcg.GridDataProviderTypesV1.ProviderContext ctx = new appcg.GridDataProviderTypesV1.ProviderContext();

// 1. Identify the Grid Definition
ctx.gridDefinitionId = [
    SELECT Id
    FROM appcg__Grid_Definition__c
    WHERE appcg__Grid_Identifier__c = 'IT_Hardware_Provisioning_Grid'
    LIMIT 1
].Id;

// Optional: Test Parent-Aware Filtering with a Contact Record ID
// ctx.parentId = '003fn000006zzphAAA';

// 2. Query Data
appcg.GridDataProviderTypesV1.QueryRequest req = new appcg.GridDataProviderTypesV1.QueryRequest();
req.context = ctx;
req.pageSize = 10;

appcg.GridDataProviderTypesV1.PageResult page = provider.getData(req);
System.debug('Total Rows: ' + page.totalCount);
for (Object r : page.records) {
    System.debug('Row: ' + JSON.serialize(r));
}

Troubleshooting

• HTTP 404 Not Found:
  • In Excel Online, typing .xlsx in the title bar causes Excel to name the file filename.xlsx.xlsx. Rename the file in Excel Online to omit the extension.
  • Or use the explicit workbookId (Drive Item ID) in your Data Provider Configuration.
• 401 / 403 AuthenticationException:
  • The Microsoft OAuth token expired or lacks Files.ReadWrite permissions. Re-authenticate the principal under Setup → Named Credentials → External Credentials → Microsoft_OAuth.
• Row shifting and formula warnings:
  • For sheets with formulas, avoid manual row insertions in Excel Online while users have pending unsaved edits in Salesforce.