TASK_FORMAT

Function Overview

TASK_FORMAT is a custom function that generates formatting instructions for spreadsheet ranges using natural language prompts and AI models. It defines formatting operations to be applied to cells, allowing users to easily create professional-looking sheets without manual formatting.

Uses

1. Create consistent header styles with a single command (bold, centered, colored backgrounds)
2. Apply conditional formatting based on cell values (highlight cells above/below thresholds)
3. Format currency and percentage columns with proper number formats
4. Create visually organized dashboards with alternating row colors
5. Apply custom text formatting (font family, size, color) across specific ranges
6. Create heatmap visualizations from numerical data
7. Format date columns with consistent date patterns
8. Set text alignment and rotation for better readability

Syntax and Parameters

=TASK_FORMAT(header, range, prompt, [model], [freeze_range])

Advanced Options

Using Natural Language for Formatting

You can describe formatting in plain English. For example:

• "Make headers bold and blue with light gray background"
• "Format numbers in column B as currency with 2 decimal places"
• "Highlight cells in column C that contain values greater than 100 in red"

Freezing Rows and Columns

The freeze_range parameter not only prevents those cells from being modified, but also sets the frozen panes in your sheet. For example, setting freeze_range to "A1:C2" will freeze the first 2 rows and first 3 columns.

Model Selection

While you can use any supported language model, models with stronger reasoning abilities like "Gemini/gemini-1.5-pro" or "Anthropic/claude-3-opus" tend to produce better formatting results, especially for complex formatting instructions.

Examples

Basic Header Formatting

=TASK_FORMAT("Format Headers", "A1:F1", "Make these headers bold, centered, with a dark blue background and white text")

Conditional Formatting Based on Values

=TASK_FORMAT("Sales Highlighting", "B2:B20", "Highlight cells in green if greater than 5000, yellow if between 3000-5000, and red if less than 3000")

Creating a Professional Table

=TASK_FORMAT("Format Table", "A1:E10", "Create a professional-looking table with alternating row colors, bold headers, gridlines, and currency format for columns C and D")

Complex Formatting with Freezing

=TASK_FORMAT("Dashboard Format", "A1:G15", "Format as a dashboard with bold headers, right-aligned numbers, and date format in column A", "Gemini/gemini-1.5-pro", "A1:G1")

Best Practices

• Be specific and detailed in your prompt instructions for better formatting results
• Use descriptive header text to make it easy to identify tasks in your spreadsheet
• Always click the "Run" button in the Sheet Workflows sidebar after adding the formula
• Test formatting on a small range first before applying to larger datasets
• Include the specific number format type in your prompt if you need precise control (e.g., "format as currency with € symbol")
• For complex formatting needs, break it down into multiple TASK_FORMAT cells, each handling a specific aspect
• Specify font names, sizes, and colors explicitly when needed
• Use the freeze_range parameter to both protect important data and set frozen panes
• Save frequently used formatting patterns as templates you can reuse

Troubleshooting

Error: Invalid Range

Cause: The specified range doesn't exist or is malformed.
Solution: Verify that the range exists in your sheet and uses proper A1 notation (e.g., "A1:B10").

Error: Invalid JSON format

Cause: The formula has syntax errors or invalid parameters.
Solution: Check that all parameters are correctly entered and comma-separated.

Error: No Model Configured

Cause: No default model is set and no model parameter was provided.
Solution: Either specify a model in the formula (e.g., "Gemini/gemini-1.5-pro") or configure a default model in Settings.

Formatting Not Applied Correctly

Cause: Prompt instructions were ambiguous or too complex.
Solution: Make instructions more specific and break complex formatting into multiple TASK_FORMAT calls.

Some Cells Not Formatted

Cause: Range might include empty cells or cells with unexpected content.
Solution: Check if your range contains the expected data and adjust if needed.

Error: Format Error

Cause: The language model couldn't generate proper formatting instructions.
Solution: Simplify your prompt, try a different model, or break down your request into simpler instructions.

FAQ

How does TASK_FORMAT differ from Google Sheets' built-in formatting?

TASK_FORMAT allows you to describe formatting in natural language, handle complex conditional formatting, and apply multiple formatting attributes simultaneously. It's like having a design assistant for your spreadsheet.

Can I use TASK_FORMAT to copy formatting from one range to another?

Yes, you can use prompts like "Apply the same formatting as in range A1:B5" to instruct the model to replicate existing formatting patterns.

How detailed should my prompt be?

The more specific you are, the better the results. Instead of "make it look nice," try "make headers bold, centered, with light blue background, and apply alternating row colors to data rows."

Will TASK_FORMAT overwrite my existing formatting?

Yes, it will apply the new formatting to the specified range. If you want to preserve certain elements, specify that in your prompt or use the freeze_range parameter.

Can I use TASK_FORMAT for conditional formatting based on formulas?

Yes, you can describe formula-based conditions in your prompt, like "highlight cells where the value is greater than the average of the column."

How can I format multiple sheets at once?

Currently, TASK_FORMAT works on one sheet at a time. For multiple sheets, you'll need to create separate TASK_FORMAT calls for each sheet.

Is there a limit to how many formatting rules I can create?

While there's no hard limit in the function, Google Sheets itself has limits on conditional formatting rules. For very complex formatting needs, consider breaking it into multiple TASK_FORMAT calls.

Can I use TASK_FORMAT with data validation rules?

TASK_FORMAT primarily handles visual formatting. For data validation rules, you'll need to use Sheet's built-in data validation features.

Will my formatting persist if I share the sheet with others?

Yes, once applied, the formatting becomes part of the sheet and will be visible to anyone with access to the sheet.

How do I remove formatting applied by TASK_FORMAT?

You can use Google Sheets' Clear formatting option or create another TASK_FORMAT with instructions to reset formatting to default.