Functions Guide

1. AI_PROMPT

Purpose: Sends a text prompt to an AI model and returns the response.

Parameters:

- prompt (string): The text input for the AI model.

Example:

- Formula: =AI_PROMPT("What is the capital of France?")

- Result: The AI-generated response, "Paris".

Notes:

- Ensure an OpenAI API key is set in the sidebar settings.

- Returns an error message if no valid prompt is provided or if the API key is missing.

---

2. URL_SUMMARY

Purpose: Summarizes the content of a webpage using AI.

Parameters:

- url (string): The URL of the webpage to summarize.

- maxLength (number, optional): The maximum length of the summary in characters (default: 300).

Example:

- Formula: =URL_SUMMARY("https://www.example.com", 200)

- Result: A summary of the webpage content, limited to 200 characters.

Notes:

- Falls back to Perplexity if OpenAI fails or no OpenAI key is available. Ie for content that is not readable by Open AI: .pdf, heavy javascript sites or behind paywall

- Returns an error message if the URL is invalid or if no content can be fetched.

---

3. AI_LIST

Purpose: Sends a prompt to the OpenAI API to generate a list of responses and returns each item in its own row.

Parameters:

- prompt (string): The text input for the AI model to generate a list from.

- numItems (number, optional): The number of list items to generate (e.g., 5).

Example:

- Formula: =AI_LIST("Generate 5 creative blog titles for healthy eating",5)

- Result: A list of 5 blog titles, one per row.

Notes:

- Returns an error message if the prompt is invalid, the number of items is not valid, or if there are issues with the API call.

---

4. VIDEO_SCRIPT

Purpose: Generates a video script based on a summary of a webpage's content using AI.

Parameters:

- url (string): The URL of the webpage to extract content from.

- duration (number, optional): Desired length of the video script in seconds (default: 20).

- tone (string, optional): Tone of voice for the script (default: "neutral").

- hook (string, optional): Additional instructions for a catchy hook (default: "Include a catchy hook at the start to grab attention.").

Example:

Formula:  =VIDEO_SCRIPT("https://www.example.com", 45, "friendly", "include a negative hook to create fomo")

Result: A video script tailored for a 45-second video, generated from the webpage summary.

Notes:

- Uses the URL_SUMMARY function to retrieve a summary of the webpage before constructing the prompt for AI.

- Returns an error message if the URL is invalid, the content cannot be retrieved, or if an API key is missing.

---

5. SEO_EXTRACT

Purpose: Extracts specific SEO or marked-up elements from a webpage using regex-based extraction.

Parameters:

- url (string): The URL of the webpage.

- tag (string): The tag or element to extract. Supported values include:

"title" – The content of the <title> tag.

"meta description" – The content of the meta description.

"h1" – Text from <h1> tags.

"h2" – Text from <h2> tags.

"alt text" – Alt attributes of <img> tags.

"canonical" – The canonical URL specified in <link rel="canonical">.

"robots meta tag" or "robots" – Content of the robots meta tag.

"internal links" – Hyperlinks that lead to pages within the same domain.

"external links" – Hyperlinks to external domains.

Example:

Formula: =SEO_EXTRACT("https://www.example.com","h1")

Result: A comma-separated list of all H1 headings found on the page.

Notes:

- For tags that can return multiple values (e.g., h1, h2, alt text, links), the results are joined into a single comma-separated string.

- Strips out any residual HTML to return plain text.

- Returns an error message if the URL is invalid or if the webpage content cannot be fetched.

---

6. EXTRACT

Purpose: Extracts structured data (e.g., emails, company names) from text using AI.

Parameters:

- text (string): The text to extract data from.

- instruction (string): A natural language instruction specifying what to extract (e.g., "Extract all email addresses").

Example:

- Formula: =EXTRACT("John Doe <john.doe@example.com>, Jane Smith <jane.smith@example.com>", "Extract all email addresses")

- Result: A comma-separated list of extracted values, such as "john.doe@example.com, jane.smith@example.com".

Notes:

- Requires an OpenAI API key.

- Returns an error message if no valid text or instruction is provided.

---

7. TRANSLATE

Purpose: Translates a given text into the specified language.

Parameters:

- input (string): The text to be translated.

- targetLanguage (string): The target language code (e.g., "fr" for French, "es" for Spanish).

Example:

- Formula: =TRANSLATE("Hello, world!", "fr")

- Result: The translated text, such as "Bonjour, le monde!".

Notes:

- Uses Google Apps Script's built-in translation service. (Google Translate)

- Returns an error message if the input text or target language is missing.

---

8. ASSIGN_TAGS

Purpose: Assigns tags to content based on relevance or a predefined tag list.

Parameters:

- content (string): The content to analyze for tagging.

- tagList (array, optional): A predefined list of tags to consider (e.g., {"technology", "business", "health"}).

- numTags (number, optional): The number of tags to assign (default: 3).

Example:

- Formula: =ASSIGN_TAGS("Google announced a new AI model.", {"technology", "business"}, 2)

- Result: A comma-separated string of tags, such as "technology, business".

Notes:

- If no tagList is provided, the AI will generate tags dynamically.

- Ensures proper formatting with spaces after commas.

---

9. FORMAT

Purpose: Formats text to a specified capitalization style.

Parameters:

- input (string): The text or cell reference to format.

- formatType (string): The desired formatting style (UPPERCASE, LOWERCASE, TITLECASE, SENTENCECASE).

Example:

- Formula: =FORMAT("hello world", "TITLECASE")

- Result: "Hello World".

Notes:

- Supports four formatting styles:

- UPPERCASE: Converts all letters to uppercase.

- LOWERCASE: Converts all letters to lowercase.

- TITLECASE: Capitalizes the first letter of each word.

- SENTENCECASE: Capitalizes the first letter of the sentence.

10. Other Features:

- Auto-Replace Formulas: replaces formulas in selected cells with output text, if no cells are selected it will apply to the whole sheet. 

- Caching: All functions leverage persistent caching to improve performance by storing previously computed results. Limit is 500k storage. To clear cached results and refresh with new API call: Extensions > AI Sheets > Refresh Custom Functions

- Error Handling: Each function includes error handling to provide meaningful feedback in case of issues (e.g., missing API keys, invalid inputs).

- API Keys: Ensure that valid API keys are entered in the sidebar settings for functions requiring external APIs (e.g., AI_PROMPT, URL_SUMMARY, EXTRACT).

- Auto-Replace Toggle: replaces AI API formulas with result when turned on. This is useful when you are satisfied the custom AI formula results on test data and want to propagate for more inputs. By returning only the result there is no risk of additional API calls once content is created.

- Block API calls: Prevents further API calls