Official websites use .gov
A .gov website belongs to an official government organization in the United States.
Secure .gov websites use HTTPS
A lock (
) or https:// means you’ve safely connected to the .gov website. Share sensitive information only on official, secure websites.
and USDA
Excel has the ability to make API requests itself, without needing additional tools or detailed programming experience.
This tutorial will demonstrate how to execute an API request using Excel’s Power Query Editor. There are two main types of API requests in Excel: GET requests and POST requests.
GET Requests are useful for performing small requests by passing input fields to the API using a URL. See the “Web Browser” for a detailed explanation of a GET request for the DSID API.
To build a GET request in Excel, first go to the Data tab → Get Data → From Other Sources → From Web
The “From Web” popup will appear on your screen. Select “Advanced”.
Example: you want to estimate calcium levels in Adult Multivitamin/Minerals (MVMs) based on a label amount of 200 mg.
In the URL Parts section, click the “Add Part” button 3 times, so that it shows 5 parts.
In the HTTP request header parameters section, click the “Add header” button once to allow two headers. Use the dropdown options to select the “Accept” and “Content-Type” headers. Fill both text boxes with the text application/json and Click OK.
You may now see a screen titled “Access Web Content” that looks like the image below; this is used in case an API requires user login or authentication. The DSID API is public and does not require user credentials. You may leave this as “Anonymous” and click Connect.
You should see the Excel Power Query Editor open and your results shown in a table.
To convert this result from JSON into a table, click the “Into Table” or “To Table” button in the top-left of the screen.
The final step is to “Close and Load” to return to your Excel window and view your final table.
Note that the study_code, ingredient, and unit fields are left-aligned since they are text values.
POST requests are useful for more complex, larger queries to the DSID API. These requests may involve ingredients with multiple label amounts, more than one ingredient from a study, or even data from multiple studies.
First, open a blank Excel sheet and go to the Data tab → Get Data → From Other Sources → Blank Query.
This will open the Power Query Editor.
Click the Advanced Editor button to open the Advanced Editor popup window.
Delete any text in the Query text box so that it is completely blank. To create a custom function, copy the following code from the attached file:
Make sure that the bottom left reads “No syntax errors have been detected” and has a green check mark.
Click “Done.” The screen should now show a function setup and look like this:
In the Properties on the right of the screen, change the name to send_api_request and hit Enter.
Now click “Close and Load” in the top left.
Enter your input data, using the four required fields – study_code, ingredient, label_amount, and unit. Ensure that the study_code column is formatted as text – either type an apostrophe first ('05) or right-click → Format Cells → Text.
Highlight the range of values you have entered and go to Data → From Table/Range.
In the “Create Table” popup box, make sure to select the corresponding range for your data and check the “My table has headers” box. Click OK.
This will re-open the Excel Power Query Editor and display your table.
At this point, be sure to check whether the study_code field still shows values of “01” and not “1”. Excel may have converted it automatically to an integer, as shown below.
If you see the “Changed Type” under APPLIED STEPS on the right hand side of the screen, click on the X next to it. You should now only see the Source step and the study_code should look like “01” in the table.
Now, navigate to Add Column → Invoke Custom Function.
In the “Invoke Custom Function” popup box, enter a name for the column and choose the send_api_request function query that you created earlier.
Four new fields will appear asking which column of your table corresponds to each API input field. Choose the dropdown on the left and select “Column Name” and provide the corresponding columns for your table.
At this point, Excel may show a warning and popup about setting Privacy Levels (for first time power query users). The DSID API and data are public, so you can set the levels for “Current Workbook” and the DSID cloud.gov URL to “Public” and hit Save.
The API results will appear as a new column (with the column name you specified) on the right of your table.
Note: if any of your input fields have typos/errors, now is the time to fix them! Rows with errors will say “Error” in the “API Output” column and the header will have a red flag near it. Any errors may prevent the entire request from going through successfully.
Example error: Misspelling of “iodine” as “iodin”.
To address any errors in data entry, click Close to exit the power query editor. It will then ask if you want to keep your changes, click Keep.
Return to your table, and fix the spelling error. Then re-open the Power Query Editor through Data → Get Data → From Table/Range.
Once you are back in the Power Query Editor, return to the table and query you were working on.
To separate the list into columns, click the 
to the right of the new “API Output” column and select “Expand to New Rows”. Click the icon again.
Uncheck the box at the bottom next to “Use original column name as prefix.” You may also wish to unselect the columns used as input fields to avoid duplicates (study_code, ingredient, label_amount, unit). Once you have selected all the columns of results you want, click OK.
You now have calculated results from the API. Scrolling can show you all of the values for your request. If a row has an invalid input field, it will return Error for all columns.
The final step is to “Close and Load” to return to your Excel window and view your final table.
The updated table will appear in the sheet you were working on.
