How to Use GPT in Google Sheets

I use Google Sheets every day, but incorporating OpenAI into Sheets has made it so much more impactful. From simple categorization to rewriting text, the chat endpoint is productivity gold. Here is a step-by-step process for how you can use #GPT with Google Sheets too.

I have a Google Sheet with 2 columns. One has brand names and the other has the brand category:

Brand	Category
OpenAI	Artificial Intelligence
Patagonia	Outdoor Clothing & Gear
Tesla	Electric Car Manufacturer

In Google Sheets, click on “Extensions” at the top of the page and “Apps Script”. We’re going to create a new function to replace the default one in there.

This is the code you can use in Apps Script in Google Sheets to create a function that will allow us to engage with the OpenAI API. Add this to a new .gs file in Apps Script (replace the code and save):

function CHAT(prompt) {
  // Replace with your OpenAI API key
  var apiKey = 'INSERT_KEY_HERE_PLEASE';
  
  // OpenAI API endpoint
  var apiUrl = 'https://api.openai.com/v1/chat/completions';

  // Set up API request headers
  var headers = {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer ' + apiKey
  };

  // Set up API request payload
  var data = {
    'model': 'gpt-3.5-turbo',
    'temperature': 0.5,
    'max_tokens': 40,
    'frequency_penalty': 1.5,
    'presence_penalty': 1.5,
    'messages': [
                {'role': 'system', 'content': "You are a sales copy specialist. You write about the best technology companies on the market. You write honestly. Write me a meta description less than 150 characters. Include a compelling call to action at the end."},
                {'role': 'user', 'content': prompt}
    ]
  };

  // Set up API request options
  var options = {
    'method': 'post',
    'headers': headers,
    'payload': JSON.stringify(data),
    'muteHttpExceptions': true
  };

  try {
    // Send API request and parse the response
    var response = UrlFetchApp.fetch(apiUrl, options);
    var jsonResponse = JSON.parse(response.getContentText());

    // Check if 'choices' exists in the response
    if (jsonResponse.choices && jsonResponse.choices.length > 0) {
      // Extract the chat completion
      var completion = jsonResponse.choices[0].message.content;

      // Return the chat completion
      return completion;
    } else if (jsonResponse.error) {
      // Return the error message from the API
      return 'Error: ' + jsonResponse.error.message;
    } else {
      // Return a general error message if 'choices' is not available
      return 'Error: Unable to retrieve chat completion.';
    }
  } catch (error) {
    // Return the error message
    return 'Error: ' + error.message;
  }
}

There are two fields to edit in the code above:

• Replace INSERT_KEY_HERE_PLEASE with your OpenAI API key (get that from here) but leave the single quotations
• In the “data” JSON object, modify the system’s “content” value to give core instructions to the AI

Save the code. Now we can run it in Google Sheets by using “=CHAT(prompt)”. The function takes one argument, “prompt” which, if you refer to the script we just pasted, you can see is set as the “content” value for the “user”. More info about the API is here:

https://platform.openai.com/docs/guides/chat/introduction

We’re going to combine with CONCATENATE to add more strings and reference the existing cells together: =CHAT(CONCATENATE(“Write me a meta description for a webpage about the brand, “,A2,” and reference it being a, “,B2,” business. Make sure it’s less than 150 characters.”))

You might have noticed one of our outputs was wrapped in quotations.

We could strip these out by amending the system prompt to see if we can request they not be used: ‘system’, ‘content’: “{rest of prompt} … Do not use quotations.”

And it worked! Playing with the inputs is key to refining your output.

Bear in mind, if you change the prompt or the script it will automatically recall the API so be sure to hardcode the output if you don’t want it to change: CMD + C (copy) CMD + Shift + V (paste values) Keep an eye on the API credits used at OpenAI. It’s very cost-effective.

And that’s it. Enjoy the productivity boost and be sure to sign up to my newsletter for more guides like this.