Part 8: How to Direct GPT-3.5-turbo or GPT-4 Responses to Specific Cells

In this blog post, we will explore how to direct the output of OpenAI’s GPT-3.5-turbo language model to specific cells in Google Sheets. By logging the generated content in designated cells, you can better organize your data, track results, and even build interactive applications in your spreadsheets.

Recap: Setting Up GPT-3.5-turbo API Calls in Google App Script

In the previous blog post, we demonstrated how to set up API calls to GPT-3.5-turbo using Google App Script. If you haven’t read that post, we recommend doing so before proceeding. As a recap, here’s the callGpt35Turbo function that we will use throughout this post:

function callGpt35Turbo(prompt) {
  let apiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
  let apiUrl = 'https://api.openai.com/v1/chat/completions';

  var messageForAI = [{role: "user", content: prompt}]

  let payload = {
    'model': model,
    'messages': messageForAI,
    'max_tokens': 1000,
    'temperature': 0.7
  };

  let options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': `Bearer` + apiKey
    },
    'payload': JSON.stringify(payload)
  };

  let response = UrlFetchApp.fetch(apiUrl, options);
  let responseData = JSON.parse(response.getContentText());

  return responseData['choices'][0]['message']['content'];
}
 /*Note: As you get more advanced you'll be able to 'Train' GPT-3.5-turbo or GPT-4 such as you see below (this would replaces messageForAI above). var messageForAI = [{role: "user", content: "You are a helpful assistant."}, {role: "user", content: "Create an SEO optimized URL slug for each of an article title 'The Ultimate Guide to Cat Training: Tips and Techniques'. Focus on the main topic keywords used in the title and use those."}, {role: "assistant", content: "cat-training-tips-techniques"}, {role: "user", content: "Perfect! Now, "+prompt}] It's important to note that GPT-3.5-turbo isn't as good at listening to directions as GPT-4.*/

Directing GPT-3.5-turbo Output to Specific Cells

Let’s start by creating a function that sends a prompt to GPT-3.5-turbo and logs the response in a specified cell:

function logGeneratedContentToCell(prompt, cellAddress) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let outputCell = sheet.getRange(cellAddress);

  let generatedContent = callGpt35Turbo(prompt);
  outputCell.setValue(generatedContent);
}

This logGeneratedContentToCell function takes a prompt and a cellAddress as input parameters. It calls the GPT-3.5-turbo API with the given prompt and writes the generated content to the specified cell.

To test this function, you can call it in the script editor with a prompt and cell address, like this:

logGeneratedContentToCell("What are the benefits of exercise?", "B1");

After running the function, you should see the generated content in cell B1 of your Google Sheet.

Logging Output from Multiple Prompts

Suppose you have a list of prompts in a column and want to generate content for each of them, logging the results in a corresponding column. To achieve this, create a function that iterates through the prompts and calls the logGeneratedContentToCell function for each one:

function logGeneratedContentForMultiplePrompts(inputColumn, outputColumn) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let lastRow = sheet.getLastRow();
  let inputRange = sheet.getRange(1, inputColumn, lastRow);
  let outputRange = sheet.getRange(1, outputColumn, lastRow);

  for (let i = 1; i <= lastRow; i++) {
    let prompt = inputRange.getCell(i, 1).getValue();
    if (prompt) {
      let cellAddress = outputRange.getCell(i, 1).getA1Notation(); logGeneratedContentToCell(prompt, cellAddress); } } }

The logGeneratedContentForMultiplePrompts function takes two input parameters: inputColumn and outputColumn. It iterates through the rows of the input column, reads the prompts, and logs the generated content in the corresponding cells of the output column.

To test this function, you can place a list of prompts in column A and then call the function in the script editor with the appropriate input and output column numbers:

logGeneratedContentForMultiplePrompts(1, 2);

After running the function, you should see the generated content for each prompt in column B of your Google Sheet.

Creating a Trigger-Based Logging System

In some cases, you may want to generate content only when a specific event occurs, such as when a new prompt is added or when the contents of a cell change. To achieve this, you can use Google Sheets‘ built-in trigger functionality to call a function automatically when an event occurs.

To set up a trigger, follow these steps:

1. In the script editor, click on “Triggers” in the left sidebar.
2. Click “Add trigger” at the bottom right corner of the page.
3. In the “Function to run” dropdown menu, select the logGeneratedContentForMultiplePrompts function (or the function you want to use for logging).
4. In the “Select event type” dropdown menu, choose the event that you want to trigger the function (e.g., “On change” or “On edit”).
5. Configure any additional trigger options, if necessary.
6. Click “Save”.

Now, your function will run automatically whenever the specified event occurs.

Conclusion

In this blog post, we explored how to direct GPT-3.5-turbo responses to specific cells in Google Sheets using Google App Scripts. We created a logging function that takes a prompt and a cell address as input, demonstrated how to log output for multiple prompts, and showed how to set up a trigger-based logging system.

By organizing and logging GPT-3.5-turbo responses in your Google Sheets, you can create interactive applications, automate data entry tasks, and improve the overall functionality of your spreadsheets.

In the next blog post, we’ll discuss best practices and common pitfalls for beginners when working with Google App Script and GPT-3.5-turbo.