Microsoft Excel Grid Integration

The Microsoft Excel Grid integration allows teams to view, search, inline-edit, insert, and delete spreadsheet rows from Microsoft Excel workbooks stored in OneDrive or SharePoint directly inside AppColab Grid via Microsoft Graph.

What’s Deployed

One-Time Manual Setup

1. Register App in Microsoft Azure Portal

1. Sign in to the Microsoft Entra / Azure Portal and navigate to App registrations.
2. Click New registration:
   • Name: AppColab Grid Excel Integration
   • Supported account types: Select “Accounts in any organizational directory (Any Microsoft Entra ID tenant - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox)”.

     [!IMPORTANT] Selecting Multitenant + Personal accounts allows both corporate Office 365 and personal @outlook.com / @hotmail.com accounts to connect.

   • Redirect URI: Select Web (leave URL blank for now; you will fill this from Salesforce in Step 2).
   • Click Register.
3. Under Overview, copy your Application (client) ID.
4. In the left navigation menu, go to Certificates & secrets:
   • Click New client secret → add a description (e.g. Salesforce Grid).
   • Click Add and immediately copy the Value (this is shown only once).
5. In the left navigation menu, go to API permissions:
   • Click Add a permission → select Microsoft Graph → Delegated permissions.
   • Add the following scopes:
     • Files.ReadWrite (read and write files in OneDrive)
     • offline_access (maintain permanent refresh token)
     • openid, profile, email
   • Click Add permissions.

2. Configure Salesforce Auth. Provider

1. In Salesforce Setup, enter Auth. Providers in the Quick Find box and click New:
   • Provider Type: OpenIdConnect
   • Name: Excel_Microsoft_OAuth
   • URL Suffix: Excel_Microsoft_OAuth
   • Consumer Key: Paste your Azure Application (client) ID from Step 1
   • Consumer Secret: Paste your Azure Client Secret Value from Step 1
   • Authorize Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize

     [!IMPORTANT] Use the /common/ endpoint in the URL, NOT /organizations/. The /organizations/ endpoint strictly blocks personal Microsoft accounts.

   • Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
   • Default Scopes: offline_access openid profile email https://graph.microsoft.com/Files.ReadWrite
2. Click Save.
3. Scroll down and copy the generated Callback URL (e.g. https://yourdomain.my.salesforce.com/services/authcallback/Excel_Microsoft_OAuth).
4. Return to your Azure App Registration:
   • Go to Authentication tab → under Platform configurations (Web), click Add URI.
   • Paste your Salesforce Callback URL and click Save.

3. Configure Credentials in Salesforce Setup

3.1 Create the External Credential

1. In Salesforce Setup, enter Named Credentials in Quick Find.
2. Click the External Credentials tab → click New:
   • Label: Excel
   • Name: Excel
   • Authentication Protocol: OAuth 2.0
   • Authentication Provider: Select Excel_Microsoft_OAuth
   • Scope: offline_access openid profile email https://graph.microsoft.com/Files.ReadWrite
   • Click Save.
3. In the Principals section, click New:
   • Parameter Name: ApiKey
   • Sequence: 1
   • Identity Type: Named Principal
   • Scope: offline_access openid profile email https://graph.microsoft.com/Files.ReadWrite
   • Click Save.
4. On the ApiKey row, click the dropdown action arrow ▼ → click Authenticate:
   • Sign in with your Microsoft account in the popup.
   • Click Accept to grant AppColab Grid permission to access files.
   • Upon returning to Salesforce, verify the Principal status displays Configured with a green checkmark.

3.2 Create the Named Credential

1. Click the Named Credentials tab (next to External Credentials) → click New:
   • Label: Excel
   • Name: Excel
   • URL: https://graph.microsoft.com
   • External Credential: Select Excel
   • Generate Authorization Header: Checked
   • Allow Formulas in HTTP Header: Checked
2. Click Save.

4. Assign Permission Set Access

1. In Salesforce Setup, enter Permission Sets in Quick Find.
2. Select Integration Admin (or Excel_Integration / your custom admin permission set).
3. Click External Credential Principal Access → click Edit.
4. Add Excel - ApiKey to Enabled External Credential Principals.
5. Click Save and verify the permission set is assigned to your Salesforce users.

Creating an Excel Grid Definition

1. In Salesforce, open AppColab Grid app → Grid Definitions tab → click New Grid Definition.
2. Fill in:
   • Grid Settings Name: Excel_Hardware_Provisioning_Grid
   • Grid Label: Excel Hardware Provisioning
   • Is Active?: Checked
   • Data Provider Class: ExcelGridDataProvider
   • Data Provider Configuration:

     {
       "filePath": "/me/drive/root:/IT/Hardware_Provisioning.xlsx",
       "sheetName": "Sheet1",
       "headerRow": 1
     }
     

     Using Drive Item ID instead of Path: If you know the OneDrive file item ID, you can configure:

     {
       "workbookId": "01BY2767N64FFWYXBRV6C2K3GDBUK62EQX",
       "sheetName": "Sheet1"
     }
     

     Contextual Child Grid Filtering: To embed on a Contact page and filter rows where an Excel column matches the Contact’s Email:

     {
       "filePath": "/me/drive/root:/IT/Hardware_Provisioning.xlsx",
       "sheetName": "Sheet1",
       "parentMatchColumn": "UserEmail",
       "parentMatchField": "Email"
     }
     

3. Click Save & Next → proceed to Preview.
4. All rows from the Excel worksheet render live with editable cells and full search!

Supported Grid Features

Troubleshooting & Common Gotchas

• “You can’t sign in here with a personal account. Use your work or school account instead.”:
  • Cause: The Salesforce Auth. Provider was configured using the /organizations/ tenant endpoint (e.g. https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize), which explicitly disallows personal Microsoft accounts.
  • Fix: In Salesforce Setup > Auth. Providers > Excel_Microsoft_OAuth, change both the Authorize Endpoint URL and Token Endpoint URL to use /common/ instead of /organizations/. Also ensure the Azure App registration Supported account types is set to multitenant and personal accounts.
• ItemNotFound / The resource could not be found:
  • Cause: The OneDrive file path in your Data Provider Configuration JSON is invalid or formatted incorrectly.
  • Fix: Ensure the path starts with /me/drive/root: followed by the path within OneDrive (e.g. /me/drive/root:/Folder/MyWorkbook.xlsx).
• Grid preview returns 0 rows (Contextual parent matching):
  • Cause: If "parentMatchColumn" is specified in the configuration, the provider filters rows where that column equals the parent record field. When viewing the grid in standalone preview (where no parent record exists), 0 records will be returned.
  • Fix: Embed the grid on a Contact or Account record page in Lightning App Builder to view contextual rows.
• 403 Forbidden / Access Denied:
  • Cause: The Azure App Registration lacks the Files.ReadWrite delegated permission, or the user who authenticated does not have access to the file.
  • Fix: Check API permissions in Azure Portal and ensure admin consent or user permission is granted for Files.ReadWrite.