mcc_code.js

//            _ _              __  __ _      
//   __ _  __| | |_ _ __ __ _ / _|/ _(_) ___ 
//  / _` |/ _` | __| '__/ _` | |_| |_| |/ __|
// | (_| | (_| | |_| | | (_| |  _|  _| | (__ 
//  \__,_|\__,_|\__|_|  \__,_|_| |_| |_|\___|
//                                           
// YouTube Channel Excluder 23.1 (MCC Version)
//
// (c) 2023 by R. Stinauer
//  
// E: info@adtraffic.de | 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.");
}