Unlock the Magic of AI in Google Sheets: How to Create Your Own ChatGPT Formula

In this guide, you'll learn how to create your very own custom formula. It’s easy, powerful, and might just make you the coolest person in your office (no guarantees).

Let’s face it—spreadsheets can feel about as exciting as watching paint dry on a Monday. But what if I told you that with a simple copy and paste, you could turn your Google Sheet into a bonafide AI assistant? Yep, we’re talking about creating a custom formula that allows you to get ChatGPT responses right in your spreadsheet. Intrigued? Good, because we’re about to upgrade your Google Sheets.

Why Use ChatGPT in Google Sheets?

Imagine this: you’re wrangling rows of customer feedback, brainstorming new taglines, or just need a quick poem about sales reports (hey, I don’t judge). Instead of hopping between apps, why not let the power of AI work for you right in your spreadsheet? With a custom askgpt() formula, you can process prompts, generate ideas, or extract insights in bulk—without ever leaving Google Sheets. Game changer!

---

What You’ll Need

1. Google account (obviously).

2. Google Sheets (obviously).

3. A ChatGPT API key from OpenAI. (Sign up at OpenAI if you don’t have one.)

4. Five minutes of your time and a willingness to impress yourself.

---

Step 1: Open the Script Editor

1. Open your Google Sheet.

2. Navigate in the menu bar to Extensions > Apps Script. This opens a code editor that looks a little intimidating but trust me—it’s friendlier than it seems.

---

Step 2: Write the Script for askgpt()

1. Delete the placeholder code in the editor.

2. Copy the code below and paste it into the editor:

   function ASKGPT(Input) {
     
     const GPT_API = "[Insert Your API Key with Quotes - No Brackets]";
     const BASE_URL = "https://api.openai.com/v1/chat/completions";
   
       const headers = {
         "Content-Type": "application/json",
         "Authorization": `Bearer ${GPT_API}`
       };
   
     const options = {
         headers,
         method: "GET",
         muteHttpExceptions: true,
           payload: JSON.stringify({
           "model": "gpt-4o",
           "messages": [{
             "role": "system",
             "content": ""
           },
           {
             "role": "user",
             "content": Input
           }
           ],
           "temperature": 0.3
         })
     }
     const response = JSON.parse(UrlFetchApp.fetch(BASE_URL, options));
     // console.log(response)
     console.log(response.choices[0].message.content);
     return response.choices[0].message.content
   }

3. Replace the [bracketed text] on the third line of the script with your own API Key which you can get from OpenAI.

---

Step 3: Save and Enable the Script

1. On the menu bar, click the floppy disk icon ‘💾’ to save your project.

2. Just to the right of the floppy disk icon, click on ‘▷ Run’

3. This first run will prompt you to authenticate your account by re-logging into Google to authorize your script (see the process below).

   

4. You have now activated you custom askgpt() formula. You can close the Apps Script editor.

---

Step 4: Use Your New Formula

1. Back in your Google Sheet, you can now use the formula in any cell like this:

   =askgpt("Write me a fun tagline for spreadsheets!")
   
   or
   
   =askgpt(A1)    
   [Write your prompt in cell 'A1']

2. Hit Enter, and voilà! ChatGPT's response will appear in the cell.

---

Pro Tips for Using askgpt()

• Keep it concise: Practice your prompt in ChatGPT to get it working the way you want before using it in Google Sheets.

• Experiment with settings: Modify max_tokens or temperature in the script to adjust response length or creativity. Adjust the Google App script you pasted into the editor earlier.

• Engineer the prompt: Concatenate text with content in other cells in the same row as shown in the example below:

  = "Using less than 260 characters, write a story about " & A2 & ". The tone should be " & A3 & " and " & A4 & "."
  
  If [A2] contained 'automation', [A3] contained 'friendly', and [A4] contained 'educational', then the prompt would be: 
  'Using less than 260 characters, write a story about automation. The tone should be friendly and educational.'

---

Wrap-Up

Congratulations! You just turned a humble spreadsheet into an AI powerhouse. Whether you're automating tasks, generating ideas, or simply showing off to your colleagues, askgpt() is here to take your spreadsheets to the next level.

Now go forth, fellow automators, and run ChatGPT in bulk rather than one prompt as a time. Please leave a comment letting everyone know how you used this custom formula.

Thanks for reading. Please subscribe if you want more content like this.