vhsu · February 24, 2024 23:14 · mirulu · May 30, 2018 · vhsu · Jun 4, 2018
diff --git a/Suisseograntsscript.js b/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
 }
	/**********************************************************************************************************************
	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
	}