TASK_SHEET_UPDATE

Function Overview

TASK_SHEET_UPDATE enables you to manipulate spreadsheet data using natural language instructions processed by an AI language model. It allows you to perform operations such as data cleaning, generation, transformation, and analysis on your Google Sheets data.

Uses

1. clean data by fixing spelling mistakes and formatting inconsistencies.
2. generate data by filling empty cells with contextually appropriate content.
3. transform data by converting formats and reordering information.
4. analyze data by creating summaries and extracting patterns.
5. create content such as descriptions or names based on existing data.
6. format and structure data for improved readability and usability.

Syntax and Parameters

=TASK_SHEET_UPDATE(range, prompt, model, image_url, additional_sheets, outputRange)

Advanced Options

Working With Multiple Sheets

Use the additional_sheets parameter to include data from other sheets as context for the AI. This is useful when referencing data across multiple sheets.

=TASK_SHEET_UPDATE("A1:B10", "Format names in column A according to the format examples in Sheet2.", "Gemini/gemini-1.5-pro", "", "Sheet2(A1:C10)")

Specifying Output Range

By default, TASK_SHEET_UPDATE modifies the original range. Use the outputRange parameter to preserve the original data and place the results in a new location.

=TASK_SHEET_UPDATE("A1:B10", "Create a summary of each person's information.", "Gemini/gemini-1.5-pro", "", "", "C1:C10")

Using Images With Your Prompts

Include an image for visual context using the image_url parameter. This is particularly useful for tasks requiring visual references.

=TASK_SHEET_UPDATE("A1:B10", "Format the data according to the template shown in the image.", "Gemini/gemini-1.5-pro", "https://example.com/template_image.jpg")

Examples

Example 1: Basic Data Cleaning

This example corrects spelling mistakes and standardizes capitalization in product names.

=TASK_SHEET_UPDATE("A2:A20", "Fix spelling errors in these product names and ensure consistent capitalization.")

Example 2: Data Generation With Specific Output

This example reads company names from column A and generates professional email domains in column B without modifying the original names.

=TASK_SHEET_UPDATE("A2:B20", "Column A contains company names. Generate a professional email domain for each company in column B.", "", "", "", "B2:B20")

Example 3: Using Data From Multiple Sheets

This example fills in missing values in column C by referencing mapping rules from Sheet2 and historical data from Sheet3.

=TASK_SHEET_UPDATE("A2:C10", "Fill in missing values in column C based on the mapping rules in Sheet2 and historical data in Sheet3.", "", "", "Sheet2(A1:B20);Sheet3")

Example 4: Data Analysis With Image Reference

This example analyzes sales data in columns A-D and categorizes transactions according to a color-coded system provided via an image, then places the results in column E.

=TASK_SHEET_UPDATE("A1:D20", "Analyze this sales data and categorize each transaction according to the color-coded system shown in the image. Add the category in column E.", "Gemini/gemini-1.5-pro", "https://example.com/category_legend.jpg", "", "A1:E20")

Best Practices

• be specific: provide clear and detailed prompts to achieve accurate results.
• start small: test your prompt on a limited dataset before applying it to larger ranges.
• use appropriate ranges: include only the necessary data to avoid overwhelming the AI model.
• preserve original data: use the outputRange parameter to avoid overwriting your original data.
• choose the right model: select a model suited for the task complexity, such as Gemini/gemini-1.5-pro or GPT-4.
• add examples: incorporate format examples in your prompt or reference them from additional sheets.
• break down complex tasks: simplify operations into smaller, manageable steps.

Troubleshooting

Invalid Range Error

Ensure your range uses correct A1 notation (e.g.,

"A1:B10"

Unexpected Results

Rewrite your prompt to be more specific and include examples if necessary to clarify the desired output.

Large Range Issues

Break your task into smaller chunks when working with very large ranges, as AI models have token limits.

Misaligned Output

Specify column purposes in your prompt to ensure that data is placed correctly in the output.

Overwriting Original Data

Use the outputRange parameter to write results to a different location and preserve the original data.

Slow Execution

Reduce the size of your input range, choose a faster model, or divide the task into smaller parts.

FAQ

Can I Use This to Generate Formulas?

Yes, but for specialized formula generation, consider using the dedicated

TASK_FORMULA

How Much Data Can I Process at Once?

There are limits based on the AI model used. It is generally best to keep your ranges under a few hundred cells for optimal performance.

Will It Work With Formatted Cells And Data Types Beyond Text?

Yes, although TASK_SHEET_UPDATE primarily processes cell values. Specify in your prompt if you need to preserve or work with specific data types.

Can I Use This to Add New Rows Or Columns?

Yes, if you specify an outputRange that is larger than your input range, the function can generate additional rows or columns.

Does It Modify Formulas In Cells?

Yes, it can read and modify formulas. Be clear in your prompt whether you want to alter the formulas themselves or only their displayed values.

Can I Automate This Function?

While TASK_SHEET_UPDATE does not run automatically, you can set up a time-driven trigger in Apps Script to execute it on a regular schedule.

How Can I Ensure Data Security?

Sheet Workflows uses your configured API keys to communicate with AI providers. Review the privacy policies of your chosen provider to understand how your data is handled.