Google Sheet with LLM API

Published

December 30, 2024

This template allows you to easily integrate with various Large Language Models (LLMs) like GPT-4o, GPT-3.5 Turbo, and others, directly within Google Sheets. You can send prompts and receive responses without leaving your spreadsheet.

Step 1: Get the Template

  1. Open the Template Link: Click the following links to access the templates:

    1. OpenAI: CLICK HERE TO OPEN THE TEMPLATE
    2. Gemini: CLICK HERE TO OPEN THE TEMPLATE

  2. Use the Template: Click the blue “Use template” button in the top right corner of the preview page. This will create a copy of the template in your own Google Drive.

  3. Rename the Template: Rename the template to something meaningful, such as “Extract Name Entities from Memorials”.

Step 2: Authorizing the Script (Important!)

The first time you use the template, Google will display security warnings because the script needs authorization to run. This is normal for scripts that access external services (like the LLM API). Here’s how to safely authorize the script:

  1. Open the Sidebar: In your new copy of the sheet, go to the “LLM API” menu and select “Configure API”. A sidebar will appear on the right.

  2. Authorization Required: A dialog box will appear saying “Authorization required”. Click “Continue”.

  3. Choose an Account: Select the Google account you want to use with the script.

Workspace Accounts

Google Workspace accounts, such as .edu accounts from universities, might not work with this script. Personal Google accounts (e.g. .gmail.com accounts) are recommended.

  1. “Google hasn’t verified this app” Warning: You’ll see a warning saying that Google hasn’t verified the app. This is because the script is attached to your copy of the sheet and isn’t a public, widely-used application.
    • Click “Advanced”. This reveals more options.
    • Click “Go to [Calling LLM APIs with GSheet] (unsafe)”. The script name will likely be something like “Calling LLM APIs with GSheet”. It says “unsafe” because it hasn’t gone through Google’s formal verification process for public apps. However, it is safe to use in this context because you have a copy of the sheet in your account.
  2. Grant Permissions: A new window will appear listing the permissions the script needs. These permissions allow the script to:
    • See, edit, create, and delete your spreadsheets in Google Drive (to read your input and write responses)
    • Connect to an external service (to communicate with the LLM API)
    • Display and run third-party web content in prompts and sidebars inside Google applications (to show the sidebar interface)
  3. Click “Allow”.

Why is this safe?

  • You created a copy of the template, so you can review the script code if you wish.
  • The script only accesses the specific spreadsheet you’re using it in.
  • The script only connects to the LLM API you configure it to use.

After authorizing the script, you can proceed with the configuration.

Step 3: Configure the LLM API

  1. Fill in the Configuration Options:

    • API Base URL: Enter the base URL for the LLM API you want to use.
      • For OpenAI’s API, the default https://api.openai.com is already filled in.
      • If you’re using a different provider, such as DeepSeek, consult their documentation for the correct base URL.
    • API Key: Enter your API key for the chosen LLM.
      • You’ll need to obtain an API key from the LLM provider (e.g., OpenAI, DeepSeek). Keep this key secure!
    • Model: Select the specific LLM model you want to use from the dropdown.
      • The available models will depend on the API you’re using. We have options gpt-4o, gpt-3.5-turbo, deepseek-chat, etc.
    • System Prompt: This is a crucial instruction that sets the context and behavior of the LLM.
      • Craft this prompt carefully to guide the LLM’s responses.
      • Examples:
        • “Translate the content from English to Japanese.”
        • “Extract the official titles from the content. Use ‘,’ to separate the titles.”
        • “Write a summary of the content in a few sentences.”
    • Content Column Letter: Enter the letter of the column where you will input your prompts or content.
      • For example, if your content are in column A, enter A.
      • Only enter a single letter (A-Z).
    • Response Column Letter: Enter the letter of the column where the LLM’s responses will be written.
      • For example, if you want the responses in column B, enter B.
      • Only enter a single letter (A-Z).
    • Temperature (0-2): This setting controls the randomness of the LLM’s output.
      • 0 makes the output more deterministic and focused.
      • 1 (or higher, up to 2) makes the output more creative and varied.
      • 0.7 is a good starting point for most tasks.

Step 4: Prepare Your Data

  1. Add Your Content: In the column you specified as the “Content Column”, enter the prompts or content you want to send to the LLM.
    • Each prompt / content should be in a separate row.
    • Leave the first row empty. It’s reserved for headers.
    • You can type your prompt / content directly into each cell or use formulas to generate the prompts / content.

Step 5: Run the Process

  1. Click “Process Rows”: Once you’ve filled in the configuration and added your prompts, click the “Process Rows” button in the sidebar.

  2. Monitor the Progress: The button will change to “Processing…”, and the script will start sending your prompts to the LLM.

    • The responses will be written to the “Response Column” you specified.
    • A small delay is added between each request to avoid hitting API rate limits.

  3. Check the Status: After the processing is complete, the button will revert to “Process Rows”, and the status area below the button will display a message:

    • Success: “Processing complete” indicates that all prompts were processed successfully.
    • Error: If there’s an issue, an error message will be displayed. Common errors include:
      • Invalid API key
      • Incorrect column letters
      • Problems with the API server

Troubleshooting

  • “Invalid column letter(s). Please use letters A-Z.”: Double-check that you’ve entered a single capital letter (A-Z) for both the Content and Response columns.
  • “No data found in sheet”: Ensure that you have at least one prompt in your content column starting from the second row.
  • “API call failed” or other API-related errors:
    • Verify your API key is correct.
    • Make sure you have sufficient API credits/usage allowance.
    • Check the LLM provider’s status page for any reported issues.
  • “Error: TypeError: Cannot read properties of null (reading ‘message’)”: This error might indicate a problem with the API response format. Check the API documentation or try a different model.
  • Responses are cut off or incomplete: The LLM might be hitting a token limit. Try shortening your prompts or adjusting the model’s settings if possible.

Tips and Best Practices

  • Experiment with System Prompts: The system prompt is powerful. Spend time crafting it to get the best results.
  • Start with a Small Batch: Test with a few rows of data first to ensure everything is working correctly.
  • Use Formulas: Leverage Google Sheets formulas to create dynamic prompts based on other data in your sheet.
  • Be Mindful of API Costs: Each API call typically consumes credits. Monitor your usage and costs.
  • Consult the API Documentation: Refer to the documentation for your chosen LLM provider for detailed information on models, parameters, and limitations.

Example Use Cases

  • Name Entity Recognition (NER) : Extract name-entities from a text.
  • Data Analysis: Summarize data, extract key insights, or categorize information.
  • Translation: Translate text between different languages.

This template provides a flexible foundation for integrating LLMs into your workflows. Feel free to adapt and expand upon it to meet your specific needs!