Build a Weekly AI Brand Monitor With a Google Sheet and Two APIs

A $300-a-month tracker is an expensive way to learn that AI answers never mention your business. A Google Sheet, an Apps Script and two API keys can run the same 25 buyer prompts each week, log the answers and show you what changed.

By the end of this afternoon, you will have a Prompts tab, a Runs log for OpenAI and Perplexity, and a Diff tab that flags gains and losses. You need a Google account, an OpenAI API key, a Perplexity API key and spending limits you are comfortable with. The cost should be small for a 25-prompt run, but check your own billing rather than treating any per-query estimate as a guarantee.

There is a bigger catch: an API answer is not necessarily the answer a customer sees in an app. The last step measures that gap instead of burying it under a visibility score.

Step 1: Freeze 25 buyer questions

Create a sheet tab named Prompts. Put these headers in row 1, in this order:

prompt_id | prompt_text | intent | competitor_set | active

Write questions a buyer might ask before hiring or buying, not search keywords. I would pull them from sales-call questions, Search Console’s Questions report and the comparisons prospects raise on demos. Split the list roughly across four types:

• Category: best emergency plumber in austin for slab leak
• Problem: water bill doubled no visible leak what should I check first
• Comparison: rethinking water heater vs tankless for 3 bath house pros cons cost
• Brand: is [your company] licensed and what do reviews say

Give each row a unique prompt_id, an intent matching one of those types and a comma-separated competitor_set of brands you want to watch. Set active to the checkbox value TRUE. Aim for about seven category, seven problem, six comparison and five brand prompts. Then leave the wording alone between runs. If you rewrite a question every Monday, you cannot tell whether the answer changed or the question did.

Expected result: 25 active rows with distinct IDs. The mistake is writing 25 versions of your own brand name and calling the inevitable name-drops visibility. I used to tell clients to start with brand prompts. I was wrong. Category and problem prompts show where you lose buyers who have not found you yet.

Step 2: Give raw answers somewhere to live

Add two tabs named Runs and Diff. Put these headers in row 1 of Runs:

run_date | prompt_id | engine | answer_text | cited_urls | brand_mentioned | brand_cited | brand_recommended | competitors_seen

Put these in row 1 of Diff:

prompt_id | prompt_text | engine | last_week | this_week | change | manual_check

Keep the three signals separate. Mentioned means the answer names you. Cited means a returned source URL points to your domain. Recommended means the answer suggests buying from or hiring you. A complaint that names your business is not a recommendation; a linked source is not automatically an endorsement.

Expected result: three tabs, with only Prompts populated below its header. The mistake is building ten charts before saving a single answer. You need the raw text when a flag looks suspicious.

Step 3: Store keys and set spending limits

Create an OpenAI key at platform.openai.com > API keys > Create new secret key and a Perplexity key at perplexity.ai > API > Keys. In the Sheet, open Extensions > Apps Script > Project Settings > Script Properties. Add four properties:

OPENAI_KEY       your OpenAI API key
PERPLEXITY_KEY   your Perplexity API key
BRAND_NAME       the name buyers use for your business
BRAND_DOMAIN     yourdomain.com

Do not put keys in spreadsheet cells or paste them into the code. Review the billing controls in both API accounts before running anything. Set the proposed $10 OpenAI and $5 Perplexity monthly limits where your accounts permit them, and check what those controls actually enforce. The script uses gpt-4o-mini and base sonar, not the more expensive models named on the upgrade screens.

Expected result: four saved script properties and spending controls you have checked. The mistake is assuming a cheap model makes an uncapped account safe. Cap and inspect spend before scheduling the run.

Step 4: Paste a runner that keeps the sources

In Extensions > Apps Script, replace the starter function with the code below. It calls each engine for every active prompt, saves answer text and returned citation URLs, grades recommendations separately, and rebuilds Diff after a complete pass. It also remembers the next prompt if a run approaches the Apps Script time limit. That matters more than a clever chart.

function settings_() {
  const p = PropertiesService.getScriptProperties();
  const s = {
    openai: p.getProperty('OPENAI_KEY'),
    perplexity: p.getProperty('PERPLEXITY_KEY'),
    brand: p.getProperty('BRAND_NAME'),
    domain: p.getProperty('BRAND_DOMAIN')
  };
  if (Object.values(s).some(v => !v)) throw Error('Set all four Script Properties first.');
  return s;
}

function post_(url, key, payload) {
  const r = UrlFetchApp.fetch(url, {
    method: 'post', contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + key },
    payload: JSON.stringify(payload), muteHttpExceptions: true
  });
  if (r.getResponseCode() >= 300) {
    throw Error('API ' + r.getResponseCode() + ': ' + r.getContentText().slice(0, 300));
  }
  return JSON.parse(r.getContentText());
}

function openai_(prompt, key, search) {
  const payload = { model: 'gpt-4o-mini', input: prompt };
  if (search) payload.tools = [{ type: 'web_search' }];
  const j = post_('https://api.openai.com/v1/responses', key, payload);
  const text = [];
  const urls = [];
  (j.output || []).forEach(item => (item.content || []).forEach(part => {
    if (part.text) text.push(part.text);
    (part.annotations || []).forEach(a => {
      if (a.url) urls.push(a.url);
    });
  }));
  return { text: (text.join('\n') || j.output_text || '').slice(0, 5000),
           urls: [...new Set(urls)] };
}

function perplexity_(prompt, key) {
  const j = post_('https://api.perplexity.ai/chat/completions', key, {
    model: 'sonar', return_citations: true,
    messages: [{ role: 'user', content: prompt }]
  });
  return { text: (j.choices[0].message.content || '').slice(0, 5000),
           urls: [...new Set(j.citations || [])] };
}

function grade_(answer, urls, competitors, s) {
  const request = 'Return JSON only with keys recommended (boolean) and ' +
    'competitors (array of names). Recommended means the answer suggests ' +
    'buying from or hiring the brand, not merely naming it. List competitor ' +
    'brands named in the answer. Brand: ' + s.brand +
    '. Competitors to check: ' + competitors +
    '. Source URLs: ' + urls.join(', ') + '\nAnswer:\n' + answer;
  const raw = openai_(request, s.openai, false).text;
  const result = JSON.parse(raw);
  if (typeof result.recommended !== 'boolean' ||
      !Array.isArray(result.competitors)) throw Error('Unexpected grader JSON.');
  return result;
}

function cited_(urls, domain) {
  const target = domain.toLowerCase().replace(/^www\./, '');
  return urls.some(url => {
    try {
      const host = new URL(url).hostname.toLowerCase().replace(/^www\./, '');
      return host === target || host.endsWith('.' + target);
    } catch (e) { return false; }
  });
}

function runWeeklyMonitor() {
  const s = settings_();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const prompts = ss.getSheetByName('Prompts').getDataRange().getValues();
  const runs = ss.getSheetByName('Runs');
  const props = PropertiesService.getScriptProperties();
  const today = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd');
  const start = Date.now();
  const next = props.getProperty('NEXT_ROW');
  const first = props.getProperty('RUN_DATE') === today && next ? Number(next) : 1;
  props.setProperty('RUN_DATE', today);

  for (let i = first; i < prompts.length; i++) {
    if (Date.now() - start > 240000) {
      props.setProperty('NEXT_ROW', String(i));
      return;
    }
    if (prompts[i][4] !== true) continue;
    const [id, question, , competitors] = prompts[i];
    for (const engine of ['openai', 'perplexity']) {
      try {
        const result = engine === 'openai'
          ? openai_(question, s.openai, true)
          : perplexity_(question, s.perplexity);
        const grade = grade_(result.text, result.urls, competitors, s);
        runs.appendRow([
          today, id, engine, result.text, result.urls.join(', '),
          result.text.toLowerCase().includes(s.brand.toLowerCase()),
          cited_(result.urls, s.domain), grade.recommended,
          grade.competitors.join(', ')
        ]);
      } catch (e) {
        runs.appendRow([today, id, engine, 'ERROR: ' + e.message, '', '', '', '', '']);
      }
    }
    props.setProperty('NEXT_ROW', String(i + 1));
    Utilities.sleep(1200);
  }
  props.deleteProperty('NEXT_ROW');
  rebuildDiff_();
}

function rebuildDiff_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rows = ss.getSheetByName('Runs').getDataRange().getValues().slice(1);
  const prompts = ss.getSheetByName('Prompts').getDataRange().getValues().slice(1);
  const diff = ss.getSheetByName('Diff');
  const dates = [...new Set(rows.map(r => String(r[0])))].sort();
  const current = dates[dates.length - 1];
  const previous = dates[dates.length - 2];
  const byKey = new Map();
  rows.forEach(r => byKey.set([String(r[0]), String(r[1]), r[2]].join('|'), r));
  const level = r => !r || String(r[3]).startsWith('ERROR:')
    ? 'not run' : r[7] === true ? 'recommended'
    : r[6] === true ? 'cited' : r[5] === true ? 'mentioned' : 'missing';
  const output = [];
  prompts.filter(p => p[4] === true).forEach(p => {
    ['openai', 'perplexity'].forEach(engine => {
      const oldLevel = level(byKey.get([previous, String(p[0]), engine].join('|')));
      const newLevel = level(byKey.get([current, String(p[0]), engine].join('|')));
      const change = !previous ? 'first run'
        : newLevel === 'not run' || oldLevel === 'not run' ? 'check run'
        : oldLevel === 'missing' && newLevel === 'missing' ? 'still missing'
        : oldLevel === 'missing' ? 'newly mentioned'
        : newLevel === 'missing' ? 'newly lost'
        : oldLevel === newLevel ? 'same' : 'level changed';
      output.push([p[0], p[1], engine, oldLevel, newLevel, change, '']);
    });
  });
  if (diff.getLastRow() > 1) diff.getRange(2, 1, diff.getLastRow() - 1, 7).clearContent();
  if (output.length) diff.getRange(2, 1, output.length, 7).setValues(output);
}

Select runWeeklyMonitor in the Apps Script function menu and click Run. Approve the requested permissions. Expected result: Runs receives up to 50 rows, with answer text, source URLs where returned, and separate brand flags. Once the full pass finishes, Diff shows first run because there is no earlier date to compare.

Do not mistake a blank citation list for proof that nobody cited you. It means no URL was captured in that response. Also check for ERROR: rows before reading any trend. If the run stops partway through, run runWeeklyMonitor again on the same day; it resumes at the next prompt. The mistake is letting a failed call disappear and then reporting the gap as a loss.

Step 5: Check the flags before believing the diff

Filter Runs to the latest run_date. Open a few rows marked brand_mentioned = TRUE and read the sentence around your name. Then inspect rows marked brand_cited = TRUE: the linked domain must be yours, not a similarly named business. Finally, read every brand_recommended = TRUE row. The grader applies a stricter rule, but it is still a model judgment, not a customer action.

Expected result: recommendations and mentions remain distinct, and competitors_seen contains names you can check against the answer. The mistake is treating a name-drop, a source link and a suggestion to hire you as three versions of the same win. If three rivals appear in tankless-water-heater answers while you do not, you have found a useful prompt to investigate. You have not yet found the cause.

Step 6: Schedule the run, then read changes

In Apps Script, open Triggers > Add Trigger. Choose runWeeklyMonitor, set the event source to Time-driven, select Week timer, then choose Monday and 6am to 7am. Time-driven triggers run within the selected window, not at an exact minute.

After the second completed weekly run, open Diff and filter change to newly lost, newly mentioned, level changed and check run. Read those answers in Runs before acting. still missing shows prompts worth a look when competitors appear; check run means fix or resume the collection, not your website.

Expected result: one row per active prompt per engine in Diff, comparing the latest recorded date with the one before it. The mistake is treating first run as a trend, or reading all 50 answers every Monday. The diff tells you where to look; the raw answer tells you what happened.

Step 7: Measure the API-to-app gap by hand

Choose five unchanged prompts: two category, two problem and one comparison. Enter each manually in the ChatGPT app and in Google. Where the interface allows it, use a logged-out browser and set the location to your service area. In Diff > manual_check, record what you actually saw for the matching OpenAI prompt, using a compact note such as:

ChatGPT: mentioned; Google: missing

Compare ChatGPT: mentioned or missing with the OpenAI row’s this_week status. If the app names you and the API says missing, or the reverse, keep the disagreement visible. Google is useful context, not a substitute for a ChatGPT-to-OpenAI comparison. Repeat the same five prompts after later runs if you want to see whether that gap persists.

Expected result: five manual notes and an honest account of any mismatch. Do not expect a fixed number of disagreements. The mistake is declaring either surface the definitive answer. Apps and APIs are different samples; buyers see the app result, not the row in your Sheet.

Verify the monitor, then fix one absence

This monitor shows absence, not cause. A lost citation may send you to a competitor’s comparison page, but the red cell cannot tell you which part of that page mattered. Rewriting your homepage headline because a citation disappeared is a guess. For the blunt version of what paid trackers do and do not solve, read Will AEO Software Get You Cited or Just Tell You You’re Missing?. Most tools stop at the same red cell your Sheet shows.

Verify the setup in this order:

1. After a completed run, filter Runs to its date. Count two rows per active prompt and resolve any ERROR: rows.
2. After the next run on a different date, open Diff. Confirm it compares the two dates and inspect at least one changed or still-missing row against its raw answer.
3. Check both API billing pages. Compare actual charges with the limits you set; do not infer cost from the row count.
4. Check the five manual prompts. Record API-to-app disagreements instead of silently correcting the Sheet to match either one.

Once that works, change one page, not the script. Pick a prompt where a competitor appears and you do not. Open the cited competitor page, inspect its answer structure, then make your own relevant page answer the question directly. If the query calls for prices or a local service, make those details easy to find. The monitor gives you a place to start, not a license to copy somebody else’s claims.

That is where I draw the DIY line. The Sheet tracks absence for a small API bill; it does not write pages, earn citations or run the fixes while you run the business. If you want the manual path, use One Person, Two Hours a Week, 30 Days: A DIY AEO Citation Playbook. If the still missing rows keep piling up and you want execution rather than another dashboard, that is the job groas takes on: continuous search work, logged actions and a human strategist accountable for the direction.