Integrating OpenAI's API with Google Sheets: A Step-by-Step Guide

You can utilize the power of OpenAI's ChatGPT and DALL·E directly in your Google Sheets spreadsheet projects. Our easy-to-follow guide is designed to bring this cutting-edge technology into your everyday tasks, enabling you to generate text and create images using AI, all from the comfort of your familiar spreadsheet environment.

Whether you're new to the world of machine learning or you're an experienced practitioner, our tutorial is tailored to help you every step of the way. We'll show you how to get started by obtaining an API key, guide you through the simple steps to set up custom scripts, and demonstrate how to activate the full capabilities of ChatGPT and DALL·E within your own Google Sheets. This means you can analyze data, create content, and even generate images without juggling multiple applications or dealing with extra costs and privacy concerns from third-party services.

If you rather download a ready-to-use Google Sheets template, you can download the one we've built in this tutorial here, and then skip ahead to "Implementing the Text Generation Script in Google Sheets on your own" below. 

Getting the ChatGPT API to work within Google Sheets

Let's guide you through the process of using a custom script to connect OpenAI's ChatGPT API with Google Sheets enabling you to integrate AI directly from your spreadsheets without using a 3rd party GPT wrapper that comes with the expense of additional usage fees and less data privacy.

Implementing OpenAI's ChatGPT API with Google Sheets can significantly enhance data processing and analysis capabilities. By following this guide you can set up your own AI-powered spreadsheet functions tapping into the vast potential of machine learning and natural language processing within your personal spreadsheets.

Why Do It This Way?

There are several reasons why someone might choose to use their own OpenAI API key instead of relying on a pre-existing ChatGPT wrapper for Google Sheets that comes with a monthly subscription:

• Cost Control: Using your own API key allows you to directly manage costs based on your actual usage. Pre-existing wrappers with monthly subscriptions might be more expensive in the long run, especially if your usage varies significantly from month to month. Also, a Chat GPT subscription costs $20 a month, and this is a true pay-as-you go function if you're not using all the features of OpenAI's Chat subscription.
• Customization and Flexibility: By using your own API key, you have more control over the implementation. You can customize the script to suit your specific needs, such as modifying the request structure, handling responses in a certain way, or integrating with other tools and APIs.
• Updated Features and Models: OpenAI continuously updates its models and features. By using your own API key, you can quickly take advantage of the latest models and features without waiting for a third-party wrapper to update their service.
• Data Privacy and Security: When using your own API key, your data is sent directly to OpenAI, reducing the risk of exposing sensitive information to third-party services. This direct connection can be crucial for projects that handle confidential or proprietary information.
• Learning and Development: Implementing the integration yourself is an excellent learning opportunity. It allows you to gain a deeper understanding of how APIs work and how to interact with them, which can be beneficial for future projects.
• API Quota Management: With your own API key, you can monitor and manage your usage quotas directly from OpenAI. This way, you can adjust your usage patterns or upgrade your plan as needed, without being bound by the limitations or pricing tiers of a third-party service.
• Avoiding Dependency: Relying on third-party services can be risky, as their availability or pricing could change. Using your own API key ensures that your implementation is not heavily dependent on another service’s business model or operational stability.

Using your own OpenAI API key provides greater control, flexibility, and potential cost savings, while also offering a more secure and customizable solution tailored to your specific needs.

Additional Resources Before Starting

• OpenAI API Documentation
• Google Sheets Script Editor Guide

Step 1: Acquiring a ChatGPT API Subscription from OpenAI

To integrate OpenAI's ChatGPT with Google Sheets, you'll first need an API key from OpenAI. Here's how to secure one along with some best practices for API management and security:

 

Step-by-Step Guide to Getting Your API Key:

• Create an OpenAI Account: Sign up at OpenAI's official website. After following the directions on the login page you’ll be guided to API reference library.

• Set Up Your Account: Once your account is active, navigate to the API section. Here, you’ll find detailed instructions and options for different API subscriptions.
• Generate Your API Key: In your account dashboard under API Key, select the option to create a new API key. Follow the prompts to generate your key

• Secure Your API Key: Store your API key safely during the creation process. It's crucial for accessing OpenAI's services and should be treated like a password. Consider using a password manager or other secure means of storage.

Best Practices for API Management:

• API Quotas: Familiarize yourself with the usage limits of your OpenAI API key. Staying within these limits ensures uninterrupted service and helps manage costs.
• Data Privacy: Treat all data processed through the API with the utmost care. Adhere to best practices in data privacy and security to protect sensitive information.
• Stay Informed: Regularly check OpenAI's API documentation for updates. New features, model improvements, and changes in policy can impact how you use the API.

Step 2: Implementing the Text Generation Script in Google Sheets on your own

If you want to skip this step you can download our studio’s template here and all you have to do is punch in your API key

If you’d like to see how the sausage is made, let’s keep going.

Integrating ChatGPT with Google Sheets involves a few key steps within the Google Sheets script editor. Let's break these down:

Accessing the Script Editor:

• Open your Google Sheets document.
• Navigate to the menu bar and click on Extensions.
• From the drop-down menu, select Apps Script. This opens the script editor in a new tab.

Pasting the Code and API Key:

• Create a new script in your file tree.

• Copy the code script below and replace the existing code in the script editor:

/**
 * Generates text based on a given prompt using OpenAI's 
 * API and returns the genreation into the desired cell.
 * Optimized for GoogleSheets 
 * 
 * This script was developed by 
 * Audrey Love at Echo Echo Studio - January 2024
 * 
 */
function callGPT(prompt, modelName) {
  // Define your OpenAI API key. Replace this with your actual API key.
  const apiKey = 'Your OPENAI API key';

  // The API URL for OpenAI's chat completions endpoint.
  const apiURL = 'https://api.openai.com/v1/chat/completions';

  // Constructing the payload for the API request.
  // This structure can be modified as per the API documentation for different use cases.
  const payload = {
    model: modelName, // Model name (e.g., 'gpt-4'). This can be parameterized to use different models.
    messages: [{ role: 'user', content: prompt }] // Chat message structure.
  };

  // Configuration for the HTTP request.
  const options = {
    method: 'post', // POST method for sending data to the API.
    contentType: 'application/json', // Content type of the request.
    headers: {
      Authorization: 'Bearer ' + apiKey // Authorization header with the API key.
    },
    payload: JSON.stringify(payload) // Stringify the payload object to JSON format.
  };

  try {
    // Sending the request to the OpenAI API and capturing the response.
    const response = UrlFetchApp.fetch(apiURL, options);

    // Parsing the response to extract useful information.
    const responseData = JSON.parse(response.getContentText());

    // Check if the response contains the expected data.
    if (responseData.choices && responseData.choices.length > 0) {
      // Return the trimmed response from the model.
      return responseData.choices[0].message.content.trim();
    } else {
      // Handle cases where the response doesn't contain the expected data.
      throw new Error('Invalid response structure from OpenAI API');
    }
  } catch (error) {
    // Enhanced error handling to provide more detailed error information.
    Logger.log('Error calling GPT-4 API: ' + error.toString());
    return 'Error: ' + error.message + ' Please check the logs for more details.';
  }
}

• Rename your script to callGPT

• Locate the section in the script where the API key is required and paste your unique OpenAI API key there.

Script Notes:

• API URL: This script uses the ChatGPT API endpoint provided by OpenAI. Endpoints are different models made by OpenAI, chatGPT 4, ChatGPT 3.5 turbo, and DALLE are just a limited collection the studio has been utilizing as we grow with these newly available tools. A list of API endpoints can be found here.
• Payload and Options: The script prepares a request with your input (the prompt) and configuration for the API call. A payload is all the data that your pushing to the cloud and what is delivered in response. 
• Handling Responses and Errors: This script was written to processes the API's response and includes error handling for a smoother user experience. If anything goes wrong, you should get information about why in the individual cell of the Google Sheet and in the console editor.

Selective Project Sharing That Includes Your API Key:

• Selective Sharing: Avoid sharing your script with the API key embedded in it publicly.
• Useage: OpenAi provides a handy dashboard to monitor your API calls and see how much you've spent on computing. 

• Throttling: Be sure to set up usage limits on your API keys directly in OpenAI’s usage portal so spending doesn’t get out of control if you have a script go rogue. You can see above that this account is set up to hit a wall once it incurrs $120 in API fees. So far that seems hard to do for our mid-scale design studio - I think the most we've maxed out in a month is ~$30. 

Saving the Script:

• After making the necessary edits, click on the disk icon or go to File > Save.
• Name your project for easy identification.
• Close the script editor and return to your Google Sheets document.

Step 3: Using the Text Generator Function in Google Sheets

To use the script:

• Function Call: Type =callGPT("Your Prompt", "gpt-4") in a Google Sheet cell. In this function call, our prompt asks the ChatGPT to generate a fact about a nearby galactic entity. Who doesn't like learning about space while their testing their app script? 
• Model Flexibility: You can switch out "gpt-4" for a different model if desired. “gpt-3.5-turbo” is faster and is more affordable

• Receiving Responses: The AI's response will appear in the cell. If you want shorter or longer responses consider adding character limits to your prompts. For now, your API prompt can be up to 128k tokens long depending on your model selection (One token is ~4 characters or 0.75 words for English text)! That's a crazy amount of context you can offer for your prompt!
• Error Messages: If an error occurs, the script will provide a readable message. You can also use ChatGPT to help you diagnose API errors, it's pretty knowledgable about the API offering and how to help you jump barricades when you get errors about your cell's equation. 

Testing and Debugging

• Conduct a test run with a simple prompt. If there are issues, check the Apps Script console for any error logs.
• Use the View > Logs and View > Execution transcript options in the script editor to troubleshoot problems.
• Ensure that your Google Sheets API and OpenAI API permissions are correctly set up.

Note: We did find a repetitive GPT-4 API call error because Google Sheets only allows for 60 seconds for the API call to hang, if you are getting error messages that say things are taking too long, consider downgrading to GPT 3.5 Turbo - If you really need to use a model that may take more than a minute to generate a response, it's recommended that you outsource that function a cloud service like Google Cloud.

 

Step 4: Using the Image Generation Function in Google Sheets

After mastering text responses with ChatGPT, let's take it a step further by integrating image generation. This feature uses OpenAI's API to create images based on your prompts, directly in Google Sheets.

The Image Script:

• Return to the Script Editor in Google Sheets and create a new script. You can find the image API code below or download the existing template here.  

/**
 * Generates an image based on a given prompt using OpenAI's 
 * API and returns the image URL. 
 * This script was developed by 
 * Audrey Love at Echo Echo Studio - January 2024
 * 
 * @param {string} prompt The text description for the 
 * image generation.
 * @return {string} URL of the generated image 
 * or an error message.
 */
function generateImage(prompt) {
  // Replace with your actual OpenAI API key.
  const apiKey = 'YOUR OPEN AI KEY';

  // Endpoint URL for OpenAI's image generation API.
  const apiURL = 'https://api.openai.com/v1/images/generations';

  // Setting up the payload for the POST request.
  // Adjust the model, size, and other parameters as needed.
  const payload = {
    model: "dall-e-2",        // Model used for image generation. Change as required.
    prompt: prompt,           // Using the function's argument as the prompt.
    n: 1,                     // Number of images to generate.
    size: "1024x1024",        // Size of the generated image. Other options available.
    response_format: "url"    // Setting response format to get the image URL.
  };

  // Configuring the HTTP request options.
  const options = {
    method: 'post',            // POST method for sending data.
    contentType: 'application/json', // Content type of the request.
    headers: {
      Authorization: 'Bearer ' + apiKey // Authorization header with the API key.
    },
    payload: JSON.stringify(payload)  // Convert the payload object to JSON format.
  };

  try {
    // Sending the POST request to OpenAI API and getting the response.
    const response = UrlFetchApp.fetch(apiURL, options);
    const responseData = JSON.parse(response.getContentText());

    // Checking and returning the image URL from the response.
    if (responseData.data && responseData.data.length > 0) {
      return responseData.data[0].url;
    } else {
      // Handling cases with no image data in the response.
      throw new Error('No image data found in response');
    }
  } catch (error) {
    // Logging the error and returning an error message.
    Logger.log('Error generating image: ' + error.toString());
    return 'Error: ' + error.message;
  }
}

• Title this script generateImage.
• This script is specially designed to interact with OpenAI's image generation API, to learn more about this model check out the OpenAI API documentation page, there are some great tips in the image section and this is where all updates are reflected.

Script Notes:

• API Key and URL: These lines connect your script to the OpenAI image generation service.
• Payload Configuration: Here, you set your image prompts and parameters. Feel free to tweak these settings.
• Error Handling: The script is designed to manage responses from the API, handling any errors gracefully with error data showing up in the cell or in the terminal of the Google App script window.

Testing the Feature:

• Once you've saved the updated script, try generating an image. Use the formula =IMAGE(generateImage("Your prompt")) in a Google Sheets cell.
• Watch as the function creates an image based on your input. 
• To freeze the cell, cut the contents from the cell and paste the contents with value only mode selected, or by pushing “Shift + command/ctrl + V” on your keyboard

 

With this feature, the possibilities are endless. You can generate creative visuals for data presentations, educational content, and much more, all within Google Sheets! This capability is ripe for innovation by other creative technologists and professional problem solvers who love tinkering with data visualizations. If you use this tutorial, we want to hear from you!

Bonus: Generate Custom Images Based on Dynamic Prompts

In testing, it became clear that you may need to have a very detailed image prompt, and we found that if we provided a few context clues, the callGPT function did an excellent job at creating image prompts for the generateImage function. 

Below shows one way to creatively generate custom images in Google Sheets using callGPT and generateImage working together:

• Prepare a List: In Column A, list down the names of your desired subject - in this example we’re using wildflowers.
• Generate Image Prompts:
• In Column B, next to each wildflower name, we'll use callGPT to create a descriptive image prompt.
• Enter the formula in the corresponding cell of Column B: =callGPT("Generate an image prompt for a " & A2, "gpt-4"). Drag this formula down alongside your list.
• Create AI-Generated Images:
• Now, in Column C, we'll translate these prompts into images.
• Use the formula in the first cell of Column C: =IMAGE(generateImage(B2)). Again, drag this formula down to match your list in Column A.

And that's it! You now have a dynamic setup where:

• Column A holds your wildflower names (or whatever subject matter you desire).
• Column B creates detailed, AI-generated prompts for each flower.
• Column C brings these prompts to life with AI-generated images.

Thank You!

Hopefully, this tutorial has emboldened you to try learning something new and how to better utilize the ChatGPT API outside of just in-app conversations with ChatGPT on OpenAI’s website. 

Lastly, we’d love to hear from you if you need any help with this implementation or any other special support for a ChatGPT-enabled project, leave a comment below, or reach out to us via our Contact Page.