Have you ever wondered how you can turbocharge your Google Sheets experience? Look no further! With GPT for Sheets, you can unlock a whole new level of productivity and efficiency. In this article, we will explore all the amazing functions available in GPT for Sheets, along with simple examples and a comprehensive list of parameters. So, let’s dive in and discover the power of GPT for Sheets!
GPT Function
How to Use
Syntax
=GPT(prompt, [value], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
A response to your query in a single cell.
Parameters
| Parameter | Definition |
|---|---|
| prompt (cannot be empty) | The instruction provided to the AI to generate a result in a single cell. The prompt parameter can be: |
| – A string of characters: “Write a catchy headline for an ice cream shop.” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
(Optional) value – Text, cell, or range to apply your query upon.
(Optional) temperature, model –
temperature is a number between 0 and 1. Use it to control creativity. Check out the temperature and model to learn more.
Check out more examples of usage.
GPT_LIST / GPT_HLIST Function
Similar to GPT, but returns the results in a column. Very handy when the output is a list.
- Use GPT_LIST to return the options listed vertically.
- Use GPT_HLIST to return the options listed horizontally.
How to Use
Syntax
=GPT_LIST(prompt, [value], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
As many responses to your query as requested, one response per cell.
Parameters
| Parameter | Definition |
|---|---|
| prompt (cannot be empty) | The instruction provided to the AI to generate a list. The prompt parameter can be: |
| – A string of characters: “Give me 5 great short ads on spreadsheets.” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
(Optional) value – Text, cell, or range to apply your query upon.
(Optional) temperature, model –
temperature is a number between 0 and 1. Use it to control creativity. Check out the temperature and model to learn more.
Check out more examples of usage.
GPT_SPLIT / GPT_HSPLIT Function
Divides the text semantically, by section, paragraph, sentence, client…
- Use GPT_SPLIT to return vertically listed outputs.
- Use GPT_HSPLIT to return horizontally listed outputs.
How to Use
Syntax
=GPT_SPLIT(text, to_split_by, [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The input text is divided as requested, one item per cell.
Parameters
| Parameter | Definition |
|---|---|
| text (cannot be empty) | The input text to be divided. The text parameter can be: |
| – A string of characters: “Hello! How are you? Have you heard about GPT for work?” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
to_split_by (cannot be empty) – How to divide the text. Examples:
- sections
- paragraphs
- sentences
- punctuation
(Optional) temperature, model –
temperature is a number between 0 and 1. Use it to control creativity. Check out the temperature and model to learn more.
Check out more examples of usage.
GPT_FILL Function
Magically fills or cleanses a range from a few examples.
How to Use
Syntax
=GPT_FILL(examples, [entries], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The empty part of the range will be filled from the provided examples.
Parameters
| Parameter | Definition |
|---|---|
| examples | Range containing complete examples for the AI to learn from. |
| (Optional) entries | Range containing incomplete data that the AI needs to complete from the examples. |
(Optional) temperature, model –
temperature is a number between 0 and 1. Use it to control creativity. Check out the temperature and model to learn more.
Check out more examples of usage.
GPT_TABLE Function
Similar to GPT, but returns the results in a table. Very handy when the output is a table.
How to Use
Syntax
=GPT_TABLE(prompt, [header], [entries], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The response to your query in a table format.
Parameters
| Parameter | Definition |
|---|---|
| prompt (cannot be empty) | Instruction to generate a table. The prompt parameter can be: |
| – A string of characters: “Top 10 most consumed fruits and their nutritional data” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
(Optional) header – Range containing at least one row. The first row should contain the headers, and the following rows can be used to specify examples. If left empty, headers will be generated automatically.
(Optional) entries – Range containing partial rows to be completed with GPT_TABLE.
(Optional) temperature, model –
temperature is a number between 0 and 1. Use it to control creativity. Check out the temperature and model to learn more.
Check out more examples of usage.
GPT_MATCH Function
Matches the values of two columns based on their similarity. This function uses the text-embedding-ada-002 model.
How to Use
Syntax
=GPT_MATCH(search_keys, search_range, [confidence], [statistics], [top_k])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
A column containing the best matches for each value in the range.
Parameters
| Parameter | Definition |
|---|---|
| search_keys (cannot be empty) | The values you want to search for in the range. |
| search_range (cannot be empty) | The range in which to search for values similar to the search keys. |
| (Optional) confidence | Sets a similarity threshold: only values with a higher similarity score are matched. Must be between 0 and 1. |
| (Optional) statistics | “true” to display similarity scores. |
| (Optional) top_k | Number of matching values to return per search key (default: 1 – only the best match). |
Check out more examples of usage.
GPT_FORMAT Function
Formats dates, currencies, addresses, names, etc. Fixes capitalization. And much more.
How to Use
Syntax
=GPT_FORMAT(input, target_format, [source_format], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The conversion of the input into a single cell, unless it is converted into a table, in which case a table is returned.
Parameters
| Parameter | Definition |
|---|---|
| input (cannot be empty) | The input text to be formatted. The input parameter can be: |
| – A string of characters: “14 February 1971” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
| (Optional) target_format | The target format for the input. Examples: “iso”, “currency iso”, “international phone number”, “lowercase/uppercase/camelcase/snakecase/titlecase/sentencecase” … |
| (Optional) source_format | The source format of the input. Optional, but including it may yield better results. |
Check out more examples of usage.
GPT_EDIT Function
Applies the given task to the given text. The default task is to correct grammar and spelling.
How to Use
Syntax
=GPT_EDIT(text, [task], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The edited text in a single cell.
Parameters
| Parameter | Definition |
|---|---|
| text (cannot be empty) | The input text to be edited. The text parameter can be: |
| – A string of characters: “For some reason, these numbers don’t add up” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
| (Optional) task | Text, cell, or range specifying how the text should be edited. By default, corrects grammar and spelling. Other examples: |
| | – “Make it funnier” |
| | – “Make it shorter” |
| | – “Make it formal” |
| | – “Add an ending” |
Check out more examples of usage.
GPT_TAG Function
Applies user-defined tags to the text or automatically suggests relevant tags if not specified.
How to Use
Syntax
=GPT_TAG(value, [tags], [examples], [top_k], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The applicable tags as a CSV in a single cell.
Parameters
| Parameter | Definition |
|---|---|
| value (cannot be empty) | The input text to be tagged. The value parameter can be: |
| – A string of characters: “I love chocolate” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
| (Optional) tags | Comma-separated tags or range of applicable tags. If left empty, GPT_TAG will automatically suggest tags. |
| (Optional) top_k | Maximum number of tags to return. |
| (Optional) examples | Array of examples in two columns. The first column should contain the inputs, and the second column should contain the outputs. |
Check out more examples of usage.
GPT_CLASSIFY Function
Classifies a given text into a single category.
How to Use
Syntax
=GPT_CLASSIFY(value, categories, [examples], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The most relevant category.
Parameters
| Parameter | Definition |
|---|---|
| value (cannot be empty) | The input text to be classified. The value parameter can be: |
| – A string of characters: “banana” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
| categories (cannot be empty) | Comma-separated categories or range of categories to choose from. Only the most relevant category will be returned. |
| (Optional) examples | Array of examples in two columns. The first column should contain the inputs, and the second column should contain the outputs. |
Check out more examples of usage.
GPT_EXTRACT Function
Extracts data (such as email addresses or company names) from text. Returns the extracted entities as comma-separated values.
How to Use
Syntax
=GPT_EXTRACT(text, to_extract, [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The extracted entities as comma-separated values in a single cell.
Parameters
| Parameter | Definition |
|---|---|
| text (cannot be empty) | The input text from which to extract data. The text parameter can be: |
| – A string of characters: “I worked at Amazon.com for 5 years and then at Apple for 3 years” | |
| – 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” |
| | – B3:E3 |
Check out more examples of usage.
GPT_SUMMARIZE Function
Summarizes text according to a given format (three sentences by default).
How to Use
Syntax
=GPT_SUMMARIZE(text, [format], [temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
A summary in the requested format in a single cell.
Parameters
| Parameter | Definition |
|---|---|
| text (cannot be empty) | The input text to be summarized. The text parameter can be: |
| – A string of characters: “A long piece of content” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
| (Optional) format | The format of the summary. Examples: |
| | – “key points” |
| | – “action items” |
| | – “two paragraphs” |
| | – “100 words” |
| | – “tweets” |
Check out more examples of usage.
GPT_TRANSLATE Function
Translates text from one language to another. Works best if you specify the source language.
How to Use
Syntax
=GPT_TRANSLATE(text, [target_language], [source_language], [instructions],[temperature], [model])Example of Copy/Paste
⚠️ Localization
In some regions, you may need to use “;” to separate the parameters instead of “,”.
Output
The requested translation in a single cell.
Parameters
| Parameter | Definition |
|---|---|
| text (cannot be empty) | The input text to be translated. The text parameter can be: |
| – A string of characters: “Refresh yourself with our delicious treats!” | |
| – A cell: A1 | |
| – A range of cells: A1:C3 |
| (Optional) target_language | The output language. Examples: “Spanish”, “Chinese”, “French”. Default: “English”. |
| (Optional) source_language | The input language. Example: “English” |
| (Optional) instructions | Additional instructions for the translation. May include a translation dictionary. |
| (Optional) temperature, model |
These parameters are present in most GPT functions. They are always optional.
For example, with the GPT function, you can set:
- temperature to 0.1
- model to “gpt-3.5-turbo-0301”
You can also set these values from the sidebar very easily. In that case, they become the default values for all future runs.
| Parameter | Definition |
|---|---|
| (Optional) temperature | A number between 0 and 1 that governs the model’s creativity: |
| – 0 makes the GPT strictly follow the request. | |
| – 0.5 makes the GPT slightly creative. | |
| – 1 makes the GPT very creative. |
Please note that the list of functions and parameters may be subject to updates and changes. Always check the official GPT for Sheets documentation for the most up-to-date information. You can also visit the Crawlan.com website for additional tips and tutorials on Google Sheets.
