TASK_API

Function Overview

TASK_API allows you to integrate external APIs directly within your Google Sheets. It enables you to call any API from your spreadsheet, pass parameters from cell values, and display results where you need them. This function simplifies API connectivity without requiring complex coding.

Uses

1. Pull real-time data from external systems into your spreadsheet.
2. Look up information based on sheet values, such as currency conversions and stock prices.
3. Submit form data collected in your sheet to external services.
4. Create custom integrations between your spreadsheet and other applications.
5. Automate data exchange with third-party platforms.
6. Enrich your spreadsheet data with external information.

Syntax And Parameters

=TASK_API(api_name, api_params, celladdress)

Advanced Options

Dynamic Parameter Values From Cells

You can populate parameter values dynamically by using curly braces

{...}

• Reference specific cells:

  "key1:{A2};key2:{B5}"

• Reference column headers:

  "product:{product};price:{price}"

  (using column headers when available)
• Reference the current row: When using a column header without a cell reference, the function uses the value from the current row.

Example:

=TASK_API("WeatherAPI", "city:{A2};units:metric", "C2")

Multi-Value Parameters (Arrays)

For APIs requiring array parameters, format them as JSON arrays. For example:

=TASK_API("ProductAPI", "product_ids:[123,456,789];format:json")

Extracting Specific Values With JSON Dropdown

After running TASK_API, a dropdown is automatically created in the adjacent column to help you extract specific fields from the JSON response.

1. Execute the function and wait for the dropdown to appear in the next column.
2. Select the desired field from the dropdown.
3. A

   JSON_EXTRACT

   formula is generated automatically to extract that value.

You can copy the dropdown to additional columns to extract multiple values from the same API response.

Examples

Example 1: Weather API

This example calls the Weather API template with "New York" as the location and uses metric units.

=TASK_API("WeatherAPI", "location:New York;units:metric", "C2")

Example 2: Stock Quote With Dynamic Parameters

This example looks up a stock quote for the ticker symbol specified in cell A2.

=TASK_API("StockQuoteAPI", "symbol:{A2}", "B2")

Example 3: Currency Conversion

This example converts an amount from a base currency to a target currency using dynamic cell references.

=TASK_API("CurrencyAPI", "from:{base_currency};to:{target_currency};amount:{C3}")

Example 4: Customer Data Lookup

This example looks up customer data based on the customer ID from the current row.

=TASK_API("CustomerAPI", "customer_id:{customer_id}")

Best Practices

• Organize your APIs by creating clear, descriptive names for your API templates.
• Test your API calls using the "Run" button in the sidebar before applying them broadly.
• Use column headers instead of specific cell addresses to enhance formula flexibility.
• Create a dedicated parameters table for complex APIs with multiple parameters.
• Document your API implementations with comments for easier maintenance.
• Handle errors gracefully to manage cases when the API does not respond as expected.
• Limit automatic recalculation in sheets with many API calls by using manual calculation mode.

Troubleshooting

API Template Not Found

• Verify that the API template has been correctly imported using the Import API form.
• Check for typos in the API name, noting that API names are case-sensitive.

Authentication Errors

• Ensure that any required API keys are correctly included in the API template.
• Verify that your API keys or tokens have not expired.
• Confirm that authentication headers are properly formatted.

Parameter Format Issues

• Confirm the parameter string follows the format "key1:value1;key2:value2".
• Ensure semicolons separate parameters and colons separate keys and values.
• For array parameters, check that they are correctly formatted as JSON arrays (e.g., [1,2,3]).

Response Issues

• Use the "Run" button to execute the function and check for any error messages.
• Verify that the API endpoint is accessible and responding correctly.
• Ensure that the expected JSON fields exist in the API response.

FAQ

How Do I Create An API Template?

Use the Import API form in the Sheet Workflows sidebar. Provide the API documentation, and the system automatically creates the template without any coding required.

Can I Modify An Existing API Template?

Yes, you can update existing API templates through the Import API form. The system manages the template structure automatically.

How Do I Pass Values From My Sheet To The API?

Pass values by referencing cells or column headers in your parameter string using curly braces. For example: "customer_id:{A2};status:active".

Can I Use This Function With Any API?

Yes, as long as the API accepts HTTP requests and returns data in a parsable format (such as JSON), you can use it with TASK_API.

How Do I Extract Specific Values From The API Response?

A dropdown automatically appears in the adjacent column after running TASK_API, allowing you to select and extract specific fields from the JSON response.

Does This Function Automatically Refresh?

TASK_API recalculates based on your sheet's calculation settings. You can also manually execute it using the "Run" button in the sidebar.

Is There A Limit To How Many API Calls I Can Make?

Google Sheets imposes quotas and limitations on API calls. For high-volume usage, consider implementing caching or batching strategies.

How Do I Handle APIs That Require OAuth Authentication?

For APIs requiring complex authentication, set up authentication separately and incorporate the tokens into your API template.