List of GEMINI Functions

Discover all available functions for GEMINI AI model in AI for Workspace, along with simple examples and an extensive list of paramaters.

Prerequisites

You have opened a Google spreadsheet and selected Extensions > AI for Workspace. To enable GEMINI AI functions, you must be the Creator of the spreadsheet.

GEM function

The simplest function to start using Gemini AI in Google Sheets. Outputs the result in a single cell.

How to use

Syntax	=GEM(prompt, [value], [temperature], [model])
Copy/paste example	=GEM(“Write a tagline for a cafe .“)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	A response to your prompt in a single cell

Parameters

Parameter Definition

Parameter	Definition
prompt (cannot be empty)	Instruction provided to the AI to generate a result in a single cell. The prompt parameter can be: A string: `"Write a tagline for a cafe."` A cell: `A1` A range of cells: `A1:C3`
(optional) value	Text, cell or range you want your prompt to apply to
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

prompt (cannot be empty)

Instruction provided to the AI to generate a result in a single cell. The prompt parameter can be:

A string: "Write a tagline for a cafe."
A cell: A1
A range of cells: A1:C3

(optional) value

Text, cell or range you want your prompt to apply to

(optional) temperature, model

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_LIST/GEM_HLIST function

GEM_List List the responses in a column /GEM_HLIST List the responses in a horizontal list of items.

How to use

Syntax	=GEM_LIST(prompt, [value], [temperature], [model])
Copy/paste example	=GEM_LIST(“Top 5 Google product names.“)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	As many responses to your prompt as you asked, one response per cell

Parameters

Parameter Definition

Parameter	Definition
prompt (cannot be empty)	Instruction provided to the AI to generate a result in a single cell. The prompt parameter can be: A string: `"Top 5 Google product names.."` A cell: `A1` A range of cells: `A1:C3`
(optional) value	Text, cell or range you want your prompt to apply to
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

prompt (cannot be empty)

Instruction provided to the AI to generate a result in a single cell. The prompt parameter can be:

A string: "Top 5 Google product names.."
A cell: A1
A range of cells: A1:C3

(optional) value

Text, cell or range you want your prompt to apply to

(optional) temperature, model

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_SPLIT/GEM_HSPLIT function

GEM_SPLIT Splits text based on input / GEM_HSPLIT function is used to horizontally split data

How to use

Syntax	=GEM_SPLIT(text, split_by, [temperature], [model])
Copy/paste example	=GEM_SPLIT(“Unlock the Power of Gemini: The Google Sheets Add-on that streamlines your workflows”, “nouns”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	Input text is split as requested, one element per cell

Parameters

Parameter Definition

Parameter	Definition
text (cannot be empty)	Input text to split. The text parameter can be: A string: `"Unlock the Power of Gemini: The Google Sheets Add-on that streamlines your workflows"` A cell: `A1` A range of cells: `A1:C3`
split_by (cannot be empty)	How to split the text. Examples: sections paragraphs sentences punctuation nouns adjectives
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

text (cannot be empty)

Input text to split.
The text parameter can be:

A string: "Unlock the Power of Gemini: The Google Sheets Add-on that streamlines your workflows"
A cell: A1
A range of cells: A1:C3

split_by (cannot be empty)

How to split the text. Examples:

sections
paragraphs
sentences
punctuation
nouns
adjectives

(optional) temperature, model

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_HSPLIT: The GEM_HSPLIT function is used to horizontally split data

GEM_FILL function

Fills or cleans a range

How to use

Syntax	=GEM_FILL(examples, [inputs], [temperature], [model])
Copy/paste example	=GEM_FILL(S4:U5,S6:S14)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	The empty part of the range will be filled based on the examples provided

Parameters

Parameter	Definition
examples	Range containing complete examples that the AI should learn from
(optional) inputs	Range containing incomplete data that the AI should complete from the examples
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_TRANSLATE function

Translates text from one language into another. Works better if you specify the source language.

How to use

Syntax	=GEM_TRANSLATE(text, [target_language], [source_language], [instructions],[temperature], [model])
Copy/paste example	=GEM_TRANSLATE(“Happy New Year!”, “spanish”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	The requested translation in a single cell

Parameters

Parameter	Definition
text (cannot be empty)	Input text to translate. The text parameter can be: A string: `Happy New Year!` A cell: `A1` A range of cells: `A1:C3`
(optional) target_language	Language of the output. Examples: “spanish”, “chinese”, “french”. Defaults to “english”.
(optional) source_language	Language of the input. Example: “english”
(optional) instructions	Additional instructions for the translation. Can include a dictionary of translations.
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_SUMMARIZE function

Summarizes text according to a given format.

How to use

Syntax	=GEM_SUMMARIZE(text, [format], [temperature], [model])
Copy/paste example	=GEM_SUMMARIZE(“some long content here”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	A summary in the requested format in a single cell

Parameters

Parameter Definition

Parameter	Definition
text (cannot be empty)	Input text to summarize. The text parameter can be: A string: `Some long content` A cell: `A1` A range of cells: `A1:C3`
(optional) format	Format of the summary. Examples: “key takeaways” “action items” “2 tweets” “two paragraphs” “100 words”
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

text (cannot be empty)

Input text to summarize.
The text parameter can be:

A string: Some long content
A cell: A1
A range of cells: A1:C3

(optional) format

Format of the summary. Examples:

“key takeaways”
“action items”
“2 tweets”
“two paragraphs”
“100 words”

(optional) temperature, model

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_EXTRACT function

Extracts data (like email addresses or company names) from a text. Outputs extracted values in a cell.

How to use

Syntax	=GEM_EXTRACT(text, to_extract, [temperature], [model])
Copy/paste example	=GEM_EXTRACT(“The major AI players in the market are OpenAI, Google & Amazon”, “companies”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	The extracted entities as values in a single cell

Parameters

Parameter Definition

Parameter	Definition
text (cannot be empty)	Input text to extract from. The text parameter can be: A string: `The major AI players in the market are OpenAI, Google & Amazon` A cell: `A1` A range of cells: `A1:C3`
to_extract (cannot be empty)	Text, cell or horizontal range specifying what you want to extract. Examples: “companies” C1:C4
(optional) temperature, model	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

text (cannot be empty)

Input text to extract from.
The text parameter can be:

A string: The major AI players in the market are OpenAI, Google & Amazon
A cell: A1
A range of cells: A1:C3

to_extract (cannot be empty)

Text, cell or horizontal range specifying what you want to extract. Examples:

“companies”
C1:C4

(optional) temperature, model

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_VISION function

Applies a prompt to an image. For example you can ask a question about the image, or ask for a description of it.

How to use:

Syntax	=GEM_VISION(prompt, image_url, [temperature])
Copy/paste example	=GEM_VISION(“Write a short description for this product”, A1)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	A response to your prompt according to the information extracted from the image

Parameters

Parameter Definition

Parameter	Definition
prompt (cannot be empty)	Instruction provided to the AI to analyze an image. The prompt parameter can be: A string: `Write a short description for this product` A cell: `A1` A range of cells: `A1:C3`
image_url	Link to the image you want the AI to analyze. Supported formats are PNG, JPEG, WEBP, HEIC or HEIF. The image_url parameter can be: A string: `https://lh3.googleusercontent.com/kIs4wRUAS_doZpi9cJE0Kn4vwR1yzcOHPzGX9ElAzCSLsNA52Es0Kb4oLUMFw-1intMQelINWuja6w=rw-lo` A cell: `A2`
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

prompt (cannot be empty)

Instruction provided to the AI to analyze an image.
The prompt parameter can be:

A string: Write a short description for this product
A cell: A1
A range of cells: A1:C3

image_url

Link to the image you want the AI to analyze. Supported formats are PNG, JPEG, WEBP, HEIC or HEIF. The image_url parameter can be:

A string: https://lh3.googleusercontent.com/kIs4wRUAS_doZpi9cJE0Kn4vwR1yzcOHPzGX9ElAzCSLsNA52Es0Kb4oLUMFw-1intMQelINWuja6w=rw-lo
A cell: A2

(optional) temperature

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_TABLE function

Outputs the results in a table. Very practical when the output is a table.

How to use:

Syntax	=GEM_TABLE(prompt, [head], [inputs], [temperature], [model])
Copy/paste example	=GEM_TABLE(“top 10 most eaten fruits and their nutrition data”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	A response to your prompt according to the information extracted from the image The best applicable category

Parameters

Parameter	Definition
prompt (cannot be empty)	Instruction to generate a table. The prompt parameter can be: A string: `top 10 most eaten fruits and their nutrition data` value (cannot be empty)
(optional) head	Range containing at least one row. the first row should be headers, the following rows can be used to specify examples If left empty, headers will be generated automatically.
(optional) inputs	Range containing partial rows to complete with GEM_TABLE categories (cannot be empty)
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_CLASSIFY

Classifies a given text into a single category.

How to use:

Syntax	=GEM_CLASSIFY(value, categories, [temperature], [model])
Copy/paste example	=GEM_CLASSIFY(“banana”, “fruit, vegetable”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	A response to your prompt according to the information extracted from the image The best applicable category

Parameters

Parameter Definition

Parameter	Definition
prompt (cannot be empty)	Input text to classify. The value parameter can be: A string: `banana` A cell: `A1` A range of cells: `A1:C3`
(optional) inputs	Comma-separated categories or range of categories to choose from. Only the most relevant category will be returned.
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

prompt (cannot be empty)

Input text to classify.
The value parameter can be:

A string: banana
A cell: A1
A range of cells: A1:C3

(optional) inputs

Comma-separated categories or range of categories to choose from. Only the most relevant category will be returned.

(optional) temperature

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_TAG

Applies user-defined tags to text or automatically suggests relevant tags if none are specified.

How to use:

Syntax	=GEM_TAG(value, [tags], [top_k], [temperature], [model])
Copy/paste example	=GEM_TAG(“I love chocolate”, “food, positive, negative”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	The applicable tags as csv in a single cell

Parameters

Parameter	Definition
value (cannot be empty)	Input text to tag. The value parameter can be: A string: `I love chocolate` A cell: `A1` A range of cells: `A1:C3`
tags (optional)	Comma-separated tags or range of tags applicable. If left empty, GEM_TAG will automatically come up with the tags
top_k (optional)	Maximum number of tags to return
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_FORMAT

Formats dates, currencies, addresses, names, etc. Fixes capitalization. And so much more.

How to use:

Syntax	=GEM_FORMAT(input, target_format, [source_format], [temperature], [model])
Copy/paste example	= GEM_FORMAT(“14 February 71”, “iso”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	The converted input in a single cell unless it is converted a table, which outputs a table

Parameters

Parameter Definition

Parameter	Definition
input (cannot be empty)	Input text to format. The value parameter can be: A string: “14 February 1971” A cell: `A1` A range of cells: `A1:C3`
target_format (cannot be empty)	Target format for the input. Examples: “iso”, “currency iso”, “international phone number”, “lowercase/uppercase/camelcase/snake case/title case/sentence case”…
(optional) source_format	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

input (cannot be empty)

Input text to format.
The value parameter can be:

A string: “14 February 1971”
A cell: A1
A range of cells: A1:C3

target_format (cannot be empty)

Target format for the input. Examples: “iso”, “currency iso”, “international phone number”, “lowercase/uppercase/camelcase/snake case/title case/sentence case”…

(optional) source_format

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_EDIT

Applies the given task to the given text. The default task is to fix grammar and spelling.

How to use:

Syntax	=GEM_EDIT(text, [task], [temperature], [model])
Copy/paste example	=GEM_EDIT(“For sum reezon thoose nunsberz arnt addin up”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	The edited text in a single cell

Parameters

Parameter Definition

Parameter	Definition
text (cannot be empty)	Input text to edit. The text parameter can be: A string: `For sum reezon thoose nunsberz arnt addin up` A cell: `A1` A range of cells: `A1:C3`
task (optional)	Text, cell or range specifying how the text should be edited. Defaults to fixing grammar and spelling. Other examples: “Make it funnier” “Make it shorter” “Make it sound formal” “Add an ending”
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

text (cannot be empty)

Input text to edit.
The text parameter can be:

A string: For sum reezon thoose nunsberz arnt addin up
A cell: A1
A range of cells: A1:C3

task (optional)

Text, cell or range specifying how the text should be edited. Defaults to fixing grammar and spelling. Other examples:

“Make it funnier”
“Make it shorter”
“Make it sound formal”
“Add an ending”

(optional) temperature

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_MATCH

Matches the values of two columns by similarity.

How to use:

Syntax	=GEM_MATCH(search_keys, lookup_range, [confidence], [stats], [top_k])
Copy/paste example	=GEM_MATCH(A1:A10,B1:B4)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	A column of the best matches to each value in the range

Parameters

Parameter	Definition
search_keys(cannot be empty)	The values that you want to look for in the range
lookup_range (cannot be empty)	The range in which to look for values that are similar to search keys
confidence (optional)	Set a similarity threshold: only values with a superior similarity score are matched. Must be comprised between 0 and 1
stats(optional)	“true” to display similarity scores
top_k (optional)	Number of matching values to return per search_key (default: 1 - only the best match)

GEM_SPAM

Classify if the given text is spam or non-spam.

How to use:

Syntax	=GEM_SPAM(text,[temperature], [model])
Copy/paste example	=GEM_SPAM(“Your text here”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	Spam text in single cell

Parameters

Parameter Definition

Parameter	Definition
text (cannot be empty)	Input text to edit. The text parameter can be: A string: `Your text here` A cell: `A1` A range of cells: `A1:C3`
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

text (cannot be empty)

Input text to edit.
The text parameter can be:

A string: Your text here
A cell: A1
A range of cells: A1:C3

(optional) temperature

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

GEM_FORMULA

Description about your Google Sheet formulas

How to use:

Syntax	=GEM_FORMULA(prompt,[temperature], [model])
Copy/paste example	=GEM_FORMULA(“I want to multiply the values in cells A2 and B2”)
⚠️ Localization	In some locales, you might need to use ”;” to separate parameters instead of ”,“
Output	Google Sheet formulas

Parameters

Parameter Definition

Parameter	Definition
prompt (cannot be empty)	Input formula description. The prompt parameter can be: A string: `I want to multiply the values in cells A2 and B2.` A cell: `A1` A range of cells: `A1:C3`
(optional) temperature	temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

prompt (cannot be empty)

Input formula description.
The prompt parameter can be:

A string: I want to multiply the values in cells A2 and B2.
A cell: A1
A range of cells: A1:C3

(optional) temperature

temperature is a number between 0 and 1. Use it to control creativity. Go to temperature and model to learn more.

More functions coming soon

…

Advanced Parameter

These parameters are present in most GEMINI functions. They are always optional.

For example with the GEMINI function, you can set:

the temprature to 0.1
the model to "gemini-1.5-flash"

You can also set these values from the sidebar very easily. In this case, they become the default values for all future executions.

Parameter	Definition
max output tokens	Maximum number of tokens that can be generated in the response. A token is approximately 4 characters. Specify a lower value for shorter responses and a higher value for potentially higher responses.
temperature	The temperature controls the degree of randomness in token selection. Lower temperatures are good for prompts that require a less open-ended or creative response, while higher temperatures can lead to more creative responses. A temperature of 0 means that the highest probability tokens are always selected. If the model returns a response that’s too generic, too short, or the model provides a fallback response, try increasing the temperature. Number between 0 and 1 that governs the creativity of the model: 0 - strictly follow the prompt 0.5 - slightly creative 1 - very creative Note: In some locales, you might need to use “0,5” instead of “0.5”.
model	Technical name of the model surrounded by quotes. The technical names are listed in the Model menu of the Default Settings section of the sidebar.
top K	Top-K changes how the model selects tokens for output. A top-K of 1 means the next selected token is the most probable among all tokens in the model’s vocabulary, while a top-K of 3 means that the next token is selected from among the three most probable tokens by using temperature. Specify a lower value for less random responses and a higher value for more random responses.
top P	Top-P also changes how the model selects tokens for output. Tokens are selected from the most to least probable until the sum of their probabilities equals the top-P value. For example, if tokens A, B, and C have a probability of 0.3, 0.2, and 0.1 respectively, and the top-P value is 0.5, then the model will select either A or B as the next token by using temperature, excluding C as a candidate. Specify a lower value for less random responses and a higher value for more random responses.