Google Grants Report Google Ads Script By Suisseo (Single Account Script)

Suisseograntsscript.js

/**********************************************************************************************************************
This Google Ads script checks a single Google grants account for mandatory requirements and logs the results in a Google Spreadsheet
UPDATED : 27.12.2025 : Updated to v21 api + Added mandatory status fields to SELECT clauses to prevent QueryError.EXPECTED_REFERENCED_FIELD_IN_SELECT_CLAUSE. FAILSAFE: Handles broken external spreadsheet links gracefully
Author : Suisseo (Vincent Hsu)
More Info : https://www.suisseo.ch/en/blog/google-ad-grants-script/
F
1. Detect if Campaigns are set to 'maximize conversion' or 'maximaise conversion value' to allow bids higher that 2 dollars
2. Detect if each Campaign has at least 2 active ad groups with at least 2 active text (or at least 1 RSA) This is not mandatory but still good practice 
3. Detect if each account has at least 2 active Sitelinks
4. Detect if each campaign has geo-targeting .
5. Detect Keywords that have a quality score under 3
6. Detect single keywords that are not branded or not in the authorized list
**********************************************************************************************************************/

//The url of the Spreadsheet
//Copy this template Google Spreadsheet in your Google Drive account : https://docs.google.com/spreadsheets/d/1rYif4Z9cTF1WmCRRl2w9vIOFy_ivs22_UpRP_qYHv08/copy
//You can change the name of the Spreadsheet, add Tabs, but do not change the names of the tabs in your Spreadsheet. 
//Save the url of and paste it below
var SPREADSHEETURL = 'https://docs.google.com/spreadsheets/d/YOURSPREADSHEETKEY/edit#gid=0';
//Array of e-mails to which a notification should be sent every time the report is executed, comma separated
var ALERTMAILS = ['YOUREMAIL@YOURDOMAIN:COM'];
//list of branded single keywords that should not be taken into account (any single keyword that contains any of these will not be reported), comma separated
var BRANDEDKEYWORDS = ['YOURBRAND','ANOTHERBRANDEDKEYWORD'];
//include paused campaigns, ad groups and keywords in the reports can be set to true or false 
var INCLUDEPAUSED = false;
var authorizedOneWordersArray = getAuthorizedSingleWords();

function main() {
    try {
        runGrantsCheck();
    } catch (e) {
        console.error("Critical Error in main execution: " + e);
        if (ALERTMAILS && ALERTMAILS[0].indexOf('@') > -1) {
            MailApp.sendEmail(ALERTMAILS.join(','), "Grants Script Failed", "The script encountered a critical error: " + e);
        }
    }
}

function runGrantsCheck() {
    const account = AdsApp.currentAccount().getCustomerId();
    const accountName = AdsApp.currentAccount().getName();
    
    // Run Checks
    const campaignSums = checkCampaigns(SPREADSHEETURL);
    const lowQSSum = getLowQualityKeywords(SPREADSHEETURL);
    const oneWorderSum = getOneWorders(SPREADSHEETURL, BRANDEDKEYWORDS);
    const ctr30Days = getAccountCtr(SPREADSHEETURL);
    const totalCost30Days = AdsApp.currentAccount().getStatsFor("LAST_30_DAYS").getCost();

    // Write Overview to Spreadsheet
    const access = new SpreadsheetAccess(SPREADSHEETURL, 'Abstract');
    access.clearAll();
    access.writeRows([
        ['Single keywords', 'Keywords with a quality \nscore smaller than 3', 'Campaigns with less \nthan 2 ad groups', 'Campaigns with \nno geo-targeting', 'Ad groups with less \nthan 2 active ads & no RSA', 'Campaigns with less \nthan 2 sitelinks', 'CTR 30 days'],
        [oneWorderSum, lowQSSum, campaignSums[0], campaignSums[1], campaignSums[3], campaignSums[2], ctr30Days],
        ['Last Run: ' + new Date(), '', '', '', '', '', '']
    ], 1, 1);
    
    access.formatRows([
        ['#00ffff', '#00ffff', '#00ffff', '#00ffff', '#00ffff', '#00ffff', '#00ffff'],
        ['#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00']
    ], 1, 1);

    // Prepare Email
    const emailMessageTitle = "Grants Report - " + accountName + " (" + account + ")";
    let emailMessageBody = "Your Google Grants report for " + accountName + " (" + account + ") is ready.\n\n";
    emailMessageBody += "Summary:\n";
    emailMessageBody += "- CTR (Last 30 Days): " + Math.round(ctr30Days * 100) / 100 + "% " + (ctr30Days < 5 ? "(WARNING: Below 5%)" : "(Good)") + "\n";
    emailMessageBody += "- Spend (Last 30 Days): $" + Math.round(totalCost30Days * 100) / 100 + "\n\n";
    emailMessageBody += "Issues Found:\n";
    emailMessageBody += "- Single Word Keywords: " + oneWorderSum + "\n";
    emailMessageBody += "- Low Quality Score (<3): " + lowQSSum + "\n";
    emailMessageBody += "- Campaigns with <2 Ad Groups: " + campaignSums[0] + "\n";
    emailMessageBody += "- Campaigns with No Geo-Targeting: " + campaignSums[1] + "\n";
    emailMessageBody += "- Ad Groups with Insufficient Ads: " + campaignSums[3] + "\n";
    emailMessageBody += "- Campaigns with <2 Sitelinks: " + campaignSums[2] + "\n\n";
    emailMessageBody += "Full details here: \n" + SPREADSHEETURL;
F
    // Send Email
    if (ALERTMAILS && ALERTMAILS.length > 0 && ALERTMAILS[0].indexOf('@') > -1) {
        sendSimpleTextEmail(emailMessageTitle, ALERTMAILS, emailMessageBody);
    }
}

function checkCampaigns(SpreadsheetUrl) {
    const campaignTabName = 'Campaign Data';
    const adGroupTabName = 'AdGroup Data';
    let campaignRows = [];
    let inc = [0, 0, 0, 0]; // [AdGroups<2, Geo<1, Sitelinks<2, Ads<2]
    let campaignFormatRows = [];
    let adGroupRows = [];
    let adGroupFormatRows = [];
    
    let status = "Status = ENABLED";
    if (INCLUDEPAUSED == true) status = "Status != REMOVED";

    // Pre-fetch Sitelink Assets via GAQL (v21)
    const campaignSitelinkCounts = getCampaignSitelinkCounts();
    const totalAccountSitelinks = getAccountSitelinkCount();

    campaignRows.push(['CAMPAIGN NAME', 'BIDDING STRATEGY', "CONVERSIONS 30 DAYS", 'ACTIVE AD GROUPS', 'TARGETED LOCATIONS', 'CAMPAIGN SITELINKS', 'ACCOUNT SITELINKS']);
    adGroupRows.push(['CAMPAIGN NAME', 'AD GROUP NAME', 'ENABLED ADS']);

    const campaignIterator = AdsApp.campaigns()
        .withCondition(status)
        .forDateRange("LAST_30_DAYS")
        .get();

    while (campaignIterator.hasNext()) {
        const currentCampaign = campaignIterator.next();
        const campaignName = currentCampaign.getName();
        const campaignId = currentCampaign.getId();
        
        const campaignBiddingStrategy = currentCampaign.getBiddingStrategyType();
        const campaignConversions = currentCampaign.getStatsFor('LAST_30_DAYS').getConversions();

        // Check Ad Groups
        const adGroupIterator = currentCampaign.adGroups().withCondition("Status = ENABLED").get();
        const totalNumAdGroups = adGroupIterator.totalNumEntities();

        // Check Geo Targeting
        const totalNumTargetedLocation = currentCampaign.targeting().targetedLocations().get().totalNumEntities();
        const totalNumTargetedProximity = currentCampaign.targeting().targetedProximities().get().totalNumEntities();
        const totalGeo = totalNumTargetedLocation + totalNumTargetedProximity;

        // Check Sitelinks (From Map)
        const totalCampaignSitelinks = campaignSitelinkCounts[campaignId] || 0;

        // Formatting
        let campaignBiddingColor = (campaignBiddingStrategy == 'MAXIMIZE_CONVERSIONS' || campaignBiddingStrategy == 'MAXIMIZE_CONVERSION_VALUE') ? '#d9ead3' : '#f4cccc';

        campaignRows.push([campaignName, campaignBiddingStrategy, campaignConversions, totalNumAdGroups, totalGeo, totalCampaignSitelinks, totalAccountSitelinks]);
        campaignFormatRows.push([' ', campaignBiddingColor, '', totalNumAdGroups < 2 ? '#f4cccc' : '#d9ead3', totalGeo < 1 ? '#f4cccc' : '#d9ead3', totalCampaignSitelinks < 2 ? '#f4cccc' : '#d9ead3', totalAccountSitelinks < 2 ? '#f4cccc' : '#d9ead3']);

        if (totalNumAdGroups < 2) inc[0]++;
        if (totalGeo < 1) inc[1]++;
        // Policy: Needs 2 sitelinks at campaign OR account level
        if (totalCampaignSitelinks < 2 && totalAccountSitelinks < 2) inc[2]++;

        // Check Ads inside Ad Groups
        while (adGroupIterator.hasNext()) {
            const currentAdGroup = adGroupIterator.next();
            // Count standard Enabled Ads
            const adsIterator = currentAdGroup.ads().withCondition("Status = ENABLED").get();
            // Count Enabled RSAs specifically
            const adsIteratorRSA = currentAdGroup.ads()
                .withCondition("Status = ENABLED")
                .withCondition("Type = RESPONSIVE_SEARCH_AD")
                .get();

            // Rule: At least 2 active ads OR 1 active RSA
            if (adsIterator.totalNumEntities() < 2 && adsIteratorRSA.totalNumEntities() == 0) {
                inc[3]++;
                adGroupRows.push([campaignName, currentAdGroup.getName(), adsIterator.totalNumEntities()]);
                adGroupFormatRows.push(['', '', '#f4cccc']);
            }
        }
    }

    let access = new SpreadsheetAccess(SpreadsheetUrl, campaignTabName);
    access.clearAll();
    access.writeRows(campaignRows, 1, 1);
    access.formatRows(campaignFormatRows, 2, 1);
    access.freezeFirstRow();

    access = new SpreadsheetAccess(SpreadsheetUrl, adGroupTabName);
    access.clearAll();
    access.writeRows(adGroupRows, 1, 1);
    access.formatRows(adGroupFormatRows, 2, 1);
    access.freezeFirstRow();

    return inc;
}

function getOneWorders(SpreadsheetUrl, branded) {
    let incW = 0;
    const singleWordTabName = 'Single Word';
    let singleWordRows = [];
    let singleWordFormatRows = [];
    
    let statusClause = "WHERE ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' AND ad_group_criterion.status = 'ENABLED' AND ad_group_criterion.negative = false ";
    if (INCLUDEPAUSED) {
        statusClause = "WHERE ad_group.status != 'REMOVED' AND campaign.status != 'REMOVED' AND ad_group_criterion.status != 'REMOVED' AND ad_group_criterion.negative = false ";
    }

    singleWordRows.push(['CAMPAIGN NAME', 'AD GROUP NAME', 'KEYWORD']);

    // FIXED QUERY: Added campaign.status, ad_group.status, ad_group_criterion.status to SELECT
    const report = AdsApp.report(
        "SELECT ad_group_criterion.keyword.text, campaign.name, ad_group.name, campaign.status, ad_group.status, ad_group_criterion.status " + 
        "FROM keyword_view " + 
        statusClause + 
        "AND segments.date during LAST_30_DAYS", 
        { apiVersion: 'v21' }
    );

    const rows = report.rows();
    while (rows.hasNext()) {
        const row = rows.next();
        const originalKw = row['ad_group_criterion.keyword.text'];
        
        // Strip match types and special chars for word counting
        let cleanKw = originalKw.replace(/[|&|\/|\\|#|,|+|(|)|\-|$|~|%|.|'|"|:|*|?|<|>|{|}|\[|\]]/g, ' ').trim();
        let wordCount = cleanKw.split(/\s+/).length;
        
        if (wordCount === 1) {
            let authorized = false;
            
            // 1. Check Global Allowed List
            for (let i = 0; i < authorizedOneWordersArray.length; i++) {
                if (authorizedOneWordersArray[i][0] && authorizedOneWordersArray[i][0].toLowerCase() === cleanKw.toLowerCase()) {
                    authorized = true;
                    break;
                }
            }
            
            // 2. Check Custom Branded List
            if (!authorized && branded) {
                for (let p = 0; p < branded.length; p++) {
                    if (cleanKw.toLowerCase().indexOf(branded[p].toLowerCase()) !== -1) {
                        authorized = true;
                        break;
                    }
                }
            }

            if (!authorized) {
                singleWordRows.push([row['campaign.name'], row['ad_group.name'], originalKw]);
                singleWordFormatRows.push(['', '', '#f4cccc']);
                incW++;
            }
        }
    }
    
    const access = new SpreadsheetAccess(SpreadsheetUrl, singleWordTabName);
    access.clearAll();
    access.writeRows(singleWordRows, 1, 1);
    access.formatRows(singleWordFormatRows, 2, 1);
    access.freezeFirstRow();
    
    return incW;
}

function getLowQualityKeywords(SpreadsheetUrl) {
    const lowQsTabName = 'Low QS';
    let lowQsRows = [];
    let lowQsFormatRows = [];
    
    let statusClause = "WHERE ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' AND ad_group_criterion.status = 'ENABLED' AND ad_group_criterion.quality_info.quality_score <= 2 AND ad_group_criterion.quality_info.quality_score > 0";
    if (INCLUDEPAUSED) {
        statusClause = "WHERE ad_group.status != 'REMOVED' AND campaign.status != 'REMOVED' AND ad_group_criterion.status != 'REMOVED' AND ad_group_criterion.quality_info.quality_score <= 2 AND ad_group_criterion.quality_info.quality_score > 0";
    }
    
    lowQsRows.push(['CAMPAIGN NAME', 'AD GROUP NAME', 'KEYWORD', 'QS']);
    let inc = 0;
    
    // FIXED QUERY: Added campaign.status, ad_group.status, ad_group_criterion.status to SELECT
    const report = AdsApp.report(
        "SELECT ad_group_criterion.keyword.text, campaign.name, ad_group.name, ad_group_criterion.quality_info.quality_score, campaign.status, ad_group.status, ad_group_criterion.status " + 
        "FROM keyword_view " + statusClause, 
        { apiVersion: 'v21' }
    );

    const rows = report.rows();
    while (rows.hasNext()) {
        const row = rows.next();
        lowQsRows.push([
            row['campaign.name'], 
            row['ad_group.name'], 
            row['ad_group_criterion.keyword.text'], 
            row['ad_group_criterion.quality_info.quality_score']
        ]);
        lowQsFormatRows.push(['', '', '#f4cccc', '#f4cccc']);
        inc++;
    }
    
    const access = new SpreadsheetAccess(SpreadsheetUrl, lowQsTabName);
    access.clearAll();
    access.writeRows(lowQsRows, 1, 1);
    access.formatRows(lowQsFormatRows, 2, 1);
    access.freezeFirstRow();
    return inc;
}

function getAccountCtr(SpreadsheetUrl) {
    const ctrTabName = 'CTR';
    let ctrRows = [];
    let ctrFormatRows = [];
    ctrRows.push(['CTR LAST 7 DAYS', 'CTR LAST 14 DAYS', 'CTR LAST 30 DAYS']);
    
    const stats7 = AdsApp.currentAccount().getStatsFor("LAST_7_DAYS");
    const stats14 = AdsApp.currentAccount().getStatsFor("LAST_14_DAYS");
    const stats30 = AdsApp.currentAccount().getStatsFor("LAST_30_DAYS");
    
    const ctr7d = stats7.getCtr() * 100;
    const ctr14d = stats14.getCtr() * 100;
    const ctr30d = stats30.getCtr() * 100;
    
    ctrRows.push([ctr7d, ctr14d, ctr30d]);
    ctrFormatRows.push([ctr7d < 5 ? '#f4cccc' : '#d9ead3', ctr14d < 5 ? '#f4cccc' : '#d9ead3', ctr30d < 5 ? '#f4cccc' : '#d9ead3']);
    
    const access = new SpreadsheetAccess(SpreadsheetUrl, ctrTabName);
    access.clearAll();
    access.writeRows(ctrRows, 1, 1);
    access.formatRows(ctrFormatRows, 2, 1);
    return ctr30d;
}

// ---------------- ASSET HELPERS (v21 Compatible) ----------------

function getCampaignSitelinkCounts() {
    let counts = {};
    // Check enabled sitelinks attached to enabled campaigns
    // Added campaign.status to SELECT for safety
    const query = "SELECT campaign.id, campaign.status FROM campaign_asset WHERE asset.type = 'SITELINK' AND campaign_asset.status = 'ENABLED' AND campaign.status = 'ENABLED'";
    const result = AdsApp.search(query, {apiVersion: 'v21'});
    
    while (result.hasNext()) {
        const row = result.next();
        const campaignId = row.campaign.id;
        counts[campaignId] = (counts[campaignId] || 0) + 1;
    }
    return counts;
}

function getAccountSitelinkCount() {
    // Check enabled sitelinks attached at account level
    const query = "SELECT asset.id FROM customer_asset WHERE asset.type = 'SITELINK' AND customer_asset.status = 'ENABLED'";
    const result = AdsApp.search(query, {apiVersion: 'v21'});
    return result.totalNumEntities();
}

// ---------------- SPREADSHEET UTILITIES ----------------

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
    this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    this.sheet = this.spreadsheet.getSheetByName(sheetName);
    
    if (!this.sheet) {
        // Auto-create sheet if missing
        this.sheet = this.spreadsheet.insertSheet(sheetName);
    }

    this.writeRows = function(rows, startRow, startColumn) {
        if (rows.length > 0) {
            this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setValues(rows);
        }
    };
    this.formatRows = function(rows, startRow, startColumn) {
        if (rows.length > 0 && rows[0]) {
            this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setBackgrounds(rows);
        }
    };
    this.getRows = function() {
        return this.sheet.getDataRange().getValues();
    };
    this.clearAll = function() {
        this.sheet.clear();
    };
    this.freezeFirstRow = function() {
        this.sheet.setFrozenRows(1);
    };
}

function sendSimpleTextEmail(title, emails, message) {
    MailApp.sendEmail(emails.join(','), title, message);
}

function getAuthorizedSingleWords() {
    // External dependency URL
    const singleKwSheet = "https://docs.google.com/spreadsheets/d/1wmllliOrBtxAn-qhT9O7BfJMLKs7MAYt50wNgUkTBPw/edit#gid=0";
    try {
        const access = new SpreadsheetAccess(singleKwSheet, 'All');
        return removeDuplicateInMultiArray(access.getRows());
    } catch(e) {
        console.warn("WARNING: Could not fetch authorized words list. The external spreadsheet link might be broken. Proceeding with empty list.");
        return []; 
    }
}

function removeDuplicateInMultiArray(arr) {
    let uniqueArray = [];
    for (let i = 0; i < arr.length; i++) {
        if (!arr[i] || arr[i].length === 0) continue;
        let found = false;
        for (let z = 0; z < uniqueArray.length; z++) {
            if (arr[i][0] == uniqueArray[z][0]) {
                found = true;
                break;
            }
        }
        if (!found) uniqueArray.push(arr[i]);
    }
    return uniqueArray;
}

Updated some stuff : Please update