Script: PMax Placement Exclusion Suggestions
What it does:
This script suggests placement exclusions for bad placements where your PMax ads showed.
If new placement exclusions are suggested the placement exclusions are reported via email.
The email contains a link to a Google Doc spreadsheet documenting all the placement exclusions suggestions.
Why you care:
PMax is notorious for showing your ads on placements that are not safe for your brand and/or only deliver fraudulent clicks and leads.
Now go ahead, and install the script (it only takes 5 mins!).
Run the script weekly (I prefer Monday mornings)
And sleep comfortably knowing that all money-wasting and brand-hurting PMax-placements will pop up in your inbox when they appear.
Happy scripting!
NB: If you like this script be sure to also check out my Google Ads Script for Non-Converting Performance Max Search Terms Alerts
INSTRUCTIONS:
– Create a copy of this spreadheet -> https://docs.google.com/spreadsheets/d/1Ehsm9zOQ6ETrZgxZKDdflOUol2AVxE96BhOElncU7fw/copy
– Add the complete URL of the copy of the spreadsheet to the SPREADSHEET_URL below
– Decide on the date range / PERIOD
– Decide on the TLDs that you consider safe and add them to SAFE_TLDS
= Add your keywords that identify spammy domain names to SPAMMY_KEYWORDS
– Add the keywords that identify political content for you to POLITICAL_KEYWORDS
– Set thresholds for MIN_IMPRESSIONS
– Add the name of your Google Ads account to ACCOUNT_NAME
– Add your email address to the script to EMAIL_ADDRESSES
– Enable ‘Youtube’ under the ‘Advanced APIs’ (at the upper-right in Google Ads Script interface)
– Authorize and Preview
– Schedule to run weekly (I prefer Mondays at 6AM)
Subscribe to my mailing list to receive updates on future versions of the script.
/**
*
* Google Ads Script - PMax Placement Exclusion Suggestions (FREE version)
*
* (C) Nils Rooijmans , https://nilsrooijmans.com
*
* This script suggests placement exclusions for bad placements where your ads show in PMax campaigns.
* If new placement exclusions are suggested the placement exclusions are reported via email.
* The email containts a link to Google Doc spreadsheet documenting all the placement exclusion suggestions.
*
* Current Version: 1.2.6
*
* Version 1.2.6 -> Added check for audience setting of videos (madeForKids)
* Version 1.2.5 -> added check for audio language of videos
* Version 1.2.4 -> added extra column to ouput sheet with placement type (for easy filtering)
* Version 1.2.3 -> added some more checks for Video titles (monetary amounts, overly capitalized titles, excessive punctuation)
* Version 1.2.2 -> added placement exclusions for videos with clickbait titles
* Version 1.2.1 -> added exlusions for Second Level Domains that are questionable
* Version 1.2 -> added placement exclusions for Mobile App placements.
* Version 1.1 -> added placement exclusions for videos with political content.
* Version: 1.0
* The 1.0 version only checks website placements to see if they are hosted on trusted Top Level Domains (TLDs).
*
* Future versions will include more intelligent checks to discriminate between good and bad placements.
*
* INSTRUCTIONS:
*
* If you are new to scripts -> follow these instructions on how to copy-paste and install Google Ads Scripts:
* https://nilsrooijmans.com/how-to-set-up-and-run-google-ads-scripts/
*
* Next:
* - Create a copy of this spreadheet -> https://docs.google.com/spreadsheets/d/1Ehsm9zOQ6ETrZgxZKDdflOUol2AVxE96BhOElncU7fw/copy
* - Add the complete URL of the copy of the spreadsheet to the SPREADSHEET_URL below
* - Decide on the date range / the LOOKBACK_WINDOW
* - Set thresholds for MIN_IMPRESSIONS
* - Add the name of your Google Ads account to ACCOUNT_NAME
* - Add your email address to the script to EMAIL_ADDRESSES
* - Decide on the TLDs that you consider safe and add them to SAFE_TLDS
* - Decide on the languages you allow for videos you want to show your ads. Add them to ALLOWED_LANGUAGES
* - Add the keywords that identify political content for you to POLITICAL_KEYWORDS
* - Enable 'Youtube' under the 'Advanced APIs' (at the upper-right in Google Ads Script interface)
* - Authorize and Preview
* - Schedule to run weekly (I prefer Mondays at 6AM)
*
* ---------------------------------------------------
**/
/// START OF SCRIPT CONFIGURATION ///
var SPREADSHEET_URL = ""; //insert url of YOUR COPY of https://docs.google.com/spreadsheets/d/1Ehsm9zOQ6ETrZgxZKDdflOUol2AVxE96BhOElncU7fw/copy
var LOOKBACK_WINDOW = 30; // the script will look at placement data for X days before today, set to any value you want. When you schedule the script to run weekly, I recommend you set it to 7.
var MIN_IMPRESSIONS = 1; // Only look at PMax placements that accrued over this amount of impressions during the LOOKBACK_WINDOW you defined above
/*** EMAIL CONFIGURATION SETTINGS ***/
var ACCOUNT_NAME = ""; // insert short name of your account to recoginze the account in the subject of your emails
var EMAIL_ADDRESSES = ""; // insert email addresses of people that want to get the placements exclusions. Insert email addresses between the quotes, seperate multipe email addresses by a comma. Ie: "jane@doe.com, john@doe.com"
var SEND_EMAIL = true; // set to false if you do not want to recieve any emails (not recommended)
/*** BAD PLACEMENT FILTER CONFIGURATIONS ***/
var SAFE_TLDS = [".com", ".edu", ".org", ".net", ".nl", ".be"]; // list of TLDs you consider to be safe. All placements of other TLDs will be added to the list of exclusion suggestions. Add TLDs like ".co.uk", ".de", ".nl" etc when you feel thay are safe.
var ALLOWED_LANGUAGES = ['EN', 'ES']; // list of languages for videos that you allow your ads to appear next to
var SPAMMY_KEYWORDS = [
'free', 'cheap', 'earn', 'win', 'get', 'hot', 'cash', 'offer', 'deal', 'bonus', 'winbig', 'prizes', 'deals', // general list of spammy keywords
'girls', 'xxx', 'naked', 'adult', 'sex', 'porn', 'cam', 'escort', 'chat', // list of adult keywords
'support', 'help', 'login', 'recover', 'fix', 'verify', 'account', 'reset', // list of potential phishing keywords
'now', 'today', 'fast', 'instant', 'top', 'amazing', 'exclusive', 'shocking', 'breaking', 'viral', 'secret', // clickbait
'bitcoin', 'crypto', 'forex', 'loan', 'payday', 'profit', 'money', 'invest', 'trading', 'wealth', 'gold' // potential scam
];
var POLITICAL_KEYWORDS = [
'Trump', 'Biden', 'Harris', 'Pence', 'Obama', 'Clinton', 'Sanders', 'Pelosi', 'Putin', 'Jinping', 'Vance',
'election', 'government', 'politics', 'president', 'congress',
'senate', 'democracy', 'republic', 'voting', 'votes',
'political', 'policy', 'referendum', 'protest', 'activism',
'lawmakers', 'liberal', 'conservative', 'reform',
'racism', 'racist'
];
var CLICKBAIT_PHRASES = [
'shocking', 'bombshell', 'unexpected', "you won't believe", 'must see',
'secret', 'revealed', 'exposed', 'caught on camera', 'nightmare', 'mystery', 'scandal',
'jaw-dropping', 'insane', 'epic', 'unbelievable', 'must-watch', 'massive', 'stunning', 'gone wrong', 'wtf', 'omg', "don't miss"
];
/// END OF SCRIPT CONFIGURATION ///
/*** DON'T CHANGE ANYTHING BELOW THIS LINE ***/
var DEBUG = false;
var VERSION = "1.2.6";
var VERSION_OUTDATED_MESSAGE = `WAKE UP CALL: I've got some good news -> there's a new and improved version of this script waiting for you!\nFind you way to https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ to get the latest version of the script.\n`;
var VERSION_UP2DATE_MESSAGE = `Congrats, you're all up-to-date with the lastest version of the script! I hope you enoy the suggestions :)\n`;
var EMAIL_SUBJECT = `${ACCOUNT_NAME} - New PMax Placement Exclusion Suggestions for bad placements`;
var EMAIL_BODY = `This script looks at all placements that had over ${MIN_IMPRESSIONS} impressions during last ${LOOKBACK_WINDOW} days for all PMax cmpaigns in your account, and suggests bad placement exclusions to add to your account.\n`;
function main() {
checkVersion();
var result = getResults();
reportResults(result);
}
function reportResults(result) {
var outputSheet = prepareOutputSheets();
var nrOfResultsReported = 0;
var output = result.output;
if (output.length > 0) {
addOutputToSheet(output, outputSheet);
nrOfResultsReported += output.length;
}
if(SEND_EMAIL && nrOfResultsReported > 0) {
var emailSubject = "[Gads Script] "+EMAIL_SUBJECT;
var emailHeader = `Hey PPC Friend, this email is generated by version ${VERSION} of the 'PMax Placement Exclusion Suggestions Script' (c) Nils Rooijmans.\n`
// check to see if the email needs a message that updates the user about the latest version of the script
var latestVersion = getLatestScriptVersion();
var versionMessage = "";
if (latestVersion != VERSION) {
versionMessage = VERSION_OUTDATED_MESSAGE+` --> Latest version: ${latestVersion}`;
} else {
versionMessage = VERSION_UP2DATE_MESSAGE;
}
emailHeader += `\n${versionMessage}\n\n`;
var emailFooter = getEmailFooter();
var emailBody = emailHeader+EMAIL_BODY+"\nNumber of PMax placement exclusion suggestions added in total: "+nrOfResultsReported+"\n\n"+
"You can find all PMax placement exclusion suggestions here: "+SPREADSHEET_URL+"\n\n"+
"---\n"+emailFooter;
var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
debug("Remaining email quota: " + emailQuotaRemaining);
MailApp.sendEmail(EMAIL_ADDRESSES, emailSubject, emailBody) ;
}
}
function getResults() {
var accountName = AdsApp.currentAccount().getName();
var results = [];
var dateRange = getDateRange(LOOKBACK_WINDOW);
var whereClause =
" WHERE metrics.impressions > " + MIN_IMPRESSIONS;
whereClause = whereClause +
" AND segments.date BETWEEN '" + dateRange.startDate + "' AND '" + dateRange.endDate +"'";
" ORDER BY metrics.impressions DESC" ;
// + " LIMIT 10";
var query =
"SELECT performance_max_placement_view.display_name, performance_max_placement_view.placement, performance_max_placement_view.placement_type, performance_max_placement_view.resource_name, performance_max_placement_view.target_url, metrics.impressions" +
" FROM performance_max_placement_view" + whereClause;
// Let's report all placements in a seperate sheet.
var allPlacementsSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName('all_placements');
gaqlToReportSheet(query, allPlacementsSheet);
//debug(query);
try {
var result = AdsApp.search(query);
while (result.hasNext()) {
var row = result.next();
//debug(row);
var performanceMaxPlacement = {
displayName: row.performanceMaxPlacementView.displayName,
placement: row.performanceMaxPlacementView.placement,
placementType: row.performanceMaxPlacementView.placementType,
resourceName: row.performanceMaxPlacementView.resourceName,
targetUrl: row.performanceMaxPlacementView.targetUrl,
impressions: row.metrics.impressions
};
var checkedPlacement = checkPlacement(performanceMaxPlacement);
if(checkedPlacement.exclude == true) {
//console.log(`PMax placement exclusion suggestion: ${performanceMaxPlacement.targetUrl} | impressions ${performanceMaxPlacement.impressions}`);
results.push([performanceMaxPlacement.targetUrl, performanceMaxPlacement.displayName, performanceMaxPlacement.placementType, performanceMaxPlacement.impressions, checkedPlacement.reason]);
}
}
} catch(e) {
alert("ERROR: "+e);
MailApp.sendEmail(EMAIL_ADDRESSES, "### ERROR: "+EMAIL_SUBJECT, "Could not suggest placement exclusions: "+e);
}
console.log(`\nFinished analyses\nNumber of new placement exclusion suggestions: ${results.length}\n--> Report available at: ${SPREADSHEET_URL}`);
return {
output: results
};
}
function checkPlacement(performanceMaxPlacement) {
// check website placement
if (performanceMaxPlacement.placementType == 'WEBSITE') {
if (questionableTLD(performanceMaxPlacement)) return {exclude:true, reason:`Questionable TLD`};
var checkedSLD = questionableSLD(performanceMaxPlacement);
if(checkedSLD.isQuestionable == true) return {exclude:true, reason:checkedSLD.reason};
}
// check YouTube placement
if (performanceMaxPlacement.placementType == 'YOUTUBE_VIDEO' && performanceMaxPlacement.placement) {
var checkedVideo = questionableVideo(performanceMaxPlacement);
if (checkedVideo.isQuestionable == true) return {exclude:true, reason:checkedVideo.reason};
}
// check Mobile App placement
if (performanceMaxPlacement.placementType == 'MOBILE_APPLICATION') {
return {exclude:true, reason:`Mobile App placement. Almost certainly low conversion value.`};
}
return {exclude:false};
}
function questionableVideo(performanceMaxPlacement) {
var videoId = performanceMaxPlacement.placement;
//var videoTitle = performanceMaxPlacement.displayName;
//console.log(`Analyzing video: ${videoTitle}`);
var videoInfo = {};
// Retrieve video information via YouTube API
try {
var results = YouTube.Videos.list('snippet, status, topicDetails', {id: videoId})
if (results.items && results.items.length > 0) {
// get title
if (results.items[0].snippet.title) {
videoInfo.title = results.items[0].snippet.title;
} else {
videoInfo.title = 'unknown';
}
// get channelId
if (results.items[0].snippet.channelId) {
videoInfo.channelId = results.items[0].snippet.channelId;
} else {
videoInfo.channelId = 'unknown';
}
// get channelTitle
if (results.items[0].snippet.channelTitle) {
videoInfo.channelTitle = results.items[0].snippet.channelTitle;
} else {
videoInfo.channelTitle = 'unknown';
}
// get defaultLanguage
if (results.items[0].snippet.defaultLanguage) {
videoInfo.defaultLanguage = results.items[0].snippet.defaultLanguage;
} else {
videoInfo.defaultLanguage = 'unknown';
}
// get defaultAudioLanguage
if (results.items[0].snippet.defaultAudioLanguage) {
videoInfo.defaultAudioLanguage = results.items[0].snippet.defaultAudioLanguage;
} else {
videoInfo.defaultAudioLanguage = 'unknown';
}
// get madeForKids
if (results.items[0].status.madeForKids) {
videoInfo.madeForKids = results.items[0].status.madeForKids;
} else {
videoInfo.madeForKids = 'unknown';
}
// get topicDetails
if (results.items[0].topicDetails.topicCategories) {
videoInfo.topicCategories = results.items[0].topicDetails.topicCategories;
} else {
videoInfo.topicCategories = 'unknown';
}
} else {
alert(`No video found with the ID ${videoId}. The video has probably been removed.`);
}
} catch (e) {
alert(`Issue retrieving video information from YouTube API: ${e}, video ID: ${videoId}`);
}
var exclude = false;
var reasons = ["Questionable video because of:"];
// First, let's check the video title
var checkedVideoTitle = questionableVideoTitle(videoInfo.title);
if (checkedVideoTitle.isQuestionable == true) {
exclude = true;
reasons.push(checkedVideoTitle.reason);
}
// Second, let's test the language
var checkedVideoLanguage = questionableVideoLanguage(videoInfo);
if (checkedVideoLanguage.isQuestionable == true) {
exclude = true;
reasons.push(checkedVideoLanguage.reason);
}
// Third, let's test the 'made for kids' setting
var checkedVideoAudience = questionableVideoAudience(videoInfo);
if (checkedVideoAudience.isQuestionable == true) {
exclude = true;
reasons.push(checkedVideoLanguage.reason);
}
// return result
if (exclude) {
var reason = reasons.join(`\n*`);
return {isQuestionable:true, reason:reason};
} else return false;
}
function questionableVideoAudience(videoInfo) {
var exclude = false;
var reasons = ["Questionable video audience:"];
if(videoInfo.madeForKids == true) {
exclude = true;
reasons.push(`-video has questionable audience setting: madeForKids`);
}
// return result
if (exclude) {
var reason = reasons.join(`\n-`);
return {isQuestionable:true, reason:reason};
} else return false;
}
function questionableVideoLanguage(videoInfo) {
var exclude = false;
var reasons = ["Questionable video language:"];
// check the audio language of the video
if (videoInfo.defaultLanguage != 'unknown') {
var languageAllowed = false;
for (var i = 0; i < ALLOWED_LANGUAGES.length; i++) {
if (videoInfo.defaultLanguage.toLowerCase().includes(ALLOWED_LANGUAGES[i].toLowerCase())) {
languageAllowed = true;
break;
}
}
if (!languageAllowed) {
exclude = true;
reasons.push(`-video has questionable language setting: ${videoInfo.defaultLanguage}`);
}
}
// check the audio language of the video
if (videoInfo.defaultAudioLanguage != 'unknown') {
var languageAllowed = false;
for (var i = 0; i < ALLOWED_LANGUAGES.length; i++) {
if (videoInfo.defaultAudioLanguage.toLowerCase().includes(ALLOWED_LANGUAGES[i].toLowerCase())) {
languageAllowed = true;
break;
}
}
if (!languageAllowed) {
exclude = true;
reasons.push(`-video has questionable audio language: ${videoInfo.defaultAudioLanguage}`);
}
}
// return result
if (exclude) {
var reason = reasons.join(`\n-`);
return {isQuestionable:true, reason:reason};
} else return false;
}
function questionableVideoTitle(videoTitle) {
var exclude = false;
var reasons = ["Questionable video title:"];
//var videoTitle = performanceMaxPlacement.displayName;
// check if title is political
for (var i = 0; i < POLITICAL_KEYWORDS.length; i++) {
if (videoTitle.toLowerCase().includes(POLITICAL_KEYWORDS[i].toLowerCase())) {
exclude = true;
reasons.push(`-video Title contains political keyword: ${POLITICAL_KEYWORDS[i]}`);
break;
}
}
// check if title is clickbait
for (var i = 0; i < CLICKBAIT_PHRASES.length; i++) {
if (videoTitle.toLowerCase().includes(CLICKBAIT_PHRASES[i].toLowerCase())) {
exclude = true;
reasons.push(`-video Title contains clickbait phrase: ${CLICKBAIT_PHRASES[i]}`);
break;
}
}
// check for excessive punctuation like "!!" or "??"
if (/[!?]{2,}/.test(videoTitle)) {
exclude = true;
reasons.push(`-video Title contains excessive punctuation like "!!" or "??"`)
}
// check for overly capitalized titles
if (/[A-Z\s]{10,}/.test(videoTitle)) {
exclude = true;
reasons.push(`-video Title is overly capitalized`)
}
// check for monetary amounts
if (/[\$\€]\d+/.test(videoTitle)) {
exclude = true;
reasons.push(`-video Title contains monetary amounts`)
}
// return result
if (exclude) {
var reason = reasons.join(`\n-`);
return {isQuestionable:true, reason:reason};
} else return false;
}
function questionableSLD(performanceMaxPlacement) {
var exclude = false;
var reasons = ["Questionable domain name:"];
var targetURL = performanceMaxPlacement.targetUrl;
var sld = getSLD(targetURL);
// check length
if(sld.length < 3 || sld.length >25) {
exclude = true;
reasons.push(`Second Level Domain length is suspicious: ${sld.length} chars`);
}
// check for spammy keywords
for (var i = 0; i < SPAMMY_KEYWORDS.length; i++) {
if(sld.toLowerCase().includes(SPAMMY_KEYWORDS[i].toLowerCase())) {
exclude = true;
reasons.push(`Domain name contains spammy keyword(s), including: ${SPAMMY_KEYWORDS[i]}`);
break;
}
}
// check for hyphens
if (sld.includes(`-`)) {
exclude = true;
reasons.push(`Domain name contains hyphen`);
}
// check for more than 3 numbers in a row
if (/[0-9]{3,}/.test(sld)) {
exclude = true;
reasons.push(`Domain name contains more than 3 numbers in a row`)
}
// check for repeated chars
if (/(.)\1{2,}/.test(sld)) { // same char repeated 3 times?
exclude = true;
reasons.push(`Domain name contains same char repeated more than 2 times in a row`)
}
// Check for presence of "xn--" (punycode for internationalized domain names)
if (sld.includes("xn--")) {
exclude = true;
reasons.push(`Domain name contains punycode, which can be abused`)
}
// return result
if (exclude) {
var reason = reasons.join(`\n`);
return {isQuestionable:true, reason:reason};
} else return false;
function getSLD(domain) {
// Remove any subdomains (if present)
const parts = domain.split(".");
if (parts.length > 2) {
parts.shift(); // Remove the first part (subdomain)
}
// Extract the SLD
return parts[0];
}
}
function questionableTLD(performanceMaxPlacement) {
var safeTLDs = SAFE_TLDS;
var targetURL = performanceMaxPlacement.targetUrl;
var tld = targetURL.substring(targetURL.lastIndexOf("."));
return !safeTLDs.includes(tld);
}
/*** REPORT FUNCTIONS ***/
function prepareOutputSheets() {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
if (!spreadsheet) {
Logger.log("Cannot open new reporting spreadsheet") ;
return ;
}
spreadsheet.rename(ACCOUNT_NAME+" - PMax Placement Exclusion Suggestions");
var sheet = spreadsheet.getSheetByName('suggested_exclusions');
if (!sheet) {
Logger.log("Cannot open new reporting sheet") ;
return ;
}
sheet.clear();
// set width of columns
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 400);
sheet.setColumnWidth(3, 150);
sheet.setColumnWidth(4, 100);
sheet.setColumnWidth(5, 500);
addHeaderToOutputSheet(sheet);
return sheet;
}
function addHeaderToOutputSheet(sheet) {
try {
var headerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zWtNFCXbq4yPSz3G0am0HxW6NHZO3VqJzH-K12jAVZw/").getSheetByName('header_sheet');
} catch(e) {
console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`);
throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`;
}
var headerRange = headerSheet.getRange(1, 1, 2, headerSheet.getLastColumn());
var headerData = headerRange.getValues();
var range=sheet.getRange(1,1,2,headerData[1].length);
range.clear();
range.clearFormat();
range.setValues(headerData)
range.setFontWeight("bold");
range = sheet.getRange(1,1,1,headerData[1].length);
range.setFontColor('#007BFF')
sheet.setFrozenRows(2);
var latestVersion = getLatestScriptVersion();
var versionMessage = "";
if (latestVersion != VERSION) {
versionMessage = VERSION_OUTDATED_MESSAGE+` --> Latest version: ${latestVersion}`;
} else {
versionMessage = VERSION_UP2DATE_MESSAGE;
}
range = sheet.getRange("A1");
var titleCell = range.getValue();
titleCell = titleCell+`\nYou are running version ${VERSION}`;
range.setValue(titleCell);
range = sheet.getRange("B1");
if (latestVersion != VERSION) {
range.setValue(versionMessage);
range.setFontColor('orange');
}
}
function addOutputToSheet(output, sheet) {
var numberOfRows=sheet.getLastRow() ;
sheet.insertRowsBefore(3, output.length);
var startRow = 3;
var range=sheet.getRange(startRow, 1, output.length, output[0].length) ;
range.setValues(output) ;
range.sort([{column: 4, ascending: false}]);
}
function getEmailFooter() {
try {
var footerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zWtNFCXbq4yPSz3G0am0HxW6NHZO3VqJzH-K12jAVZw/").getSheetByName('email_footer');
} catch(e) {
console.log(`### There was an issue opening the email_footer sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`);
throw `### There was an issue opening the email_footer sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`;
}
var footerRange = footerSheet.getRange(1, 1, footerSheet.getLastRow(), 1);
var footerData = footerRange.getValues();
var footer = footerData.join('\n');
return footer;
}
/*** HELPER FUNCTIONS ***/
function checkVersion() {
console.log(`Hey PPC Friend, you're running verion ${VERSION} of the 'PMax Placement Exclusion Suggestions Script' (c) Nils Rooijmans.`);
var latestVersion = getLatestScriptVersion();
if (latestVersion != VERSION) {
console.log(VERSION_OUTDATED_MESSAGE+` --> Latest version: ${latestVersion}`);
} else {
console.log(VERSION_UP2DATE_MESSAGE);
}
}
function getLatestScriptVersion() {
try {
var latestVersionSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zWtNFCXbq4yPSz3G0am0HxW6NHZO3VqJzH-K12jAVZw/").getSheetByName('latest_version');
} catch(e) {
console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`);
throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`;
}
var latestVersionRange = latestVersionSheet.getRange(1, 2, 1, 1);
var latestVersion = latestVersionRange.getValue();
return latestVersion;
}
function debug(string) {
if (DEBUG == true) {
Logger.log("** "+string);
}
}
function alert(string) {
Logger.log("### "+string);
}
function getDateRange(lookbackWindow) {
var account = AdsApp.currentAccount();
var timezone = account.getTimeZone();
var days_before = new Date();
days_before.setDate(days_before.getDate() - lookbackWindow)
var startDate = Utilities.formatDate(days_before, timezone, "yyyy-MM-dd");
var yesterday = new Date();
yesterday.setDate(yesterday.getDate() - 1)
var endDate = Utilities.formatDate(yesterday, timezone, "yyyy-MM-dd");
return {startDate:startDate, endDate:endDate};
}
// this function sends the output of the GAQL query to a spreadsheet
function gaqlToReportSheet(gaqlQuery, sheet) {
var report = AdsApp.report(gaqlQuery);
report.exportToSheet(sheet);
}
Join thousands of PPC geeks who already have access: