How to extract the API data in an Excel sheet
- What is the API data in the GMS?
- How to extract the API data
The GMS harbors multiple series of datasets drawn from the information entered and generated on the system. This data can be used for multiple purposes, including the redaction of Annual Reports. This is why the GMS team is offering OCHA staff the possibility to extract this data depending on their needs, in the form of API Datasets.
The API data contains the raw data from the GMS. The datasets have been grouped by themes (Annual reports – contributions, annual reports – allocations, MPTF data extract, etc.)
The datasets can be downloaded as JSON and CSV files. Both these formats require some degree of specific knowledge to read them.
But Don’t Panic! You don’t need to be an IT expert to use these datasets. This blog post will take you through the step-by-step process on how to extract the API data as an Excel sheet for internal purposes.
Please do not share the API data publicly, as it includes critical information on partners and projects across all CBPFs. While our team is in the process of narrowing down the data per individual fund, it is very important that the data provided through the APIs remains private and for internal use ONLY.
Step 1: Go to https://cbpfapi.unocha.org/vo3 and copy the CSV version of the Dataset you wish to extract.
Please note that, at the moment, you cannot select a specific Pooled Fund from the drop-down list at the top of the page. The extract you chose will be applied for all funds.
Paste the copied URL in the text field. Keep the default option ‘basic’ ticked. Click on [OK].
Important Note: Please double check that the beginning of the URL is “https”, and not “http”. If it is “http”, please add an ‘s’ (refer to screenshot below for an illustration of this)
Step 4: Enter your credentials in the new pop-up window to retrieve the data.
To know what your username and the password are, please contact firstname.lastname@example.org
Please note, make sure that you are in the ‘basic’ tab of this pop-up window to enter your credentials, as illustrated in the screenshot.
Then, click on the drop-down list ‘Select which level to apply these settings to’, and select the last option of the list: ‘https://cbpfapi.unocha.org/vo3/odata/GlobalGenericDataExtractSecure’.
And click on [Connect].
The data might take a few minutes to load on the Excel document.