Free AI Integration in Google Sheets: (Step-by-Step Guide)

In today's data-driven world, spreadsheets remain an essential tool for organizing and analyzing information. But what if we could enhance their capabilities with the power of artificial intelligence, all for free? In this tutorial, we'll explore how to integrate Groq's generative AI into Google Sheets using Google Extension Apps Script. This powerful combination allows you to generate AI-powered responses directly within your spreadsheets, opening up a world of possibilities for data analysis, content creation, and more, without any additional cost, only limited with rate limit.

FULL CODE AT THE END

Showcase

Here in this screenshot showing three rows labeled CONTENT, PROMPT, and AI - REPLY. The CONTENT cell contains "Lewis Hamilton won his first World Driver Championship in F1". The PROMPT cell asks "Reply only the Year that it occurred".

This demo illustrates the simplicity and effectiveness of our AI integration:

Content Input: In cell B1, we've entered a fact about Lewis Hamilton's first World Driver Championship in F1.
AI Prompt: Cell B2 contains a specific instruction for the AI, asking it to reply with only the year of the event.
AI Response: In cell B3, we see the AI's response: "2008". This demonstrates how the AI accurately extracted the requested information from the given content.

This example showcases how you can use AI to quickly extract specific information from your data, answer questions, or generate insights based on the content in your spreadsheet cells. The possibilities are vast, ranging from data analysis and summarization to content generation and fact-checking.

Now that you've seen what's possible, let's dive into how you can set up this powerful tool in your own Google Sheets for free.

Step 1: Setting Up Your Environment

Open your Google Sheet: Start by opening the Google Sheet where you want to add AI capabilities.
Access the Apps Script editor:
- Click on "Extensions" in the top menu
- Select "Apps Script" from the dropdown
Set up your API key:
- Click on "Project settings" in the left-hand menu of the Apps Script editor
- Scroll down to the "Script properties" section
- Add a new property with the following details:
  - Property name: GROQ_API_KEY
  - Value: [Your Groq API key]
- Click "Save" to store your API key securely
- Tutorial to get GROQ API KEY here

Step 2: Understanding the Code and Using it

Copy these methods and replace it with App Script default code one by one in same order and save it

ThegetApiKey Function

The first function we'll explore is getApiKey, which retrieves the Groq API key from the script properties. This function is straightforward, using the PropertiesService to access the script properties and retrieve the value associated with the GROQ_API_KEY property.
```
 /**
  * Retrieves the Groq API key from the script properties.
  * @return {string} The Groq API key.
  */
 function getApiKey() {
   var properties = PropertiesService.getScriptProperties();
   var apiKey = properties.getProperty('GROQ_API_KEY');
   return apiKey;
 }
```

TheresolveCellReferences Function

The resolveCellReferences function is responsible for replacing cell references in the input prompt with their corresponding values. This function takes three parameters: inputPrompt, activeSheet, and activeCell. It uses a regular expression to match cell references in the input prompt and replaces them with the actual values from the active sheet.

 /**
  * Replaces cell references in the input prompt with their corresponding values.
  * @param {string} inputPrompt The original prompt.
  * @param {Sheet} activeSheet The active sheet.
  * @param {Range} activeCell The cell that called the function.
  * @return {string} The processed prompt with cell values.
  */
 function resolveCellReferences(inputPrompt, activeSheet, activeCell) {
   const cellPattern = /\b[A-Z]+\d+\b/g;
   return inputPrompt.replace(cellPattern, (match) => {
     try {
       // Attempt to retrieve the cell value and convert it to a string
       return activeSheet.getRange(match).getValue().toString();
     } catch (error) {
       // If the cell reference is invalid, return the original match
       return match;
     }
   });
 }

TheGROQ Function

The GROQ function is the main event, where the magic happens. This function takes two parameters: userPrompt and tokenLimit (optional, defaulting to 450). It's a custom function that can be called from a cell in your Google Sheet.

Here's a step-by-step breakdown of what the GROQ function does:
1. Retrieves the Groq API key using the getApiKey function.
2. Gets the active sheet and cell using the SpreadsheetApp service.
3. Resolves cell references in the user prompt using the resolveCellReferences function.
4. Validates the user prompt, returning an error message if it's empty.
5. Defines the API endpoint and request body for the Groq API.
6. Defines the API request options, including the API key, content type, and payload.
7. Sends the API request using the UrlFetchApp service and retrieves the response.
8. Handles API errors, returning an error message if the response code is not 200.
9. Parses the API response and extracts the generated text.
10. Returns the generated text, trimmed to remove any unnecessary whitespace.

    /**
     * Custom function to call Groq from a cell.
     * @param {string} userPrompt The input prompt for Groq, can include cell references.
     * @param {number} tokenLimit The maximum number of tokens for the response. Optional, default is 450.
     * @return The generated text from Groq.
     * @customfunction
     */
    function GROQ(userPrompt, tokenLimit = 450) {
      const API_KEY = getApiKey(); // Retrieve the Groq API key
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      const cell = sheet.getActiveCell();

      // Resolve cell references in the user prompt
      userPrompt = resolveCellReferences(userPrompt, sheet, cell);

      // Validate the user prompt
      if (!userPrompt) {
        return "Error: Please provide a prompt.";
      }

      // Define the API endpoint and request body
      const endpoint = 'https://api.groq.com/openai/v1/chat/completions';
      const requestBody = {
        'model': 'mixtral-8x7b-32768',
        'messages': [
          {'role': 'system', 'content': 'You are a helpful assistant.'},
          {'role': 'user', 'content': userPrompt}
        ],
        'max_tokens': tokenLimit
      };

      // Define the API request options
      const requestOptions = {
        'method': 'post',
        'contentType': 'application/json',
        'headers': {
          'Authorization': 'Bearer ' + API_KEY
        },
        'payload': JSON.stringify(requestBody),
        'muteHttpExceptions': true
      };

      try {
        // Send the API request and retrieve the response
        const apiResponse = UrlFetchApp.fetch(endpoint, requestOptions);
        const responseCode = apiResponse.getResponseCode();
        const responseText = apiResponse.getContentText();

        // Handle API errors
        if (responseCode !== 200) {
          return `Error: API returned status ${responseCode}. ${responseText}`;
        }

        // Parse the API response and extract the generated text
        const responseData = JSON.parse(responseText);
        return responseData.choices[0].message.content.trim();
      } catch (error) {
        // Handle any errors that occur during the API request
        return "Error: " + error.toString();
      }
    }

Step 3: Using the GROQ Function in Your Spreadsheet

Now that you've set up the script, you can use the GROQ function directly in your spreadsheet cells. The basic syntax is:

=GROQ(<Prompt> & <Content or Select Cell Content>)

Step 4: Advanced Usage and Tips

Dynamic prompts: You can create more complex prompts by combining multiple cells. For example:

text
```
 =GROQ("Summarize the following in " & B1 & " words: " & A1)
```
Adjusting token limit: The GROQ function allows you to specify a maximum token limit for the response. By default, it's set to 450, but you can change it:

text
```
 =GROQ(A1, 200)  // Limits the response to approximately 200 tokens
```
Error handling: The script includes error handling to provide informative messages if something goes wrong, such as API errors or invalid prompts.

Conclusion:

By integrating Groq's generative AI into Google Sheets, you've unlocked a powerful tool for enhancing your spreadsheet workflows. From generating summaries and analyses to creating content based on your data, the possibilities are vast. As you experiment with different prompts and use cases, you'll discover innovative ways to leverage AI within your spreadsheets, saving time and gaining new insights from your data.

Remember to use this capability responsibly and in compliance with any applicable data privacy and usage policies.

Supercharge Your Google Sheets with AI for FREE: A Step-by-Step Guide