TASK_API

Overview

TASK_API is a custom function in Sheet Workflows that enables users to execute API calls directly from Google Sheets.
This function creates a JSON string that can be executed via the Run button in the sidebar, which then makes the API request and places the results in the specified cell.
Before you can use an API, you first need to import it automatically using AI, you can use almost any api that you want, not limited to specific supported apis.

Syntax

=TASK_API(header, api_name, api_params, celladdress)

Parameters

• header (string, optional): Column header for the task template. Displayed above the JSON string in the cell.
• api_name (string, required): The name of the API template to use for the request.
• api_params (string, required): Parameters to populate the API template, in the format "key1:value1;key2:value2".
• celladdress (string, optional): The cell address where the result should be placed. If not specified, the cell to the right of the function cell is used.

Return Value

Returns a JSON string containing all the parameters, which can then be executed using the Run button in the sidebar.

How It Works

1. When TASK_API is called in a cell, it generates a JSON string with the function name and parameters
2. When the user clicks the Run button in the sidebar, the system:
   • Parses the JSON string
   • Loads the specified API template
   • Populates the template with the provided parameters
   • Executes the API request
   • Places the result in the specified cell

Example Usage

=TASK_API("Weather API", "WeatherAPI", "city:New York;units:imperial", "B5")

This example:

• Uses "Weather API" as the header text
• Loads the "WeatherAPI" template
• Populates the template with city="New York" and units="imperial"
• Places the result in cell B5

API Templates

API templates follow the Postman collection format and include:

• Request method (GET, POST, etc.)
• URL with placeholders for parameters
• Headers
• Body content (for POST/PUT requests)

Templates are stored in the user's properties and can be managed through the manage apis page.

Parameter Formatting

The api_params string supports several formats:

• Simple key-value pairs: "key1:value1;key2:value2"
• Array values: "items:[item1,item2,item3]"
• Dynamic placeholders: "cell:{A1};range:{B2:C5}"

When the API is executed, these parameters replace corresponding placeholders in the API template.

Error Handling

The function includes robust error handling for:

• Invalid JSON
• Missing or invalid API templates
• API execution errors
• Invalid cell addresses

Error messages are displayed in the output cell with details about what went wrong.

Tips

• Access the API Management and api import pages through the sidebar to create and edit API templates
• API keys are stored in configuration to avoid exposing them in sheets
• For complex API responses, use TASK_API with JSON_EXTRACT to process specific parts of the response

Limitations

• API requests are subject to Google Apps Script timeout limits (30 seconds)
• APIs requiring OAuth authentication are not supported
• Only APIs which return JSON responses are currently supported
• Binary responses are not supported yet