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
// This script monitors product performance and checks actual stock status
// Written by Matinique Roelse from Adcrease. Senior-only Google Ads agency.
// Linkedin: https://www.linkedin.com/in/matiniqueroelse/
// Website: https://www.adcrease.nl
//
// 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
const MIN_COST_THRESHOLD = 0; // Minimum cost to consider a product for monitoring
const ALERT_EMAIL = 'ads@adcrease.nl'; // Replace with your email address
const DAILY_ALERT_ENABLED = true; // Set to false to disable daily alerts
//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
`;
const PERFORMANCE_QUERY = `
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 DURING LAST_7_DAYS
AND campaign.status = "ENABLED"
ORDER BY metrics.clicks DESC
`;
function main() {
try {
Logger.log("Starting Product Stock Status Monitor...");
// Calculate and log date range
const today = new Date();
const sevenDaysAgo = new Date(today.getTime() - (7 * 24 * 60 * 60 * 1000));
const dateRange = `${sevenDaysAgo.toISOString().split('T')[0]} to ${today.toISOString().split('T')[0]}`;
Logger.log(`Date range for analysis: ${dateRange} (Last 7 days)`);
// 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 performanceRows = AdsApp.search(PERFORMANCE_QUERY);
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.`);
Logger.log(`These products meet your performance criteria (clicks ≥ ${MIN_CLICKS_THRESHOLD}, cost ≥ ${MIN_COST_THRESHOLD}).`);
} catch (e) {
Logger.log(`Error in main function: ${e}`);
}
}
function processStockData(rows) {
const stockMap = new Map();
let processedCount = 0;
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) {
stockMap.set(id, {
resourceName,
title,
id,
availability,
condition,
brand
});
processedCount++;
}
} catch (e) {
Logger.log("Error processing stock row: " + e);
}
}
Logger.log(`Processed ${processedCount} products with stock status`);
return stockMap;
}
function processPerformanceData(rows) {
const performanceMap = new Map();
let processedCount = 0;
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 (by product only, not by campaign)
const productKey = productItemId;
if (!performanceMap.has(productKey)) {
performanceMap.set(productKey, {
productTitle,
productItemId,
totalImpressions: 0,
totalClicks: 0,
totalCost: 0,
totalConversions: 0,
totalConversionValue: 0,
lastDate: date,
dayCount: 0,
campaigns: new Set() // Track which campaigns this product appears in
});
}
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++;
product.campaigns.add(campaignName); // Add campaign to the set
processedCount++;
}
} catch (e) {
Logger.log("Error processing performance row: " + e);
}
}
Logger.log(`Processed ${processedCount} performance data rows`);
return performanceMap;
}
function combineStockAndPerformanceData(stockData, performanceData) {
const combinedData = [];
let matchedCount = 0;
let unmatchedCount = 0;
const unmatchedProducts = [];
const unmatchedIds = [];
const unmatchedProductsData = [];
for (const [productKey, performance] of performanceData) {
const productTitle = performance.productTitle;
const productId = performance.productItemId;
// Match by product ID (much more reliable than title matching)
const stockInfo = stockData.get(productId);
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;
// Create campaign list for display
const campaignList = Array.from(performance.campaigns).join(', ');
combinedData.push([
campaignList, // Show all campaigns this product appears in
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++;
} else {
unmatchedCount++;
unmatchedProducts.push(productTitle);
unmatchedIds.push(productId);
// 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;
// Create campaign list for display
const unmatchedCampaignList = Array.from(performance.campaigns).join(', ');
unmatchedProductsData.push([
unmatchedCampaignList, // Show all campaigns this product appears in
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 (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 belowThresholdCount = 0;
let belowClicksCount = 0;
let belowCostCount = 0;
const filteredData = data.filter(row => {
const availability = row[3]; // availability
const clicks = row[5]; // totalClicks (corrected index)
const cost = row[6]; // totalCost (corrected index)
const productId = row[2]; // product ID
// Only monitor products that meet click/cost thresholds
const meetsClicksThreshold = clicks >= MIN_CLICKS_THRESHOLD;
const meetsCostThreshold = cost >= MIN_COST_THRESHOLD;
const meetsThresholds = meetsClicksThreshold && meetsCostThreshold;
if (meetsThresholds) {
if (availability === 'OUT_OF_STOCK') {
outOfStockCount++;
} else {
inStockCount++;
}
} else {
belowThresholdCount++;
if (!meetsClicksThreshold) belowClicksCount++;
if (!meetsCostThreshold) belowCostCount++;
}
return meetsThresholds;
});
Logger.log(`Filtering complete: ${outOfStockCount} out-of-stock products, ${inStockCount} in-stock products meet thresholds`);
Logger.log(`Products below thresholds: ${belowThresholdCount} total`);
Logger.log(` - Below clicks threshold (${MIN_CLICKS_THRESHOLD}): ${belowClicksCount}`);
Logger.log(` - Below cost threshold (${MIN_COST_THRESHOLD}): ${belowCostCount}`);
Logger.log(`Total products processed: ${data.length}`);
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}`);
}
}
That’s it!
Any comments or add-ons?
Happy to hear!
