Learning How to use the ChatGPT API with Google Sheets & Apps Script

I write articles like this as part of my learning process. I may not explain every concept because that’s not what I was learning while writing the article. If you like learning with me and can Google, “What is an API,” then this is the right place for you! If my rambling inspired you, connect with me on LinkedIn and hit me up with any thoughts! I’m always interested in quick chats where you’re not trying to sell me links!

For about a year I taught an evening SEO course at HackerYou, now Juno College, and I got to chat with the students and teachers attending the coding bootcamps. One of the assignments given to the development students was to connect to the LCBO API to get information about different types of alcohol in Ontario. It’s taught because it is a super easy to use.

ChatGPT’s API is just as easy as the LCBO API. If a Juno College student with less than 3 months of total time programming can do it, so can we!

Both the LCBO and ChatGPT APIs are RESTful. I associate this type of API with not having a headache. So, good start!

Now, how hard is it to get onto the bleeding edge of technology?

Google Sheets & Google Apps Script

Most of my programming starts here. Here’s the quick tutorial to get set up:

  1. Go to Google Drive and create a new Google Sheet.
  2. In the menu, click “Extensions” and “Apps Script”
  3. Change the code to:
function myFunction(name) {
  return "Hello " + name;
  1. Hit Save.
  2. Back in the sheet, in A1, enter your name. In A2, enter =myFunction(A1)

Now you should see “Hello Troy” (or your name) as an output in A2.

What we just did was write some code that takes in a value and outputs some different value. You’re a developer Harry!

What’s cool about Google Sheets and Apps Script is that, if you’re like me, this is your default environment anyway. And what we can do now is take any little piece of knowledge we have or learn about programming and apply it immediately. No complex setup, no environments, no installing or updating, we’re just good to go.

Our First ChatGPT Call

If you have a trusting friend with ChatGPT Plus, ask them to get your their API key. Otherwise, you have to do the following.

  1. Sign up for ChatGPT Plus. This is $20 USD / month.
  2. Go to the Billing Overview and Add to credit balance under Pay as You Go. You could add as little as $5 here really.
  3. Under API Keys, click Create new secret key and copy it down. It should look something like: sk-proj-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.

Great, now let’s write our first ChatGPT call!

Back in the Apps Script window, delete all of the code and replace it with this and save it:


function hit_ai(system_prompt,user_prompt) {
    var requestOptions = {
      "method": 'POST',
      "headers": {
        "Content-Type": "application/json",
        "Authorization": `Bearer ${AIAPIKEY}`
      "payload": JSON.stringify({
        "model": 'gpt-3.5-turbo',
          {"role": "system", "content": system_prompt},
          {"role": "user", "content": user_prompt}

  var response = UrlFetchApp.fetch("https://api.openai.com/v1/chat/completions", requestOptions);
  if (response.getResponseCode() !== 200) {
    throw new Error("aiRequest Failed: " + response.getContentText());
  response = JSON.parse(response);

  return response.choices[0].message.content;

function animal_or_fruit_ai(text) {
  var system_prompt = `You will respond only with Animal, Fruit, or Neither.`
  var user_prompt = `${text}`;

  return hit_ai(system_prompt, user_prompt);

First we’ve defined our API key.

Make SURE you replaced that text with your actual bought or borrowed API key.

Then we’re building a function called hit_ai which accepts a system and user prompt, and then passes those on to ChatGPT and returns the response.

Lastly we’ve built a function called animal_or_fruit_ai. If you give it some text, it passes it onto ChatGPT and ChatGPT will tell us if what we entered was an animal, or a fruit.

When we’re building queries in the future we’ll likely make hit_ai a lot more robust and snazzier, and then all we’ll be changing is the function equivalent to animal_or_fruit_ai when we need to. That should take 5-10 minutes every time you need to solve a big problem!

In your spreadsheet:

  1. In column A, write out some names of animals, fruits, or something random.
  2. In B1, enter: =animal_or_fruit_ai(A1)
  3. Fill B1 down to the last row in which you have data in column A.

You should be getting results like this, where our ChatGPT function has correctly classified the words we added in column A:

Just to show you how amazing this is already, let’s duplicate this function to return a sentiment analysis by just altering the prompt.

Add this code at the bottom:

function sentiment_analysis_ai(text) {
  var system_prompt = `You will respond only with Positive, Negative, or Neutral. This represents the sentiment in the provided text.`
  var user_prompt = `${text}`;

  return hit_ai(system_prompt, user_prompt);

And try it on a couple of sample texts:

So… Here we’re doing bulk sentiment analysis just by changing a line of text. We’re not even really coding.

This is a very quick and very dirty example, but now I can do to spreadsheet data what I could have done in a ChatGPT chat.

More Complex ChatGPT API Queries

I used this learning to solve a real business problem, taking in over 240,000 characters of information to rename 150 products using detailed descriptions and meta data. It took a couple of hours of figuring things out, and 2 minutes for ChatGPT to run.

… But I can’t show you that; so let’s instead write SEO-Friendly Title Tags for each of these product and category pages. This is crude and for example use only.

To follow along, use spreadsheet data that looks like this:

Spreadsheet Data
Page NameInternal DescriptionPage CategoryKeywords
AnvilThe quintessential tool of the aspiring prankster, the ACME Anvil embodies durability and gravity-defying impact. Crafted from the finest iron alloys, this timeless classic promises a symphony of clangs and a crescendo of laughs. Whether you’re plotting the ultimate gag or in dire need of swift justice, the ACME Anvil delivers with unwavering reliability. From surprise birthday parties to impromptu talent shows, let the weight of this iconic device elevate your comedic genius to new heights.ProductAnvil for sale (250)
Buy Anvil online (180)
Heavy-duty Anvil (120)
Cartoon Anvil (90)
Prank Anvil (70)
ACME Anvil (200)
Anvil for gags (50)
Anvil for pranks (40)
Durable Anvil (110)
Anvil with free shipping (150)
Rocket Powered Roller SkatesStrap in and brace for exhilaration with the ACME Rocket Powered Roller Skates. Engineered for thrill-seekers and adventurers alike, these skates redefine the boundaries of speed and excitement. Powered by cutting-edge rocket propulsion technology, each stride propels you into a whirlwind of adrenaline-fueled velocity. Whether you’re chasing after a fleeting moment of glory or simply outrunning your mundane routine, these skates are your ticket to boundless escapades.ProductRocket Roller Skates (300)
Buy Rocket Skates (180)
Rocket Powered Skates (250)
ACME Rocket Skates (220)
Speedy Roller Skates (110)
High-speed Skates (90)
Rocket-powered Wheels (80)
Roller Skates for sale (200)
Thrill-seeking Skates (150)
Rocket Skates online (170)
Giant Kite KitSoar to new heights of fun and adventure with the ACME Giant Kite Kit. Designed to ignite imaginations and elevate leisure time, this kit is your gateway to the skies. Crafted from lightweight yet durable materials, each component is meticulously engineered to withstand gusts and glides effortlessly through the air. Whether you’re a novice dreamer or a seasoned kite enthusiast, the ACME Giant Kite Kit promises endless hours of airborne excitement and unforgettable memories.ProductGiant Kite for sale (200)
Buy Giant Kite online (150)
ACME Kite Kit (180)
High-flying Kite (120)
Kite Kit for beginners (90)
Outdoor Kite Kit (100)
Kite Kit with string (80)
Easy-to-fly Kite (110)
Giant Kite with handle (70)
Kite Kit assembly (60)
Artificial RockTransform your landscape into a rugged oasis with the ACME Artificial Rock. Crafted with meticulous attention to detail, each rock exudes the timeless charm of nature’s handiwork. Whether you’re concealing unsightly utilities or creating a captivating focal point, these faux rocks blend seamlessly into any environment. Made from weather-resistant materials, they withstand the test of time and the elements, ensuring years of aesthetic enhancement for your outdoor space.ProductArtificial Rock for landscaping (180)
Buy Artificial Rock online (140)
ACME Rock (160)
Faux Rock for sale (120)
Outdoor Rock decor (90)
Weather-resistant Rock (110)
Garden Rock cover (80)
Realistic Artificial Rock (100)
Rock disguise (70)
Rock camouflage (60)
DisguisesEmbrace the art of deception with the ACME Disguises collection. Whether you’re plotting a prank or attending a masquerade ball, these clever disguises are your key to anonymity and amusement. From whimsical masks to elaborate costumes, each ensemble is meticulously crafted to conceal your identity while showcasing your creativity. Unleash your inner chameleon and embark on a journey of disguise and delight with ACME’s unparalleled selection of costumes and accessories.CategoryDisguises for sale (180)
Buy Disguises online (150)
ACME Disguise collection (170)
Costume Disguises (140)
Masked Disguises (110)
Female roadrunner disguises (90)
Party Disguises (120)
Fancy Dress Disguises (100)
Secret Agent Disguises (80)
Creative Disguises (70)

The parameters for our SEO friendly (ish) title tags are that they:

  • Have to be under 60 characters in length
  • End with “ | ACME”
  • Use as many of the keywords in some variations as possible, with the top keywords being the most important to be included.
  • Use Title Case.
  • Be easy to read for humans.

So, let’s write our prompt and use some code to get this done.

function get_seo_title(pageName,description,category,keywords) {
  // If any variable is falsey just return nothing.
  if (!pageName || !description || !category || !keywords) {
    return "";

  var system_prompt = `We want to write SEO-Friendly Title Tags for each of these product and category pages. These Title Tags
- Have to be under 60 characters in length
- End with " | ACME"
- Use as many of the keywords in some variations as possible, with the top keywords being the most important to be included.
- Use Title Case.
- Be easy to read for humans.`
  var user_prompt = `
  Page Name: ${pageName}
  Description: ${description}
  Category: ${category}
  Keywords: ${keywords}

  return hit_ai(system_prompt, user_prompt);

This is what I get (I’ve run this a couple times to get different issues):

Page NameTitle
Anvil“Buy ACME Anvil Online for Heavy-Duty Gags | ACME”
Rocket Powered Roller Skates“ACME Rocket Roller Skates for Sale”
Giant Kite KitTitle Tag: Buy Giant Kite Kit Online for High-Flying Fun | ACME
Artificial Rock“ACME Artificial Rock for Landscaping | ACME”
Disguises1. Buy Disguises Online | ACME
2. ACME Disguise Collection for Sale | ACME
3. Creative Costume Disguises | ACME
4. Party & Fancy Dress Disguises | ACME
5. Masked & Secret Agent Disguises | ACME
6. Female Roadrunner & Creative Disguises | ACME

There are lots of problems already.

  1. The length is often much shorter than 60 characters.
  2. The ending isn’t consistent.
  3. The result is wrapped in quotation marks.
  4. The result is provided with “Title Tag:” at the beginning
  5. Multiple responses are provided.

Let’s try to resolve all of these.

function get_seo_title(pageName,description,category,keywords) {
  var system_prompt = `We want to write SEO-Friendly Title Tags for each of these product and category pages. These Title Tags
- Have to be under 53 characters in length but no shorter than 45 characters.
- Prioritized from top down, use as many of the words in the provided Keywords as possible. E.g. if "buy rocket-skates online" is the first keyword, use each of "buy" "rocket-skates" and "online" once in the title.
- Use Title Case.
- Be easy to read for humans.

Respond with three variations using only RFC8259 compliant JSON in this format including the character count and a selected best variation with the closest character count and solution:
    "TitleTagVar1": {
      "text": "Buy Giant Kite Kit Online for High-Flying Fun",
      "chars": 45
    "TitleTagVar2": {
      "text": "Buy Giant Kite Kit Online: High-Flying Kite Kit for Sale",
      "chars": 56
    "TitleTagVar3": {
      "text": "Buy Giant Kite Kit Online: Outdoor Kite Kit for Sale",
      "chars": 52
    "bestVar": "TitleTagVar2"

  var user_prompt = `
  Page Name: ${pageName}
  Description: ${description}
  Category: ${category}
  Keywords: ${keywords}

  var response = JSON.parse( hit_ai(system_prompt, user_prompt) );
  var bestVarKey = response[0].bestVar;
  var bestTitleTag = response[0][bestVarKey].text;
  return bestTitleTag + " | ACME";

Here’s the response:

Page NameTitle
AnvilBuy ACME Anvil Online: Heavy-Duty Anvil for Sale | ACME
Rocket Powered Roller SkatesHigh-speed Rocket Roller Skates for Sale Online | ACME
Giant Kite KitBuy Giant Kite Kit Online: High-Flying Fun for Sale | ACME
Artificial RockBuy Realistic Artificial Rock Online: ACME Garden Decor | ACME
DisguisesBuy ACME Disguises Online: Costume & Masked Disguises | ACME

None of the previous issues have arisen. Here’s what changed:

  1. The length is often much shorter than 60 characters.
    1. ChatGPT and AI in general seems to suck at hitting exact character counts. Asking for three variations doesn’t exactly or always hit it, but it does let the AI think a bit more step-wise and then evaluate its own work and suggest the best outcome.
  2. The ending isn’t consistent.
    1. Any consistent elements I’ve switched to manually adding. Here I removed 7 characters from the character count target and then appended the desired characters myself at the end.
  3. The result is wrapped in quotation marks.
    1. For formatting and character wrapping etc. I really like just forcing JSON as a response type, it’s eloquent and generally keeps undesirable characters out.
  4. The result is provided with “Title Tag:” at the beginning
    1. Again with JSON as a solution. The AI likes to sometimes tag its answers even if you’re pretty specific about the format you want your response in. With JSON it’s tagged already.
    2. I also used something called “Few Shot Training” here where I provided example responses in the format that I like.
  5. Multiple responses are provided.
    1. In this case I’ve specified the format and the number of responses I want. Actually if the AI makes a mistake and provides titleTagVar8 the code would still work.

ChatGPT API Tips & Tricks

Here were my biggest take-aways from my learning so far as it relates to what we covered here:

  • Default to JSON: Request every response to come in as JSON, even if I just want one response. It solves a lot of problems before they even arise, so I’m just defaulting to this for now.
  • Specify Format: Include the response format exactly as you want it to appear, don’t just ask for JSON.
  • Include Examples: Provide “Few Shot Training” (examples) in the prompt. To save space, include the examples in the prompt where you’re including the response format.
  • Multiple Attempts: Let ChatGPT try several times and pick its best solution. It’s fast and cheap to get multiple responses. Assume that it “thinks on paper,” so the selection of the best solution has to be last.
  • Use Code Checks: Use code where necessary and have ChatGPT try again. Not included in the example above, but if you regularly get specific issues, use your code to check for them and resubmit the request to the API and hope the next one works. Some quick examples:
    • Making claims. “Free Shipping,” “Same Day Delivery,” etc. come up pretty often in marketing-related queries. If they don’t apply in your situation, I’d ask to exclude them via the prompt but also use code to remove them as they’re a big no-no.
    • I’ve had “NoArgsConstructor” come in as a response for whatever reason.
    • Character count is a huge issue when it’s relevant. If it comes up a lot I’d add more steps to the request (give me 10 for example) and just check the character count in my code for the best response and send it back if it’s still too far off.
    • I sometimes use variables in my example format, e.g. “text”: “{{TEXT HERE}}”, and sometimes ChatGPT (especially 3.5) literally just returns {{TEXT HERE}}.
  • Cheapest Required Model: Use the lowest required GPT. This whole article is using 3.5 Turbo, and it would not have benefited greatly from using 4.0.

ChatGPT API Pricing & Costs

At the time of writing, pricing was $0.50 / 1M tokens for the prompt and $1.50 / 1M tokens for the completion (output) for 3.5-turbo. You can see how many tokens were used in a particular query in the object that it returns.

To keep this concise, I’ve been using the API for a month and made hundreds of queries, frequently with a lot of data provided and text in the prompt passed back and forth. I’ve spent a grand total of $0.08.

ChatGPT is dirt cheap for what it does, so much so that I’d encourage looking for ways to replace other APIs you use, as long as some hallucination won’t kill you. Try asking about:

  • Weather data: Respond in JSON with 1 or 0 for each day of March 2023 that it rained for most of the day in the GTA.
  • Currency Conversion: Respond in JSON with monthly currency conversion data from CAD to USD for 2023.
  • Map Data: Respond in JSON with the latidude and longitude map boundary coordinates for The Junction neighbourhood in Toronto.
  • Travel Time Data: Respond in JSON with the walking time from 1 Yonge St, Toronto, ON M5E 1E5 to each of: a grocery store, a park, a subway station.

I imagine that, frequently, this data can be cheaper and more accessible than getting it via specific APIs.

What’s Next?

I’ll be working on learning and writing about these two techniques:

  • Prompt Chaining: Writing a prompt and feeding its response into another. Ideally here I want to go a bit absurd, like chaining 5 prompts and responses together, or getting a hyper accurate solution to a problem that’s generally challenging for AI.
  • Fine Tuning / Training: Training a model with 50-100 examples of prompts and desirable and undesirable responses to get a better outcome from a prompt. I really want to learn the whole process of testing the accuracy of the responses, adding human validation and building a system to feed the human-validation back into the example prompts and model training. I especially want to come up with some benchmark numbers in my head for how many examples I’d need in a new project, how many times I’d need to run it (epochs?) and how much it would cost to train a model for a given task.

I don’t have a newsletter, so if this article inspired you, connect with me on LinkedIn and hit me up with any thoughts! I’m always interested in quick chats where you’re not trying to sell me links!

Leave a Comment

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