-
Notifications
You must be signed in to change notification settings - Fork 0
/
mcc_code.js
309 lines (273 loc) · 12 KB
/
mcc_code.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
// _ _ __ __ _
// __ _ __| | |_ _ __ __ _ / _|/ _(_) ___
// / _` |/ _` | __| '__/ _` | |_| |_| |/ __|
// | (_| | (_| | |_| | | (_| | _| _| | (__
// \__,_|\__,_|\__|_| \__,_|_| |_| |_|\___|
//
// YouTube Channel Excluder 23.1 (MCC Version)
//
// (c) 2023 by R. Stinauer
//
// E: [email protected] | W: www.adtraffic.de
//
// Prepare the MCC version of the YouTube Channel Excluder 23.1
// Get a copy of the following Google Spreadsheet and replace the SPREADSHEET_URL with your own URL:
// https://docs.google.com/spreadsheets/d/1Pgy_AwPdqVFfwZSn-rYFr5dQ8wnpRan01LcZX_PcAk0/copy
// Add one line for each account to be processed beginning with the account ID in the format "123-456-7890"
// Make sure you set values for "allowedCountries", "impressionTimeRange" and "impressionThreshold". You'll find example values in the second line of the sheet.
// "campaignSearchString" and "skipCampaignIds" are optional, "mode" is ignored in the actual version.
const SPREADSHEET_URL = 'INSERT_YOUR_SPREADSHEET_URL';
const settings = loadValues(SPREADSHEET_URL,"settings");
const accountIds = settings.filter(f => f[0].match(/\d{3}-\d{3}-\d{4}/)).map(e => e[0]);
console.log(accountIds);
function main() {
let accountSelector
if ( accountIds.length > 0 ) {
accountSelector = AdsManagerApp.accounts().withIds(accountIds);
} else {
console.log("No account IDs found. Please add the ID(s) of the account(s) to be processed in your spreadsheet.");
}
accountSelector.executeInParallel('processAccount');
}
function processAccount(){
let customerLog = "";
try {
// Start processing
const accountId = AdsApp.currentAccount().getCustomerId();
const accountName = AdsApp.currentAccount().getName();
console.log(`Processing account ${accountName} ( ${accountId} ) ...`);
// Load settings
const accountSettings = settings.map(m => m).filter(f => f[0] == accountId)[0];
const campaignSearchString = accountSettings[1];
const skipCampaignIds = accountSettings[2].toString();
const allowedCountries = new RegExp(accountSettings[3].split(',').join('|'));
const impressionsTimeRange = accountSettings[4];
const impressionsThreshold = accountSettings[5];
const eMailAddresses = accountSettings[7];
if ( !accountSettings[8] ) {
writeAccountName(accountId,accountName);
}
const whitelist = loadSheet(accountId,"Whitelist");
const blacklist = loadSheet(accountId,"Blacklist");
let toBeChecked = new Array;
let toBeWhitelisted = new Array();
let toBeBlacklisted = new Array();
let processedCampaignIds = new Array();
const videoCampaignIterator = AdsApp.videoCampaigns().withCondition("Name CONTAINS_IGNORE_CASE '"+ campaignSearchString +"'").withCondition("Status = 'ENABLED'").get();
console.log(`${videoCampaignIterator.totalNumEntities()} enabled video campaigns found.`);
while (videoCampaignIterator.hasNext()) {
const videoCampaign = videoCampaignIterator.next();
const videoCampaignId = videoCampaign.getId();
if (skipCampaignIds && skipCampaignIds.indexOf(videoCampaignId) !== -1) {
console.log(`Skipping campaign ${videoCampaign.getName()} ...`);
continue;
} else {
console.log(`Processing campaign ${videoCampaign.getName()} ...`);
}
processedCampaignIds.push(videoCampaign.getId());
let query =
"SELECT detail_placement_view.display_name, detail_placement_view.group_placement_target_url FROM detail_placement_view " +
"WHERE " +
" campaign.id = " + videoCampaign.getId() +
" AND segments.date DURING " + impressionsTimeRange +
" AND metrics.impressions > " + impressionsThreshold +
" AND detail_placement_view.placement_type IN ('YOUTUBE_VIDEO','YOUTUBE_CHANNEL') " +
" AND detail_placement_view.group_placement_target_url IS NOT NULL ";
if ( whitelist.length > 0 ) {
query += " AND detail_placement_view.group_placement_target_url NOT REGEXP_MATCH '" + whitelist.join("|") + "'";
}
if ( blacklist.length > 0 ) {
query += " AND detail_placement_view.group_placement_target_url NOT REGEXP_MATCH '" + blacklist.join("|") + "'";
}
let result = AdsApp.search(query);
console.log(result.totalNumEntities() + " YouTube Placements found.");
while (result.hasNext()) {
let row = result.next();
const channelId = row['detailPlacementView']['groupPlacementTargetUrl'].match(/^.*\/([^\/]*)$/)[1];
const channelName = row['detailPlacementView']['displayName'];
if ( toBeChecked.filter(f => f["id"] == channelId).length === 0 ) {
toBeChecked.push(
{
id: channelId,
name: channelName
}
);
}
}
}
if ( toBeChecked.length > 0 ){
console.log(`${toBeChecked.length} channels to be queried ...`);
for ( i=0; i<toBeChecked.length; i++ ){
let channelId = toBeChecked[i]["id"];
let channelName = toBeChecked[i]["name"];
const channelInfo = getChannelInfo(channelId); // [ Country, Default Language ]
if ( channelInfo && channelInfo[0]){
if (channelInfo[0].match(allowedCountries)){
if ( whitelist.indexOf(channelId) == -1 && toBeWhitelisted.filter(f => f["id"] == channelId).length === 0){
toBeWhitelisted.push(
{
id: channelId,
name: channelName,
country: channelInfo[0],
language: channelInfo[1]
}
);
}
}
else if (!channelInfo[0].match(allowedCountries)){
if ( blacklist.indexOf(channelId) == -1 && toBeBlacklisted.filter(f => f["id"] == channelId).length === 0){
toBeBlacklisted.push(
{
id: channelId,
name: channelName,
country: channelInfo[0],
language: channelInfo[1]
}
);
}
}
}
}
}
if (toBeWhitelisted.length > 0){
// write channel IDs to whitelist
customerLog += `${toBeWhitelisted.length} CHANNEL(S) WHITELISTED\n=========================\n\n`;
let collectedIds = new Array();
for (i=0;i<toBeWhitelisted.length;i++){
collectedIds.push(toBeWhitelisted[i]["id"]);
customerLog += `${toBeWhitelisted[i]["name"]} ( Country: ${toBeWhitelisted[i]["country"]} / Language: ${toBeWhitelisted[i]["language"]} )\n`;
customerLog += `"https://youtube.com/channel/${toBeWhitelisted[i]["id"]}\n\n`;
}
writeValuesToSheet(accountId,"Whitelist",collectedIds);
}
if (toBeBlacklisted.length > 0){
customerLog += `\n\n${toBeBlacklisted.length} CHANNEL(S) BLACKLISTED\n=========================\n\n`;
// write channel IDs to blacklist
let collectedIds = new Array();
for (i=0;i<toBeBlacklisted.length;i++){
collectedIds.push(toBeBlacklisted[i]["id"]);
customerLog += `${toBeBlacklisted[i]["name"]} ( Country: ${toBeBlacklisted[i]["country"]} / Language: ${toBeBlacklisted[i]["language"]} )\n`;
customerLog += `"https://youtube.com/channel/${toBeBlacklisted[i]["id"]}\n\n`;
}
writeValuesToSheet(accountId,"Blacklist",collectedIds);
}
// update excluded channels in all campaigns
const videoCampaignIteratorSecondRun = AdsApp.videoCampaigns().withIds(processedCampaignIds).get();
let campaignLog = "";
while (videoCampaignIteratorSecondRun.hasNext()) {
const videoCampaign = videoCampaignIteratorSecondRun.next();
let excludedYouTubeChannels = new Array();
let addedExludedYouTubeChannelsCount = 0;
const exludedYouTubeChannelIterator = videoCampaign.videoTargeting().excludedYouTubeChannels().get();
while (exludedYouTubeChannelIterator.hasNext()) {
const excludedYouTubeChannel = exludedYouTubeChannelIterator.next();
excludedYouTubeChannels.push(excludedYouTubeChannel.getChannelId());
}
for (i=0;i<toBeBlacklisted.length;i++){
if (excludedYouTubeChannels.indexOf(toBeBlacklisted[i]["id"] == -1)){
videoCampaign.videoTargeting().newYouTubeChannelBuilder().withChannelId(toBeBlacklisted[i]["id"]).exclude();
addedExludedYouTubeChannelsCount++;
}
}
if (addedExludedYouTubeChannelsCount > 0){
campaignLog += `Campaign "${videoCampaign.getName()}":\n${addedExludedYouTubeChannelsCount} excluded YouTube channel(s) added.\n\n`;
}
}
if (campaignLog.length > 0) {
customerLog += `\n\nPROCESSED CAMPAIGN(S)\n====================\n\n${campaignLog}`;
}
if ( customerLog.length === 0 ){
customerLog = `Nothing to do today. :)`;
}
sendConfirmationEmail(customerLog,accountName,eMailAddresses);
console.log(customerLog);
return(JSON.stringify(`Account ${accountName} complete.`));
} catch (err) {
// TODO (developer) - Handle exceptions from Youtube API
console.log(`Failed with an error: %s`, err.message);
}
}
function sendConfirmationEmail(customerLog,accountName,eMailAddresses){
var eMailSubject = "adtraffic YouTube Channel Excluder 23.1 // " + accountName;
var eMailContent = customerLog;
sendSimpleTextEmail(eMailAddresses,eMailSubject,eMailContent)
}
// HELPERS
function loadValues(spreadsheetUrl,sheetName){
const ss = SpreadsheetApp.openByUrl(spreadsheetUrl);
const sheet = ss.getSheetByName(sheetName);
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();
if (lastRow === 1) {
return [];
} else {
var values = sheet.getRange(2,1,lastRow-1,lastColumn).getValues();
return values;
}
}
function loadSheet(accountId,sheetName){
const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
let sheet = ss.getSheetByName(`${accountId} ${sheetName}`);
if (!sheet){
let templateSheet = ss.getSheetByName(`templateSheet${sheetName}`);
ss.insertSheet(`${accountId} ${sheetName}`, 4, {template: templateSheet});
sheet = ss.getSheetByName(`${accountId} ${sheetName}`);
sheet.showSheet();
}
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();
if (lastRow === 1) {
return [];
} else {
var returnValues = [];
var values = sheet.getRange(2,1,lastRow-1,lastColumn).getValues();
for (var row in values) {
for (var col in values[row]) {
returnValues.push(values[row][col]);
}
}
return returnValues;
}
}
function getIndex(arr,val){
for (var i = 0; i < arr.length; i++) {
var index = arr[i].indexOf(val);
if (index > -1) {
return i;
}
}
}
function getChannelInfo(channelId){
try {
const results = YouTube.Channels.list('snippet,localizations', {
id: channelId,
maxResults: 1
});
if (results === null) {
console.log(`ID ${channelId}: Unable to search videos`);
return;
}
return [ results.items[0].snippet.country, results.items[0].snippet.defaultLanguage ];
} catch (err) {
// TODO (developer) - Handle exceptions from Youtube API
console.log(`ID ${channelId}: Failed with an error: %s`, err.message);
}
}
function writeValuesToSheet(accountId,sheetName,values){
const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
let sheet = ss.getSheetByName(`${accountId} ${sheetName}`);
const lastRow = sheet.getLastRow();
var range = sheet.getRange(lastRow+1,1,values.length);
var formattedValues = new Array();
while(values.length) formattedValues.push(values.splice(0,1));
range.setValues(formattedValues);
}
function writeAccountName(accountId,accountName){
const sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName('settings');
const lineNumber = getIndex(settings,accountId);
sheet.getRange(lineNumber+2, 9, 1, 1).setValues([[accountName]]);
}
function sendSimpleTextEmail(eMailAddress,eMailSubject,eMailContent) {
MailApp.sendEmail(eMailAddress,eMailSubject,eMailContent);
Logger.log("Mail sent.");
}