LM

Function Overview

The LM function unleashes the advanced capabilities of AI language models directly in Google Sheets. It converts natural language prompts into actionable outputs—generating content, analyzing data, and answering questions—without leaving your spreadsheet.

Uses

1. Generate engaging content like blog posts, product descriptions, and social media updates.
2. Analyze data by summarizing information and extracting key insights.
3. Answer research questions and provide detailed explanations.
4. Translate text and refine language for various communication needs.
5. Create creative writing outputs such as stories, poems, and more.
6. Generate organized lists of ideas, steps, or items.
7. Produce programming code, formulas, or scripts.
8. Classify and categorize text data effectively.
9. Personalize responses based on customer or contextual data.

Syntax and Parameters

=LM(prompt, [model], [use_cache], [output_mode], [max_tokens], [temperature], [image_url])

Parameter	Data Type	Required	Description
prompt	string	Required	The instruction or question for the AI model. Supports cell references using curly braces (e.g., {A2} or {A} ).
model	string	Optional	The AI model to use in the format "provider/model_name" . If not specified, the default model is used.
use_cache	boolean	Optional	Set to true to cache results and reduce unnecessary API calls. Default is false .
output_mode	string	Optional	Controls result display: leave blank to return text in a single cell; use "list" for a vertical list or "hlist" for a horizontal list.
max_tokens	number	Optional	The maximum number of tokens allowed in the generated response (e.g., 500 ).
temperature	number	Optional	Adjusts the randomness of the output (from 0.0 to 1.0 ). Lower values yield more focused responses, while higher values produce creative variations.
image_url	string	Optional	A URL pointing to an image to include with your prompt (applicable for models that support vision features).

Advanced Options

Dynamic Cell References

You can make prompts dynamic by referencing other cells. Examples include:

• {A2}

  to insert the content of cell A2.

• {A}

  to use the value from column A in the active row.

• {Customer Name}

  to reference a column header matching that name.

• {Sheet2!A1}

  to reference a cell in another sheet.

Example:

=LM("Summarize the following customer feedback: {Feedback}")

Working with Lists

Use the output_mode parameter to format list outputs:

=LM("List 5 healthy breakfast ideas", , true, "list")

This returns each breakfast idea in its own cell vertically.

=LM("List 5 primary colors", , true, "hlist")

This returns each color in its own cell horizontally.

Caching for Performance

Enable caching to save API costs and improve speed:

=LM("Explain quantum computing in simple terms", "Gemini/gemini-1.5-pro", true)

Controlling Creativity vs. Precision

Adjust the temperature parameter for deterministic or creative outputs:

=LM("Write a formal business email to request a meeting", , , , , 0.1)

Produces a focused, consistent response.

=LM("Write a short poem about autumn", , , , , 0.9)

Generates a more creative, varied poem.

Multimodal Capabilities (Vision + Text)

For models with vision support, include an image URL:

=LM("Describe what's in this image and identify any issues", "Gemini/gemini-1.5-pro", , , , , "https://example.com/product_photo.jpg")

Examples

Basic Usage

Retrieves a simple factual answer.

=LM("What is the capital of France?")

Expected output:

The capital of France is Paris.

Content Generation

Generates a product description for an eco-friendly water bottle.

=LM("Write a short product description for an eco-friendly water bottle made of recycled materials.")

Data Analysis

Analyzes a specified data range using cell references.

=LM("Based on the data in {A2:B10}, what trends do you observe?")

List Generation With Vertical Output

Creates a vertical list of ideas.

=LM("List 5 ways to improve team communication", , true, "list")

Translation With Specific Model

Translates text from English to Spanish using a specific model.

=LM("Translate the following English text to Spanish: {C5}", "Gemini/gemini-1.5-pro")

Personalized Email With Row Reference

Generates a personalized email based on row data.

=LM("Write a thank you email to {Customer Name} for their purchase of {Product}. Mention that their order #{Order ID} will arrive by {Delivery Date}.")

Creative Writing With High Temperature

Produces a creative short story using a higher temperature.

=LM("Write a short story about a robot learning to paint", , , , 800, 0.9)

Image Analysis

Analyzes a product image for potential issues.

=LM("What's wrong with this product image?", "Gemini/gemini-1.5-pro", , , , , {Image URL})

Best Practices

• Be specific in your prompts to yield better results.
• Use cell references to provide context and increase output relevance.
• Choose the appropriate AI model based on your specific task.
• Enable caching for repeated queries to reduce API costs.
• Control output length with the max_tokens parameter.
• Start with lower temperature values for consistent results.
• Test formulas with a few sample rows before applying them broadly.
• Monitor rate limits and upgrade your API plan if necessary.
• Use array formulas carefully to avoid performance issues.
• Keep API keys secure and never share them publicly.

Troubleshooting

Unknown Provider Error

Ensure the provider name is entered correctly in the model parameter and that the corresponding API key is configured.

API Key Not Set

Verify that you have set the API key in the Sheet Workflows settings; otherwise, the function cannot make API calls.

Inconsistent or Unexpected Results

Adjust the temperature parameter to obtain more consistent outputs, and reword your prompt for clarity.

Slow Calculation

Enable caching by setting use_cache to

true

, use a faster model, or simplify your prompt to improve performance.

Cell Reference Issues

Confirm that cell references use the correct format, such as

{A1}

or

{Column Name}

, and that headers match exactly.

List Formatting Issues

Check that you have used the proper output_mode parameter (

"list"

or

"hlist"

) to format the response correctly.

Provider Response Failure

If you receive an error like "Failed to get response from [Provider]," check your internet connection, validate your API key, and ensure the provider is operational.

Image Analysis Failure

Make sure the model supports vision capabilities, verify the image URL is correct and accessible, and ensure the image format is supported.

FAQ

Which AI model should I use?

It depends on your task. Models like Gemini 1.5 Pro and Claude are versatile for complex reasoning, while others might specialize in speed or specific tasks. Experiment to determine which fits your needs best.

How much does it cost to use the LM function?

Costs vary by provider and model configuration. Many providers offer free tiers with usage limits, so check the pricing details of your chosen provider.

What’s the difference between "list" and "hlist" output modes?

The

"list"

mode displays results vertically (each item in its own row), whereas the

"hlist"

mode displays results horizontally (each item in its own column).

How can I make my LM results more consistent?

Use a lower temperature value (such as

0.1

–

0.3

), provide detailed and specific prompts, and consider using system prompts to set clear expectations.

Can I use the LM function in combination with other Google Sheets functions?

Yes! You can nest the output of LM with other functions, allowing powerful integrations of AI-generated content and traditional spreadsheet operations.

What’s the maximum length of text the LM function can process?

The limit depends on the AI model in use, but most models can handle several thousand tokens. For very large inputs, consider splitting the text.

Do I need to set up API keys for each model provider?

Yes. Each provider requires its own API key configuration, which must be set in the Sheet Workflows settings.

How does caching work with the LM function?

When use_cache is set to

true

, the function saves the result for that specific prompt and parameters so future calls with identical inputs can retrieve the cached response, reducing API usage.

Can I use the LM function in custom scripts or Google Apps Script?

The LM function is primarily designed for cell use within Google Sheets. For more advanced, programmatic applications, consider using the provider’s API directly in your scripts.

How can I process images that aren’t available via URL?

Upload your image to a web-accessible location (such as Google Drive with a shareable link) and use that URL in the image_url parameter.