Below, you’ll find a Google Ads script that finds your out of stock products.
It sends you an email when a product meets your click or cost treshold, so you know what’s happening.
Now, you can ensure your Buying department knows about it.
No more guessing if your products are still in stock.
It’s called the Out Of Stock Alert for Google Shopping.
What The Script Does
👉 Monitors 7-day performance of all shopping products
👉 Checks stock status for those products
👉 Identifies out-of-stock products with significant performance
👉 Sends email alerts with key metrics for out of stock products (cost, conversions, revenue, CPA, ROAS)
👉 Exports detailed data to Google Sheets for analysis
👉 Included performance data, product ID and ‘Last Date’ that shows when the product was last active.
👉 Runs daily to catch stock issues before they impact revenue
Installing The Google Ads Script
- Put the script in Google Ads (Tools > Bulk Actions > Scripts)
- Add an optional Google sheet URL in ‘SHEET_URL’.
The first time, leave it empty. - Optional: decide on a minimal click treshold for the products (MIN_CLICKS_TRESHOLD)
- Optional: decide on a minimal cost treshold for the products (MIN_COST_TRESHOLD)
- Add your email to ‘ALERT_EMAIL’
- Choose to enable the email alert
DAILY_ALERT_ENABLED = true - Hit ‘Preview’ (always Preview first!) and monitor the ‘Logs’:)
Script Introduction
It’s a pain when your products are disapproved for Google Shopping.
But, those alerts can be automated.
Know what else is painful?
Your performance is dropping.
You checked everything.
But, it’s a top-selling product that went out of stock.
And you not getting alerts.
So, I wrote a script to fix it 🎁
Find the script below, enjoy!
Script Code
// Google Ads Script: Product Stock Status Monitor
// Version: 1.2
// Last Updated: 2025-10-08
//
// This script monitors product performance and checks actual stock status
// Written by Matinique Roelse from Adcrease and Adjusted by: Django Westland. Senior-only Google Ads agency.
// Linkedin: https://www.linkedin.com/in/matiniqueroelse/ / https://www.linkedin.com/in/djangowestland
// Website: https://www.adcrease.nl
//
//
// VERSION HISTORY:
// v1.2 (2025-10-08) - Enhanced debugging and date range flexibility:
// - Added configurable date ranges (7, 30, 90, etc days)
// - added case-insensitive matching (e.g. for Shopify)
// - Added configurable target product tracking for debugging
// - Added detailed logging toggle and comprehensive debugging
// v1.0
// - Monitor product performance and stock status
// - Email alerts for out-of-stock products
// - Export data to Google Sheets
//
// APPROACH:
// 1. Get stock status from shopping_product resource
// 2. Get performance data from shopping_performance_view resource
// 3. Combine to identify high-performing products that are out of stock
//
// Fill Configuration section with your own values.
const SHEET_URL = ''; // Leave empty to create a new spreadsheet
const TAB = 'Product Performance';
// Configuration
const MIN_CLICKS_THRESHOLD = 1; // Minimum clicks to consider a product for monitoring (temporarily lowered for debugging)
const MIN_COST_THRESHOLD = 0; // Minimum cost to consider a product for monitoring (temporarily lowered for debugging)
const ALERT_EMAIL = 'ads@adcrease.nl'; // Replace with your email address
const DAILY_ALERT_ENABLED = true; // Set to false to disable daily alerts
const CASE_INSENSITIVE_MATCHING = true; // Set to true to match product IDs regardless of case (recommended)
const PERFORMANCE_DATA_DAYS = 90; // Number of days to look back for performance data (default: 7 days)
const TARGET_PRODUCT_ID = 'shopify_nl_8541653270874_47085062750554'; // Set a specific product ID to track in detailed logs (leave empty to disable)
const DETAILED_LOGGING = true; // Set to true to enable detailed matching logs
//Don't change anything below this line!
// Global variable to store unmatched products data
let globalUnmatchedProductsData = [];
const STOCK_QUERY = `
SELECT
shopping_product.resource_name,
shopping_product.title,
shopping_product.item_id,
shopping_product.availability,
shopping_product.condition,
shopping_product.brand
FROM shopping_product
`;
// Performance query will be built dynamically based on PERFORMANCE_DATA_DAYS
function buildPerformanceQuery() {
// Calculate the date range
const today = new Date();
const startDate = new Date(today.getTime() - (PERFORMANCE_DATA_DAYS * 24 * 60 * 60 * 1000));
// Format dates as YYYY-MM-DD
const endDateStr = today.toISOString().split('T')[0];
const startDateStr = startDate.toISOString().split('T')[0];
// Use specific date range instead of LAST_X_DAYS for better compatibility
return `
SELECT
campaign.name,
segments.product_title,
segments.product_item_id,
segments.date,
metrics.impressions,
metrics.clicks,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
campaign.advertising_channel_type,
campaign.status
FROM shopping_performance_view
WHERE segments.date BETWEEN '${startDateStr}' AND '${endDateStr}'
AND campaign.status = "ENABLED"
ORDER BY metrics.clicks DESC
`;
}
// Note: We'll calculate 30-day revenue dynamically based on each product's last date
// This will be done in the processRevenueData function
function main() {
try {
Logger.log("Starting Product Stock Status Monitor...");
// Calculate and log date range
const today = new Date();
const daysAgo = new Date(today.getTime() - (PERFORMANCE_DATA_DAYS * 24 * 60 * 60 * 1000));
const dateRange = `${daysAgo.toISOString().split('T')[0]} to ${today.toISOString().split('T')[0]}`;
Logger.log(`Date range for analysis: ${dateRange} (Last ${PERFORMANCE_DATA_DAYS} days)`);
// Log configuration settings
Logger.log(`Configuration: Case-insensitive matching: ${CASE_INSENSITIVE_MATCHING}, Detailed logging: ${DETAILED_LOGGING}`);
if (TARGET_PRODUCT_ID) {
Logger.log(`🎯 Target product for detailed tracking: ${TARGET_PRODUCT_ID}`);
}
// Step 1: Get stock status data
Logger.log("Fetching stock status data...");
const stockRows = AdsApp.search(STOCK_QUERY);
const stockData = processStockData(stockRows);
Logger.log(`Found ${stockData.size} products with stock status`);
// Step 2: Get performance data
Logger.log("Fetching performance data...");
const performanceQuery = buildPerformanceQuery();
if (DETAILED_LOGGING) {
Logger.log(`Performance query date range: ${dateRange}`);
}
const performanceRows = AdsApp.search(performanceQuery);
const performanceData = processPerformanceData(performanceRows);
Logger.log(`Found ${performanceData.size} products with performance data`);
// Step 3: Combine data and identify high-performing out-of-stock products
const combinedData = combineStockAndPerformanceData(stockData, performanceData);
// Filter products that meet our monitoring criteria
const productsToMonitor = filterProductsForMonitoring(combinedData);
// Filter only out-of-stock products for spreadsheet export
const outOfStockProducts = productsToMonitor.filter(product => product[3] === 'OUT_OF_STOCK');
// Export out-of-stock products and unmatched products to spreadsheet
exportToSheet(outOfStockProducts, globalUnmatchedProductsData);
// Generate alerts if email is configured and alerts are enabled
if (ALERT_EMAIL && DAILY_ALERT_ENABLED && productsToMonitor.length > 0) {
generateAlerts(productsToMonitor);
}
Logger.log(`Script completed successfully. Found ${productsToMonitor.length} products to monitor.`);
} catch (e) {
Logger.log(`Error in main function: ${e}`);
}
}
function processStockData(rows) {
const stockMap = new Map();
let processedCount = 0;
let targetProductFound = false;
if (DETAILED_LOGGING) {
Logger.log("🔍 Starting stock data processing...");
}
while (rows.hasNext()) {
try {
const row = rows.next();
// Try different access patterns based on the actual structure
let resourceName, title, id, availability, condition, brand;
// Try direct access first
if (row['shopping_product.resource_name']) {
resourceName = row['shopping_product.resource_name'];
title = row['shopping_product.title'] || '';
id = row['shopping_product.item_id'] || '';
availability = row['shopping_product.availability'] || 'UNKNOWN';
condition = row['shopping_product.condition'] || '';
brand = row['shopping_product.brand'] || '';
} else if (row['shoppingProduct']) {
// Try nested object access
const product = row['shoppingProduct'];
resourceName = product.resourceName || '';
title = product.title || '';
id = product.itemId || '';
availability = product.availability || 'UNKNOWN';
condition = product.condition || '';
brand = product.brand || '';
}
if (id) {
// Store with original case and optionally lowercase key for matching
const stockKey = CASE_INSENSITIVE_MATCHING ? id.toLowerCase() : id;
stockMap.set(stockKey, {
resourceName,
title,
id: id, // Keep original case for display
availability,
condition,
brand
});
processedCount++;
// Check if this is our target product
if (TARGET_PRODUCT_ID && (id === TARGET_PRODUCT_ID || (CASE_INSENSITIVE_MATCHING && id.toLowerCase() === TARGET_PRODUCT_ID.toLowerCase()))) {
targetProductFound = true;
Logger.log(`🎯 TARGET PRODUCT FOUND in stock data: "${title}" (ID: ${id}, Availability: ${availability})`);
}
// Log first few products for debugging
if (DETAILED_LOGGING && processedCount <= 3) {
Logger.log(`📦 Stock product ${processedCount}: "${title}" (ID: ${id}, Key: ${stockKey}, Availability: ${availability})`);
}
}
} catch (e) {
Logger.log("Error processing stock row: " + e);
}
}
Logger.log(`Processed ${processedCount} products with stock status`);
if (TARGET_PRODUCT_ID && !targetProductFound) {
Logger.log(`⚠️ TARGET PRODUCT "${TARGET_PRODUCT_ID}" NOT FOUND in stock data`);
}
return stockMap;
}
function processPerformanceData(rows) {
const performanceMap = new Map();
let processedCount = 0;
let targetProductFound = false;
if (DETAILED_LOGGING) {
Logger.log("🔍 Starting performance data processing...");
}
while (rows.hasNext()) {
try {
const row = rows.next();
// Try different access patterns based on the actual structure
let campaignName, productTitle, productItemId, date;
let impressions, clicks, costMicros, conversions, conversionValue;
// Try direct access first
if (row['campaign.name']) {
campaignName = row['campaign.name'] || '';
productTitle = row['segments.product_title'] || '';
productItemId = row['segments.product_item_id'] || '';
date = row['segments.date'] || '';
impressions = Number(row['metrics.impressions']) || 0;
clicks = Number(row['metrics.clicks']) || 0;
costMicros = Number(row['metrics.cost_micros']) || 0;
conversions = Number(row['metrics.conversions']) || 0;
conversionValue = Number(row['metrics.conversions_value']) || 0;
} else if (row['campaign'] && row['segments'] && row['metrics']) {
// Try nested object access
campaignName = row['campaign'].name || '';
productTitle = row['segments'].productTitle || '';
productItemId = row['segments'].productItemId || '';
date = row['segments'].date || '';
impressions = Number(row['metrics'].impressions) || 0;
clicks = Number(row['metrics'].clicks) || 0;
costMicros = Number(row['metrics'].costMicros) || 0;
conversions = Number(row['metrics'].conversions) || 0;
conversionValue = Number(row['metrics'].conversionsValue) || 0;
}
if (productItemId) {
// Create unique key for product aggregation
const productKey = `${productItemId}_${campaignName}`;
if (!performanceMap.has(productKey)) {
performanceMap.set(productKey, {
campaignName,
productTitle,
productItemId,
totalImpressions: 0,
totalClicks: 0,
totalCost: 0,
totalConversions: 0,
totalConversionValue: 0,
lastDate: date,
dayCount: 0
});
}
const product = performanceMap.get(productKey);
product.totalImpressions += impressions;
product.totalClicks += clicks;
product.totalCost += costMicros / 1000000; // Convert micros to actual currency
product.totalConversions += conversions;
product.totalConversionValue += conversionValue;
product.lastDate = date;
product.dayCount++;
// Check if this is our target product
if (TARGET_PRODUCT_ID && (productItemId === TARGET_PRODUCT_ID || (CASE_INSENSITIVE_MATCHING && productItemId.toLowerCase() === TARGET_PRODUCT_ID.toLowerCase()))) {
targetProductFound = true;
Logger.log(`🎯 TARGET PRODUCT FOUND in performance data: "${productTitle}" (ID: ${productItemId}, Campaign: ${campaignName}, Clicks: ${clicks}, Cost: ${(costMicros / 1000000).toFixed(2)})`);
}
// Log first few products for debugging
if (DETAILED_LOGGING && processedCount <= 3) {
Logger.log(`📊 Performance product ${processedCount}: "${productTitle}" (ID: ${productItemId}, Campaign: ${campaignName}, Clicks: ${clicks})`);
}
processedCount++;
}
} catch (e) {
Logger.log("Error processing performance row: " + e);
}
}
Logger.log(`Processed ${processedCount} performance data rows`);
if (TARGET_PRODUCT_ID && !targetProductFound) {
Logger.log(`⚠️ TARGET PRODUCT "${TARGET_PRODUCT_ID}" NOT FOUND in performance data`);
}
return performanceMap;
}
function processRevenueData(rows) {
const revenueMap = new Map();
let processedCount = 0;
while (rows.hasNext()) {
try {
const row = rows.next();
let productItemId, productTitle, conversionValue;
// Try direct access first
if (row['segments.product_item_id']) {
productItemId = row['segments.product_item_id'] || '';
productTitle = row['segments.product_title'] || '';
conversionValue = Number(row['metrics.conversions_value']) || 0;
} else if (row['segments'] && row['metrics']) {
// Try nested object access
productItemId = row['segments'].productItemId || '';
productTitle = row['segments'].productTitle || '';
conversionValue = Number(row['metrics'].conversionsValue) || 0;
}
if (productItemId && conversionValue > 0) {
if (!revenueMap.has(productItemId)) {
revenueMap.set(productItemId, {
productItemId,
productTitle,
totalRevenue: 0
});
}
const product = revenueMap.get(productItemId);
product.totalRevenue += conversionValue;
processedCount++;
}
} catch (e) {
Logger.log("Error processing revenue row: " + e);
}
}
Logger.log(`Processed ${processedCount} revenue data rows`);
return revenueMap;
}
function combineStockAndPerformanceData(stockData, performanceData) {
const combinedData = [];
let matchedCount = 0;
let unmatchedCount = 0;
const unmatchedProducts = [];
const unmatchedIds = [];
const unmatchedProductsData = [];
let targetProductMatched = false;
if (DETAILED_LOGGING) {
Logger.log("🔍 Starting data combination and matching...");
}
for (const [productKey, performance] of performanceData) {
const productTitle = performance.productTitle;
const productId = performance.productItemId;
// Match by product ID with case sensitivity option
const lookupKey = CASE_INSENSITIVE_MATCHING ? productId.toLowerCase() : productId;
const stockInfo = stockData.get(lookupKey);
// Check if this is our target product for detailed logging
const isTargetProduct = TARGET_PRODUCT_ID && (productId === TARGET_PRODUCT_ID || (CASE_INSENSITIVE_MATCHING && productId.toLowerCase() === TARGET_PRODUCT_ID.toLowerCase()));
if (stockInfo) {
// Calculate metrics
const convRate = performance.totalClicks > 0 ? performance.totalConversions / performance.totalClicks : 0;
const cpa = performance.totalConversions > 0 ? performance.totalCost / performance.totalConversions : 0;
const roas = performance.totalCost > 0 ? performance.totalConversionValue / performance.totalCost : 0;
combinedData.push([
performance.campaignName,
performance.productTitle,
performance.productItemId,
stockInfo.availability,
performance.totalImpressions,
performance.totalClicks,
Number(performance.totalCost.toFixed(2)),
Number(performance.totalConversions.toFixed(2)),
Number(performance.totalConversionValue.toFixed(2)),
Number((convRate * 100).toFixed(2)),
Number(cpa.toFixed(2)),
Number(roas.toFixed(2)),
performance.lastDate
]);
matchedCount++;
if (isTargetProduct) {
targetProductMatched = true;
Logger.log(`🎯 TARGET PRODUCT SUCCESSFULLY MATCHED: "${productTitle}" (ID: ${productId}, Stock Status: ${stockInfo.availability})`);
Logger.log(` Performance: Clicks: ${performance.totalClicks}, Cost: ${performance.totalCost.toFixed(2)}, Conversions: ${performance.totalConversions}`);
}
// Log first few matches for debugging
if (DETAILED_LOGGING && matchedCount <= 3) {
Logger.log(`✅ Match ${matchedCount}: "${productTitle}" (ID: ${productId}, Stock: ${stockInfo.availability})`);
}
} else {
unmatchedCount++;
unmatchedProducts.push(productTitle);
unmatchedIds.push(productId);
if (isTargetProduct) {
Logger.log(`🎯 TARGET PRODUCT FOUND IN PERFORMANCE BUT NOT IN STOCK: "${productTitle}" (ID: ${productId})`);
Logger.log(` Lookup key used: "${lookupKey}", Case insensitive: ${CASE_INSENSITIVE_MATCHING}`);
}
// Log first few unmatched for debugging
if (DETAILED_LOGGING && unmatchedCount <= 3) {
Logger.log(`❌ No match ${unmatchedCount}: "${productTitle}" (ID: ${productId}, Lookup key: ${lookupKey})`);
}
// Store unmatched product data for export
const unmatchedConvRate = performance.totalClicks > 0 ? performance.totalConversions / performance.totalClicks : 0;
const unmatchedCpa = performance.totalConversions > 0 ? performance.totalCost / performance.totalConversions : 0;
const unmatchedRoas = performance.totalCost > 0 ? performance.totalConversionValue / performance.totalCost : 0;
unmatchedProductsData.push([
performance.campaignName,
performance.productTitle,
performance.productItemId,
'NO_STOCK_DATA',
performance.totalImpressions,
performance.totalClicks,
Number(performance.totalCost.toFixed(2)),
Number(performance.totalConversions.toFixed(2)),
Number(performance.totalConversionValue.toFixed(2)),
Number((unmatchedConvRate * 100).toFixed(2)),
Number(unmatchedCpa.toFixed(2)),
Number(unmatchedRoas.toFixed(2)),
performance.lastDate
]);
}
}
Logger.log(`Data combination complete: ${matchedCount} products matched with stock data, ${unmatchedCount} unmatched`);
if (TARGET_PRODUCT_ID && !targetProductMatched) {
Logger.log(`⚠️ TARGET PRODUCT "${TARGET_PRODUCT_ID}" was found in performance data but could not be matched with stock data`);
}
if (unmatchedCount > 0) {
Logger.log(`Unmatched performance products (showing first 5):`);
for (let i = 0; i < Math.min(5, unmatchedProducts.length); i++) {
Logger.log(` - "${unmatchedProducts[i]}" (ID: ${unmatchedIds[i]})`);
}
if (unmatchedProducts.length > 5) {
Logger.log(` ... and ${unmatchedProducts.length - 5} more products`);
}
}
// Store unmatched data globally for export
globalUnmatchedProductsData = unmatchedProductsData;
return combinedData;
}
function filterProductsForMonitoring(data) {
let outOfStockCount = 0;
let inStockCount = 0;
let targetProductFiltered = false;
if (DETAILED_LOGGING) {
Logger.log("🔍 Starting product filtering for monitoring thresholds...");
Logger.log(`Thresholds: Min clicks: ${MIN_CLICKS_THRESHOLD}, Min cost: ${MIN_COST_THRESHOLD}`);
}
const filteredData = data.filter(row => {
const campaignName = row[0];
const productTitle = row[1];
const productId = row[2];
const availability = row[3]; // availability
const clicks = row[5]; // totalClicks (corrected index)
const cost = row[6]; // totalCost (corrected index)
// Check if this is our target product
const isTargetProduct = TARGET_PRODUCT_ID && (productId === TARGET_PRODUCT_ID || (CASE_INSENSITIVE_MATCHING && productId.toLowerCase() === TARGET_PRODUCT_ID.toLowerCase()));
// Only monitor products that meet click/cost thresholds
const meetsThresholds = clicks >= MIN_CLICKS_THRESHOLD && cost >= MIN_COST_THRESHOLD;
if (isTargetProduct) {
Logger.log(`🎯 TARGET PRODUCT FILTERING: "${productTitle}" (ID: ${productId})`);
Logger.log(` Clicks: ${clicks} (threshold: ${MIN_CLICKS_THRESHOLD}), Cost: ${cost} (threshold: ${MIN_COST_THRESHOLD})`);
Logger.log(` Stock Status: ${availability}, Meets thresholds: ${meetsThresholds}`);
if (meetsThresholds) {
targetProductFiltered = true;
}
}
if (meetsThresholds) {
if (availability === 'OUT_OF_STOCK') {
outOfStockCount++;
if (DETAILED_LOGGING && outOfStockCount <= 3) {
Logger.log(`🚨 Out-of-stock product ${outOfStockCount}: "${productTitle}" (ID: ${productId}, Clicks: ${clicks}, Cost: ${cost.toFixed(2)})`);
}
} else {
inStockCount++;
if (DETAILED_LOGGING && inStockCount <= 3) {
Logger.log(`✅ In-stock product ${inStockCount}: "${productTitle}" (ID: ${productId}, Status: ${availability})`);
}
}
} else {
if (DETAILED_LOGGING && (outOfStockCount + inStockCount) <= 3) {
Logger.log(`⏭️ Skipped (below thresholds): "${productTitle}" (Clicks: ${clicks}, Cost: ${cost.toFixed(2)})`);
}
}
return meetsThresholds;
});
Logger.log(`Filtering complete: ${outOfStockCount} out-of-stock products, ${inStockCount} in-stock products meet thresholds`);
if (TARGET_PRODUCT_ID && !targetProductFiltered) {
Logger.log(`⚠️ TARGET PRODUCT "${TARGET_PRODUCT_ID}" did not meet monitoring thresholds`);
}
return filteredData;
}
function exportToSheet(outOfStockData, unmatchedData) {
// Handle spreadsheet
let ss;
if (!SHEET_URL) {
// Get account name for spreadsheet naming
const accountName = AdsApp.currentAccount().getName();
const spreadsheetName = `${accountName} - Product Stock Status Monitor`;
ss = SpreadsheetApp.create(spreadsheetName);
const url = ss.getUrl();
Logger.log("No SHEET_URL provided. Created new spreadsheet: " + url);
} else {
ss = SpreadsheetApp.openByUrl(SHEET_URL);
}
// Create headers
const headers = [
'Campaign', 'Product Title', 'Product ID', 'Stock Status', 'Impressions', 'Clicks', 'Cost',
'Conversions', 'Conv. Value', 'Conv. Rate (%)', 'CPA', 'ROAS', 'Last Date'
];
// Export out-of-stock products to main tab
let sheet;
if (ss.getSheetByName(TAB)) {
sheet = ss.getSheetByName(TAB);
sheet.clear();
} else {
sheet = ss.insertSheet(TAB);
}
if (outOfStockData.length > 0) {
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
sheet.getRange(2, 1, outOfStockData.length, headers.length).setValues(outOfStockData);
Logger.log(`Successfully wrote ${outOfStockData.length} out-of-stock products to the main tab.`);
} else {
Logger.log("No out-of-stock products to write to spreadsheet.");
sheet.getRange(1, 1).setValue("No out-of-stock products found meeting the monitoring criteria.");
}
// Export unmatched products to separate tab
if (unmatchedData && unmatchedData.length > 0) {
let unmatchedSheet;
if (ss.getSheetByName('Unmatched Products')) {
unmatchedSheet = ss.getSheetByName('Unmatched Products');
unmatchedSheet.clear();
} else {
unmatchedSheet = ss.insertSheet('Unmatched Products');
}
// Add explanation text at the top
const explanationText = [
['❓ UNMATCHED PRODUCTS EXPLANATION'],
[''],
['These products have performance data in Google Ads but NO stock data in Google Merchant Center.'],
[''],
['These products are likely DELETED or REMOVED from your Google Merchant Center feed.'],
[''],
['Common reasons:'],
['• Product was removed from inventory'],
['• Product feed was updated/cleaned'],
['• Product is no longer available for sale'],
['• Data sync delay between Google Ads and GMC'],
['• Product exists in ads but not in current feed'],
[''],
['⚠️ ACTION REQUIRED:'],
['1. Check if these products still exist in your inventory and Google Merchant Center'],
['2. If still available: Re-add to Google Merchant Center feed'],
['3. If data sync issue: Wait 24-48 hours for sync'],
[''],
['---'],
['']
];
// Write explanation text
unmatchedSheet.getRange(1, 1, explanationText.length, 1).setValues(explanationText);
// Write headers and data below explanation
const dataStartRow = explanationText.length + 1;
unmatchedSheet.getRange(dataStartRow, 1, 1, headers.length).setValues([headers]);
unmatchedSheet.getRange(dataStartRow + 1, 1, unmatchedData.length, headers.length).setValues(unmatchedData);
Logger.log(`Successfully wrote ${unmatchedData.length} unmatched products to the 'Unmatched Products' tab.`);
}
}
function generateAlerts(products) {
if (!ALERT_EMAIL) return;
// Get account name
const accountName = AdsApp.currentAccount().getName();
// Filter only out-of-stock products
const outOfStockProducts = products.filter(product => product[3] === 'OUT_OF_STOCK');
if (outOfStockProducts.length === 0) {
return; // Don't send email if no out-of-stock products
}
const subject = `🚨 Out of Stock Alert - ${accountName} - ${outOfStockProducts.length} Product(s)`;
let body = `🚨 OUT OF STOCK PRODUCTS ALERT\n\n`;
body += `Account: ${accountName}\n`;
body += `Date Range: Last 7 days\n\n`;
outOfStockProducts.forEach((product, index) => {
const [campaign, title, id, availability, impressions, clicks, cost, conversions, convValue, convRate, cpa, roas, lastDate] = product;
body += `${index + 1}. ${title}\n`;
body += ` Product ID: ${id}\n`;
body += ` Cost: €${cost.toFixed(2)}\n`;
body += ` Conversions: ${conversions.toFixed(2)}\n`;
body += ` Conv. Value: €${convValue.toFixed(2)}\n`;
body += ` CPA: €${cpa.toFixed(2)}\n`;
body += ` ROAS: ${roas.toFixed(2)}x\n`;
body += ` Last Date: ${lastDate || 'N/A'}\n\n`;
});
try {
MailApp.sendEmail(ALERT_EMAIL, subject, body);
Logger.log(`✅ Out-of-stock alert email sent to ${ALERT_EMAIL}`);
} catch (e) {
Logger.log(`❌ Error sending alert email: ${e}`);
}
}
// Helper function to get products that might be out of stock
// This would need to be enhanced with actual stock status data
function checkForOutOfStockProducts() {
// This is a placeholder for future enhancement
// You could integrate with a Google Sheet that contains stock status
// or use other methods to determine stock status
Logger.log("Stock status checking functionality would be implemented here");
}
// Function to help set up daily scheduling
function setupDailySchedule() {
Logger.log("📅 Setting up daily schedule for Product Stock Monitor...");
Logger.log("To schedule this script to run daily:");
Logger.log("1. Go to Google Ads Scripts");
Logger.log("2. Click on this script");
Logger.log("3. Click 'Schedule'");
Logger.log("4. Set frequency to 'Daily'");
Logger.log("5. Choose your preferred time (recommended: 9:00 AM)");
Logger.log("6. Click 'Save'");
Logger.log("");
Logger.log("The script will now run daily and send email alerts for products meeting your criteria.");
}
// Function to test email functionality
function testEmailAlert() {
Logger.log("🧪 Testing email alert functionality...");
if (!ALERT_EMAIL || ALERT_EMAIL === 'your-email@example.com') {
Logger.log("❌ Please set ALERT_EMAIL to your actual email address first");
return;
}
const testProducts = [
['Test Campaign', 'Test Product 1', 'TEST001', 'OUT_OF_STOCK', 'NEW', 100, 25, 50.00, 5, 250.00, 0.20, 10.00, 5.00, '2024-01-15'],
['Test Campaign', 'Test Product 2', 'TEST002', 'IN_STOCK', 'NEW', 80, 20, 40.00, 3, 150.00, 0.15, 13.33, 3.75, '2024-01-15']
];
generateAlerts(testProducts);
Logger.log("✅ Test email sent! Check your inbox.");
}
That’s it!
Any comments or add-ons?
Happy to hear!