What you’ll learn: How to write Google Apps Script to automate Sheets, connect to AI APIs from Apps Script, build custom menu functions, and set up time-based triggers. Includes 4 production scripts.
The What: Apps Script Is Free Automation Inside Google
Google Apps Script is JavaScript that runs inside Google Sheets, Docs, and Gmail. It requires no server, no deployment, and no cost. For marketers who live in Google Sheets, Apps Script is the fastest path to automation. It can call external APIs (including Claude and ChatGPT), process spreadsheet data, send emails, and run on automatic schedules.
The How: Your First Apps Script
Step 1: Open the Script Editor
// In any Google Sheet:
// 1. Click Extensions > Apps Script
// 2. Delete the placeholder code
// 3. Paste your script
// 4. Click the play button (triangle) to run
// 5. Grant permissions when prompted
Step 2: Script: AI-Powered Ad Copy Generator in Sheets
function generateAdCopy() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
// Assumes: Col A = Product, Col B = Keyword, Col C = empty (for output)
for (var i = 1; i < data.length; i++) {
var product = data[i][0];
var keyword = data[i][1];
if (!product || !keyword) continue;
var prompt = 'Write 3 Google RSA headlines (max 30 chars each) for: ' +
product + '. Target keyword: ' + keyword +
'. Return only the headlines, one per line.';
var response = callClaudeAPI(prompt);
sheet.getRange(i + 1, 3).setValue(response);
Utilities.sleep(1000); // Rate limit protection
}
SpreadsheetApp.getUi().alert('Ad copy generation complete!');
}
function callClaudeAPI(prompt) {
var url = 'https://api.anthropic.com/v1/messages';
var apiKey = PropertiesService.getScriptProperties().getProperty('CLAUDE_API_KEY');
var payload = {
model: 'claude-sonnet-4-20250514',
max_tokens: 300,
messages: [{role: 'user', content: prompt}]
};
var options = {
method: 'post',
contentType: 'application/json',
headers: {
'x-api-key': apiKey,
'anthropic-version': '2023-06-01'
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
var response = UrlFetchApp.fetch(url, options);
var json = JSON.parse(response.getContentText());
return json.content[0].text;
}
// Add to Sheet menu
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('AI Tools')
.addItem('Generate Ad Copy', 'generateAdCopy')
.addToUi();
}
Step 3: Script: Automated Weekly Report Email
function sendWeeklyReport() {
var sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('Weekly Data');
var data = sheet.getDataRange().getValues();
// Build summary
var totalSpend = 0, totalConversions = 0;
for (var i = 1; i < data.length; i++) {
totalSpend += data[i][2]; // Column C = Cost
totalConversions += data[i][4]; // Column E = Conversions
}
var avgCPA = totalSpend / Math.max(totalConversions, 1);
// Send email
var html = '<h2>Weekly PPC Summary</h2>' +
'<p>Total Spend: $' + totalSpend.toFixed(2) + '</p>' +
'<p>Conversions: ' + totalConversions + '</p>' +
'<p>Avg CPA: $' + avgCPA.toFixed(2) + '</p>';
MailApp.sendEmail({
to: '[email protected]',
subject: 'Weekly PPC Report - ' + new Date().toLocaleDateString(),
htmlBody: html
});
}
// Set up automatic trigger: Run every Monday at 8 AM
// Go to: Triggers (clock icon) > Add Trigger
// Function: sendWeeklyReport | Time-driven | Week timer | Monday | 8-9 AM
Step 4: Script: Google Sheets as AI Frontend
This turns any Google Sheet into an AI-powered analysis tool. Type a question in column A, get AI analysis in column B:
function analyzeCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getActiveCell();
var question = cell.getValue();
if (!question) {
SpreadsheetApp.getUi().alert('Type a question in the selected cell first.');
return;
}
var response = callClaudeAPI(
'You are a PPC analyst. Answer concisely: ' + question
);
// Write answer to next column
sheet.getRange(cell.getRow(), cell.getColumn() + 1).setValue(response);
}
📦 GitHub: https://github.com/itallstartedwithaidea/sheet-shell-demo — Demo of using Google Sheets as a frontend for AI and script automation
📦 GitHub: https://github.com/itallstartedwithaidea/wrike-calendar-automation — Google Apps Script syncing project management with Google Calendar
Website: googleadsagent.ai | GitHub: https://github.com/itallstartedwithaidea | Tools: googleadsagent.ai/tools
John Williams | Senior Paid Media Specialist, Seer Interactive | $48M+ managed spend | Creator, GoogleAdsAgent.ai | Hero Conf Speaker | github.com/itallstartedwithaidea