TASK_SHEET_UPDATE

Function Overview

The TASK_SHEET_UPDATE function generates a JSON-based task definition to manipulate spreadsheet data using a language model. When executed via the "Run" button in the sidebar, it processes specified ranges according to your prompt and modifies the sheet data intelligently.

Uses

1. Update product descriptions across multiple cells with consistent formatting and tone
2. Fill empty cells based on patterns in existing data
3. Fix spelling and grammar errors in a range of cells
4. Enhance text by making it more professional, concise, or engaging
5. Convert raw data into formatted content (e.g., turning bullet points into paragraphs)
6. Standardize inconsistent data entries across rows or columns
7. Generate content for empty cells based on header information
8. Transform data formats (e.g., rewrite dates, phone numbers, or addresses in a consistent format)
9. Translate content while preserving context and formatting
10. Analyze and update numerical data with calculations or conversions

Syntax and Parameters

=TASK_SHEET_UPDATE(header, range, prompt, [model], [image_url], [additional_sheets], [outputRange], [modify_existing_cells])

Advanced Options

Referencing Multiple Sheets

The additional_sheets parameter lets you include data from other sheets as context for the language model:

• Format: "SheetName(A1:B10);AnotherSheet(C1:D5);EntireSheet"
• Use semicolons (;) to separate multiple sheets
• For whole sheets, omit the range: "Sheet2"
• For specific ranges, use parentheses: "Sheet2(A1:B10)"

Output Range Control

The outputRange parameter determines where modified data appears:

• Format: "A1:B10" (must be a valid A1 notation range)
• If omitted, changes apply to the original input range
• The output range must have dimensions compatible with the expected output

Protecting Existing Content

Control whether existing cell content can be changed:

• modify_existing_cells = false (default): Only blank cells will be updated
• modify_existing_cells = true: All cells in the output range can be modified

Visual Context with Images

For multimodal models, the image_url parameter accepts:

• URLs to public images
• Google Drive image links (must have appropriate sharing permissions)
• Data URLs (base64-encoded images)

Examples

Standardizing Contact Information

Create a formula that standardizes inconsistent contact information:

=TASK_SHEET_UPDATE("Standardize Contacts", "A2:C20", "Standardize the phone numbers to format (XXX) XXX-XXXX, fix email formatting to lowercase, and ensure names are in Title Case", "Gemini/gemini-1.5-pro")

Data Enrichment with Context from Multiple Sheets

Use data from another sheet to enrich product descriptions:

=TASK_SHEET_UPDATE("Enhance Descriptions", "B2:B10", "Update these product descriptions to include details from the specifications sheet. Make them more compelling and highlight key features.", "Anthropic/claude-3-5-sonnet", "", "Specifications(A1:F20)")

Data Transformation with Image Reference

Transform data based on an image reference:

=TASK_SHEET_UPDATE("Chart Analysis", "C2:C10", "Update these sales projections based on the trend shown in the image. Increase declining categories by 5% and growing categories by 3%", "Gemini/gemini-1.5-pro", "https://drive.google.com/uc?id=YOUR_IMAGE_ID")

Filling Empty Cells While Preserving Existing Data

Generate content only in empty cells:

=TASK_SHEET_UPDATE("Fill Empties", "A1:B10", "Generate appropriate content for empty cells based on the row context and column headers", "", "", "", "", false)

Best Practices

• Always click the "Run" button in the Sheet Workflows sidebar to execute the function
• Be specific in your prompt about the exact changes you want to make
• Use column headers in your range to provide context to the language model
• Test on a small range first before processing large datasets
• Include example formats in your prompt for more consistent results
• If using additional_sheets, reference only what's needed to avoid overwhelming the model
• Set modify_existing_cells to false when you want to preserve existing data
• Use a model with multimodal capabilities when including an image_url
• When translating content, specify the target language clearly in your prompt
• Save frequently used task configurations as templates in separate cells

Troubleshooting

Error: No prompt provided

Cause: The prompt parameter is empty or undefined.
Solution: Ensure you've included a clear instruction in the prompt parameter.

Error: Invalid range format

Cause: The range notation is incorrect or not in A1 format.
Solution: Use standard A1 notation format like "A1:B10" for ranges.

Error: Sheet not found

Cause: A sheet name in additional_sheets doesn't exist in the spreadsheet.
Solution: Double-check the spelling of sheet names and ensure they exist in the current spreadsheet.

Error: Invalid sheet range

Cause: A range specified for an additional sheet is invalid.
Solution: Make sure all ranges use valid A1 notation like "A1:B10".

Error: Language model error

Cause: The language model failed to process your request properly.
Solution: Check if your API key is valid, try a different model, or simplify your prompt.

No changes appear in cells

Cause: The model output wasn't properly formatted, or modify_existing_cells is false while trying to modify non-empty cells.
Solution: Set modify_existing_cells to true if you want to update cells that already have content.

Results appearing in wrong location

Cause: outputRange is specified incorrectly or has different dimensions than expected.
Solution: Ensure your output range has appropriate dimensions to accommodate the results.

FAQ

How do I know which language model to use?

Choose a model based on your task complexity. For simple text transformations, models like "Gemini/gemini-1.5-flash" are fast and cost-effective. For complex reasoning or creative tasks, use "Anthropic/claude-3-opus" or "OpenAI/gpt-4".

Will TASK_SHEET_UPDATE modify my original data?

By default, it only modifies data in the range you specify, and only updates blank cells unless you set modify_existing_cells to true. You can also specify a different outputRange to leave original data untouched.

How large a range can I process at once?

This depends on your model's context window. Most modern models can handle hundreds of cells, but extremely large ranges might need to be broken down into smaller chunks. Start with modest ranges (under 100 cells) for best results.

Can I use TASK_SHEET_UPDATE with formulas?

TASK_SHEET_UPDATE works with formula results (displayed values) when reading data, but it will replace formulas with static values when writing. Consider copying formula results to a separate range before processing.

How can I make sheet updates more consistent?

Include example outputs in your prompt, be specific about formatting requirements, and consider creating a dedicated "instructions" cell that's included in your range to give the model clear guidance.

Do I need to configure API keys first?

Yes, you need to have configured API keys for the language model provider you intend to use. Set them up in the Sheet Workflows settings before using TASK_SHEET_UPDATE.

How can I save the generated task for later use?

The TASK_SHEET_UPDATE formula outputs a JSON string that's stored in the cell. You can copy this cell to save the task configuration, then use it later by clicking "Run" when that cell is selected.

Can I include images from the spreadsheet?

Not directly from cells. You need to use the image_url parameter with a URL to an externally hosted image or a Google Drive link with proper sharing permissions.

What happens if the language model makes a mistake?

You can always revert changes using Google Sheets' Undo feature (Ctrl+Z/Cmd+Z) or edit the prompt to be more specific and run the task again.

Does this function work with protected sheets or ranges?

No, TASK_SHEET_UPDATE cannot modify protected ranges or sheets. You'll need to temporarily unprotect them before running the function.

How do I format dates and numbers consistently?

Be explicit in your prompt about the desired format (e.g., "Format dates as MM/DD/YYYY and numbers with two decimal places"). Including examples in your prompt helps ensure consistency.

Can I use this in combination with other Sheet Workflows functions?

Yes, you can use TASK_SHEET_UPDATE as part of a larger workflow with other functions. The output from one function can serve as input to another.