Excel

Microsoft Excel—the ubiquitous spreadsheet product—is available on the Office365 cloud. Skuid can access Excel workbooks, spreadsheets, tables, and charts with a pre-configured Skuid data source type. While you do not need to set up a OneDrive data source in Skuid to use the Excel data source type, Excel workbooks must be stored in OneDrive to be accessed by Skuid.

To use Excel with Skuid, you’ll need to complete the following three steps:

  1. Create an app in the Microsoft API.
  2. Create the Excel authentication provider.
  3. Create the Excel data source.

Configuration

In Microsoft API

Create the app:

  1. Navigate to apps.dev.microsoft.com.

  2. Click Sign in (at top) or Register your app.

  3. Sign in using your Microsoft account and password.

  4. If offered, do not Select a platform to get started. Instead, click Skip Quickstart.

    Note

    This screen will not appear if you have previously created an app at apps.dev.microsoft.com.

  5. Under My Applications (which can be found in Converged Applications if you have previously set up an Azure AD application), click Add an app.

  6. Enter a Name for your application registration.

  7. Click Create Application.

    Note

    It may take a while for the app to show up in the My Applications list. Don’t despair; it’s coming!

After creating the app, you should be taken to the App Registration’s settings page. If not, click on the app’s name in the list to view the App registration page.

Now, set the callback URL:

  1. Under the Platforms heading, click Add Platform.

  2. Click Web.

  3. In the Redirect URIs field, enter the redirect URI for your Skuid instance.

    When using Skuid on Salesforce, the callback URL will depend on several things:

    • Whether or not the Remove Instance Names from URLs critical update is activated
      • If the above critical update is not activated, then the Salesforce org’s instance
    • If set, the Salesforce org’s My Domain
    • Whether the org is a developer edition or sandbox org

    Each of these variables will change parts of the callback URL.

    There are also different URLs needed based on whether the Skuid page is deployed using the Redirect or skuid:page Visualforce component override method. Because of this, it’s best to enter two callback URLs for Skuid on Salesforce orgs.

    To ensure the accuracy of your callback URLs, fill out the form below to generate the appropriate Salesforce callback URLs for your org:

    Note

    Unsure what your instance and My Domain are? The URL of the Skuid Pages list within the Salesforce Classic layout is a quick way to find the information needed for callback URLs. For example:

    https://customMyDomain-dev-ed--skuid.na30.visual.force.com/apex/PageList

    From the above URL you can deduce the following:

    • customMyDomain is the My Domain
    • -dev-ed indicates this is likely a developer’s edition org
    • na30 is the instance.

    How these URLs are formed [[]]

    If the Remove Instance Names critical update is activated, and the org’s My Domain is configured, the callback URLs should be:

    • https://<myDomain>--skuid.visualforce.com/apex/skuid__oauthcallback
    • https://<myDomain>--c.visualforce.com/apex/oauthcallback

    If the org’s My Domain is configured, their format should be:

    • https://<myDomain>--skuid.<Instance>.visual.force.com/apex/skuid__oauthcallback
    • https://<myDomain>--c.<Instance>. visual.force.com/apex/oauthcallback

    If the org’s My Domain is not configured, then the URLs will be:

    • https://skuid.<Instance>.visual.force.com/apex/skuid__oauthcallback
    • https://c.<Instance>.visual.force.com/apex/oauthcallback

    If the org is a developer’s edition or sandbox, the skuid/c section of the URL will be prepended with -dev-ed– or –sandboxName– respectively.

Finally, in the Microsoft Graph Permissions section, add the permissions necessary for Skuid to read from and write to Excel files:

  1. Beside Delegated Permissions, click Add.
  2. Check the following:
    • Files.Read
    • Files.ReadWrite
    • User.Read
  3. Click Ok.
  4. Click Save.

Credentials

With these settings saved, retrieve the OAuth credentials needed for a Skuid authentication provider. The app registration page provides two important credentials needed for that process:

  • The application Id (aka the client ID):
    • Find this displayed in the Properties section, under the application name.
  • The password or “client secret”:

Obtain this by clicking Generate New Password. This password will only be displayed once, within a dialog box; be sure to copy and store it in a secure location.

In Skuid

Create an authentication provider

Once you’ve got the app set up, use the credentials you copied down in the previous step to create your Skuid authentication provider for Excel.

Note

If you have created an authentication provider Microsoft Outlook or OneDrive, you cannot use that provider for the Excel data source. You must create a separate authentication provider for Excel.

  1. Navigate to Configure > Data Sources > Authentication Providers.

  2. Click New Authentication Provider.

  3. Fill out the necessary fields:

    • Name: Enter a unique name, such as ExcelAuth.

    • Authentication Method: OAuth 2.0 / OpenID

    • Provider Type: Microsoft Excel

    • Grant Type: Authorization Code

    • The Authorize and Token Endpoint URLs will be set automatically by the provider type.

    • Default Scopes: If not auto-populated, insert the following:

      • MyFiles.Read,MyFiles.ReadWrite

      Note: do not add spaces.

    • Client ID: Enter your registered app’s Application ID from the previous section.

    • Client Secret: Enter your registered app’s generated password from the previous section.

  4. Click Save.

  5. If Skuid asks you to create a Remote Site Setting, click OK.

Create a data source

With the authentication provider configured, create a data source that uses it to authenticate and gain access to Excel data.

  1. Navigate to Configure > Data Sources > Data Sources.
  2. Click New Data Source.
  3. Select the Microsoft Excel data source type.
  4. Enter a unique name for your data source, such as Excel.
  5. Click Next Step.
  6. Select the authentication provider you created in the previous section.
  7. Click Save new Data Source.
  8. If Skuid asks to create a Remote Site Setting, click OK.

You can now select your Excel data source within a model in the App Composer.

Using the Excel data source

The Excel data source type provides access to workbooks, worksheets, tables, charts, cell ranges, and table ranges—so there are model entities for each of those options. What makes the Excel unique is that each of these entities is nested within one or more entities. (Think of it as the matryoshka data source type!)

All entities (other than the Workbook) require a model condition that identifiesits parent entities. Note: You cannot use the name as the value in the workbook model condition; you must use the Id field. This can be obtained by creating a Skuid component that includes both the name and Id fields. When displayed in runtime, copy the ID that corresponds to the desired workbook. Other conditions do accept the name of the parent entity.

So for an Excel model on the Table Range entity, you are required to specify the following conditions:

  • The name or Id of range for the Excel table
  • The name or Id of the Excel worksheet in which the table exists
  • The Id of the Excel workbook file in which the Excel worksheet exists

Keep this in mind when creating or querying your models. Double check your conditions if you run into any errors.

When using Excel data source type, it’s important to know the general purpose of its model entities. The workbook, worksheet, and table entities are designed to return the lists of each entity. You will not see cell data unless you use the range or table range entities.

When previewing a page using the Excel data source, Skuid may ask to create a Remote Site Setting. Click Ok; once the Remote Site has been created by the Skuid Administrator, end users will not see this prompt.

Warning

At runtime, Skuid will display a standard popup inviting end users to log into their Office365 account. Logging in and out of multiple accounts is not recommended and will cause unexpected behaviors.

Workbook

The Workbook entity displays a list of all workbooks within a specific OneDrive directory, set in its parent condition. By default, this entity looks at the end user’s root directory.

Note

  • Excel workbooks must be stored in OneDrive to be accessible by Skuid.
  • New workbook creation is not possible with this model entity. Instead, a OneDrive data source is required.

Fields

The following fields are available in a model using the Workbook entity:

  • Created by (default): The user who created the workbook.
  • Created Date Time (default): A timestamp of the file’s creation.
  • Directory up: A Skuid-generated field that displays the folder containing the workbook. If a file is in /Documents/Books, the Directory Up field display Documents. Editing this field in runtime will not move the workbook file, but will instead rename the directory that contains it.
  • Id (default): The alphanumeric ID for the workbook.
  • Last Modified: A timestamp for the last modification to the workbook.
  • Last Modified by (default): The user who last modified the workbook
  • Name (default): The user-friendly name of the workbook.
  • Parent Directory (default): The name of the directory where the workbook is stored.
  • Size (default): the size of the file
  • Web Url: This displays a link that, if clicked, opens the workbook in OneDrive.

Model conditions

There is one required condition for workbooks, parent, which determines which OneDrive directory or specified workbook(s) to load.

This condition can be modified with the following values:

  • If “Value” is left blank, the model (by default) loads the OneDrive root directory.

  • /[workbook name].xlsx returns a specific workbook.

  • /[directory] returns a list of all workbooks in a specified directory.

  • /[directory]/[workbook name].xlsx returns a specific workbook in a specific directory.

    Note: These conditions must be URL encoded. If your directory or workbook name has spaces, you’ll need to use %20 instead. For example: Directory/My File Name.xlsx would become A%20Directory/My%20File%20Name.xlsx.

Worksheet

The Worksheet entity displays a list of all worksheets within a selected workbook, which is set by its file condition.

Fields

The following fields are available in a model using the Worksheet entity:

  • Id: The alphanumeric ID for the worksheet
  • Name: The user-friendly name displayed within a worksheet.
  • Position: The tab position of the worksheet in the workbook.
  • Visibility: Indicates whether the tab is visible or hidden.

Model conditions

The Worksheet entity requires a workbook file to work from. Because of this, it has one required condition:

  • file (required): Requires a workbook’s Id number as the value. This identifies the specific workbook from which the list of worksheets will be generated.

Using the Worksheets list in Skuid

Once the list of worksheets is displayed on the Skuid page, you can:

  • Add a new worksheet
  • Rename or delete a current worksheet

Table

While end users can enter and format data in a variety of ways within an Excel spreadsheet, Excel’s table feature lets users select a range of rows and columns and format this related data into an Excel table using preset styles. Skuid’s Table entity displays a list of all Excel tables within a selected worksheet.

Fields

The following fields are available in a model using the Table entity:

  • Highlight First Column: Indicates whether the first column contains special formatting.
  • Highlight Last Column: Indicates whether the last column contains special formatting.
  • Id: The alphanumeric ID for the table
  • Name: The user-friendly label for the table.
  • Show Banded Columns: Indicates whether the columns display “banded” formatting (that is, odd columns are highlighted differently from even ones).
  • Show Banded Rows: Indicates whether the rows display “banded” formatting (that is, odd rows are highlighted differently from even ones).
  • Show Filter Button: displays filter buttons at the top of each column header. This field can only be set if the table contains a header row.
  • Show Headers: Displays a list of the headers used in the table.
  • Show Totals: Sets whether the totaling row is visible or not.
  • Table Style: Displays the Excel style used in the table.

Model conditions

In addition to the required workbook entity, the Table entity requires a model condition to identify the specific worksheet, within the workbook, that the tables will be drawn from.

  • File (required): Set the workbook’s Id number as the value. This identifies the specific workbook from which the list of worksheets will be generated.
  • Worksheet (required): Set the worksheet’s name (or the Id in curly brackets, i.e. {0000-00-00001}) as the value to identify the specific worksheet.

Using the Tables list in Skuid

From the displayed list of tables, you can:

  • Rename the table or delete the table
  • Edit some of the fields, such as table style.

You cannot, however, create a new table from the Table entity. To do that, you must use the Create Table data source action (see Data Source Actions, below).

Chart

An effective way to display data in Excel is by using charts. The Chart entity displays a list of all Excel charts within a selected worksheet.

Fields

The following fields are available in a model using the Chart entity:

  • Height: displays the table height; cannot be modified from runtime.
  • Id: the alphanumeric ID for the table
  • Left: The distance (in points) from the left edge of the worksheet to the left side of the chart.
  • Name: The user-friendly label for the chart.
  • Top: The distance (in points) from the top edge of the worksheet to the top of the chart.
  • Width: displays the table width; cannot be modified from runtime.

Model conditions

In addition to the required workbook entity, Charts require a model condition to identify the specific worksheet within the workbook that the Chart will be drawn from.

  • File (required): Set the workbook’s Id number as the value. This identifies the specific workbook from which the list of worksheets will be generated.
  • Worksheet (required): Set the worksheet’s name (or Id) as the value to identify the specific worksheet.

Using the Charts list in Skuid

From the displayed list of charts, you can rename or delete the chart.

You cannot, however, create a new chart from the Charts entity. To do that, you must use the Create Chart data source action (see Data Source Actions, below).

Hint: Want to display specific data that’s used to create a chart? Use the Range entity (see next section).

Range

In contrast to the previous model entities, the Range entity displays the actual cell data contained within a specified range on a specified worksheet. When you create a model using Range, then use that model with a Skuid Table component, the Table component will populate with the data from the selected range.

Fields

The fields available on a Range entity model correspond to the columns in the selected range. To see all available fields, set the hasHeader (see below, in Model Conditions) condition before selecting fields for the Model.
  • If hasHeader condition (see below, in Model Conditions) is checked (true), the available fields will be designated using the content from the first row of the range.
  • If hasHeader condition (see below, in Model Conditions) is unchecked (false), the available fields will be designated using generic labels that correspond to the columns employed in the range (“ColumnA,” “ColumnB,” “ColumnC and so on).

Note

When a Skuid Table component is attached to the Table Range entity, it will display the value of the cell when in Read mode, even if the value is the result of an Excel formula. If the end user clicks on that field and enters Edit mode, the field will then display the raw excel data, meaning that Excel formulas will also be displayed. For example:

Read mode: 4

Edit Mode: =SUM(2,2)

Model conditions

  • File (required): Set the workbook’s Id number as the value. This identifies the specific workbook from which the list of worksheets will be generated.
  • Worksheet (required): Set the worksheet’s name (or the Id in curly brackets, i.e. {0000-00-00001}) as the value to identify the specific worksheet.
  • Range (required): Set the value using the spreadsheet cell range.
  • hasHeader: A boolean condition that is set to false by default, this lets you specify table rows to be used as a header.
    • When Value **is checked (**true), the first row of the selected range will be used populate the field metadata labels; you can then select those fields from Model > Fields.
    • When Value **is unchecked (**false), field metadata labels will be generic and correspond to the columns used in the selected range:
      • For a range that extends from Columns A to C, the field metadata labels will be “Column A,” “Column B,” and “Column C.”
      • For a range that extends from Columns J to L, the field metadata labels will be “Column J,” “Column K,” and “Column L.”
    • Do not change the Content field in this condition.

Using the Range display in Skuid

When displayed, Range allows you to both view and edit the values in each field.

In edit mode, Range displays any Excel formulas used to derive the values. These formulas can be edited in Skuid and the changes will update in the worksheet.

Table Range

The Table Range entity displays the all of the data contained within a specified table. When you create a model using Table Range, then use that model with a Skuid table component, the Table component will populate with the Excel table’s data.

Hint: Want to display a specific range of data in a table, rather than the whole table? Use the Range entity (see previous section).

Fields

The fields available on model using the Table Range entity correspond to the columns in the selected table.

To see all available fields, set the hasHeader (see below, in Model Conditions). condition before selecting fields for the Model.

  • If hasHeader (see below, in Model Conditions) is checked (true), the available fields will be designated using the names of each table header.
  • If hasHeader (see below, in Model Conditions) is unchecked (false), the available fields will be designated using generic labels that correspond to the columns employed in the table (“ColumnA,” “ColumnB,” “ColumnC and so on).

Note

When a Skuid Table component is attached to the Table Range entity, it will display the value of the cell when in Read mode, even if the value is the result of an Excel formula. If the end user clicks on that field and enters Edit mode, the field will then display the raw excel data, meaning that Excel formulas will also be displayed. For example:

Read mode: 4

Edit Mode: =SUM(2,2)

Model conditions

  • File (required): Set the workbook’s Id number as the value. This identifies the specific workbook from which the list of worksheets will be generated.

  • Worksheet (required): Set the worksheet’s name (or the Id in curly brackets, i.e. {0000-00-00001}) as the value to identify the specific worksheet.

  • Table (required): Set the table name as the value to identify the specific table.

  • hasHeader: A boolean condition that is set to true by default, this lets you specify whether the table has a header.

    • When Value **is checked (**true), the table headers—the first row of the table—will be used populate the field metadata labels; you can then select those fields from Model > Fields.

    Note: If you do not set this to true, the content in the header row will display as data records.

    • When Value **is unchecked (**false), field metadata labels will be generic and correspond to the columns used for the table:
      • For a table that extends from Columns A to C, the field metadata labels will be “Column A,” “Column B,” and “Column C.”
      • For a table that extends from Columns J to L, the field metadata labels will be “Column J,” “Column K,” and “Column L.”

Using the Table Range display in Skuid

As with the Range entity, Table Range allows you to both view and edit the values in each cell.

When in edit mode, Table Range displays any Excel formulas used to derive the values. These formulas can be edited in Skuid and the changes will update in the worksheet.

Data Source Actions

Some of Skuid’s pre-configured data source types may also incorporate specific functionality from their respective services as data source actions.

The Excel data source type has three data source actions:

  • Excel Functions
  • Create Excel Chart
  • Create Excel Table

Excel Functions

This data source uses Excel’s API to add standard Excel functions to Skuid elements, such buttons and button sets. Currently, there more than 20 functions available from the picklist list. For example, you could use a button that has the Excel AVERAGE function to calculate the average of a set of defined values.

These functions can be used individually, or combined to create formulas, but you cannot nest functions (for example, SUM(AVERAGE(C3:C5)+(AVERAGE(C12:C19). If you need to perform complex function, use a Range model to identify and edit the individual cell.

To use this data source action:

  • Create a new action and select Run Data Source Action as the action type:

    • Data Source: Name of Excel data source.
    • Action: Excel Function.
    • Workbook ID: Add a workbook’s ID.

    Note: To use this data source action, you must specify a workbook, even if you will not be working within one. The action will not necessarily affect the workbook.

    • Function Name: Select the desired Excel function from the picklist. Depending on the function, there may be additional options: Number or Range.
      • Number: Enter one or more comma-separated numbers.
      • Range: One or more comma-separated ranges to be acted on by the Excel function in one of the following ways:
        • Specific comma-separated numbers ({{value}}280,500,1865.35)
        • A worksheet by name, followed by a bang, and the cell range (January!B20-D25)
        • A mixture of ranges and numbers (again, comma separated): (January!A1:E5,February!B15:B20,1,2,3,4,5)
  • To use this Excel value in a model, create a new action and select Update Output Field as the action type.

    • Model: Select the model with the field to be updated
    • Field: The field to be updated
    • Value: {{$Excel.value}}
    • Rows to use:
      • Rows in `context <https://docs.skuid.com/latest/en/skuid/glossary.html#context>`__: In this instance, any rows selected by the end user.
      • All rows in model.
  • Create a new action and select Save Model Changes.

    • Models to Save: Select the model.
    • Roll back entire save on any error: Check

Hint: Want to use an Excel function on a series of rows in a table? Merge syntax does not support this, but here’s a workaround:

  • Create a UI-only text field containing your rows, separated by a comma.
  • Use merge syntax (for example, {{value}}UI-only field name) to perform the Excel function on that UI-only field.

Create Excel Chart

This data source action allow you add a chart to a specific worksheet within a workbook.

To use this data source action:

  • Create a new action and select Run Data Source Action as the action type:

    • Data Source: name of Excel data source.
    • Action: Create Chart.
    • Workbook ID: The workbook where the chart will be added.

    Note: You must specify a workbook.

    Worksheet Name or ID: The worksheet where the chart is to be added

    • Chart type: Choose the type of Excel chart from the picklist.
    • Source data: Specify range of cells to use to create the chart.
    • Chart Series By:
      • Auto
      • Columns
      • Rows
  • Create a new action and select Save Model Changes for your Charts model.

Create Excel Table

This data source action allow you add a table to a specific worksheet within a workbook.

To use this data source action:

  • Create a new action and select Run Data Source Action as the action type:

    • Data Source: name of Excel data source.
    • Action: Create Excel Table.
    • Workbook ID: The workbook where the table will be added.

    Note: You must specify a workbook.

    • Range: The range that will be used to create the Excel table.

    • Has Headers:

      • Check if you want the first row of the range to be used as headers in the table.

      Note: You cannot use this action to name the table headers

  • Create a new action and select Save Model Changes for your Table model.

Troubleshooting

  • I’m using a Query Model action to query one or more Excel models, but they are not updating. No error message is displayed.

    • One of the models being queried may not have all the required conditions set. If even one of the models selected in the Query Models action type fails to query, the action type will not proceed.
    • Ensure each required condition on each model has a value set, either in the App Composer or through the Activate & Set value of Model Condition action type.
  • When attempting to add model fields or previewing my Skuid Page, I receive the following error:

    Request to data source "MyExcelDataSource" failed: [object Object]

    • You may not have set the required conditions for all of your model entities. Ensure each required condition on each model has a value set.

When configuring Microsoft data sources, several common errors may deal with authentication and data source configuration. If you receive errors when logging into the OAuth popup window, check the following:

  1. The authentication provider’s authorize endpoint URL contains your Office365 domain name.
  2. The client ID and client secret match the application ID and a key from your app registration
  3. Ensure that you are not logging into to another (different) Office365 account. Switching accounts will cause issues with cookies and not allow you to properly authenticate, especially if the accounts are in two separate Office365 domains.

There are several specific errors you may encounter:

  • Data source and app registration scopes have been updated, but the end user can’t access data from Skuid.

    The changes to permissions may have not had enough time to propagate in Microsoft’s servers. Try again later.

  • This application requires application permissions to another application. Consent for application permissions can only be performed by an administrator. Sign out and sign in as an administrator or contact one of your organization's administrators.

    There is an issue with your application registration permissions. Ensure you have set your permissions as Application Permissions instead of Delegation Permissions.

  • Could+not+find+the+authenticating+server,+https://login.microsoftonline.com/common/oauth2/token

    This error message indicates issues with your OAuth authentication flow.

    Try each of the following steps in turn:

    1. If logging in with multiple Office365 accounts, or using an account without the appropriate licenses, try clearing the cookies.
    2. Check that your client ID and client secret have been entered correctly in the authentication provider. If you’ve lost your client secret, create a new key in your Azure AD app registration and update the authentication provider.
    3. Delete the Skuid data source and recreate it, pointing to the same authentication provider.
    4. If all else fails, delete any and all existing data sources and authentication providers for the Microsoft service you’re configuring and then recreate them.