TASK_ROW_UPDATE

Function Overview

TASK_ROW_UPDATE is a powerful custom function that intelligently updates the current row in a Google Sheet based on natural language instructions. It analyzes the current row data in context with the header row and uses a language model to apply specific changes to the values in the row according to your instructions.

Uses

  1. Update employee records with new contact information while maintaining existing data structure
  2. Translate content in the current row to another language
  3. Standardize format and style of data entries across specific cells
  4. Fill in missing data in a row based on values in other cells
  5. Convert data formats (e.g., dates, currency symbols, measurement units)
  6. Generate calculated values or summaries based on row data
  7. Clean and normalize text entries for consistency
  8. Enrich row data with additional information inferred from existing content
  9. Modify specific cells while preserving others based on custom logic

Syntax and Parameters

=TASK_ROW_UPDATE(header, prompt, model, modify_existing_cells)

Parameter Data Type Required Description
header string No Column header for the task template, used to identify what the task is about
prompt string Yes Natural language instructions describing how to update the current row
model string No Language model identifier in "provider/model_name" format (e.g., "Gemini/gemini-2.5-flash")
modify_existing_cells boolean No Whether to allow updating of non-empty cells (default: false)

Advanced Options

Current Row Awareness

TASK_ROW_UPDATE automatically identifies which row it's being called from. It extracts the header row (row 1) and the current row data, providing both to the language model for context. This ensures the model understands both the column structure and the current values before making changes.

Cell Value Protection

When modify_existing_cells is set to false (default), TASK_ROW_UPDATE will only populate empty cells in the current row, preserving any existing values. Set it to true to allow the function to modify all cells in the current row, including those with existing values.

Dynamic Column Detection

The function automatically detects and includes all columns up to the rightmost non-empty cell in either the header or current row. This ensures that all relevant columns are considered, even if some cells are empty.

Template Variable Support

TASK_ROW_UPDATE supports template variables in prompts. You can reference specific cells, headers, or include URLs using curly braces: {A1}, {Header Name}, {https://example.com}.

Examples

Example 1: Translating Content

=TASK_ROW_UPDATE("Translate", "Translate all text in this row to Spanish", "Gemini/gemini-2.5-flash")

All text cells in the current row will be translated to Spanish after clicking Run in the Sheet Workflows sidebar.

Example 2: Filling Missing Information

=TASK_ROW_UPDATE("Complete Row", "Based on the first name, last name, and company columns, populate the email column with a business email address", "Anthropic/claude-3-5-sonnet")

The function will generate and populate a business email address in the email column based on the name and company information after clicking Run in the sidebar.

Example 3: Standardizing Formats

=TASK_ROW_UPDATE("Standardize", "Format all phone numbers to the (XXX) XXX-XXXX pattern and ensure all product codes follow the ABC-12345 format", "OpenAI/gpt-4")

Standardizes formats across the row while preserving the existing data after clicking Run in the sidebar.

Example 4: Modifying Existing Content

=TASK_ROW_UPDATE("Update", "Capitalize the first letter of each word in the Title column and remove any HTML tags from the Description column", "Gemini/gemini-2.5-flash", true)

Updates existing text in the Title and Description columns with the specified formatting changes after clicking Run in the sidebar.

Example 5: Data Enrichment

=TASK_ROW_UPDATE("Enrich", "Based on the Country column, fill in the appropriate Currency and Time Zone columns", "Anthropic/claude-3-5-sonnet")

Adds relevant currency and time zone information based on the country value after clicking Run in the sidebar.

Best Practices

  • Always select empty cells in the column and click Run in the Sheet Workflows sidebar after creating your TASK_ROW_UPDATE formula to execute the row update
  • Keep prompts clear and specific about what cells to update and how they should be modified
  • Include the header parameter to add context about what the task is doing
  • Set modify_existing_cells to false (default) when you want to preserve existing data
  • Only set modify_existing_cells to true when you explicitly want to allow overwriting existing values
  • For complex transformations, consider breaking them into multiple TASK_ROW_UPDATE steps

Troubleshooting

Error: Invalid JSON

Cause: The JSON string generated by the function is malformed.
Solution: Check that the parameters are valid and try again with simpler parameter values.

Error: Could not detect last column

Cause: The function couldn't determine the rightmost column in the sheet.
Solution: Ensure your sheet has headers in row 1 and/or data in the current row.

Error: No model specified and no default model set

Cause: No model parameter was provided and no default model is configured.
Solution: Either specify a model in the function call or set a default model in the Sheet Workflows settings.

Error: No changes applied

Cause: When modify_existing_cells is false, but all cells already have values.
Solution: Set modify_existing_cells to true if you want to modify existing cell values.

Error: Changes not appearing

Cause: You haven't clicked the Run button in the Sheet Workflows sidebar.
Solution: TASK_ROW_UPDATE requires clicking Run in the Sheet Workflows sidebar to execute the changes.

Error: Provider not found

Cause: The specified provider in the model parameter doesn't exist or isn't configured.
Solution: Check the model parameter format (e.g., "Gemini/gemini-2.5-flash") and ensure the provider is set up.

FAQ

Do I need to click the Run button in the sidebar?

Yes, TASK_ROW_UPDATE returns JSON instructions that must be executed by clicking the Run button in the Sheet Workflows sidebar. The function itself doesn't modify the sheet until Run is clicked.

Will TASK_ROW_UPDATE change my existing data?

By default, no. When modify_existing_cells is set to false (the default), it will only populate empty cells. Set it to true if you want to allow modification of existing data.

Can I update multiple rows at once?

Yes, once the TASK_ROW_UPDATE is placed in the header column in row 1, you can run cells below in the columns in other rows to run the logic on those rows.
To update the entire sheet at once, you can also use TASK_SHEET_UPDATE

How does the function know which row to update?

It automatically detects which row the formula is running in and uses that as the current row for updates. When it runs, it copies the formula from row one to the current row with the empty cell you ran and runs it.

What models can I use with TASK_ROW_UPDATE?

You can use any language model configured in your Sheet Workflows settings. Format is "provider/model_name" (e.g., "Gemini/gemini-2.5-flash", "Anthropic/claude-3-5-sonnet", "OpenAI/gpt-4").

Can I reference other sheets or external data in my prompt?

Yes, you can use template variables like {Sheet2!A1} or {https://example.com} in your prompt to incorporate external data.

Will formulas in my row be preserved?

Yes, the function identifies and preserves formulas when modify_existing_cells is set to false. When set to true, formulas may be replaced with their calculated values.

Can I update specific columns but leave others untouched?

Yes, be explicit in your prompt about which columns to modify. For example: "Update only the Status and Priority columns based on the Description."

Elisha

1 week ago

Categories
Features
Made with ProductLift