Using the ChatGPT API to automate tasks

Ray Poynter, 13 August 2024

---

 

The web interface for ChatGPT is great, but it does not always deliver what we want. In this post I want to describe how creating a simple call to the ChatGPT API can give a range of benefits. The example I am going to share in this post comes from a course I am running on how to the ChatGPT API, check it out at.

What is the API?
API stands for Application Programming Interface and refers to the ability to access a program through a relatively simple interface. In the case of ChatGPT, you access the API by creating an account, by obtaining an API key, and then writing a routine or function in a programming language.

A simple call to ChatGPT
As an example, I have created a function in Google Sheets called ChatGPT. In this version, the user simply enters a question, and the function returns the answer.

For example, if I type ‘=chatgpt(“What is the capital of Austria?”)’, it returns “The capital of Austria is Vienna.” As you can see in the image below.

At this level the API has no benefits over the web interface, but it is reassuring to see it in action.

The next step in using the API
Instead of typing the question straight into the function call, we can make the spreadsheet more general-purpose by typing the question into an empty cell and asking the ChatGPT function to read that cell.

For example, if I type in Cell A1, “What is the Capital of Australia?” and call the function ChatGPT in cell B1 and ask it to read Cell A1, it will give me the answer, “The capital of Australia is Canberra,” as shown below.

With this approach, I can keep changing the questions in A1 and B1, which will show me the updated answer. For example, in the image below, I changed the question in A1 to ask about grocery retailers in Australia and received a list and description of six brands.

We now have a general-purpose tool where the user can ask any standard question and get an answer, from the safety and comfort of their own environment.

Expanding the function to a more typical Spreadsheet use
To illustrate how organisations can utilize this approach, let’s consider how we might use a spreadsheet to highlight personally identifiable information (PII). In many situations we do not want the data to have personal information because it raises privacy concerns.

In the example below, I have six hypothetical responses from an online survey, and I want to check if there is any PII in each row.

Obviously, with only 6 rows I would have no problem checking them manually, but if I had 100, or 1,000, or 10,000 I would not want to do it manually.

In the example below, I have called the ChatGPT function in cell C2, with a question that is written in cell C1: “Does this comment contain personally identifiable information?” and the text in B2. This generates the answer “The comment “Thank you for the survey, I enjoyed it” does not contain any personally identifiable information (PII). It does not reveal the identity of the individual or any specific details that could be used to identify them.”

When I look at the answer ins cell C2 I can see that it is too verbose. If there is no PII, I just want a ‘No’, but if there is PII, I want to know what it is. To get what, I want I edit cell C1, which automatically updates the answer in cell C2.

In the image below I have expanded the question in C1 to “Does this comment contain personally identifiable information? If there is no PII, just display No. If there is PII, display the PII.”. I have also filled the call to ChatGPT down for all of the rows. In the cells in column C, we see either No or we see the PII.

How to Create the Function ChatGPT
In this example, I created the function in Google Apps Script, a language provided by Google which is based on JavaScript. To access the ChatGPT API, you need to write some code, but it does not need to be Google Apps Script. Other popular options include Python, Java, C and R.

I have chosen Google Apps Script because it is widely available, free and integrates nicely with Google Sheets.

To get the code for the function ChatGPT, you can either Google it (there are lots of versions out there), or you can book a place on our Introduction to using the ChatGPT API course on 28 August, 2024 – see below.

An Introduction to using the ChatGPT API
With Will Poynter, I have created a course to introduce you to the ChatGPT API. The 90-minute online course will show you how to set up your API-enabled account, create functions like the ChatGPT one described above, and deploy it to start automating tasks.

As an attendee of the workshop, you will receive a briefing on how to set up your account and create the key function, a copy of the slides from the course, and a recording of the course. You do not need any programming skills to complete the tasks we will be covering, but some experience with macros would be helpful.

To buy a place on this course, click here.

Related