Using Wonde API with Excel

[OFNF]

Good Afternoon all, I am wanting to use the Wonde API to extract data from sims into excel. the one bit that seems to be stumping me is Pagination and how to pull for example all pupil records at once or is this not possible. i have managed to pull the page 1 through with this = Json.Document(Web.Contents("https://api.wonde.com/v1.0/schools/A12345678910/students?include=registration,year,contact_details,extended_details&per_page=200&page=1")) but is there a way in the syntax that will do all pages or is there a way of indexing how many pages and parsing through?

 

any thoughts would be appreciated.

[dhicks]

any thoughts would be appreciated.

 

Sorry, I haven't looked at the Wonde API recently (although I keep meaning ot get back to it), but they might be expecting you to step through page-by-page until you are returned 0 records / an error / an exception of some kind so you know you've hit the end.

[jthompson]

From what I remember of doing this, the response will include a 'next page' token of some kind, such that you can do a while/do loop to process any and all pages of the response until there are no more pages.

 

IIRC when fetching student details from Wonde I think you need to specify an 'updated since' parameter so that only student records updated since then are included in the response. If you want all of the students then you just need to specify that as being far enough back to cover them all.

 

Happy to share what I've written for this, although it's written in Google Apps Script.

[OFNF]

that would be great any information you may have/Scripts would be welcomed. just beginning my journey into the world of APIs and the more info i can gather the better! Edited November 27, 2024 by OFNF

[OFNF]

Thanks for the reply yeah I suspect something along these lines but i am fairly new to working with API and up till now have only really used PowerShell to test curl commands available in the wonde api documentation

[jthompson]

Here's the bit of my code that deals with the pagination. This is written for Apps Script.

 

The bit at the end of the "do" block is where the url of the next page is extracted from each response (from the "meta" property of each page), so that another API call can then be made to that next url, with the response from that appended to the "pages" array.

 

Each page received from the Wonde API is an object with both "data" and "meta" properties. It's the values in the "meta" bit that allow you to work through the pagination, with the "data" bit containing the actual student details or whatever that you're after. For example, after using this function to get data from the students endpoint, I'd need to use pages[0].data[0].legal_surname to get the legal surname of the first student record returned.

 

/**
* Function to fetch data from the Wonde API.
* 
* @param {sting} endpoint - The required API URL path (not including the base URL. See https://docs.wonde.com/docs/api/sync#introduction).
* @return {Array} Returns an array of JSON objects. Each element is a page of results from the Wonde API (see https://docs.wonde.com/docs/api/sync#pagination).
*/
function fetchWondeEndpoint_(endpoint) {
 var base_url = 'https://api.wonde.com/v1.0/schools/{TheSchoolID}/';
 var url = base_url + endpoint;
 var pages = [];
 do {
   response = WondeLibrary.getData(url);
   if (response.getResponseCode() != 200) {
     Logger.log('Wonde response code: %s', response.getResponseCode());
   }
   page = JSON.parse(response);
   pages.push(page);
   if (page.meta.pagination.more) {
     url = page.meta.pagination.next;
   }
 }
 while (page.meta.pagination.more)
 return pages;
}

[dhicks]

From what I remember of doing this, the response will include a 'next page' token of some kind, such that you can do a while/do loop to process any and all pages of the response until there are no more pages.

 

Ah, good point, that sounds familiar. I've seen that in other APIs, I think they're using a standard tool to create the API (Swagger?) and documentation (complete with handy test environment), which makes sense.

[Marci]

Functions for PowerQuery for handling Wonde pagination etc are all documented yonder: https://powerbiforschools.com/wonde-integration-with-power-bi-using-the-api/

PowerQuery for PowerBI vs PowerQuery for Excel - 99.999% the same thing. What works in one invariably works in the other (allbeit Excel gets far less memory per mashup container with which to work so tends to take a while vs PBI).

Edited November 28, 2024 by Marci