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. You will need an Authorization Key and a Subscription Key.
For instructions on finding your API keys, please see QuickHelp APIs - Getting Access.
Locate the API Endpoint
- Click here to locate the QuickHelp API Endpoints Documentation.
- Download the API Endpoints Documentation pdf file.
- Search the API Endpoints that you are looking for.
- Copy the Request URL through to the name of the endpoint (e.g. https://qhapi.quickhelp.com/v1/Content/Assets)
Building the API Connection
Please follow these steps to create the connection between QuickHelp and your Excel.
- Launch Excel Desktop
- Open a blank worksheet
- From the Data tab, click From Web
- Click the Advanced radio button in the From Web dialog
- Paste the Request URL (see above) into URL parts
- 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
- In the HTTP request header parameters section, type Authorization in the Enter or select a value field
- In the corresponding field, enter your Authorization Key (from the QuickHelp Admin Portal)
- Click Add header
- Type Ocp-Apim-Subscription-Key in the second Enter or select a value field
- 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.
- Click OK
- 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.
- 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. 
- Click To Table
- Click OK in the To Table dialog (leave everything as default)
- Click the icon to the right of Column1
- 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
- Click OK
- 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.