Skip to main content

IMPORTJSON Custom Function

Formula​

=IMPORTJSON(address, Keyrespons)

Description​

IMPORTJSON is a custom function in Google Sheets used to retrieve JSON data from APIs or URLs and display it directly inside a spreadsheet.

This function is not built into Google Sheets and is usually created using:

  • Google Apps Script
  • External script libraries
  • Custom integrations

This formula is commonly used for:

  • REST API integration
  • Retrieving realtime data
  • Monitoring dashboards
  • External data synchronization
  • Reporting automation

Formula Structure

Parameter Explanation

1. api_address​

api_address;

Contains the API endpoint URL to be requested.

Example:

https://api.example.com/users

Purpose of this parameter:

  • Defines the data source
  • Sends requests to the API server
  • Retrieves JSON responses

2. Keyrespons​

Keyrespons;

Used to specify the JSON key or path to retrieve from the API response.

Example JSON response:

{
  "name": "Akmad",
  "email": "akmad@mail.com"
}

Example usage:

=IMPORTJSON(api_url, "name")

Result:

Akmad

If you want to get all data response used:

Example JSON response:

{
  "data": {
    "name": "Akmad",
    "email": "akmad@mail.com"
  }
}

Example usage:

=IMPORTJSON(api_url, "data")

Result:

nameemail
akmadakmad@mail.com
hamsyanihamsyani@mail.com
nudinnudin@mail.com

Formula Workflow

Send request to API
        ↓
Receive JSON response
        ↓
Search requested key
        ↓
Retrieve key value
        ↓
Display result in spreadsheet

Example Usage

Formula​

=IMPORTJSON(
"https://api.example.com/users",
"name"
)

API Response​

{
  "name": "Akmad",
  "role": "Backend Developer"
}

Result​

name
Akmad

Use Cases

This formula is commonly used for:

  • API monitoring dashboards
  • Realtime tracking data
  • ERP integration
  • Inventory system integration
  • Automatic external data display
  • JSON data fetching

Notes

Since IMPORTJSON is a custom function:

  • Requires Google Apps Script
  • May be affected by API quota limits
  • Requires internet access
  • Depends on the API JSON structure

Conclusion

IMPORTJSON is used to retrieve JSON data directly from APIs into Google Sheets using a custom function.

Highly suitable for:

  • API integration
  • Data automation
  • Realtime spreadsheets
  • Monitoring dashboards
  • External system integration