Using OpenAI’s GPT API with Google Sheets for Text Generation

Using OpenAI's GPT API with Google Sheets for Text Generation

Google Sheets can integrate OpenAI’s GPT API for text generation. Google Sheets App Script can create a custom function to generate text by calling OpenAI GPT. It allows you to leverage powerful text generation features directly within your spreadsheet. This GPT API integration is useful to automate content creation, summarize data, and much more. This post will show you the use of OpenAI’s GPT API with Google Sheets for text generation.

Get the OpenAI’s API Key

To use OpenAI’s GPT API you will need to copy it from your OpenAI account.

  • If you don’t have an account, sign up at OpenAI and get your key.
  • If you already have an account, log in to the OpenAI account.
  • From Dashboard >> select API Key >> click on Create new secret key.
  • In Create new secret key dialog box, insert a Name >> select Permissions >> click on Create secret key.

Using OpenAI's GPT API with Google Sheets for Text Generation

  • Copy the generated API key and store it in a safe place to authenticate your API requests. It won’t be visible once you create an API key.
  • Click on Done.

Using OpenAI's GPT API with Google Sheets for Text Generation

Never share your API key publicly or expose it in client-side code.

Creating Custom Function to Use OpenAI’s GPT API in Google Sheets

Now open the Google Sheets and create a custom function to use it for text generation by using OpenAI’s GPT API.

  • Open Google Sheets and create a new spreadsheet.
  • List the prompts or data in Column A that you want to use for text generation.
  • From Extension >> select Apps Script.
  • Insert the script to create a function that calls the OpenAI API to generate text based on prompts or cell references.
function callGPTAPI(prompt) {
  const apiKey = 'Your_OpenAI_API_Key'; // Replace with your OpenAI API key
  const url = 'https://api.openai.com/v1/chat/completions'; // Updated URL for chat models

  // API request data with chat-specific structure
  const requestData = {
    model: "gpt-3.5-turbo",
    messages: [{role: "user", content: prompt}], // Messages format for chat models
    max_tokens: 100, // Limit on the generated tokens
    temperature: 0.7 // Controls creativity
  };

  // Request options
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: `Bearer ${apiKey}` // API key for authentication
    },
    payload: JSON.stringify(requestData) // Converts data to JSON
  };
  try {

    // API call to fetch the response
    const response = UrlFetchApp.fetch(url, options);
    const json = JSON.parse(response.getContentText()); // Parse response
    return json.choices[0].message.content.trim(); // Extracts the text
  } catch (e) {

    // Handles errors and returns the error message
    return `Error: ${e.message}`;
  }
}
function GPTTextGeneration(inputText) {
  if (inputText === "") return "Input text is required"; // Validation
  return callGPTAPI(inputText); // Calls GPT API with input text
}

Explanation:

  • apiKey: Insert your own OpenAI API key for authentication.
  • url: OpenAI API endpoint for generating text.
  • requestData: Defines the model, prompt, max tokens, and creativity level (temperature).
    • model: Specifies the GPT model to use (e.g., “gpt-3.5-turbo”).
    • prompt: The input text that GPT will use to generate a response.
    • max_tokens: Controls how much text GPT generates. One token is roughly 4 characters.
    • temperature: Controls creativity. A higher value produces more varied results; lower values make output more deterministic.
  • options: Sets request method (POST), content type (JSON), and authorization (API key).
  • UrlFetchApp.fetch(): Sends the request to OpenAI.
  • JSON.parse(): Converts the response into JSON.
  • return json.choices[0].text.trim();: Extracts and returns the generated text.

Use the Custom Function:

  • Save the script then Run the script to allow access and go back to your sheet.
  • Now, insert the custom function in the sheet.  Quote the prompt in the function to generate text.

=GPTTextGeneration(“What is google sheet?”)

Using OpenAI's GPT API with Google Sheets for Text Generation

  • If you have a prompt column A, select any cell and insert the following formula.

=GPTTextGeneration(A2)

  • inputText: If you pass a cell reference (e.g., A2), the content of that cell will automatically be passed to this function.
  • The function checks if the input is valid (not empty or null), and then it calls the callGPTAPI() function with that input to generate text.

Using OpenAI's GPT API with Google Sheets for Text Generation

Things to Remember:

  • Ensure your API key is kept secure and not exposed publicly.
  • Be aware of the usage limits and pricing of the OpenAI API to avoid unexpected charges. OpenAI supports a limited free API for one-time use.
  • Generating text via the API may take some time, depending on the complexity of the request and the API’s load.

Conclusion

The above steps and explanation will help you to generate text by integrating OpenAI’s GPT API with Google Sheets. Google Sheets is effective for various text-generation tasks.  By creating custom functions, you can pass cell references to the GPT API in Google Sheets. This makes it easy to dynamically generate text based on inputs from different cells, enabling more flexible and automated workflows within your spreadsheet.

Leave a Reply

Your email address will not be published. Required fields are marked *