Does your brand appear in ChatGPT’s responses when your target audience asks questions there? You can easily track this by listing questions that your target audience will likely ask in Google Sheets, and use Apps Script to pull in ChatGPT’s responses on a recurring basis.

Here’s an example of an Apps Script I created:

function fetchChatGPTResponses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var newColumn = lastColumn + 1;
  
  // Set date as column header
  var today = new Date();
  var formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "dd-MM-yyyy");
  sheet.getRange(1, newColumn).setValue(formattedDate);

  var questions = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues();
  
  for (var i = 0; i < questions.length; i++) {
    if (questions[i][0]) {
      var response = getChatGPTResponse(questions[i][0]);
      sheet.getRange(i + 2, newColumn).setValue(response);
    }
  }
}

function getChatGPTResponse(question) {
  var apiKey = "Add your API key";
  var url = "https://api.openai.com/v1/chat/completions";

  var payload = {
    model: "gpt-4o",
    messages: [{ role: "user", content: question }],
    max_tokens: 700
  };

  var options = {
    method: "post",
    headers: {
      "Authorization": "Bearer " + apiKey,
      "Content-Type": "application/json"
    },
    payload: JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch(url, options);
  var json = JSON.parse(response.getContentText());
  return json.choices[0].message.content;
}

How to Set Up the Tracking

If you want to use my tracking script, follow these steps to get it working:

1. Create a Google Sheet

• Column A: List the questions you want to track.
• Columns B, C, D, etc.: The Apps Script will store ChatGPT’s responses in these columns, so you can leave them empty. The date of the analysis will be automatically added to the column headers.

2. Add the Script in Google Sheets

• In Google Sheets, go to Extensions > Apps Script.
• Delete any existing code and add the provided script, or generate one with ChatGPT.

3. Create an API Key

• Go to the OpenAI platform to create an API key (Settings > API Keys).
• Under Budget, set an amount for credits (e.g., $5 to start testing).
• For safety, uncheck the option to automatically charge your card when credits run out until you confirm everything is working as expected.

4. Automate Weekly Updates

• In Apps Script, click the clock icon (Triggers).
• Click + Add Trigger and set the function to run weekly.
• This feeds in ChatGPT’s responses on a weekly basis in the next available column, allowing you to track changes over time.

5. Analyze the Mentions

• In a separate tab, list the brands you want to track, including your own, in Column A.
• In Column B, use a formula like =SUMPRODUCT(--ISNUMBER(SEARCH(A2, B:B))) to count how often your brand appears in responses, along with competitors’ mentions.

Now, you can track your brand mentions in ChatGPT on a recurring basis. Good luck! If you have any questions, feel free to comment or reach out.

Few notes:

• The current model is GPT-4o, but you can change it in the line model: "gpt-4o".
• Make sure the script's max_tokens value is set appropriately to prevent responses from being cut off. I've currently set it to 700 tokens.