Please find below steps necessary on how to use OpenApply's APIs to PowerBI. Please note that V1 API will soon be deprecated, and we encourage new projects to use V3. Old projects are highly recommended to migrate to V3.
In addition to the advantages of more security to V3, it also supports pulling custom fields, which V1 does not.
V3 API
Version 3 uses a more secure authentication method with oauth2, the Power BI Desktoip client does not support that particular method out-of-the-box. There is an article that discusses an approach that utilizes a script which works around the issue. This code comes courtesy of Christopher Andrews and ChatGPT:
$ClientID = "xyu"
$ClientSecret = "zse"
# Combine Client ID and Client Secret, then Base64 encode them
$AuthHeader = [System.Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${ClientID}:${ClientSecret}"))
# Make the request to get the access token
$Response = Invoke-RestMethod -Uri "https://lordwandsworth.openapply.com/oauth/token" `
-Method Post `
-Headers @{Authorization = "Basic $AuthHeader"} `
-Body "grant_type=client_credentials" `
-ContentType "application/x-www-form-urlencoded"
# Display the access token in the console
Write-Output "Access Token: $($Response.access_token)"
# Optionally, save the access token to a text file
$Response.access_token | Out-File -FilePath "access_token.txt" -Encoding utf8
This saves the token, and then another PowerQuery can read from the text file to obtain the bearer token.
The Power Query is as follows:
let
// Define the access token (replace YOUR_ACCESS_TOKEN with the actual token)
AccessToken = "dsafkjh",
// Define the base URL of the API (adjust the subdomain as needed)
BaseUrl = "https://mydomain.openapply.com/api/v3/students",
// Define the function to get data from a specific page
GetPage = (PageNumber as number) =>
let
// Build the URL with the page parameter
Url = BaseUrl & "?page=" & Number.ToText(PageNumber) & "&per_page=50",
// Set up the headers with the Bearer token
Headers = [
#"Authorization" = "Bearer " & AccessToken,
#"Content-Type" = "application/json"
],
// Fetch data from the API
Response = Web.Contents(Url, [Headers = Headers]),
// Parse the JSON response
JsonResponse = Json.Document(Response),
// Get the "students" data from the response
Data = JsonResponse[students]
in
Data,
// Define the function to recursively fetch all pages
FetchAllPages = (PageNumber as number, AccumulatedData as list) =>
let
// Get the current page data
CurrentPageData = GetPage(PageNumber),
// Combine it with accumulated data
NewAccumulatedData = List.Combine({AccumulatedData, CurrentPageData}),
// Next page number
NextPageNumber = PageNumber + 1
in
// Check if the current page data is empty (end of data)
if List.IsEmpty(CurrentPageData) then
AccumulatedData
else
@FetchAllPages(NextPageNumber, NewAccumulatedData),
// Start fetching from page 1
AllData = FetchAllPages(1, {}),
in
All Data
V1 API
First, set up a bearer token for authentication on the OpenApply v1 API. The v1 API is exactly the same as the v3 API apart from the way you authenticate. If you are using ManageBac's APIs, this token is the same token generated in the API manager.
In PowerBI Desktop
- Click on Get Data
- Click on Web
In the From Web box, paste the API URL in the format
https://{subdomain}.openapply.{tld}/api/v1/students?auth_token={token}
Where {subdomain} is your school's subdomain, and {tdl} it your top-level domain ("cn" in China region, "com" elsewhere). The {token} should be replaced with your bearer token.
Tip
Please note that by default 100 results will be downloaded. To view more, you can extend the amount of results per page, or change the page number in the API URL. For example:
https://f1.openapply.com/api/v1/students?per_page=200&page=2&auth_token=1a2b3c...
Note
If you are using ManageBac's APIs, note that it does not require {subdomain}, use for example https://api.managebac.com/v2/students instead.
You will be presented with the following screen in Power Query Editor:
We will first process the students so change the name of the query to students for future reference
Click on List for students:
Click To Table
Click OK in the pop up box
Click the Expand Column icon for Column1
Uncheck the Use original column name as prefix
Click the Expand Column icon for tags
Click Expand to New Rows
Click the Expand Column icon for sibling_ids
Click Expand to New Rows
Click the Expand Column icon for parent_ids
Click Expand to New Rows
Click Close & Apply
This will load the fields available for students to be added to visuals within Power BI