Using the API with MS Excel

(Note 1: this only works on the Microsoft version of Excel, and will only load up to 10000 rows).

In a blank Excel workbook, select Data > Get & Transform Data > From Web.

Then, choose the dataset on the API reference page and fill in the required parameters on that page for the dataset.

Then copy and paste the link produced. We would recommend selecting the whole url displayed in the python code sample window, as seen below. Usually the link is wider than the screen.

Back to Excel: after "From Web" is selected, select "Advanced" as shown below. Put the link you copied into the "URL parts" section. Under "HTTP request header parameters", type "X-Token" into the drop-down menu (rather than selecting any items in the drop-down). Then, paste your X-Token, into the space next to it (which you can retrieve from the developers tab of your profile).

Click OK. This will open a new window titled "Access Web content". Make sure "Anonymous" is selected on the left hand side, as in the image below, and if the link option in the image below is selected, then your X-Token will be saved for future calls in that file for all of Modo's API.

Click Connect. This will establish a connection and load the data into Power Query Editor.

When the data loads, it should look like this. Click Into Table under Convert.

The new table will have a "Name" column. Filter this by clicking the down arrow on this column as shown below. Then, filter to just "results".

This should then look how it is below. Select the button highlighted in the image below, and select "Expand to New Rows"

Select that icon again on the new table:

Then, deselect the checkbox highlighted below (if the column names are different it is just because this example uses specific data).

Press "OK" and the data should load into the Power Query Editor! Then, press "Close & Load" in the top left corner, and the data should load into your Excel file.

If your data of interest is more than the number of rows that your account allows you to retrieve at once (100, or 1000). To retrieve the next 1000 rows, reopen the Power Query Editor from Get Data > Launch Power Query Editor. Click "Source" on the right hand side to load how the data looked previously, to make the Power Query Editor look as below.

Select the link next to next. Then, repeat the process detailed here with this link in Get Data instead of the one directly from the API reference page. This will be able to be loaded onto a new tab on this sheet.

To refresh the data for the same calls you have made on the entire sheet, click "Refresh all" under the Data section.