A brand-monitoring dashboard can tell you that ChatGPT mentioned a competitor instead of you. So can a spreadsheet and a script. I spent years using that combination for Google Ads reporting, and the same approach gives a small business a low-cost way to watch AI answers every week. It also has three ways to lie to you: API answers are not consumer answers, one run is noise, and a mention is not a citation.
What you’ll build, and what you need first
You’ll build a Google Sheet that sends buyer questions to OpenAI with web search, Perplexity Sonar, and Gemini Flash with Google Search grounding. Each week it will record brand and competitor mentions, cited URLs, and the share of successful runs that mention your brand. The output is a directional monitor, not a transcript of what every buyer sees.
Start with a fresh Google Sheet and API keys for OpenAI, Perplexity, and Google AI Studio. Enable billing where your accounts require it. At this scale, the bill can be a few dollars a month, but do not treat the draft’s under-$5 estimate as a cap: tokens, search requests, and retries affect the total. Check each provider’s pricing and set a spend limit before you schedule the script.
Step 1: Put buyer questions in Prompts
In the Sheet, create a tab named Prompts. Add these headers in row 1:
Prompt | Intent | Target_Brand | Competitors | Target_Domain
Enter 20 to 40 complete questions, one per row. Use Discovery, Comparison, or Evaluation in Intent. In Target_Brand, separate the brand’s common name variants with commas. Do the same for three to five direct rivals in Competitors. Put your domain, without https://, in Target_Domain.
A buyer might ask what software mid-sized dental practices use to automate claims, then compare tools on integration speed and cost, then ask about the drawbacks of a specific vendor. Those are three different prompts. Do not paste keyword fragments such as b2b dental billing software and assume they stand in for buyer questions.
Expected result: each row gives the script a question, its intent, names to match, and a domain to check against cited links. The common mistake is quietly rewriting prompts after a bad week. Keep the panel stable; otherwise, as our guide to measuring brand visibility explains, you lose the baseline you meant to track.
Step 2: Keep keys out of the grid
Get your keys from OpenAI’s platform.openai.com API keys page, Perplexity’s perplexity.ai/settings/api, and Google AI Studio’s aistudio.google.com Get API key control. Confirm the accounts can make billed requests before testing the monitor.
In the Sheet, open Extensions > Apps Script. In the editor, open Project Settings > Script Properties > Add script property and add:
OPENAI_API_KEY
PERPLEXITY_API_KEY
GEMINI_API_KEY
Paste each key into its corresponding property value. Expected result: the script can retrieve keys through PropertiesService without reading them from a cell. Do not put keys in the Sheet, even briefly. A viewable cell is a poor place for a secret, and clearing it does not erase revision history.
Step 3: Add the script and run one test
In Extensions > Apps Script, replace the contents of Code.gs with the code below. Save it. The three request functions stay separate because web retrieval and citations come back in different shapes: Sonar provides a citations array, Gemini provides grounding metadata, and OpenAI’s web-search responses provide citation annotations.
The script limits each execution to nine calls or roughly four minutes, then schedules a continuation if work remains. That matters because Apps Script has a six-minute execution limit. A single synchronous loop over 40 prompts, three engines, and three runs can stop halfway through.
const PROPS = PropertiesService.getScriptProperties();
const ENGINES = ['OpenAI', 'Perplexity', 'Gemini'];
function request(url, key, payload, useQueryKey) {
const res = UrlFetchApp.fetch(url, {
method: 'post',
contentType: 'application/json',
headers: useQueryKey ? {} : { Authorization: 'Bearer ' + key },
payload: JSON.stringify(payload),
muteHttpExceptions: true
});
if (res.getResponseCode() < 200 || res.getResponseCode() >= 300) {
throw new Error('HTTP ' + res.getResponseCode() + ': ' +
res.getContentText().slice(0, 200));
}
return JSON.parse(res.getContentText());
}
function callEngine(engine, prompt) {
if (engine === 'Perplexity') {
const data = request('https://api.perplexity.ai/chat/completions',
PROPS.getProperty('PERPLEXITY_API_KEY'),
{ model: 'sonar', messages: [{ role: 'user', content: prompt }] });
return {
text: data.choices?.[0]?.message?.content || '',
urls: data.citations || []
};
}
if (engine === 'Gemini') {
const key = PROPS.getProperty('GEMINI_API_KEY');
const url = 'https://generativelanguage.googleapis.com/v1beta/models/' +
'gemini-2.5-flash:generateContent?key=' + encodeURIComponent(key);
const data = request(url, key, {
contents: [{ parts: [{ text: prompt }] }],
tools: [{ googleSearch: {} }]
}, true);
const candidate = data.candidates?.[0];
return {
text: (candidate?.content?.parts || [])
.map(part => part.text || '').join('\n'),
urls: (candidate?.groundingMetadata?.groundingChunks || [])
.map(chunk => chunk.web?.uri).filter(Boolean)
};
}
const data = request('https://api.openai.com/v1/responses',
PROPS.getProperty('OPENAI_API_KEY'), {
model: 'gpt-4o-mini',
tools: [{ type: 'web_search_preview' }],
input: prompt
});
const blocks = (data.output || []).flatMap(item => item.content || []);
return {
text: blocks.map(block => block.text || '').join('\n'),
urls: blocks.flatMap(block => block.annotations || [])
.map(annotation => annotation.url).filter(Boolean)
};
}
function named(text, names) {
return names.split(',').map(s => s.trim()).filter(Boolean).some(name => {
const safe = name.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
return new RegExp('(^|[^a-z0-9])' + safe +
'(?=$|[^a-z0-9])', 'i').test(text);
});
}
function cited(urls, domain) {
const safe = domain.trim().toLowerCase()
.replace(/^https?:\/\//, '').replace(/\/.*$/, '')
.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
if (!safe) return false;
return urls.some(url => new RegExp(
'^https?:\\/\\/(?:[^/]+\\.)?' + safe + '(?=[:/?#]|$)', 'i'
).test(url));
}
function resultsSheet() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Raw_Results') ||
book.insertSheet('Raw_Results');
if (sheet.getLastRow() === 0) sheet.appendRow([
'Date', 'Run_Index', 'Engine', 'Prompt', 'Intent',
'Brand_Mentioned', 'Domain_Cited', 'Competitor_Mentions',
'Citation_URLs', 'Status'
]);
return sheet;
}
function clearContinuationTriggers() {
ScriptApp.getProjectTriggers()
.filter(t => t.getHandlerFunction() === 'continueMonitor')
.forEach(t => ScriptApp.deleteTrigger(t));
}
function runWeeklyMonitor() {
clearContinuationTriggers();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Prompts');
if (!sheet || sheet.getLastRow() < 2) throw new Error('Fill Prompts first.');
PROPS.setProperties({
MONITOR_CURSOR: '0',
MONITOR_DATE: new Date().toISOString()
});
continueMonitor();
}
function continueMonitor() {
clearContinuationTriggers();
const lock = LockService.getScriptLock();
if (!lock.tryLock(1000)) return;
try {
const book = SpreadsheetApp.getActiveSpreadsheet();
const input = book.getSheetByName('Prompts');
const rows = input.getRange(2, 1, input.getLastRow() - 1, 5)
.getValues().filter(row => row[0]);
const output = resultsSheet();
const total = rows.length * ENGINES.length * 3;
let cursor = Number(PROPS.getProperty('MONITOR_CURSOR') || 0);
const started = Date.now();
let calls = 0;
while (cursor < total && calls < 9 && Date.now() - started < 240000) {
const promptIndex = Math.floor(cursor / 9);
const engineIndex = Math.floor((cursor % 9) / 3);
const runIndex = cursor % 3 + 1;
const [prompt, intent, brand, competitors, domain] = rows[promptIndex];
const engine = ENGINES[engineIndex];
let mention = '', domainCited = '', rivalNames = '', urls = [];
let status = 'OK';
try {
const answer = callEngine(engine, String(prompt));
if (!answer.text) throw new Error('Empty answer');
urls = answer.urls;
mention = Number(named(answer.text, String(brand)));
domainCited = Number(cited(urls, String(domain)));
rivalNames = String(competitors).split(',').map(s => s.trim())
.filter(name => name && named(answer.text, name)).join(', ');
} catch (error) {
status = 'ERROR: ' + String(error.message).slice(0, 180);
}
output.appendRow([
new Date(PROPS.getProperty('MONITOR_DATE')), runIndex, engine,
prompt, intent, mention, domainCited, rivalNames,
urls.join(' | '), status
]);
cursor++;
calls++;
PROPS.setProperty('MONITOR_CURSOR', String(cursor));
if (cursor < total) Utilities.sleep(500);
}
if (cursor < total) {
ScriptApp.newTrigger('continueMonitor').timeBased()
.after(60 * 1000).create();
}
} finally {
lock.releaseLock();
}
}
Select runWeeklyMonitor in the editor and click Run. Approve the requested permissions. Expected result: a Raw_Results tab appears with up to nine rows, followed by continuation runs until the prompt list is complete. Each row has a Status; an API error leaves the mention fields blank rather than recording a false zero. The common mistake is pressing Run again while continuations are pending. That restarts the weekly job, so inspect the rows and triggers first.
The 500-millisecond pause is spacing, not a guarantee against provider rate limits. If you see ERROR rows, check the provider response, key, billing, and limits before interpreting any rate. Gemini grounding links may also use an intermediary URL; a domain that is not visible in the returned URL cannot be counted as your domain by this simple parser. Keep those URLs for inspection rather than calling them citations to your site.
Step 4: Schedule Monday and calculate a rate
In Apps Script, open Triggers > Add Trigger. Choose runWeeklyMonitor, then Time-driven, Week timer, Every Monday, and 5am to 6am. Save. Expected result: Monday starts a fresh job, and the script’s temporary continuation triggers finish it in batches. The mistake is scheduling continueMonitor as the weekly trigger: it is a worker, not the job starter.
Create a tab named Trend_Summary. In cell A1, paste this formula:
=QUERY(Raw_Results!A:J,"SELECT D, C, COUNT(F), AVG(F) WHERE J = 'OK' AND A >= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"' GROUP BY D, C LABEL COUNT(F) 'Successful Runs', AVG(F) '30-Day Mention Rate'",1)
Format the rate column as a percentage. Expected result: one row per prompt and engine, with a count of successful calls and the share that mentioned your brand. Since Brand_Mentioned stores 1 or 0, its average is the mention rate. Read the count beside the percentage. A 100% rate from one successful call is not the same observation as three out of three; an ERROR row should not enter either number.
For a single week’s three successful runs, the possible rates are 0%, about 33%, about 67%, and 100%. Do not call three out of three model consensus. It is a stronger signal than one out of one, not proof that the next buyer will see the same answer.
Step 5: Read mentions and citations separately
Open Raw_Results and compare Brand_Mentioned, Domain_Cited, Competitor_Mentions, and Citation_URLs for the prompts that matter commercially. A named brand and a cited domain are different events. The model can name you while linking to a review site or a competitor’s comparison page. It can cite your documentation to answer a factual question without recommending your product. Neither case should be relabelled to make the chart look better.
Filter for prompts where rivals appear and you do not, then inspect the returned URLs and the answer itself. The sheet stores names and links, not the full wording of each recommendation; rerun important prompts manually when context matters. The common mistake is treating a competitor mention as a win for the competitor without checking whether the answer praised it, criticised it, or merely listed it.
Step 6: Check what the sheet cannot see
Before these numbers enter a leadership deck, make one pass through this checklist. It is the difference between a useful trend and a precise-looking mistake.
- Compare API and consumer answers: Run five prompts in the consumer interfaces alongside the script; note differences rather than treating the API output as the screen a buyer saw.
- Check repeat counts: confirm three successful runs per prompt and engine before reading a weekly rate.
- Keep mentions apart from citations: inspect the text and links behind a few
1values in each column. - Check recommendation context: a mention can be a warning, an alternative, or a recommendation.
- Check Google AI Overviews separately: Gemini with grounding is not the Google Search results page.
- Check competitor context: a rival named beside you may be framed as the stronger choice.
Recommendation context is the item people skip most. A high mention rate that mostly records negative or passing references can send months of content work in the wrong direction.
Step 7: Verify the job, then change the first thing that matters
After the continuations finish, verify that Raw_Results contains three OK rows for each prompt and engine, with no unexplained gaps. Check a handful of Citation_URLs against the returned answers, compare five prompts with consumer interfaces, and confirm that Trend_Summary excludes errors. If the totals do not match, fix the job or the inputs before presenting a trend. The first change after a clean run is the weakest high-intent prompt, not the chart. Keep its wording fixed and inspect why the answer names a rival, cites someone else, or leaves your brand out.
This is where the sheet stops. It tells you what to investigate; it does not repair a page, change site structure, or earn a citation. If your team has the time to turn those findings into work, keep the cheap monitor. If the findings only become a growing task backlog, groas earned search is built for the execution side: specialised models work continuously within commercial guardrails, with a human strategist responsible for direction and accountability. Run the monitor first. Then change what the answers reveal.
Frequently asked questions
Does this spreadsheet show exactly what buyers see when they ask ChatGPT about my brand?
No. The monitor sends buyer-style questions to APIs from OpenAI, Perplexity, and Gemini and records what those APIs return, so the output is a directional monitor rather than a transcript of what every buyer sees. API answers can differ from consumer answers, which is why the checklist recommends running five prompts in the consumer interfaces alongside the script.
Should I paste keyword phrases like 'b2b dental billing software' into the Prompts tab?
No. Enter 20 to 40 complete questions, one per row, using Discovery, Comparison, or Evaluation as the intent. Keyword fragments do not stand in for buyer questions; for example, a dental practice might ask what software mid-sized practices use, compare tools on integration speed and cost, and ask about a specific vendor's drawbacks as three separate prompts.
Where should I store the OpenAI, Perplexity, and Gemini API keys for this monitor?
Store each key as a script property in Apps Script under Project Settings > Script Properties, named OPENAI_API_KEY, PERPLEXITY_API_KEY, and GEMINI_API_KEY. Do not put keys in the Sheet, even briefly, because a viewable cell is a poor place for a secret and clearing it does not erase revision history. The script reads them through PropertiesService.
Why does the script only make nine calls per run instead of processing all prompts at once?
Apps Script has a six-minute execution limit, and a single synchronous loop over 40 prompts, three engines, and three runs could stop halfway through. The script limits each execution to nine calls or roughly four minutes, then schedules a continuation trigger that resumes the work one minute later until the prompt list is complete.
What happens to the results when an API call fails during a run?
The row is saved with an ERROR status and the mention fields are left blank rather than recorded as a zero, so failed calls never look like a real 'brand not mentioned' observation. ERROR rows are also excluded from the Trend_Summary rate, which counts only rows where the Status is OK.
Which function should I set as the weekly trigger, runWeeklyMonitor or continueMonitor?
Schedule runWeeklyMonitor. In Apps Script under Triggers, choose runWeeklyMonitor, Time-driven, Week timer, Every Monday, 5am to 6am. continueMonitor is a worker that the script schedules itself as a temporary continuation trigger, so making it the weekly trigger restarts the job incorrectly. Also avoid pressing Run again while continuations are pending.
If a prompt shows a 100% mention rate, can I trust that number?
Read the count of successful calls beside the percentage. A 100% rate from one successful call is not the same observation as three out of three, and with a single week's three runs the possible rates are only 0%, about 33%, about 67%, and 100%. Three out of three is a stronger signal than one out of one, but not proof the next buyer sees the same answer.
What is the difference between a brand mention and a domain citation in the results?
A named brand and a cited domain are different events. A model can name your brand while linking to a review site or a competitor's comparison page, or cite your documentation to answer a factual question without recommending your product. Neither case should be relabelled to make the chart look better, so inspect the returned text and URLs behind each value.
