Have you ever wanted to quickly generate compelling Google Ads headlines and descriptions right inside Google Sheets—without paying for fancy add-ons or extensions? Now you can! This free script (plus an OpenAI ChatGPT API key) transforms your Google Sheets into an ad-building machine. Below is exactly how to set it up, step by step.
What You’ll Need
- Google Account (to access Google Sheets and App Script).
- OpenAI API Key (from platform.openai.com).Be sure to prefix your key with the word Bearer in the script.
- This Free Google Sheets Template (no sign-up required) https://docs.google.com/spreadsheets/d/1CJGZuEQm3EaHusjIOhhpUABuUY-i5Swbbz-XdO6gyps/edit?gid=0#gid=0
How It Works
- Open the Sheet and go to Extensions > Apps Script.
- Copy & Paste the code below into the editor (removing any pre-existing boilerplate).
- Add Your API Key in the Authorization header:
'Authorization': 'Bearer YOUR_OPENAI_API_KEY_HERE'
- Save and Refresh the Google Sheet.
- You’ll see a custom menu called “GPT Ad Builder”. Click “Generate GPT Ads” to automatically read each row, send your prompts to ChatGPT, and fill in 15 headlines and 4 descriptions.
Step-by-Step Implementation
- Open the Free Google SheetMake a copy if needed.
- Go to Extensions → Apps ScriptThis opens the Google Apps Script editor.
- Delete the Existing Code (if any) and paste the following script:
/************************************
* 1) onOpen(e)
* - Creates a custom menu in the
* active Google Sheet
************************************/
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('GPT Ad Builder')
.addItem('Generate GPT Ads', 'generateAdHeadlines')
.addToUi();
}
/****************************************************
* 2) generateAdHeadlines()
* - Main function to read each row, call GPT,
* and write headlines/descriptions back
****************************************************/
function generateAdHeadlines() {
var sheet = SpreadsheetApp.getActiveSheet();
// We assume the first row is headers (row 1).
var startRow = 2; // Data starts from row 2
var lastRow = sheet.getLastRow();
// We'll read columns A through AH (1 to 34).
// A:Customer ID, B:Campaign, C:Ad Group, ...,
// up to Bulk GPT (32), Custom GPT (33),
// Feel,Tone,Personalization (34).
var numColsToRead = 34;
var dataRange = sheet.getRange(startRow, 1, lastRow - startRow + 1, numColsToRead);
var data = dataRange.getValues();
// Object to track how many ads have been created per Ad Group
var adGroupCounter = {};
for (var i = 0; i < data.length; i++) {
var row = data;
// Column references (0-based in the row array):
var customerId = row[0]; // A
var campaign = row[1]; // B
var adGroup = row[2]; // C
// row[3] would be Status (D), if needed
// ...
// row[25] is Final URL (Z)
var finalUrl = row[25]; // index 25
// row[31] is Bulk GPT (column 32)
var bulkGPT = row[31];
// row[32] is Custom GPT (column 33)
var customGPT = row[32];
// row[33] is Feel,Tone,Personalization (column 34)
var feelTone = row[33];
// Skip rows missing essential data
if (!customerId || !campaign || !adGroup || !finalUrl) {
Logger.log(
'Skipping row ' + (startRow + i) +
' due to missing data (Customer ID, Campaign, Ad Group, or Final URL).'
);
continue;
}
// If the Ad Group doesn't exist yet, initialize it
if (!adGroupCounter) {
adGroupCounter = 0;
}
// Check if we’ve already created 3 ads for this Ad Group
if (adGroupCounter >= 3) {
var tooManyAdsMsg = 'Error: More than 3 ads attempted for Ad Group: ' + adGroup;
Logger.log(tooManyAdsMsg);
SpreadsheetApp.getUi().alert(tooManyAdsMsg);
// Stop the entire script or just break out of the loop
return;
}
// Build a "prompt" for GPT
var userPrompt = createPrompt(finalUrl, campaign, adGroup, bulkGPT, customGPT, feelTone);
// Call OpenAI's API
var gptResponse = callOpenAI(userPrompt);
// The GPT response should have { headlines: [...], descriptions: [...] }
if (gptResponse && gptResponse.headlines && gptResponse.descriptions) {
// Truncate each headline & description to their respective limits
var truncatedHeadlines = gptResponse.headlines.map(function(h) {
return truncateText(h, 30); // Max 30 chars for headlines
});
var truncatedDescriptions = gptResponse.descriptions.map(function(d) {
return truncateText(d, 90); // Max 90 chars for descriptions
});
// Write them back to the sheet.
// Example: columns E–S (5–19) for h1–h15, T–W (20–23) for d1–d4.
// That is 15 headlines, 4 descriptions.
// Headlines (15 columns)
for (var hIndex = 0; hIndex < 15; hIndex++) {
var headlineValue = truncatedHeadlines || '';
// Column E = 5, so E + hIndex => 5 + hIndex
sheet.getRange(startRow + i, 5 + hIndex).setValue(headlineValue);
}
// Descriptions (4 columns)
for (var dIndex = 0; dIndex < 4; dIndex++) {
var descValue = truncatedDescriptions || '';
// Column T = 20, so T + dIndex => 20 + dIndex
sheet.getRange(startRow + i, 20 + dIndex).setValue(descValue);
}
// Increment ad count for this Ad Group
adGroupCounter++;
} else {
// If the GPT response was invalid or empty
Logger.log(
'GPT Error at row ' + (startRow + i) +
'. gptResponse: ' + JSON.stringify(gptResponse)
);
sheet.getRange(startRow + i, 5).setValue('GPT Error');
}
}
SpreadsheetApp.getUi().alert('GPT ad generation complete!');
}
/***********************************************************
* 3) createPrompt(finalUrl, campaign, adGroup, bulkGPT,
* customGPT, feelTone)
* - Crafts a user message string for Chat Completions.
* 1) If Bulk GPT = TRUE, ignore Custom GPT and feelTone
* 2) Else if Custom GPT = TRUE, incorporate feelTone
* 3) Else use default prompt logic
***********************************************************/
function createPrompt(finalUrl, campaignName, adGroupName, bulkGPT, customGPT, feelTone) {
// Convert TRUE/FALSE (could be strings or booleans) to booleans
var isBulkGPT = (bulkGPT === true || bulkGPT === 'TRUE');
var isCustomGPT = (customGPT === true || customGPT === 'TRUE');
var feelToneStr = feelTone || '';
// Base instructions: we want 15 headlines + 4 descriptions
// with their respective max lengths (30 & 90 chars).
// We'll incorporate extra instructions depending on flags.
var instructions = `
Write **15 unique** ad headlines (max 30 characters each)
and 4 descriptions (max 90 characters each).
Final URL: "${finalUrl}"
Campaign: "${campaignName}"
Ad Group: "${adGroupName}"
Respond in valid JSON with the structure:
{
"headlines": ["Headline 1", "Headline 2", ... up to 15 ...],
"descriptions": ["Description 1", "Description 2", ... up to 4 ...]
}
`;
if (isBulkGPT) {
// Bulk GPT is checked => ignore custom GPT + feel tone
return `
${instructions}
(BULK GPT MODE: ignore any custom tone/personalization.)
`;
} else if (isCustomGPT) {
// Custom GPT is checked => incorporate the feel/tone text
return `
${instructions}
(CUSTOM GPT MODE: incorporate the following feel/tone
and personalization instructions into your copy:
"${feelToneStr}")
`;
} else {
// Neither Bulk GPT nor Custom GPT => plain prompt
return `
${instructions}
(DEFAULT MODE: no extra tone or personalization.)
`;
}
}
/************************************
* 4) callOpenAI(userPrompt)
* - Official OpenAI Chat Completions endpoint
* - Includes sanitization to parse strict JSON
************************************/
function callOpenAI(userPrompt) {
try {
var url = 'https://api.openai.com/v1/chat/completions';
// System and user messages
var messages = [
{
role: 'system',
content: `
You are an expert Google Ads copywriter
integrated into a Google Sheet.
Always respond with strictly valid JSON—no code fences or markdown.
`
},
{
role: 'user',
content: userPrompt
}
];
var payloadData = {
model: 'gpt-3.5-turbo', // or 'gpt-4' if you have access
messages: messages,
max_tokens: 1000,
temperature: 0.7
};
var options = {
method: 'post',
contentType: 'application/json',
muteHttpExceptions: true,
payload: JSON.stringify(payloadData),
headers: {
// Replace with your real API key:
'Authorization': 'Bearer YOUR_OPENAI_API_KEY_HERE'
}
};
var response = UrlFetchApp.fetch(url, options);
var statusCode = response.getResponseCode();
var responseBody = response.getContentText();
Logger.log('statusCode: ' + statusCode);
Logger.log('responseBody: ' + responseBody);
if (statusCode === 200) {
var result = JSON.parse(responseBody);
// The actual text from ChatGPT is in result.choices[0].message.content
if (!result.choices || !result.choices[0]) {
Logger.log('No choices returned from OpenAI.');
return null;
}
var rawText = result.choices[0].message.content;
Logger.log('rawText: ' + rawText);
// 1) Remove any code fences
var sanitizedText = rawText
.replace(/```json([\s\S]*?)```/g, '$1')
.replace(/```([\s\S]*?)```/g, '$1')
.trim();
// 2) Remove trailing commas before closing brackets/braces
sanitizedText = sanitizedText.replace(/,\s*([\]}])/g, '$1');
// 3) Parse the cleaned string
try {
var parsed = JSON.parse(sanitizedText);
// E.g. { "headlines": [...], "descriptions": [...] }
return parsed;
} catch (e) {
Logger.log('Error parsing sanitizedText as JSON: ' + e);
return null;
}
} else {
Logger.log('Error from OpenAI: ' + statusCode + ' ' + responseBody);
return null;
}
} catch (err) {
Logger.log('callOpenAI() error: ' + err);
return null;
}
}
/************************************
* 5) truncateText(text, maxLength)
* - Helper to ensure text does not
* exceed character limits
************************************/
function truncateText(text, maxLength) {
if (!text) return '';
if (text.length <= maxLength) return text;
return text.substring(0, maxLength);
}
2. Save and Refresh
- In the Apps Script editor, click the Save button.
- Return to your Google Sheet and refresh the page.
3. Use the GPT Ad Builder Menu
- After refresh, you’ll notice a new menu called “GPT Ad Builder” in the top toolbar.
- Choose “Generate GPT Ads” to run the script.
4. Auto-Created Ads
- The script will generate 15 headlines (each up to 30 characters) and 4 descriptions (each up to 90 characters) per row.
- It uses the URL in your sheet (column Z, i.e., Final URL) to ensure each ad is specifically aligned with its destination.
- Important: Each Ad Group can only have 3 ads. If you go beyond that, you’ll see a pop-up warning.
5. Data Requirements
- Make sure you have Account Number, Campaign, Ad Group, and Final URL populated.
- The script bases the ad copy on the Final URL, while the other fields (Account, Campaign, Ad Group) are for your own tracking or upload reference.
Understanding the Custom Columns
At the end of the sheet, there are three columns that control how the ads get generated:
1. Bulk GPT
- What It Does: When marked TRUE, ChatGPT produces standard, no-frills ad copy in “bulk mode.”
- Why Use It: Ideal for large-scale campaigns needing straightforward ads with minimal personalization.
- Script Behavior: The prompt includes (BULK GPT MODE: ignore any custom tone/personalization.), ensuring ChatGPT provides generic, ready-to-use headlines and descriptions.
2. Custom GPT
- What It Does: Mark TRUE if you want to reflect a specific style or brand voice in your ads. You’ll describe that voice in the “Feel, Tone, Personalization” column.
- Why Use It: Perfect for ads that need a particular vibe—professional, friendly, humorous, or even referencing specific topics (e.g., certain sports stars).
- Script Behavior: If Custom GPT is TRUE (and Bulk GPT is not), the script adds (CUSTOM GPT MODE: …) to the prompt so ChatGPT applies your custom instructions.
3. Feel, Tone, Personalization
- What It Contains: A free-form text field describing how you want the ads to sound—casual, academic, humorous, or thematic references.
- Why Use It: It’s a direct line to ChatGPT, guiding the exact style or messaging you need.
- Script Behavior: Only relevant if Custom GPT is TRUE. If both Bulk GPT and Custom GPT are FALSE, the script defaults to a generic prompt with no special tone.
How These Settings Interact
- Bulk GPT = TRUE → Creates quick, basic ads at scale.
- Custom GPT = TRUE + “Feel, Tone, Personalization” → Produces fine-tuned, brand-specific ads with your chosen style.
- Neither Checked (FALSE/FALSE) → Default mode (simple, generic headlines and descriptions).
Why This Script Is Awesome
- Completely Free: No monthly fees or extra Google Sheets extensions.
- Directly in Google Sheets: Work within the spreadsheet you already use.
- Highly Customizable: Adjust prompts to match any campaign or brand voice.
- Powered by GPT: Generate engaging ad copy with just one click.
That’s it! You now have a convenient, budget-friendly way to generate unlimited headlines and descriptions for Google Ads—all from a single spreadsheet.
If you have any questions, feel free to reach out. Otherwise, enjoy your new AI-powered ad creation workflow!
Note: ChatGPT can sometimes make mistakes. Always review and verify your ad copy before publishing.