Account Audit Helper v1.8

/**
 * Account Audit Helper v1.8 (Single Account)
 * 
 * Created by Francesco Cifardi
 * For issues or questions reach out on Linkedin (https://www.linkedin.com/in/francescocifardi/)
 * Free to use and share, a mention would be appreciated :-)
 * 
 * This script helps streamline Google Ads account audits by automatically collecting
 * and organizing key account data. It saves 1+ hours of manual checks by gathering
 * information across seven areas:
 * 
 * - Campaign Analysis (settings, targeting, extensions)
 * - Ad Group Analysis (ads, keywords, quality scores)
 * - Audience Analysis (list sizes and eligibility)
 * - Conversion Tracking (action configurations)
 * - Auto-Applied Recommendations (30-day history)
 * - Extension Analysis (account/campaign/ad group coverage)
 * - Performance Max Analysis (campaign settings)
 * 
 * The data is organized into separate sheets in a Google Spreadsheet, with an
 * additional Audit Checks sheet that provides automated insights. Each report
 * can be individually enabled/disabled through a control panel.
 * 
 * Setup:
 * 1. Create a copy of the template spreadsheet: https://docs.google.com/spreadsheets/d/1jJme71tj5mrXPX90ZZd45FWhIqBgh4QRVQPqJ11ueVY/copy
 * 2. Replace SPREADSHEET_URL with your copy's URL
 * 3. Configure which reports to run in the Controls sheet
 * 4. Run the script or schedule it for regular updates
 * 
 * Note: This script is designed to assist with audits by automating data collection.
 * Always combine these insights with your expertise when making optimization decisions.
 */

// Spreadsheet URL - replace this with your spreadsheet URL
var SPREADSHEET_URL = "YOUR_SHEET_URL"; // Template spreadsheet: https://docs.google.com/spreadsheets/d/1jJme71tj5mrXPX90ZZd45FWhIqBgh4QRVQPqJ11ueVY/copy

// Don't change any code below this line
function main() {
    Logger.log("Script started");
    
    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    
    // Get control settings
    var controls = getControlSettings(spreadsheet);
    
    // Process each report based on control settings
    if (controls['Search & Display'] === 'ON') {
        processSearchAndDisplayData(spreadsheet);
    }
    
    if (controls['Ad groups'] === 'ON') {
        processAdGroupData(spreadsheet);
    }
    
    if (controls['Audiences'] === 'ON') {
        processAudienceData(spreadsheet);
    }
    
    if (controls['Conversions'] === 'ON') {
        processConversionData(spreadsheet);
    }
    
    if (controls['Auto-applied'] === 'ON') {
        processAutoAppliedRecommendations(spreadsheet);
    }
    
    if (controls['Extensions'] === 'ON') {
        processExtensionData(spreadsheet);
    }
    
    if (controls['Performance Max'] === 'ON') {
        processPerformanceMaxData(spreadsheet);
    }
    
    Logger.log("Script completed");
}

function getControlSettings(spreadsheet) {
    // Get control settings from the Controls sheet to determine which reports to run
    var controlSheet = getOrCreateSheet(spreadsheet, "Controls");
    
    // Map specific cell locations to control settings
    var controls = {
        'Conversions': controlSheet.getRange("C3").getValue(),
        'Audiences': controlSheet.getRange("C4").getValue(),
        'Performance Max': controlSheet.getRange("C5").getValue(),
        'Search & Display': controlSheet.getRange("C6").getValue(),
        'Ad groups': controlSheet.getRange("C7").getValue(),
        'Extensions': controlSheet.getRange("C8").getValue(),
        'Auto-applied': controlSheet.getRange("C9").getValue(),
        'Include Paused': controlSheet.getRange("C10").getValue()
    };
    
    Logger.log("Control settings loaded: " + JSON.stringify(controls));
    return controls;
}

function getCampaignSelector(spreadsheet) {
    // Create a campaign selector with date range and status filters based on control settings
    var controls = getControlSettings(spreadsheet);
    var selector = AdsApp.campaigns()
        .forDateRange("LAST_30_DAYS");
    
    // Apply campaign status filter based on Include Paused setting
    if (controls['Include Paused'] === 'ON') {
        selector = selector.withCondition("Status IN ['ENABLED', 'PAUSED']");
    } else {
        selector = selector.withCondition("Status = ENABLED");
    }
    
    return selector;
}

function processSearchAndDisplayData(spreadsheet) {
    Logger.log("Starting campaign analysis");
    
    var sheet = getOrCreateSheet(spreadsheet, "Search & Display");
    sheet.getDataRange().clearContent();
    
    // Updated headers - added "Number of" to extension headers (A1:T1)
    sheet.getRange("A1:T1").setValues([[
        "Campaign Name",
        "Campaign Type", 
        "Number of Excluded Placement Lists",
        "Number of Negative Keywords Lists",
        "Number of Targeted Audiences",
        "Number of Excluded Audiences",
        "Number of Targeted Locations",
        "Number of Excluded Locations",
        "Location Targeting",
        "Bidding Strategy",
        "Target",
        "Has Ad Schedule",
        // Extension columns with "Number of" prefix
        "Number of Sitelinks",
        "Number of Callouts",
        "Number of Snippets",
        "Number of Call Extensions",
        "Number of Price Extensions",
        "Number of Location Extensions",
        "Number of Promotion Extensions",
        "Number of Image Extensions"
    ]]);
    
    // Pass spreadsheet to getCampaignSelector
    var campaigns = getCampaignSelector(spreadsheet).get();
    
    var row = 2;
    var campaignCount = 0;
    
    while (campaigns.hasNext()) {
        var campaign = campaigns.next();
        campaignCount++;
        
        var locationSettings = campaign.targeting().getTargetingSetting('USER_INTEREST_AND_LIST');
        var targetingMode = (locationSettings === 'TARGET_ALL_TRUE') ? 'Interest' :
            (locationSettings === 'TARGET_ALL_FALSE') ? 'Presence' :
            locationSettings;
        
        // Count excluded placement lists
        var excludedListsCount = 0;
        var placementLists = campaign.excludedPlacementLists().get();
        while (placementLists.hasNext()) {
            placementLists.next();
            excludedListsCount++;
        }
        
        // Count negative keyword lists
        var negKeywordListsCount = 0;
        var keywordLists = campaign.negativeKeywordLists().get();
        while (keywordLists.hasNext()) {
            keywordLists.next();
            negKeywordListsCount++;
        }
        
        // Count targeted audiences
        var targetedAudiencesCount = 0;
        var targetedAudiences = campaign.targeting().audiences().get();
        while (targetedAudiences.hasNext()) {
            targetedAudiences.next();
            targetedAudiencesCount++;
        }
        
        // Count excluded audiences
        var excludedAudiencesCount = 0;
        var excludedAudiences = campaign.targeting().excludedAudiences().get();
        while (excludedAudiences.hasNext()) {
            excludedAudiences.next();
            excludedAudiencesCount++;
        }
        
        // Get location targeting information
        var targetedLocations = campaign.targeting().targetedLocations().get();
        var excludedLocations = campaign.targeting().excludedLocations().get();
        
        // Get bidding strategy and target information
        var biddingStrategy = campaign.getBiddingStrategyType();
        var target = "None";
        
        // Determine target based on bidding strategy
        switch(biddingStrategy) {
            case "MANUAL_CPC":
                target = "Max CPC";
                break;
            case "TARGET_ROAS":
                try {
                    target = campaign.getBiddingStrategyConfiguration().getTargetRoas() * 100 + "% ROAS";
                } catch(e) {
                    target = "tROAS";
                }
                break;
            case "TARGET_CPA":
                try {
                    target = campaign.getBiddingStrategyConfiguration().getTargetCpa().getMicroAmount() / 1000000 + " CPA";
                } catch(e) {
                    target = "tCPA";
                }
                break;
        }
        
        // Check for ad schedule using the correct method
        var hasAdSchedule = false;
        var adSchedules = campaign.targeting().adSchedules().get();
        if (adSchedules && adSchedules.length > 0) {
            hasAdSchedule = true;
        }
        
        // Get extension counts
        var extensionCounts = getExtensionCounts(campaign, spreadsheet);
        
        sheet.getRange(row, 1, 1, 20).setValues([[
            campaign.getName(),
            campaign.getAdvertisingChannelType(),
            excludedListsCount,
            negKeywordListsCount,
            targetedAudiencesCount,
            excludedAudiencesCount,
            targetedLocations.totalNumEntities(),
            excludedLocations.totalNumEntities(),
            targetingMode,
            biddingStrategy,
            target,
            hasAdSchedule,
            // Extension counts
            extensionCounts.sitelinks,
            extensionCounts.callouts,
            extensionCounts.snippets,
            extensionCounts.call,
            extensionCounts.price,
            extensionCounts.location,
            extensionCounts.promotion,
            extensionCounts.image
        ]]);
        row++;
    }

    Logger.log("Completed campaign analysis for " + campaignCount + " campaigns");
}

function getExtensionCounts(campaign, spreadsheet) {
    var controls = getControlSettings(spreadsheet);
    var counts = {
        sitelinks: 'N/A',
        callouts: 'N/A',
        snippets: 'N/A',
        call: 'N/A',
        price: 'N/A',
        location: 'N/A',
        promotion: 'N/A',
        image: 'N/A'
    };
    
    try {
        // Add status condition to query
        const statusCondition = controls['Include Paused'] === 'ON' 
            ? "campaign_asset.status IN ('ENABLED', 'PAUSED')"
            : "campaign_asset.status = 'ENABLED'";
            
        const query = 
            'SELECT campaign.id, asset.type ' +
            'FROM campaign_asset ' +
            'WHERE campaign.id = "' + campaign.getId() + '" ' +
            'AND ' + statusCondition;
        
        const results = AdsApp.search(query);
        
        // Reset counts to 0 before counting
        Object.keys(counts).forEach(key => counts[key] = 0);
        
        // Count each type of extension
        while (results.hasNext()) {
            const row = results.next();
            switch (row.asset.type) {
                case 'SITELINK':
                    counts.sitelinks++;
                    break;
                case 'CALLOUT':
                    counts.callouts++;
                    break;
                case 'STRUCTURED_SNIPPET':
                    counts.snippets++;
                    break;
                case 'CALL':
                    counts.call++;
                    break;
                case 'PRICE':
                    counts.price++;
                    break;
                case 'LOCATION':
                    counts.location++;
                    break;
                case 'PROMOTION':
                    counts.promotion++;
                    break;
                case 'IMAGE':
                    counts.image++;
                    break;
            }
        }
    } catch (e) {
        Logger.log("Error getting extension counts for campaign: " + campaign.getName() + " - " + e);
    }
    
    return counts;
}

function processAdGroupData(spreadsheet) {
    Logger.log("Starting ad group analysis");
    
    var sheet = getOrCreateSheet(spreadsheet, "Ad groups");
    sheet.getDataRange().clearContent();
    
    // Set headers 
    sheet.getRange("A1:H1").setValues([["Campaign Name", "Campaign Type", "Ad Group Name", 
                                       "Number of Ads", "Number of Keywords", 
                                       "Average Quality Score", "Keywords with QS", 
                                       "Keywords without QS"]]);
    
    var controls = getControlSettings(spreadsheet);
    var campaigns = getCampaignSelector(spreadsheet).get();
    var row = 2;
    var totalAdGroups = 0;
    
    while (campaigns.hasNext()) {
        var campaign = campaigns.next();
        
        // Apply status filter to ad groups
        var adGroupSelector = campaign.adGroups();
        if (controls['Include Paused'] === 'ON') {
            adGroupSelector = adGroupSelector.withCondition("Status IN ['ENABLED', 'PAUSED']");
        } else {
            adGroupSelector = adGroupSelector.withCondition("Status = ENABLED");
        }
        
        var adGroups = adGroupSelector.get();
        while (adGroups.hasNext()) {
            var adGroup = adGroups.next();
            totalAdGroups++;
            
            var stats = getAdGroupStats(adGroup, controls);
            
            sheet.getRange(row, 1, 1, 8).setValues([[
                campaign.getName(),
                campaign.getAdvertisingChannelType(),
                adGroup.getName(),
                stats.adCount,
                stats.keywordCount,
                stats.averageQualityScore || "No QS data",
                stats.keywordsWithQS,
                stats.keywordsWithoutQS
            ]]);
            
            row++;
        }
    }
    
    Logger.log("Completed ad group analysis for " + totalAdGroups + " ad groups");
}

function getAdGroupStats(adGroup, controls) {
    // Count ads in the ad group with status filter
    var adCount = 0;
    var adSelector = adGroup.ads();
    if (controls['Include Paused'] === 'ON') {
        adSelector = adSelector.withCondition("Status IN ['ENABLED', 'PAUSED']");
    } else {
        adSelector = adSelector.withCondition("Status = ENABLED");
    }
    var ads = adSelector.get();
    while (ads.hasNext()) {
        ads.next();
        adCount++;
    }
    
    // Analyze keywords and quality scores with status filter
    var keywordCount = 0;
    var qualityScoreSum = 0;
    var keywordsWithQS = 0;
    var keywordsWithoutQS = 0;
    
    var keywordSelector = adGroup.keywords();
    if (controls['Include Paused'] === 'ON') {
        keywordSelector = keywordSelector.withCondition("Status IN ['ENABLED', 'PAUSED']");
    } else {
        keywordSelector = keywordSelector.withCondition("Status = ENABLED");
    }
    var keywords = keywordSelector.get();
    
    while (keywords.hasNext()) {
        var keyword = keywords.next();
        keywordCount++;
        
        var qualityScore = keyword.getQualityScore();
        if (qualityScore !== null) {
            qualityScoreSum += qualityScore;
            keywordsWithQS++;
        } else {
            keywordsWithoutQS++;
        }
    }
    
    var averageQualityScore = keywordsWithQS > 0 ? (qualityScoreSum / keywordsWithQS).toFixed(2) : null;
    
    return { 
        adCount: adCount, 
        keywordCount: keywordCount,
        averageQualityScore: averageQualityScore,
        keywordsWithQS: keywordsWithQS,
        keywordsWithoutQS: keywordsWithoutQS
    };
}

function processAudienceData(spreadsheet) {
    Logger.log("Starting audience analysis");
    
    var sheet = getOrCreateSheet(spreadsheet, "Audiences");
    sheet.getDataRange().clearContent();
    
    // Set headers for audience data
    sheet.getRange("A1:G1").setValues([[
        "Name",
        "Description",
        "Type",
        "Size for Display",
        "Size for Search",
        "Eligible for Display",
        "Eligible for Search"
    ]]);
    
    var userlists = AdsApp.userlists().get();
    var row = 2;
    var listCount = 0;
    
    while (userlists.hasNext()) {
        try {
            var userList = userlists.next();
            listCount++;
            
            // Safely get values with error handling
            var sizeForDisplay = 0;
            var sizeForSearch = 0;
            var eligibleForDisplay = false;
            var eligibleForSearch = false;
            
            try { sizeForDisplay = userList.getSizeForDisplay() || 0; } catch(e) {}
            try { sizeForSearch = userList.getSizeForSearch() || 0; } catch(e) {}
            try { eligibleForDisplay = userList.isEligibleForDisplay() || false; } catch(e) {}
            try { eligibleForSearch = userList.isEligibleForSearch() || false; } catch(e) {}
            
            sheet.getRange(row, 1, 1, 7).setValues([[
                userList.getName() || "Unknown",
                userList.getDescription() || "No description",
                userList.getType() || "Unknown",
                sizeForDisplay,
                sizeForSearch,
                eligibleForDisplay,
                eligibleForSearch
            ]]);
            row++;
        } catch(e) {
            Logger.log("Error processing audience: " + e.toString());
            continue;
        }
    }
    
    Logger.log("Completed audience analysis for " + listCount + " audiences");
}

function processConversionData(spreadsheet) {
    Logger.log("Starting conversion actions analysis");
    
    var sheet = getOrCreateSheet(spreadsheet, "Conversions");
    sheet.getDataRange().clearContent();
    
    // Set headers for conversion data with clear categorization
    sheet.getRange("A1:E1").setValues([[
        "Conversion Name",
        "Conversion Type",
        "Conversion Category", // Primary or Secondary
        "Action Category",
        "Counting Type"
    ]]);
    
    // Query to get conversion action data with status and configuration
    const query = `
        SELECT 
            conversion_action.name, 
            conversion_action.type, 
            conversion_action.include_in_conversions_metric,
            conversion_action.category,
            conversion_action.counting_type
        FROM conversion_action`;
    
    const report = AdsApp.report(query);
    const rows = report.rows();
    var rowNum = 2;
    var conversionCount = 0;
    
    while (rows.hasNext()) {
        const row = rows.next();
        conversionCount++;
        
        // Convert include_in_conversions_metric to Primary/Secondary
        const category = row['conversion_action.include_in_conversions_metric'] ? 'Primary' : 'Secondary';
        
        sheet.getRange(rowNum, 1, 1, 5).setValues([[
            row['conversion_action.name'],
            row['conversion_action.type'],
            category,
            row['conversion_action.category'],
            row['conversion_action.counting_type']
        ]]);
        rowNum++;
    }
    
    Logger.log("Completed conversion analysis for " + conversionCount + " conversion actions");
}

function processAutoAppliedRecommendations(spreadsheet) {
    Logger.log("Starting auto-applied recommendations analysis");
    
    var sheet = getOrCreateSheet(spreadsheet, "Auto-applied");
    sheet.getDataRange().clearContent();
    
    // Set headers for tracking automated changes
    sheet.getRange("A1:M1").setValues([[
        "Campaign",
        "Ad Group",
        "Change Date",
        "Change Resource Type",
        "Changed Fields",
        "Client Type",
        "Feed",
        "Feed Item",
        "New Resource",
        "Old Resource",
        "Operation",
        "Resource Name",
        "Changed By"
    ]]);
    
    // Calculate date range for last 29 days (API limitation)
    const today = new Date();
    const daysAgo = new Date(today);
    daysAgo.setDate(today.getDate() - 29);
    
    const formattedStart = daysAgo.toISOString().split('T')[0];
    const formattedEnd = today.toISOString().split('T')[0];
    
    const query = `
        SELECT 
            campaign.name,
            ad_group.name,
            change_event.change_date_time,
            change_event.change_resource_type,
            change_event.changed_fields,
            change_event.client_type,
            change_event.feed,
            change_event.feed_item,
            change_event.new_resource,
            change_event.old_resource,
            change_event.resource_change_operation,
            change_event.resource_name,
            change_event.user_email
        FROM change_event
        WHERE change_event.change_date_time >= '${formattedStart}'
            AND change_event.change_date_time <= '${formattedEnd}'
            AND change_event.client_type = 'GOOGLE_ADS_RECOMMENDATIONS_SUBSCRIPTION'
        ORDER BY change_event.change_date_time DESC
        LIMIT 9999`;
    
    const report = AdsApp.report(query);
    const rows = report.rows();
    var rowNum = 2;
    var changeCount = 0;
    
    while (rows.hasNext()) {
        const row = rows.next();
        changeCount++;
        
        sheet.getRange(rowNum, 1, 1, 13).setValues([[
            row['campaign.name'] || 'N/A',
            row['ad_group.name'] || 'N/A',
            row['change_event.change_date_time'],
            row['change_event.change_resource_type'] || '',
            row['change_event.changed_fields'] || '',
            row['change_event.client_type'],
            row['change_event.feed'] || '',
            row['change_event.feed_item'] || '',
            row['change_event.new_resource'] || '',
            row['change_event.old_resource'] || '',
            row['change_event.resource_change_operation'] || '',
            row['change_event.resource_name'] || '',
            row['change_event.user_email'] || 'Auto-applied'
        ]]);
        rowNum++;
    }
    
    if (changeCount > 0) {
        Logger.log("Found " + changeCount + " changes in the last 29 days");
    } else {
        Logger.log("No changes detected in the last 29 days");
    }
}

function getAccountExtensionCounts() {
    Logger.log("Starting extension analysis");
    
    // Initialize counters for all extension types across levels
    var allExtensionCounts = {
        'Account': {
            'Callouts': 0,
            'Mobile Apps': 0,
            'Phone Numbers': 0,
            'Prices': 0,
            'Sitelinks': 0,
            'Snippets': 0,
            'Call Extensions': 0,
            'Location Extensions': 0,
            'Promotion Extensions': 0,
            'Image Extensions': 0
        },
        'Campaign': {
            'Callouts': 0,
            'Mobile Apps': 0,
            'Phone Numbers': 0,
            'Prices': 0,
            'Sitelinks': 0,
            'Snippets': 0,
            'Call Extensions': 0,
            'Location Extensions': 0,
            'Promotion Extensions': 0,
            'Image Extensions': 0
        },
        'Ad Group': {
            'Callouts': 0,
            'Mobile Apps': 0,
            'Phone Numbers': 0,
            'Prices': 0,
            'Sitelinks': 0,
            'Snippets': 0,
            'Call Extensions': 0,
            'Location Extensions': 0,
            'Promotion Extensions': 0,
            'Image Extensions': 0
        }
    };

    try {
        // Query account-level extensions
        const accountQuery = `
            SELECT 
                asset.type
            FROM asset
            WHERE asset.type IN (
                'CALLOUT',
                'MOBILE_APP',
                'CALL',
                'PRICE',
                'SITELINK',
                'STRUCTURED_SNIPPET',
                'LOCATION',
                'PROMOTION',
                'IMAGE'
            )
        `;

        var accountResults = AdsApp.search(accountQuery);
        while (accountResults.hasNext()) {
            var row = accountResults.next();
            var assetType = row.asset.type;
            updateExtensionCounts(allExtensionCounts['Account'], assetType);
        }
        
        // Campaign-level query
        const campaignQuery = `
            SELECT 
                campaign.name,
                asset.type
            FROM campaign_asset
            WHERE asset.type IN (
                'CALLOUT',
                'MOBILE_APP',
                'CALL',
                'PRICE',
                'SITELINK',
                'STRUCTURED_SNIPPET',
                'LOCATION',
                'PROMOTION',
                'IMAGE'
            )
        `;

        var campaignResults = AdsApp.search(campaignQuery);
        while (campaignResults.hasNext()) {
            var row = campaignResults.next();
            var assetType = row.asset.type;
            updateExtensionCounts(allExtensionCounts['Campaign'], assetType);
        }
        
        // Ad Group-level query
        const adGroupQuery = `
            SELECT 
                ad_group.name,
                asset.type
            FROM ad_group_asset
            WHERE asset.type IN (
                'CALLOUT',
                'MOBILE_APP',
                'CALL',
                'PRICE',
                'SITELINK',
                'STRUCTURED_SNIPPET',
                'LOCATION',
                'PROMOTION',
                'IMAGE'
            )
        `;

        var adGroupResults = AdsApp.search(adGroupQuery);
        while (adGroupResults.hasNext()) {
            var row = adGroupResults.next();
            var assetType = row.asset.type;
            updateExtensionCounts(allExtensionCounts['Ad Group'], assetType);
        }
        
        Logger.log("Extension analysis completed successfully");
    } catch (e) {
        Logger.log("Error retrieving extension counts: " + e);
        return {};
    }

    return allExtensionCounts;
}

function updateExtensionCounts(counts, assetType) {
    // Update extension counts based on asset type
    switch (assetType) {
        case 'CALLOUT':
            counts['Callouts']++;
            break;
        case 'MOBILE_APP':
            counts['Mobile Apps']++;
            break;
        case 'CALL':
            counts['Call Extensions']++;
            break;
        case 'PRICE':
            counts['Prices']++;
            break;
        case 'SITELINK':
            counts['Sitelinks']++;
            break;
        case 'STRUCTURED_SNIPPET':
            counts['Snippets']++;
            break;
        case 'LOCATION':
            counts['Location Extensions']++;
            break;
        case 'PROMOTION':
            counts['Promotion Extensions']++;
            break;
        case 'IMAGE':
            counts['Image Extensions']++;
            break;
    }
}

function processExtensionData(spreadsheet) {
    Logger.log("Starting extension data processing");
    
    var sheet = getOrCreateSheet(spreadsheet, "Extensions");
    sheet.getDataRange().clearContent();
    
    // Set headers for extension data
    sheet.getRange("A1:C1").setValues([["Extension Type", "Count", "Level"]]);
    
    var extensionCounts = getAccountExtensionCounts();
    var row = 2;
    
    // Process all levels of extensions
    ['Account', 'Campaign', 'Ad Group'].forEach(level => {
        for (var type in extensionCounts[level]) {
            if (extensionCounts[level][type] > 0) {
                sheet.getRange(row, 1, 1, 3).setValues([[
                    type, 
                    extensionCounts[level][type],
                    level
                ]]);
                row++;
            }
        }
    });
    
    Logger.log("Extension data processing completed");
}

function processPerformanceMaxData(spreadsheet) {
    Logger.log("Starting Performance Max campaign analysis");
    
    var sheet = getOrCreateSheet(spreadsheet, "Performance Max");
    sheet.getDataRange().clearContent();
    
    // Set headers for Performance Max data
    sheet.getRange("A1:G1").setValues([[
        "Campaign Name",
        "Bidding Strategy",
        "Asset Group Count",
        "Shopping Campaign",
        "Merchant ID",
        "Number of Targeted Locations",
        "Number of Excluded Locations"
    ]]);
    
    // Get control settings
    var controls = getControlSettings(spreadsheet);
    
    // Modify the status condition based on Include Paused setting
    var statusCondition = controls['Include Paused'] === 'ON' 
        ? "campaign.status IN ('ENABLED', 'PAUSED')"
        : "campaign.status = 'ENABLED'";
    
    // Updated GAQL query with status condition
    const query = `
        SELECT 
            campaign.name,
            campaign.bidding_strategy_type,
            campaign.id,
            campaign.shopping_setting.merchant_id
        FROM campaign
        WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX"
        AND ${statusCondition}
        ORDER BY campaign.name ASC`;
    
    try {
        const results = AdsApp.search(query);
        var row = 2;
        var campaignCount = 0;
        
        while (results.hasNext()) {
            const result = results.next();
            campaignCount++;
            
            // Get asset group count
            const assetGroupQuery = `
                SELECT asset_group.id 
                FROM asset_group 
                WHERE campaign.id = ${result.campaign.id}`;
            const assetGroupResults = AdsApp.search(assetGroupQuery);
            let assetGroupCount = 0;
            while (assetGroupResults.hasNext()) {
                assetGroupResults.next();
                assetGroupCount++;
            }
            
            // Get merchant ID info
            const merchantId = result.campaign.shoppingSetting?.merchantId || "N/A";
            const isShoppingCampaign = merchantId !== "N/A" ? "Yes" : "No";
            
            // Get location targeting counts using GAQL
            const locationQuery = `
                SELECT 
                    campaign_criterion.location.geo_target_constant 
                FROM campaign_criterion 
                WHERE campaign.id = ${result.campaign.id} 
                AND campaign_criterion.type = "LOCATION"`;
            
            const excludedLocationQuery = `
                SELECT 
                    campaign_criterion.location.geo_target_constant 
                FROM campaign_criterion 
                WHERE campaign.id = ${result.campaign.id} 
                AND campaign_criterion.type = "LOCATION"
                AND campaign_criterion.negative = TRUE`;
            
            let targetedLocations = 0;
            let excludedLocations = 0;
            
            // Count targeted locations
            const targetedResults = AdsApp.search(locationQuery);
            while (targetedResults.hasNext()) {
                targetedResults.next();
                targetedLocations++;
            }
            
            // Count excluded locations
            const excludedResults = AdsApp.search(excludedLocationQuery);
            while (excludedResults.hasNext()) {
                excludedResults.next();
                excludedLocations++;
            }
            
            sheet.getRange(row, 1, 1, 7).setValues([[
                result.campaign.name,
                result.campaign.biddingStrategyType,
                assetGroupCount,
                isShoppingCampaign,
                merchantId,
                targetedLocations,
                excludedLocations
            ]]);
            row++;
        }
        
        Logger.log(`Completed Performance Max analysis for ${campaignCount} campaigns`);
    } catch (e) {
        Logger.log("Error processing Performance Max campaigns: " + e);
        Logger.log(e.stack);
    }
}

function getOrCreateSheet(spreadsheet, sheetName) {
    // Helper function to get or create a sheet if it doesn't exist
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
        sheet = spreadsheet.insertSheet(sheetName);
    }
    return sheet;
}