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;
}

4. Hit Save.
5. 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:

const AIAPIKEY = "{{PASTE YOUR API KEY HERE}}";

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',
        "messages":[
          {"role": "system", "content": system_prompt},
          {"role": "user", "content": user_prompt}
        ],
        "max_tokens":500
      })
    };

  var response = UrlFetchApp.fetch("https://api.openai.com/v1/chat/completions", requestOptions);
  if (response.getResponseCode() !== 200) {
    Logger.log(response.getContentText());
    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:

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):

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:

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!