Lesson 1: Understanding AI-Generated Content
What is ChatGPT?
ChatGPT is an AI tool that can generate text, including structured data and code, based on prompts.
How AI Generates Responses
When given a well-crafted prompt, ChatGPT can generate country-specific answers that we will use in our project.
Lesson 2: Writing Effective Prompts
Prompt to get an answer for each country
After getting the list in ChatGPT, we're going to need to get the answer to whatever question you'd like to ask each country. You could be simple and ask for each capital city or you may want a full article as an answer. A long way to do this would be to ask ChatGPT a question for each country like so:
In the country [COUNTRY NAME], what is the capital city? Provide a brief answer.However, this defeats the power and purpose of ChatGPT. One of the fantastic things about AI is that it remembers the information it has already given you. You can ask a follow-up question because it has listed all of the EU countries already. There is a lot of talk about getting the right prompt, and there is merit to that, but I find with ChatGPT that if you treat it like a personal assistant, you'll get the information you need in the format you need it, if you keep asking for it.
Let's take a real example that I did. I want to get a brief history of each country. You'll notice something in my prompt where I have used [COUNTRY] to denote it as a variable. In coding, a variable is a value that changes and in this case, I want ChatGPT to go through this prompt where the country changes each time. But again, don't worry about this!
I would like to ask a question and get the answer for each of the countries.
You can display it in a grid. My question is: Tell me a brief history of [COUNTRY] in less than 300 words.
The only instruction I gave was to put the information in a grid and have a word count of less than 300 words. I think I could have been more specific with the word count, i.e. between 250 and 300 words but here is what I got:
The annoying thing about this type of grid is that you can't copy and paste the cells so you're going to have to add another prompt. Sometimes it takes a couple of goes but what we want is a table we can copy and paste. Let's try this prompt and see what it gives us:
Display this as a table that I can copy and paste into a spreadsheet.
This gives the output in a different format which can be copied and pasted into your Google Sheet.
Once you paste it into Google Sheets (Tip, copy both columns to save yourself a headache) and you'll come up with this very messy looking spreadsheet!
Lesson 3: Automating with Google Apps Script (Optional)
Using OpenAI's API to Fetch ChatGPT Answers
🚨 WARNING 🚨 This section is for advanced users who want to use OpenAI's API. An API allows you to plug into ChatGPT's servers and ask it questions in a script. It is really powerful but does require a subscription to OpenAI, which at the moment is $20 a month. I'd recommend you skip this section until you gain more experience of working with ChatGPT. I use it a lot but I don't want to scare you off so click the button to move to Module 4 or scroll down if you're interested in seeing what it can do!
💃🏻
🕺🏼
🪩
🙋🏼 Still here? OK...here you go!
I'm going to have to assume you know how to add a script to a Google Sheet. If you don't I'll be going into detail in a later module so look into that before you come here. (I'm really trying to put you off! 😂)
Basically, this is the script to generate answers to any question. There are two scripts: askChatGPTForCountries and getChatGPTResponse. Paste it into Google App Scripts and you'll need to make the following changes:
- The variable prompt contains the question you want to ask. You'll see there are two variables here
surrounded by curly brackets: ${question} ${country} The variable ${question} is found in the cell B1
and is the start of the question. For example "What is the captial of "
The variable ${country} matches each of the countries in the first column. If your question is in a different format, such as "Tell me about ${country}'s flag." then you will need to change the format to ${question} ${country}'s flag. and you'll need to write "Tell me about"
Under all that code are extra instructions. You'll need to change them to suit your question. - On line 6, there's a line starting headers. You'll need to replace YOUR_API_KEY is, you've guessed it, your API key from OpenAI. (You'll have to google how to do that but it is easy)
- Further down, you'll notice a line max_tokens which equals 1000. 100 tokens ~= 75 words so switch it to whatever you think is best.
function askChatGPTForCountries() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var countriesRange = sheet.getRange("A2:A" + sheet.getLastRow()); // Get country names
var countries = countriesRange.getValues();
var question = sheet.getRange("C1").getValue(); // Get the question in C1
var apiKey = "YOUR_API_KEY"; // Replace with your OpenAI API key
if (!question) {
SpreadsheetApp.getUi().alert("Error: The question in C1 is empty.");
return;
}
var responses = [];
for (var i = 0; i < countries.length; i++) {
var country = countries[i][0]; // Get country name from the row
if (country) {
var prompt = `
${question} ${country}
- Output only the name of the capital city
- If there is a dispute about the capital city, output the generally accepted one
- Avoid calls-to-action
- Write in standard British English
`;
var response = getChatGPTResponse(prompt, apiKey);
if (response) {
responses.push([response]); // Store response in the array
} else {
responses.push(["Error"]); // Store an error message if the API fails
}
// **Prevent hitting OpenAI rate limits** by adding a 1-second pause
Utilities.sleep(1000);
} else {
responses.push([""]); // Leave blank if no country
}
}
// **Ensure there are responses to write**
if (responses.length > 0) {
Logger.log("Writing responses to sheet...");
sheet.getRange(2, 2, responses.length, 1).setValues(responses);
} else {
Logger.log("No responses to write.");
}
}
function getChatGPTResponse(prompt, apiKey) {
var url = "https://api.openai.com/v1/chat/completions";
var payload = {
model: "gpt-4",
messages: [{role: "user", content: prompt}],
max_tokens: 1000
};
var options = {
method: "post",
contentType: "application/json",
headers: {
Authorization: "Bearer " + apiKey
},
payload: JSON.stringify(payload)
};
try {
var response = UrlFetchApp.fetch(url, options);
var json = JSON.parse(response.getContentText());
Logger.log("Response received!"); // Debugging
return json.choices[0].message.content.trim();
} catch (e) {
Logger.log("API Error: " + e.toString()); // Log API errors
return "Error: " + e.toString();
}
}
Once that's done and you've put your question into cell B1, you can run the script. You may need to give Google permissions - just agree to everything! - and the function you want to select and run is: askChatGPTForCountries()
Let's the script run - it can take a couple of minutes - and you'll have your answers loaded up nicely in column B!
I told you to skip this section!