Integrating the OpenAI API into Google Sheets

Unlock AI Capabilities Within Your Spreadsheets

Many of us work with communities and artists who aren’t always comfortable using advanced functions in spreadsheets. To make things easier, we’ve been exploring ways to integrate artificial intelligence into Google Sheets, allowing for a more accessible and efficient workflow.

With a simple function, anyone can tap into OpenAI’s language models to streamline tasks like content generation, data analysis, and project planning—without needing to be a spreadsheet expert. This makes it easier for teams to collaborate, organize information, and get creative with their data.

For example, in a recent museum and art activity, we needed a way to quickly edit and generate data within our project spreadsheet. Leveraging AI directly in Google Sheets, we simplified the process and made it more intuitive. We thought it would be helpful to share some of these tips so others working with communities and artists can do the same!

Here’s an example we used in a spreadsheet and the approach we used.

The callMuseumPlanning Function: Your Gateway to AI in Sheets

We called this function “callMuseumPlanning” but you can call the function anything you want. The callMuseumPlanning function acts as a bridge between your Google Sheet and OpenAI’s advanced language models. This enables you to generate text for various applications, from drafting content to assisting with data analysis, all within the convenience of your spreadsheet.

Step-by-Step Guide: Implementing the AI Function in Google Sheets

Here’s a clear guide on how to set up and use the callMuseumPlanning function:

Step 1: Access the Script Editor: In your Google Sheet, navigate to “Extensions” in the top menu and select “Apps Script” . This action will open a new window where you can input code.   

Step 2: Paste the Function Code: Copy the following code block and paste it into the Apps Script editor, replacing any code that might already be present . This script contains the necessary instructions for Google Sheets to communicate with the OpenAI API.   

JavaScript

function callMuseumPlanning(prompt, modelName) {
  // Define your OpenAI API key. Replace this with your actual API key.
  const apiKey = 'enter your API key here';

  // 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.
  const payload = {
    model: modelName, // Model name (e.g., 'gpt-4'). This can be parameterized to use different models.
    messages:
  };

  // Configuration for the HTTP request.
  const options = {
    method: 'POST', // Corrected to uppercase 'POST'
    headers: {
      'Content-Type': 'application/json', // Corrected header for content type
      '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.';
  }
}

Step 3: Save Your Script: Click the save icon, which typically resembles a floppy disk, and assign a name to your script, for example, “OpenAIIntegration” . This saves the code you’ve added.   

Step 4: Make sure to input Your OpenAI API Key: Within the script, locate the line that reads const apiKey = 'enter your API key here'; and replace the placeholder text 'enter your API key here' with your personal API key obtained from OpenAI (https://platform.openai.com/) . This key is essential for the function to access OpenAI’s services.   

Step 5: Understanding the Sample Prompt: You provided an example of how to construct a prompt dynamically using the Concatenate function within Google Sheets:

=Concatenate("We need to write two very detailed paragraphs detailing how we will achieve the following tasks: ",ProjectInfo!B5,". Incorporate any notes from the following text: ",ProjectInfo!F5,". Write exactly what we have to do, why we have to do it and write as an arts administrator and humanize text to be brief, using no flashy buzzwords and always staying concise and to the point. No titles or extra words. Be tight.")

This formula works by:

• Combining different text elements into a single, coherent prompt for the AI.
• "We need to write two very detailed paragraphs detailing how we will achieve the following tasks: " sets the initial instruction for the AI.
• ProjectInfo!B5 refers to a specific cell (B5) in a sheet named “ProjectInfo,” which is expected to contain a description of the tasks you need assistance with. You can change this to your specific sheet and row/cell as needed.
• ". Incorporate any notes from the following text: " adds a further instruction to include relevant notes.
• ProjectInfo!F5 points to another cell (F5) in the “ProjectInfo” sheet, where you likely have supporting notes for the AI.
• The remaining text provides detailed guidance on the desired length (two paragraphs), style (brief, humanized, concise), tone (direct, no buzzwords), and formatting (no titles or extra words) for the AI’s output.

Step 6: Calling the Function in Your Spreadsheet: You also demonstrated how to call the callMuseumPlanningfunction using the following formula:

=callMuseumPlanning(ProjectQueries!F5, "o1")

Here’s a breakdown of this formula:

• =callMuseumPlanning(...) is the syntax for using the custom function you’ve added in Google Sheets.
• ProjectQueries!F5 is the first argument of the function. It refers to cell F5 in a sheet named “ProjectQueries,” where you would typically place the prompt you want to send to the AI. In your example, this cell would contain the result of the Concatenate formula from Step 5.
• "o1" is the second argument, specifying the name of the OpenAI model you wish to utilize . "o1" is one of the available models, and you can explore other models like "gpt-3.5-turbo" or "gpt-4" based on your specific requirements and the level of sophistication needed for your task . Ensure that the model name is enclosed in quotation marks.   

When you enter the formula =callMuseumPlanning(ProjectQueries!F5, "o1") into any cell in your Google Sheet, it will retrieve the prompt from cell F5 of the “ProjectQueries” sheet and send it to the OpenAI model named “o1”. The AI-generated response will then be displayed in the cell where you entered the formula.

Benefits: Enhancing Efficiency Across Various Applications

Integrating the OpenAI API into Google Sheets through this function offers numerous benefits for a wide range of users. It allows for the rapid generation of text for diverse purposes, such as drafting marketing materials, creating project summaries, or even assisting with data analysis by generating insightful reports. This can lead to significant time savings and provide a valuable tool for overcoming writer’s block or quickly extracting key information from data. The flexibility of this approach makes it a powerful asset for anyone looking to leverage AI to improve their productivity within a familiar spreadsheet environment.

This is just a simple example—there’s so much more you can do! You can build advanced AI-powered functions that automate workflows, analyze trends, or even generate personalized content at scale. Don’t be afraid to explore and experiment with different use cases—AI in spreadsheets opens up endless possibilities.