Auto-Marking Student Work with GPT-5 Nano in Google Sheets 

If you are a teacher looking to automate the marking of short answer questions, you've come to the right place. 

This updated guide will walk you through the process using Google Sheets, building upon the foundations of integrating Google Apps Script with the OpenAI API (newly updated for GPT-5 Nano model). This method offers a low-code, low-cost, and efficient solution for marking multiple student responses for various questions.

What You'll Need

• An OpenAI API key: This allows Google Sheets to communicate with the GPT models.

• Access to Google Sheet: This will host your student responses and mark schemes.

Instructions

Step 1: Obtain Your OpenAI API Key

To begin, you will need an OpenAI API key. This key is essential for your Google Sheet to 'talk' to GPT.

1. Create an OpenAI Account: If you're an existing user of ChatGPT, you can use the same account for this. The OpenAI API only charges for the tokens used, which is typically very cost-effective for this type of application. For instance, you could mark approximately 150,000 short-answer questions for less than USD$1.

2. Access API Keys: To find your API keys, go to this link or click on API keys in the side bar of the Dashboard panel.

3. Security Precaution: It is crucial that you never share your API key publicly, as this could allow others to use GPT at your expense. For enhanced security within your script, consider using Google Apps Script’s PropertiesService to store your API key securely, rather than hardcoding it directly into the script.

Step 2: Access Google Apps Script

Google Apps Script is a cloud-based scripting language, based on JavaScript, that allows you to automate tasks and extend the functionality of Google Workspace applications like Google Sheets.

1. Open your Google Sheet.

2. Go to Extensions in the top menu.

3. Select Apps Script.

Note: In the code snippet above, the API_KEY is hardcoded for convenience. However, it is strongly recommended to avoid hardcoding API keys directly in your script for security reasons. Instead, utilize Google Apps Script’s PropertiesService to store them securely. See further notes at the bottom of the page.

Step 4: Save, Authorize, and Test Your Function

After pasting the code, you need to save and authorize your script.

1. Save the Script: Click on the Save icon at the top.

2. Run the Script: Click on the Run icon next to the save button.

3. Grant Authorization: Google will prompt you to authorize your custom function to access the OpenAI model (GPT-5 Nano in this case). Follow the instructions to grant access. This process might tell you some intimidating warnings, but you are authorizing your own function. You can verify the code provided with a trusted person.

4. Test the Function: Return to your Google Sheet. In any cell, type: =GPT5Nano("Hi")
   Press Enter. You should see "Loading…" appear in the cell, followed by GPT’s reply shortly after. Congratulations! You have successfully defined your own custom function to interact with GPT-5 Nano in Google Sheets.

Step 5: Set Up Your Input Data

Organize your mark schemes in a table within your Google Sheet. This makes it easy to reference them when sending prompts to GPT. I have the following setup that you can use as an example or adjust to make your own.

1. Create columns: Add column labels such as: 

Student Name | Student response: | Marking Guide:  | Marks scored out of |  AI Feedback | Original question.

2. Enter details: Input your mark schemes and student answers for each question into respective cells. 

For example: 

Student name: Ali

Student response: v=u+at =0+(2)(5) =10m/s

Marking guide: - Correct equation identified: v = u+at  (1 mark) - Substitution: v = 0+ 2*5 (1 mark) - Correct answer: v = 10m/s (1 mark, units required)

Marks scored out of: 3

AI Feedback: 

Original question: A car starts from rest and accelerates uniformly at 2.0m/s/s for 5.0s. Calculate the final velocity of the car after 5.0s.

(Note: The original question column is not required as the AI does not need to know the question to mark based on the marking scheme. However, if you find that the marking is not very consistent or it is not understanding the nuance of the students' responses, having the extra context may help.)

Step 6: Apply the Auto-Marking Formula

Now, you will use your GPT5Nano custom function to send the student responses along with the relevant mark scheme to GPT for scoring and feedback.

1. Create instructions for GPT: In a new cell (somewhere out of the way), you can copy the prompt below which will be used to explain what you are trying to do and give some specific instructions for marking. Feel free to adjust and modify these instructions according to your needs and how the responses turn out. 

System prompt: Please mark the provided answers based on the marking guide. If answer is fully correct, simply respond with one word 'Correct'. If answer is not fully correct or is missing some required working out/details, refer to marking guide to give a suitable score out of the total marks available and then briefly explain how the answer can be improved.

User prompt: 

2. Combine everything: In order to send all required information to GPT in one neat little package, we need to use the following Excel formula to put all the pieces together. You can do this in a new 'helper column' for each row or directly in the GPT5Nano custom functions. I prefer using a helper column to preview the input first.
   CONCATENATE()         This Excel function combines text from different cells into one cell to create your full prompt.
   Example usage:
   =CONCATENATE($H$1,$C$1,C2,$B$1,B2,$D$1,D2)

where the cell references are pointing to various parts of the table we made in Step 5. In my case:
$H$1 is where I decided to place the System prompt instructions.
$C$1 and C2 are the heading and contents of the marking guide column.

B2 and $B$1 are the student responses and heading.

$D$1 and D2 are for the marks column.

(Note the use of $ for absolute references. This keeps the relevant cells fixed when dragging the formula later.)

3. In the first row under the AI Feedback column, type the following formula. This formula uses your custom function to call GPT-5 Nano, referencing cell E2 which holds the instructions from the previous combining step.

=GPT5Nano(E2)

After a few seconds, you should see the response and your first question marked! You can click on the bottom right corner of the cell and drag down to copy the formula all the way to your last question without having to retype it each time. An even better way is to double click on the dot and you don't even need to drag.

Important Considerations and Best Practices

• Sensitive Data: Avoid sending personal identifiers, student details, or proprietary information in the prompt. Consider using nicknames or initials if necessary. In my example, none of the data sent to OpenAI contains student names (listed in column A, which is not part of the message being sent).

• Review Marks and Feedback: Always screen through the marks and feedback provided by GPT. While it mostly performs well in marking accuracy, its output is non-deterministic, meaning it might change its response when posed the same question over time. The temperature parameter in the code is currently set to 1, as that is the only option available when using GPT-5 Nano. Other models may have the option to make this more predictable (closer to 0).

• Tweaking Mark Schemes: You might need to refine your mark schemes by adding details such as alternative answers to accept/reject or necessary keywords for marks to be awarded. For example, I needed to specify in the marking guide that units are required for the final answer to ensure GPT looked out for that when marking.

• API Key Sharing: As mentioned earlier, avoid hardcoding API keys directly into your script. Use Google Apps Script’s PropertiesService to store them securely. If data privacy is a significant concern, you might consider running models locally or within a secure environment you control, rather than relying on a cloud-based API.

Advantages Over Web ChatGPT

This method offers several advantages over simply copying and pasting responses into ChatGPT directly. 

• It bypasses timed message limits (no more 'wait until 9am tomorrow to continue') without needing a monthly subscription.

• Reduces instances of hallucination from long prompts by starting a new instance with each question. 

• Student responses can be readily downloaded from the school LMS or Google Forms as .csv or .xlsx files and used directly on Sheets, further streamlining the process. 

• For infrequent usage, the steps presented here are extremely low-cost, making it an accessible solution for many teachers. 

• Many teachers can use this Sheet and share the same API Key, eliminating the need for every teacher to have their own individual account linked to a payment method. 

• The model used here (GPT-5 Nano) is an off-the-shelf one and can easily be replaced with a newer or more powerful model (e.g., GPT-5) if needed, simply by updating the model parameter in the custom function.

Bonus Features:

You can choose to adjust some of the settings in the Apps Script code according to your needs. Simply edit the second line of the code:

function GPT5Nano(prompt, reasoning = "low", verbosity = "low", temperature = 1, tokens = 1000)

• reasoning = "low": Sets reasoning_effort to "low", controlling the depth of thought GPT applies. You could adjust this to "minimal", "medium" or "high" depending on your needs.

• verbosity = "low": Similarly sets verbosity to "low", controlling the length of response outputs. You could adjust this to "minimal", "medium" or "high" depending on your needs.

• tokens = 1000: Sets max_completion_tokens to 1000. This is the maximum number of tokens GPT will generate for its response. You will need to increase this limit if reasoning or verbosity is increased, otherwise you may end up with an empty response after the 'thinking...' stage. For short-answer questions, it's unlikely you will exceed the token limit, but this provides a safeguard against surprisingly large costs being charged to your account.

Here is an interactive example of the Sheet, but with the GPT responses pre-filled. Click on the expand icon in the top right corner of the spreadsheet to open the full view for formulas.