/*
*
* Advanced ad scheduling
*
* This script will apply ad schedules to campaigns or shopping campaigns and set
* the ad schedule bid modifier and mobile bid modifier at each hour according to
* multiplier timetables in a Google sheet.
*
* This version creates schedules with modifiers for 4 hours, then fills the rest
* of the day and the other days of the week with schedules with no modifier as a
* fail safe.
*
* Version: 3.0
* Updated to allow -100% bids, change mobile adjustments and create fail safes.
* brainlabsdigital.com
*
*/

function main() {
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //Options

  //The Google sheet to use
  //The default value is the example sheet
  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/13AV3Tbb19wYMC6Qz_IAWij2X-r1BqdVb7rX2kit-o30/edit#gid=0";

  //Shopping or regular campaigns
  //Use true if you want to run script on shopping campaigns (not regular campaigns).
  //Use false for regular campaigns.
  var shoppingCampaigns = true;

  //Use true if you want to set mobile bid adjustments as well as ad schedules.
  //Use false to just set ad schedules.
  var runMobileBids = false;

  //Optional parameters for filtering campaign names. The matching is case insensitive.
  //Select which campaigns to exclude e.g ["foo", "bar"] will ignore all campaigns
  //whose name contains 'foo' or 'bar'. Leave blank [] to not exclude any campaigns.
  var excludeCampaignNameContains = [];

  //Select which campaigns to include e.g ["foo", "bar"] will include only campaigns
  //whose name contains 'foo' or 'bar'. Leave blank [] to include all campaigns.
  var includeCampaignNameContains = [];

  //When you want to stop running the ad scheduling for good, set the lastRun
  //variable to true to remove all ad schedules.
  var lastRun = false;

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Initialise for use later.
  var weekDays = ["MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY"];
  var adScheduleCodes = [];
  var campaignIds = [];

  //Retrieving up hourly data
  var scheduleRange = "B2:H25";
  var accountName = AdWordsApp.currentAccount().getName();
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheets = spreadsheet.getSheets();

  var timeZone = AdWordsApp.currentAccount().getTimeZone();
  var date = new Date();
  var dayOfWeek = parseInt(Utilities.formatDate(date, timeZone, "uu"), 10) - 1;
  var hour = parseInt(Utilities.formatDate(date, timeZone, "HH"), 10);

  var sheet = sheets[0];
  var data = sheet.getRange(scheduleRange).getValues();

  //This hour's bid multiplier.
  var thisHourMultiplier = data[hour][dayOfWeek];
  var lastHourCell = "I2";
  sheet.getRange(lastHourCell).setValue(thisHourMultiplier);

  //The next few hours' multipliers
  var timesAndModifiers = [];
  var otherDays = weekDays.slice(0);
  for (var h=0; h<5; h++) {
    var newHour = (hour + h)%24;
    if (hour + h > 23) {
      var newDay = (dayOfWeek + 1)%7;
    } else {
      var newDay = dayOfWeek;
    }
    otherDays[newDay] = "-";

    if (h<4) {
      // Use the specified bids for the next 4 hours
      var bidModifier = data[newHour][newDay];
      if (isNaN(bidModifier) || (bidModifier < -0.9 && bidModifier > -1) || bidModifier > 9) {
        Logger.log("Bid modifier '" + bidModifier + "' for " + weekDays[newDay] + " " + newHour + " is not valid.");
        timesAndModifiers.push([newHour, newHour+1, weekDays[newDay], 0]);
      } else if (bidModifier != -1 && bidModifier.length != 0) {
        timesAndModifiers.push([newHour, newHour+1, weekDays[newDay], bidModifier]);
      }
    } else {
      // Fill in the rest of the day with no adjustment (as a back-up incase the script breaks)
      timesAndModifiers.push([newHour, 24, weekDays[newDay], 0]);
    }
  }

  if (hour>0) {
    timesAndModifiers.push([0, hour, weekDays[dayOfWeek], 0]);
  }

  for (var d=0; d<otherDays.length; d++) {
    if (otherDays[d] != "-") {
      timesAndModifiers.push([0, 24, otherDays[d], 0]);
    }
  }

  //Pull a list of all relevant campaign IDs in the account.
  var campaignSelector = ConstructIterator(shoppingCampaigns);
  for(var i = 0; i < excludeCampaignNameContains.length; i++){
    campaignSelector = campaignSelector.withCondition('Name DOES_NOT_CONTAIN_IGNORE_CASE "' + excludeCampaignNameContains[i] + '"');
  }
  campaignSelector = campaignSelector.withCondition("Status IN [ENABLED,PAUSED]");
  var campaignIterator = campaignSelector.get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    var campaignName = campaign.getName();
    var includeCampaign = false;
    if(includeCampaignNameContains.length === 0){
      includeCampaign = true;
    }
    for(var i = 0; i < includeCampaignNameContains.length; i++){
      var index = campaignName.toLowerCase().indexOf(includeCampaignNameContains[i].toLowerCase());
      if(index !== -1){
        includeCampaign = true;
        break;
      }
    }
    if(includeCampaign){
      var campaignId = campaign.getId();
      campaignIds.push(campaignId);
    }
  }

  //Return if there are no campaigns.
  if(campaignIds.length === 0){
    Logger.log("There are no campaigns matching your criteria.");
    return;
  }

  //Remove all ad scheduling for the last run.
  if(lastRun){
    checkAndRemoveAdSchedules(campaignIds, []);
    return;
  }

  // Change the mobile bid adjustment
  if(runMobileBids){
    if (sheets.length < 2) {
      Logger.log("Mobile ad schedule sheet was not found in the Google spreadsheet.");
    } else {
      var sheet = sheets[1];
      var data = sheet.getRange(scheduleRange).getValues();
      var thisHourMultiplier_Mobile = data[hour][dayOfWeek];

      if (thisHourMultiplier_Mobile.length === 0) {
        thisHourMultiplier_Mobile = -1;
      }

      if (isNaN(thisHourMultiplier_Mobile) || (thisHourMultiplier_Mobile < -0.9 && thisHourMultiplier_Mobile > -1) || thisHourMultiplier_Mobile > 3) {
        Logger.log("Mobile bid modifier '" + thisHourMultiplier_Mobile + "' for " + weekDays[dayOfWeek] + " " + hour + " is not valid.");
        thisHourMultiplier_Mobile = 0;
      }

      var totalMultiplier = ((1+thisHourMultiplier_Mobile)*(1+thisHourMultiplier))-1;
      sheet.getRange("I2").setValue(thisHourMultiplier_Mobile);
      sheet.getRange("T2").setValue(totalMultiplier);
      ModifyMobileBidAdjustment(campaignIds, thisHourMultiplier_Mobile);
    }
  }

  // Check the existing ad schedules, removing those no longer necessary
  var existingSchedules = checkAndRemoveAdSchedules(campaignIds, timesAndModifiers);

  // Add in the new ad schedules
  AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns);

}

/**
* Function to add ad schedules for the campaigns with the given IDs, unless the schedules are
* referenced in the existingSchedules array. The scheduling will be added as a hour long periods
* as specified in the passed parameter array and will be given the specified bid modifier.
*
* @param array campaignIds array of campaign IDs to add ad schedules to
* @param array timesAndModifiers the array of [hour, day, bid modifier] for which to add ad scheduling
* @param array existingSchedules array of strings identifying already existing schedules.
* @param bool shoppingCampaigns using shopping campaigns?
* @return void
*/
function AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns){
  // times = [[hour,day],[hour,day]]
  var campaignIterator = ConstructIterator(shoppingCampaigns)
  .withIds(campaignIds)
  .get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    for(var i = 0; i < timesAndModifiers.length; i++){
      if (existingSchedules.indexOf(
        timesAndModifiers[i][0] + "|" + (timesAndModifiers[i][1]) + "|" + timesAndModifiers[i][2]
          + "|" + Utilities.formatString("%.2f",(timesAndModifiers[i][3]+1)) + "|" + campaign.getId())
      > -1) {

        continue;
      }

      campaign.addAdSchedule({
        dayOfWeek: timesAndModifiers[i][2],
        startHour: timesAndModifiers[i][0],
        startMinute: 0,
        endHour: timesAndModifiers[i][1],
        endMinute: 0,
        bidModifier: Math.round(100*(1+timesAndModifiers[i][3]))/100
      });
    }
  }
}

/**
* Function to remove ad schedules from all campaigns referenced in the passed array
* which do not correspond to schedules specified in the passed timesAndModifiers array.
*
* @param array campaignIds array of campaign IDs to remove ad scheduling from
* @param array timesAndModifiers array of [hour, day, bid modifier] of the wanted schedules
* @return array existingWantedSchedules array of strings identifying the existing undeleted schedules
*/
function checkAndRemoveAdSchedules(campaignIds, timesAndModifiers) {

  var adScheduleIds = [];

  var report = AdWordsApp.report(
    'SELECT CampaignId, Id ' +
    'FROM CAMPAIGN_AD_SCHEDULE_TARGET_REPORT ' +
    'WHERE CampaignId IN ["' + campaignIds.join('","')  + '"]'
  );

  var rows = report.rows();
  while(rows.hasNext()){
    var row = rows.next();
    var adScheduleId = row['Id'];
    var campaignId = row['CampaignId'];
    adScheduleIds.push([campaignId,adScheduleId]);
  }

  var chunkedArray = [];
  var chunkSize = 10000;

  for(var i = 0; i < adScheduleIds.length; i += chunkSize){
    chunkedArray.push(adScheduleIds.slice(i, i + chunkSize));
  }

  var wantedSchedules = [];
  var existingWantedSchedules = [];

  for (var j=0; j<timesAndModifiers.length; j++) {
    wantedSchedules.push(timesAndModifiers[j][0] + "|" + (timesAndModifiers[j][1]) + "|" + timesAndModifiers[j][2] + "|" + Utilities.formatString("%.2f",timesAndModifiers[j][3]+1));
  }

  for(var i = 0; i < chunkedArray.length; i++){
    var unwantedSchedules = [];

    var adScheduleIterator = AdWordsApp.targeting()
    .adSchedules()
    .withIds(chunkedArray[i])
    .get();
    while (adScheduleIterator.hasNext()) {
      var adSchedule = adScheduleIterator.next();
      var key = adSchedule.getStartHour() + "|" + adSchedule.getEndHour() + "|" + adSchedule.getDayOfWeek() + "|" + Utilities.formatString("%.2f",adSchedule.getBidModifier());

      if (wantedSchedules.indexOf(key) > -1) {
        existingWantedSchedules.push(key + "|" + adSchedule.getCampaign().getId());
      } else {
        unwantedSchedules.push(adSchedule);
      }
    }

    for(var j = 0; j < unwantedSchedules.length; j++){
      unwantedSchedules[j].remove();
    }
  }

  return existingWantedSchedules;
}

/**
* Function to construct an iterator for shopping campaigns or regular campaigns.
*
* @param bool shoppingCampaigns Using shopping campaigns?
* @return AdWords iterator Returns the corresponding AdWords iterator
*/
function ConstructIterator(shoppingCampaigns){
  if(shoppingCampaigns === true){
    return AdWordsApp.shoppingCampaigns();
  }
  else{
    return AdWordsApp.campaigns();
  }
}

/**
* Function to set a mobile bid modifier for a set of campaigns
*
* @param array campaignIds An array of the campaign IDs to be affected
* @param Float bidModifier The multiplicative mobile bid modifier
* @return void
*/
function ModifyMobileBidAdjustment(campaignIds, bidModifier){

  var platformIds = [];
  var newBidModifier = Math.round(100*(1+bidModifier))/100;

  for(var i = 0; i < campaignIds.length; i++){
    platformIds.push([campaignIds[i],30001]);
  }

  var platformIterator = AdWordsApp.targeting()
  .platforms()
  .withIds(platformIds)
  .get();
  while (platformIterator.hasNext()) {
    var platform = platformIterator.next();
    platform.setBidModifier(newBidModifier);
  }
}