/*
License Note for PMax Placement Cleaner Script
Author: Dmytro Tonkikh, TrueClicks & chiliad
Version: 2.0
Date: 20.01.2024
License:
This PMax Placement Cleaner script is made available under the GPL licensing. By using this script, you agree to the terms and conditions of this license.
Description:
The PMax Placement Cleaner is designed for Google Ads users to manage and clean up their placement lists efficiently. It automates the process of identifying and excluding undesirable placements, enhancing ad performance.
Disclaimer:
This script is provided 'as is' and without any warranty. The author or company will not be liable for any damages or losses arising from its use. Users are responsible for adhering to Google Ads policies and applicable laws.
Contact Information:
dmytro@trueclicks.com
dmytro@chiliad.agency
*/
var REPORT_URL = "";
var placementListName = "TrueClicks | common negative";
function main() {
if (typeof AdsManagerApp !== "undefined") {
pMaxPlacementlCleaner();
} else {
throw "This is a MCC-level script.";
}
}
function pMaxPlacementlCleaner() {
var customerId = AdsApp.currentAccount().getCustomerId();
var scriptName = "Placement Cleaner";
var summaryFileName = "TrueClicks | Placement Cleaner";
var mainScriptFolder = getOrCreateFolder("🤖 TrueClicks scripts");
var accountFolder = getOrCreateClientFolder(mainScriptFolder);
var folder = getOrCreateScriptFolder(accountFolder, scriptName);
var mainFile;
var fileIterator = folder.getFiles();
while (fileIterator.hasNext()) {
var spreadsheet = fileIterator.next();
if (spreadsheet.getName() == summaryFileName) {
mainFile = spreadsheet;
}
}
try {
if (!mainFile) mainFile = addSummaryFile(folder, summaryFileName);
} catch (e) {
Logger.log(e);
}
var ss = SpreadsheetApp.open(mainFile);
Logger.log("Spreadsheet url: " + ss.getUrl());
checkPlacementList(placementListName);
var bulkUpload = AdsApp.bulkUploads().newCsvUpload([
"Action",
"Customer ID",
"Placement Exclusion List Name",
"Placement url",
]);
var placementData = [];
var checked = [];
var exclusion = [];
var sheet = ss.getSheetByName("results");
var regExp = createRegExp(ss, "keywords");
var domain_regExp = createRegExp(ss, "domains");
var union = unionArrays(
getExludedPlacements(placementListName),
columnData(sheet)
);
var placements = Object.keys(urlReport(union)).reduce(function (s, c) {
var match = c.match(domain_regExp || []);
if (match) {
exclusion.push(c);
checked.push([c, "domain"]);
} else {
s.push(c);
}
return s;
}, []);
if (placements.length > 700) {
placements = placements.slice(0, 700);
}
var requests = chunkArray(placements, 10);
for (var i in requests) {
try {
var reqs = UrlFetchApp.fetchAll(requests[i]);
reqs.forEach(function (x, n) {
placementData.push([requests[i][n], request(requests[i][n], x)]);
});
} catch (e) {
requests[i].forEach(function (x, n) {
placementData.push([requests[i][n], getTitle(requests[i][n])]);
});
}
}
for (var y in placementData) {
try {
var p = placementData[y];
var match = p[1].match(regExp || []);
var match_domain = p[0].match(domain_regExp || []);
if (match || match_domain) {
exclusion.push(p[0]);
checked.push(p);
bulkUpload.append({
Action: "Add",
"Customer ID": customerId,
"Placement Exclusion List Name": placementListName,
"Placement url": pl,
});
} else {
checked.push(p);
}
} catch (e) {
checked.push(p);
Logger.log(p);
}
}
if (exclusion[0]) {
bulkUpload.setFileName("TrueClicks - Placement Cleaner").apply();
}
if (checked[0]) {
sheet
.getRange(sheet.getLastRow() + 1, 1, checked.length, checked[0].length)
.setValues(checked);
}
Logger.log("Excluded placements: " + exclusion.length);
Logger.log("Checked placements: " + checked.length);
}
function checkPlacementList(name) {
var epl = AdsApp.excludedPlacementLists()
.withCondition("shared_set.name = '" + name + "'")
.get();
if (!epl.hasNext()) {
epl = AdsApp.newExcludedPlacementListBuilder()
.withName(name)
.build()
.getResult();
}
}
function urlReport(union) {
var reportSS = SpreadsheetApp.openByUrl(REPORT_URL);
var values = reportSS.getActiveSheet().getDataRange().getValues();
var headers = values.filter(function (p) {
if (p.indexOf("Performance Max placement URL") !== -1) {
return p;
}
});
var index = headers[0].indexOf("Performance Max placement URL");
var regex = new RegExp(
"^(?:https?://)?(?:[^@/\n]+@)?(?:www.)?([^:/?\n]+)",
"igm"
);
var domains = {};
var urls = columnData(reportSS.getActiveSheet(), index).forEach(function (x) {
if (
!domains[x] &&
union.indexOf(x) == -1 &&
x.indexOf("mobileapp::") == -1 &&
x.indexOf("youtube") == -1
) {
domains[x] = true;
}
});
return domains;
}
// Get placement title
function getTitle(url) {
try {
var title = request(url);
} catch (e) {
Logger.log(e.message + " " + url);
}
return title;
}
function request(url, resp) {
try {
if (!resp) {
var resp = UrlFetchApp.fetch(url, {
muteHttpExceptions: true,
followRedirects: true,
});
}
var headers = resp.getAllHeaders();
if (headers["Content-Type"]) {
try {
var charset = headers["Content-Type"]
.match(/charset=.*/g || [])[0]
.replace("charset=", "")
.toUpperCase();
} catch (e) {
var charset = "";
}
} else {
var charset = "";
}
var content = resp.getContentText(charset).toLowerCase();
var start = content.indexOf("<title");
var end = content.indexOf("</title>");
var title = content.substring(start + 7, end);
return title;
} catch (e) {
Logger.log(e + " " + url);
}
}
//Creating new RegExp
function createRegExp(ss, name) {
var string = "(" + columnData(ss.getSheetByName(name)).join("|") + ")";
if (string.indexOf(".") !== -1) {
string = string.replace(/\./g, "\\.");
}
var regexp = new RegExp(string, "g");
return regexp;
}
//Get checked placement
function columnData(sheet, index) {
if (index == undefined) {
index = 0;
}
return sheet
.getDataRange()
.getValues()
.filter(function (i) {
return i[index] !== "";
})
.map(function (x) {
return x[index];
});
}
function unionArrays(x, y) {
var obj = {};
for (var i = x.length - 1; i >= 0; --i) obj[x[i]] = x[i];
for (var i = y.length - 1; i >= 0; --i) obj[y[i]] = y[i];
var res = [];
for (var k in obj) {
if (obj.hasOwnProperty(k))
// <-- optional
res.push(obj[k]);
}
return res;
}
/*
* Creates a spreadsheet for the summary and stores it in the folder.
* Populates header rows and columns.
*/
function addSummaryFile(folder, name, currentSS) {
if (currentSS) {
DriveApp.getFileById(currentSS.getId()).makeCopy(name, folder);
} else {
DriveApp.getFileById(
"1iVdtOQt0WsOyA5rtdSkZHhaiel9Pdqd2K2vIujLsrrM"
).makeCopy(name, folder);
}
return folder.getFilesByName(name).next();
}
/*
* Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created.
* The folderName can be in the form of a complete path with subfolders, like "QS Reports/123/whatever".
* Returns the folder.
*/
function getOrCreateFolder(folderName) {
if (!DriveApp.getFoldersByName(folderName).hasNext()) {
var currentFolder = DriveApp.createFolder(folderName);
} else {
var currentFolder = DriveApp.getFoldersByName(folderName).next();
}
return currentFolder;
}
/*
* Checks if there is a folder for the current client account in the base folder. If not, the folder is created.
* Existing client folders are recognized by the client id in parentheses. This way, folders can be found again, even if an account has been renamed.
*/
function getOrCreateClientFolder(baseFolder) {
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(AdsApp.currentAccount().getCustomerId());
while (folderIterator.hasNext()) {
var folder = folderIterator.next();
if (folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName =
AdsApp.currentAccount().getName() +
" (" +
AdsApp.currentAccount().getCustomerId() +
")";
Logger.log("Creating folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}
function getOrCreateScriptFolder(baseFolder, scriptName) {
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(scriptName);
while (folderIterator.hasNext()) {
var folder = folderIterator.next();
if (folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName = scriptName;
Logger.log("Creating script folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}
function getExludedPlacements(name) {
var result = [];
let query =
"SELECT shared_criterion.placement.url FROM shared_criterion WHERE shared_set.name = '" +
name +
"'";
let report = AdsApp.search(query);
while (report.hasNext()) {
let r = report.next();
if (r.sharedCriterion.placement) {
let url = r.sharedCriterion.placement.url;
result.push(url);
}
}
return result;
}
function chunkArray(myArray, chunk_size) {
var index = 0;
var arrayLength = myArray.length;
var tempArray = [];
for (index = 0; index < arrayLength; index += chunk_size) {
myChunk = myArray.slice(index, index + chunk_size);
// Do something if you want with the group
tempArray.push(myChunk);
}
return tempArray;
}
