=API()

The =API() custom formula is a powerful tool that allows you to pull data from an API (Application Programming Interface) directly into your Google Sheets. This can be extremely useful for pulling in live data from various online sources.

How to Use the =API() Custom Formula

The =API() formula has the following structure:

=API(method, url, header, requestBody, path, noHeader, inheritParent)

Let's break down what each of these parameters means:

1. method: This is the HTTP request method for the API request. It can be one of the following options: GET, POST, PUT, PATCH, DELETE.

2. url: This is the URL of the API endpoint. You can also include request parameters in the URL like /v1/api?limit=10&page=3.

3. header (optional): This is a comma-separated list of header items. You can use it to pass authentications. For example: "apikey=YOUR_API_KEY,content-type=application/json". No space before or after commas.

4. requestBody (optional): The request body sent to the API endpoint. Type in JSON format if you are using application/json. It is recommended to use a reference to a cell containing the raw JSON payload in this parameter to avoid double quotes conflicts.

5. path (optional): A comma-separated list of paths to be imported. For example: "/first_name,/email" will only import data under the "first_name" and "email" paths. No space before or after commas.

6. noHeader (optional): Whether or not to include a header row in the response, i.e. TRUE or FALSE. The default value is FALSE.

7. inheritParent (optional): Whether or not to inherit values from parent elements. TRUE or FALSE. The default value is FALSE.

Examples

Example 1: Simple GET Request

Let's say you want to pull data from a public API, such as the JSONPlaceholder API. You can use the GET method to retrieve data

=API("GET", "https://jsonplaceholder.typicode.com/posts")

This will pull in all the posts from the JSONPlaceholder API into your Google Sheet.

Example 2: GET Request with Headers

If the API requires an API key for authentication, you can include it in the headers:

=API("GET", "https://api.example.com/data", "apikey=YOUR_API_KEY,content-type=application/json")

Replace YOUR_API_KEY with your actual API key.

Example 3: POST Request with Request Body

If you need to send a POST request with a JSON payload, you can include it in the requestBody parameter. However, remember that in Google Sheets, you need to escape the double quotes in your JSON by using two double quotes:

=API("POST", "https://api.example.com/data", "content-type=application/json", "{""key1"":""value1"", ""key2"":""value2""}")

This sends a POST request to the API with the JSON payload {"key1":"value1", "key2":"value2"}.