Getting Arrays

The most comminly used function is GET(). It allows you to make an API request and specify what part of the request you want to present. You can even specify multiple parmeters to populate multiple columns.

However, what if you want to call an API that returns a list of items, and present all these items on seperate rows? This is where the GETARR() function comes in handy. It allows you to pull a list of items from the API (an array), puting every item in the list on a row. Like the GET() function, for every item, you can specify what parameters you whish to retrive, and those will be the columns.

Using the GETARR() function

For this example, we'll use the free odm-organizations endpoint of the Crunchbase API to get a list of all companies located in San Francisco.

Making a request to the endpoint with location set to San Francisco and organization_types set to company, you can see the list of companies recieved back.

Expanding one of the companies in the list, you can see all the parameters it contains:

In this example, we'll select the name,short_description and domain parameters.

To make the request, use the GETARR() function with the following arguments:

  • URL - this is the URL of the endpoint you’re using, as seen on the first screenshot. In this case it’ll be: https://crunchbase-crunchbase-v1.p.rapidapi.com/odm-organizations.

  • ArrayPath - this is the path to the list you want to present in the response. In this example, the list location is data.items. Every item in this list will be presented in a new row.

    • SelectPath - this will highlight what part of the response data you want to pull into the spreadsheet for each item in the list. In our example, we'll use properties.name,properties.domain,properties.short_description to pull these 3 properties.

    • ApiKey - this is your API key to use APIs through RapidAPI, seen on the first screenshot.

    • Parameters:
      For each parameter (which can be seen on the second screenshot), you should pass two arguments to the function - the parameterName and parameterValue.
      In this example we'll pass 2 parameters: "locations","San Francisco","organization_types","company".

The full function will thus be:

=GETARR("https://crunchbase-crunchbase-v1.p.rapidapi.com/odm-organizations","data.items","properties.name,properties.domain,properties.short_description","YOUR_API_KEY","locations","San Francisco","organization_types","company")

Watch the following video to see the full process: