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 : 23.02.2024 : Updated to v16 api + updated the adgroup counting to consider DSA (thanks Frederique Boitelle for reporting this issue).
Author : Suisseo (Vincent Hsu)
More Info : https://www.suisseo.ch/en/blog/google-ad-grants-script/

1. Detect if Campaigns are set to 'maximize conversion' 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) 
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() {
    runGrantsCheck()
}

function runGrantsCheck() {

    const account = AdsApp.currentAccount().getCustomerId();
    const SpreadsheetUrl = SPREADSHEETURL;
    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();
    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],['=HYPERLINK("https://www.suisseo.ch/en/blog/google-ad-grants-script/","To check for script updates visit : https://www.suisseo.ch/en/blog/google-ad-grants-script/")','','','','','','']
    ], 1, 1);
    access.formatRows([
        ['#00ffff', '#00ffff', '#00ffff', '#00ffff', '#00ffff', '#00ffff', '#00ffff'],
        ['#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00']
    ], 1, 1);

    const emailMessageTitle = "Suisseo Grants Report - " + AdsApp.currentAccount().getName() + " - " + account;
    let emailMessageBody = "Your Google Grants report for the account : " + AdsApp.currentAccount().getName() + " - " + account + " is ready \n\n";
    emailMessageBody += "Here's what we found : \n\n";
    emailMessageBody += "Your CTR for the last 30 days is " + Math.round(ctr30Days * 100) / 100 + "%.\n";
    emailMessageBody +=  "You spent " + Math.round(totalCost30Days*100) / 100 + "$ during the last 30 days.\n\n";
    emailMessageBody += oneWorderSum + " Keywords with one word.\n";
    emailMessageBody += lowQSSum + " Keywords with a quality score under 3.\n";
    emailMessageBody += campaignSums[0] + " campaigns with less than 2 active ad groups.\n";
    emailMessageBody += campaignSums[1] + " campaigns with no geo-targeting.\n";
    emailMessageBody += campaignSums[3] + " ad groups with less than 2 active ads & no RSA.\n";
    emailMessageBody += campaignSums[2] + " campaigns with less than 2 sitelinks.\n\n\n";
    emailMessageBody += "Please visit this spreadsheet for more details: \n" + SPREADSHEETURL;

//    if (new Date().getDay() == 3) {
        sendSimpleTextEmail(emailMessageTitle, ALERTMAILS, emailMessageBody)
//    }
}

function checkCampaigns(SpreadsheetUrl) {
    const campaignTabName = 'Campaign Data';
    const adGroupTabName = 'AdGroup Data';
    let campaignRows = [];
    // less than 2 ad groups, campaign geo, campaign sitelinks, ads per ad group
    let inc = [0, 0, 0, 0];
    let campaignFormatRows = [];
    let adGroupRows = [];
    let adGroupFormatRows = [];
    let status = "Status = ENABLED";
    if(INCLUDEPAUSED == true){ status =  "Status != REMOVED";}
    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();

        //to check if it is set to 'MAXIMIZE_CONVERSIONS'
        const campaignBiddingStrategy = currentCampaign.getBiddingStrategyType();
        const campaignConversions = currentCampaign.getStatsFor('LAST_30_DAYS').getConversions();
        const adGroupIterator = currentCampaign.adGroups()
            .withCondition("Status = ENABLED")
            .get();

        //We need to check if the number of ad groups is greater or equal to 2 or if there is an RSA ad
        const totalNumAdGroups = adGroupIterator.totalNumEntities();
        //the location + the proximity number should be equal to 1 at least
        const totalNumargetedLocation = currentCampaign.targeting().targetedLocations().get().totalNumEntities();
        const totalNumargetedProximity = currentCampaign.targeting().targetedProximities().get().totalNumEntities();
        const totalGeo = totalNumargetedLocation + totalNumargetedProximity;
        const totalCampaignSitelinks = currentCampaign.extensions().sitelinks().get().totalNumEntities();
        const totalAccountSitelinks = checkAccountSiteLinks();
        // Red if not set to Maxime Conversions, green if set to Maximize Conversions
        let campaignBiddingColor = '';
        if ((campaignBiddingStrategy != 'MAXIMIZE_CONVERSIONS') ) {
            campaignBiddingColor = '#f4cccc'
        }
        if ((campaignBiddingStrategy == 'MAXIMIZE_CONVERSIONS') ) {
            campaignBiddingColor = '#d9ead3'
        }
        //Logger.log(campaignName + " : Bid : " + campaignBiddingStrategy + " : Ad groups in campaign : " + totalNumAdGroups + " Targeted Locations + Proximities : " + totalGeo + " Campaign Sitelinks : " + totalCampaignSitelinks );
        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] += 1;
        }
        if (totalGeo < 1) {
            inc[1] += 1;
        }
        if (totalCampaignSitelinks < 2 && totalAccountSitelinks < 2) {
            inc[2] += 1;
        }
        //Lets check the number of ads in each ad group
        while (adGroupIterator.hasNext()) {
            const currentAdGroup = adGroupIterator.next();
            const adsIterator = currentAdGroup.ads()
                .withCondition("Status = ENABLED")
                .get();
                
            const adsIteratorRSA = currentAdGroup.ads()
	        .withCondition("Status = ENABLED")
	        .withCondition("ad_group_ad.ad.type IN (RESPONSIVE_SEARCH_AD)")
                .get();
		
                
            if (adsIterator.totalNumEntities() < 2 && adsIteratorRSA.totalNumEntities() == 0) {
                const currentAdGroupName = currentAdGroup.getName();
                inc[3] += 1;
                //Logger.log("Ad group : " + currentAdGroupName + " : has less than 2 enabled ads and no RSA" )
                adGroupRows.push([campaignName, currentAdGroupName, 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 status = "WHERE ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' AND ad_group_criterion.status = 'ENABLED' AND ad_group_criterion.negative = false ";
    if (INCLUDEPAUSED == true) {
        status = "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']);
    const report = AdsApp.report("SELECT ad_group_criterion.keyword.text, campaign.name, campaign.status, ad_group.name, ad_group.status, ad_group_criterion.status " +
        "FROM keyword_view " +
        status +
        "AND segments.date during LAST_30_DAYS", {
            apiVersion: 'v16'
        });
    // Logger.log( "One Worder Keyywords" )
    const rows = report.rows();
    while (rows.hasNext()) {
        const row = rows.next();
        let kwLength = countWords(row['ad_group_criterion.keyword.text']);

        if (kwLength == 1) {
            kwLength = countWords(row['ad_group_criterion.keyword.text'].replace(/[|&|\/|\\|#|,|+|(|)|\-|$|~|%|.|'|"|:|*|?|<|>|{|}|]/g, ' ').trim());

            if (kwLength == 1) {
                //Logger.log( row['ad_group_criterion.keyword.text'] + ' -> ' + row['CampaignName'] + ' -> ' + row['AdGroupName']);
                let authorized = false
                for (let i in authorizedOneWordersArray) {
                    if (authorizedOneWordersArray[i][0].toLowerCase() == row['ad_group_criterion.keyword.text'].toLowerCase().replace(/^\+/, '')) {
                        authorized = true;
                        //Logger.log(authorizedOneWordersArray[i][0]); 
                        break
                    }
                }
                for (let p = 0; p < branded.length; p++) {
                    if (row['ad_group_criterion.keyword.text'].toLowerCase().replace(/^\+/, '').indexOf(branded[p].toLowerCase()) != -1) {
                        authorized = true;
                        //Logger.log(branded[p]); 
                        break
                    }
                }
                if (authorized == false) {
                    singleWordRows.push([row['campaign.name'], row['ad_group.name'], row['ad_group_criterion.keyword.text']]);
                    singleWordFormatRows.push(['', '', '#f4cccc']);
                    incW += 1;
                }
            }
        }
    }
    const access = new SpreadsheetAccess(SpreadsheetUrl, singleWordTabName);
    access.clearAll();
    access.writeRows(singleWordRows, 1, 1);
    access.formatRows(singleWordFormatRows, 2, 1);
    access.freezeFirstRow();
    //Logger.log('Found ' + incW + ' Keywords with one word')
    function countWords(str) {
        return str.trim().split(/\s+/).length;
    }

    return incW
}

function getLowQualityKeywords(SpreadsheetUrl) {

    const lowQsTabName = 'Low QS';
    let lowQsRows = [];
    let lowQsFormatRows = [];
    let status = "WHERE ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' AND ad_group_criterion.status = 'ENABLED' AND ad_group_criterion.quality_info.quality_score <= 2";
    if (INCLUDEPAUSED == true) {
        status = "WHERE ad_group.status != 'REMOVED' AND campaign.status != 'REMOVED' AND ad_group_criterion.status != 'REMOVED' AND ad_group_criterion.quality_info.quality_score <= 2";
    }
    lowQsRows.push(['CAMPAIGN NAME', 'AD GROUP NAME', 'KEYWORD']);
    let inc = 0;
    const report = AdsApp.report("SELECT ad_group_criterion.keyword.text, campaign.name, campaign.status, ad_group.name, ad_group.status, ad_group_criterion.status, ad_group_criterion.quality_info.quality_score " +
        "FROM keyword_view " +
        status, {
            apiVersion: 'v16'
        });
    //Logger.log( "Low Quality Keywords <=2" )
    const rows = report.rows();
    while (rows.hasNext()) {
        const row = rows.next();
        //Logger.log( row['ad_group_criterion.keyword.text'] + ' -> ' + row['campaign.name'] + ' -> ' + row['ad_group.name']);
        lowQsRows.push([row['campaign.name'], row['ad_group.name'], row['ad_group_criterion.keyword.text']]);
        lowQsFormatRows.push(['', '', '#f4cccc']);
        inc += 1;
    }
    const access = new SpreadsheetAccess(SpreadsheetUrl, lowQsTabName);
    access.clearAll();
    access.writeRows(lowQsRows, 1, 1);
    access.formatRows(lowQsFormatRows, 2, 1);
    access.freezeFirstRow();
    //Logger.log('Found ' + inc + ' Keywords with QS <= 2')
  
    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 ctr7d = AdsApp.currentAccount().getStatsFor("LAST_7_DAYS").getCtr() * 100;
    const ctr14d = AdsApp.currentAccount().getStatsFor("LAST_14_DAYS").getCtr() * 100;
    const ctr30d = AdsApp.currentAccount().getStatsFor("LAST_30_DAYS").getCtr() * 100;

    //Logger.log(ctr7d + ' : ' + ctr14d + ' : ' + ctr30d)
    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
}

function checkAccountSiteLinks() {
    //check account Sitelinks
    const accountSitelinkSelector = AdsApp.currentAccount().extensions().sitelinks()
    const accountSitelinkIterator = accountSitelinkSelector.get();
    const totalAccountSitelinks = accountSitelinkIterator.totalNumEntities();
    //Logger.log("Total Account Sitelinks " + totalAccountSitelinks)
    return totalAccountSitelinks
}

function SpreadsheetAccess(spreadsheetUrl, sheetName) {

    this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    this.sheet = this.spreadsheet.getSheetByName(sheetName);
    this.writeRows = function(rows, startRow, startColumn) {
        this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).
        setValues(rows);
    };
    this.formatRows = function(rows, startRow, startColumn) {
        if (rows[0]) {
            this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).
            setBackgrounds(rows);
        }
    };

    this.getRows = function(startColumn, endColumn) {
        const data = this.sheet.getDataRange().getValues();
        return data
    };

    this.clearAll = function() {
        this.sheet.clear();
    };
  
    this.freezeFirstRow = function() {
        this.sheet.setFrozenRows(1);   
    };
}

//send e-mail
function sendSimpleTextEmail(title, emails, message) {
    let recipients = '';
    for (let key in emails) {
        recipients += emails[key] + ',';

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

//Get single keywords from Suisseo's Spreadsheet
function getAuthorizedSingleWords() {
    let words = [];
    const tabName = 'All'
    const singleKwSheet = "https://docs.google.com/spreadsheets/d/1wmllliOrBtxAn-qhT9O7BfJMLKs7MAYt50wNgUkTBPw/edit#gid=0"
    const access = new SpreadsheetAccess(singleKwSheet, tabName);
    const data = removeDuplicateInMultiArray(access.getRows());
    //for (i in data) {
    //  Logger.log(data[i][0]);
    //}

    return data
}

//Remove duplicates from first column in 2d array
function removeDuplicateInMultiArray(arr) {
    let uniqueArray = [];
    for (let i = 0; i < arr.length; i++) {
        let found = false;
        for (let z = 0; z < uniqueArray.length; z++) {
            if (arr[i][0] == uniqueArray[z][0]) {
                found = true;
                break;
            }
        }
        if (found == false) {
            uniqueArray.push(arr[i]);
        }
    }
    return uniqueArray
}

Hi,

Accès to https://docs.google.com/spreadsheets/d/YOURSPREADSHEETKEY/edit#gid=0 no work?

Regards

Hi Mirulu, please read the installation guide here : https://www.suisseo.ch/en/blog/google-ad-grants-script/

IMPORTANT UPDATE : Updated API version to v201806

IMPORTANT UPDATE : Updated API version to v201809

Updated some stuff : Please update