Information is everywhere! The challenge you may face is how to make effective use of it. Every time you use data, you need to know how to access it and how to display it so that it makes sense.

The QuickHelp Admin portal has prebuilt reports that can be downloaded into a CSV and used to create charts and pivot tables within Excel. However, you might spend hours gathering and formatting this data. And then you need to repeat the same process every week.

What if you could change that?

One way to change it is to use QuickHelp’s APIs to create a connection between QuickHelp data and Excel to collect this data automatically.

What you will need

The following information will be required when configuring Excel, so we suggest you note them somewhere easily accessible.

Authorization Key (QuickHelp Admin Portal > Settings) 

Subscription Key (QuickHelp API portal)

For instructions on finding your API keys, please see QuickHelp APIs - Getting Access.

Request URL

To get the Request URL, please follow these steps.

  1. Log in to the QuickHelp API portal
  2. Click the APIS tab
  3. Click v1
  4. Click the desired Endpoint
    • For Excel you will focus on the “get” endpoints (e.g.: getAssets, getAttendedEvents, getSkillPathAssets, etc.
  5. Copy the Request URL through to the name of the endpoint (e.g.

Building the API Connection

Please follow these steps to create the connection between QuickHelp and your Excel.

  1. Launch Excel Desktop
  2. Open a blank worksheet
  3. From the Data tab, click From Web
  4. Click the Advanced radio button in the From Web dialog
  5. Paste the Request URL (see above) into URL parts
  6. To filter by additional parameters, click Add part (optional)
    • Parameters are used to apply filters to your data
    • VERY IMPORTANT for almost all endpoints you will NOT need to use Customer ID. If you do need to use your customer ID and do not know your Customer ID, please check with your QuickHelp Client Success Manager
    • The first parameter will begin with a ‘?’ e.g., ?customerid=XXXX (Place your organization's customer ID here)
    • Each additional part will begin with a ‘&’ e.g., &typeid=1 (Video Assets)

      NOTE: The descriptions for these filters are in the API portal
  7. In the HTTP request header parameters section, type Authorization in the Enter or select a value field
  8. In the corresponding field, enter your Authorization Key (from the QuickHelp Admin Portal)
  9. Click Add header
  10. Type Ocp-Apim-Subscription-Key in the second Enter or select a value field
  11. In the corresponding field, enter your Subscription Key (from the QuickHelp API Portal)
    NOTE: While the Excel wizard states that this is optional, it is not optional for QuickHelp APIs to function.
  12. Click OK
  13. An Untitled Power Query Editor will open (This may take a few moments depending on your internet connection)
    NOTE: If the data does not display, please check a few things:
    • Wait up to 48 hours after having generated your API Key in the QuickHelp Admin Portal
    • Verify that you are connecting to the API Anonymously (Data tab > Recent Sources) and that the correct URL has been selected.
    • Click Connect
    • If you continue to experience problems, please enter a support ticket for assistance.
  14. In the Power Query Editor pop up page, click the List Tools Transform tab
    NOTE: If you are sent to Record Tools instead of List Tools you will need to click on the records link. This will open up the list of records. 
  15. Click To Table
  16. Click OK in the To Table dialog (leave everything as default)
  17. Click the icon to the right of Column1
  18. Uncheck Use original column name as prefix
    NOTE: If you are running getUserList and you want the SSO columns you will need to expand that section
    Then select which SSO attributes you want displayed
  19. Click OK
  20. From the Home tab, click Close & Load

You are now ready to start building your Excel reports. For information on refreshing your data within Excel, please see Refresh Your Excel Connections for a Web API. To view this asset, the Excel Content must be enabled in your QuickHelp library.