Skip to main content

Microsoft Excel datasource

Microsoft Excel datasources allow you to fetch and display data from Excel workbooks stored in SharePoint or personal OneDrive accounts directly in your widgets and templates. The system automatically processes spreadsheet data and makes it available for dynamic content display with real-time updates.

Prerequisites​

Before creating a Microsoft Excel datasource, ensure you have:

  • A Microsoft account with access to Excel workbooks
  • The target Excel workbook with proper sharing permissions (for SharePoint)
  • Understanding of your spreadsheet structure and data layout
  • Administrative access to configure Microsoft credentials in Wallboard
  • Network connectivity to Microsoft's APIs
  • For SharePoint workbooks: appropriate SharePoint site access permissions

Supported Excel Features​

The Microsoft Excel datasource supports:

Data Types:

  • Text, numbers, dates, and formula results
  • Multiple worksheets within a single workbook
  • Dynamic ranges that expand with new data
  • Formatted cells and calculated values
  • Chart data extraction (when enabled)

Limitations:

  • Maximum performance optimized for workbooks under 10,000 cells per sheet
  • Complex formulas show calculated results only
  • Conditional formatting is not preserved in data extraction
  • VBA macros and embedded objects are not processed
  • Chart images are not imported (chart data only)

Data Structure Examples:

Employee Roster Format:

| Employee ID | Name       | Department | Salary   | Hire Date  |
|-------------|------------|------------|----------|------------|
| EMP001      | John Smith | Sales      | $55,000  | 2023-03-15 |
| EMP002      | Jane Doe   | Marketing  | $60,000  | 2023-04-01 |

Inventory Tracking Format:

| Product Code | Description | Quantity | Unit Price | Last Updated |
|--------------|-------------|----------|------------|-------------|
| PRD001       | Widget A    | 150      | $25.99     | 2024-01-15  |
| PRD002       | Widget B    | 89       | $32.50     | 2024-01-14  |

Microsoft Excel Credentials Setup​

To create a Microsoft Excel datasource you need to create and authenticate your Microsoft credential in the Wallboard system.

Creating Microsoft Credentials​

1. Navigate to Credentials
Go to Settings > Cloud Integration > Microsoft credentials

2. Add New Credential
Click Add new and select the Workbook (Excel) scope from the dropdown, where you can choose between:

  • SharePoint: For Excel workbooks stored in SharePoint sites (organizational accounts)
  • Personal: For Excel workbooks in personal OneDrive accounts

3. Team Assignment (Optional)
You can assign the credential to specific teams if your organization requires access control

4. Authentication Process
After selecting the credential type:

  • Complete the Microsoft OAuth authentication flow
  • Grant the necessary permissions for Excel workbook access
  • Verify the authentication was successful
Credential Type Limitation

After you create a credential, you cannot modify the type (SharePoint vs Personal), only the name of the credential. If you need to change the credential type, you must create a new credential with the desired type and update your datasources accordingly.

Microsoft Permissions Required

The Wallboard system requires read access to your Excel workbooks. During authentication, you'll be prompted to grant:

  • View your Excel files and workbooks
  • Read spreadsheet data and structure
  • Access to SharePoint sites (for SharePoint credentials)

Once authenticated, you can create external datasources using the acquired Microsoft Excel access.

Create Datasource​

Go to Datasources > Click Add new in the External datasource tab > Choose the Microsoft Excel type.

Basic Configuration​

1. Credential Selection
Select the Microsoft credential you created previously from the dropdown.

2. Source Selection
The selection process depends on your credential type:

  • Personal Credential: Choose the workbook directly, then select the specific worksheet
  • SharePoint Credential: First select the SharePoint site, then choose the workbook, and finally select the worksheet

3. Update Schedule
Decide how your datasource updates from the original Excel workbook:

  • Select a Refresh Frequency for regular intervals (every minute, hour, day, etc.)
  • Specify a Cron Expression for custom scheduling
Example Cron Expressions
  • 0 */30 * * * * - Every 30 minutes
  • 0 0 */4 * * * - Every 4 hours
  • 0 0 9 * * 1-5 - Weekdays at 9:00 AM
Excel Refreshing

For more information about external datasource refresh options, see the External Datasource Refreshing documentation.

Advanced Configuration Options​

4. Worksheet Settings
Configure how the system interprets your Excel data:

  • First row is header: Enable this if your first row contains column names that should be used as field identifiers in data binding
  • Range: Specify a custom cell range (e.g., A1:D50) to limit data extraction to specific areas of the worksheet
  • Fetch Charts: Extract chart data alongside cell values, making chart data available for widgets
  • Active: Enable/disable the datasource without deleting the configuration
  • Ignore Error counter: Continue operation even if temporary errors occur during data fetching from Excel

5. Editor Settings
Configure how the system processes Excel content:

  • Cache external resources: Store external content (images, files) referenced in cells locally for faster access
  • Remove broken external resource references: Automatically clean up invalid links found in Excel data
  • Rotate cache on every update: Clear and rebuild cache with each data refresh to ensure current content
  • Exchange internal resource references: Update internal file paths when content is moved or reorganized
  • Request settings: Configure timeout, retry options, and other request parameters

6. Array Processing
Enable Randomize arrays to shuffle the order of worksheet rows each time the datasource updates. This is useful for:

  • Rotating through different data entries
  • Displaying varied information on each refresh
  • Creating dynamic content presentation from static Excel data

Authentication and Access Settings​

The system uses the Microsoft credential you configured earlier to access Excel workbook data. Ensure:

  1. The credential has not expired or been revoked
  2. The target workbook is accessible by the authenticated Microsoft account
  3. SharePoint permissions allow the service account to read the data (for SharePoint workbooks)
  4. The workbook location has not changed if using direct links

7. Save Configuration
Once all settings are configured according to your Excel workbook requirements, click Save to create the datasource.

Workbook Access Verification

After saving, the system will attempt to fetch data from your Excel workbook. Check the datasource status to confirm successful connection and data retrieval.

Using Excel Data in Widgets​

After creating the Microsoft Excel datasource, you can bind the Excel data to widgets:

Header Row Access (when "First row is header" is enabled):

  • data.EmployeeID, data.Name, data.Department
  • data[0].Name, data[1].Department for specific rows

Index-Based Access (when headers are not used):

  • data[0][0] for cell A1 content
  • data[1][2] for cell C2 content
  • data.length for total number of rows

Chart Data Access (when "Fetch Charts" is enabled):

  • charts[0].title for chart titles
  • charts[0].data for chart dataset values
  • charts[0].labels for chart axis labels

Dynamic Data Binding Examples:

  • Employee information: data[0].Name - data[0].Department
  • Product inventory: data.ProductCode: Stock data.Quantity
  • Sales metrics: data.Region had data.Sales this quarter

Common Use Cases​

Financial Reporting: Budget tracking, expense reports, quarterly summaries, KPI dashboards
Inventory Management: Stock levels, product catalogs, supplier information, purchase orders
Human Resources: Employee directories, organizational charts, attendance tracking, performance metrics
Project Management: Task lists, milestone tracking, resource allocation, timeline data
Sales Analytics: Lead tracking, sales performance, customer data, territory management
Operational Dashboards: Real-time metrics, status reports, equipment monitoring, process tracking

Regional Locale Settings

Some data formatting relies on regional locale settings, so please ensure SharePoint's regional locale is correctly configured.
This setting can only be changed by SharePoint Owner! Please contact the appropriate personnel.
An incorrect locale may lead to formatting inconsistencies in the generated data.

To avoid this issue, consider using raw text without formatting in your Excel sheets instead of specialized number formats, as raw text remains unaffected by regional settings.

Data Structure Best Practices

For optimal display, structure your Excel workbooks with consistent data types in each column and clear headers. This ensures reliable data binding and consistent widget presentation. Keep formulas simple and avoid complex nested calculations for best performance.

Watch YouTube tutorial video​