テレビ スケジュールに基づく入札調整

このスクリプトでは、スプレッドシートにあらかじめ設定したスケジュールに合わせて、キャンペーンの入札単価に単価調整比を適用できます。

キャンペーンの入札単価をテレビ CM の放送に合わせて引き上げるのが一般的な利用方法ですが、スポーツ イベントの期間に合わせて調整するなどの利用方法もあります。

スクリプトの仕組み

スクリプトは、単価を引き上げる期間の日付と時間をあらかじめ入力したスプレッドシートを読み取ることで、処理を実行します。入札単価調整比と単価の引き上げ期間を、スプレッドシートで厳密に指定することも、幅を持たせることもできます。

スクリプトは 1 時間ごとに実行されるたびにキャンペーンのスケジュールを更新します。スプレッドシートは随時変更が可能で、変更は次回のスクリプト実行時から反映されます。

スクリプトの仕組みについての詳細は、下記の詳細情報をご覧ください。

スクリプト使用上の注意

  • 入札単価調整: 自動入札戦略を使用しているキャンペーンでは、広告のスケジュールとの両立ができず、このスクリプトを使用できない場合があります。入札単価調整の要件をご覧ください。
  • 広告スケジュールの利用: 既存の広告スケジュールに基づくキャンペーンや、広告のスケジュール設定が必要な他のスクリプトに基づくキャンペーンでは、このスクリプトは使用しないでください。
  • 設定範囲: 入札単価調整比は、-90~+900% の範囲で指定します。単価を引き上げる期間は、15~180 分の範囲で値を指定します。
  • タイムゾーン: アカウントのタイムゾーンが使用されます。アカウントが夏時間を認識する設定になっているかどうか、確認が必要です。

設定方法

  1. AdWords アカウントで、「TV Scheduled」というラベルを作成します。

  2. 作成したラベルを、テレビの放送スケジュールに応じて入札単価調整比を変更したいキャンペーンに適用します。

  3. スプレッドシートにスケジュールを入力します。こちらのテンプレートを使用することをおすすめしますが、すでにお手元にあるスプレッドシートで作業する場合は、スプレッドシートの設定をご覧ください。

  4. AdWords アカウントでスクリプトを作成します。

  5. INSERT_SPREADSHEET_URL_HERE をご自身のスプレッドシートの URL で置き換えて、リンクを追加します。

  6. エラーが発生したときに通知を受け取ることができるように、INSERT_EMAIL_ADDRESS_HERE をご自身のメールアドレスで置き換えます。

テスト

  1. プレビューをクリックして、スクリプトを実行します。問題なく実行されると、スプレッドシートからログウィンドウに日付が出力されます。エラーが生じた場合は、以下のスプレッドシートの設定をご覧ください。また、エラーの詳細はメールで送信されますので、ご自身で指定したメール アカウントをご確認ください。

  2. 日付が正しく読み取られていることを確認できるまで、プレビューを繰り返します。

実際の運用

  1. スクリプトが毎時間実行されるようスケジュールを設定します。

スクリプトの使用中止

スクリプトの使用を中止する際は、次の手順に従って、キャンペーンを通常の運用に戻します。

  1. スクリプト内の var UNINSTALL = false; という行を、var UNINSTALL = true; に変更します。

  2. スクリプトを実行します。プレビューはしません

  3. スクリプトに設定していた毎時間のスケジュールを削除します。

スプレッドシートの設定

テンプレート スプレッドシートが用意されていますが、ご自身のスプレッドシートも使用できます。

スプレッドシートの設定は、次のようにスクリプトに含まれています。

var SPREADSHEET_CONFIG = {
  hasHeader: true,
  date: 'A',
  bidModifier: 'B',
  duration: 'C'
};

hasHeader フィールドは、1 行目をスキップすべきかどうかを示します。他の値は、スプレッドシート内に含まれる列を示します。この値を他の列の値(たとえば DE)に変更すると、異なる列が選択されます。

固定値の指定

スプレッドシートに入力単価調整比や期間が含まれていない場合、= の記号を使用して固定値で指定できます。たとえば次の設定では、スプレッドシート内のすべてのエントリーに +50% の入札単価調整比を適用します。

var SPREADSHEET_CONFIG = {
  hasHeader: true,
  date: 'A',
  bidModifier: '=1.5',
  duration: 'C'
};

次の例では、単価を引き上げる期間も固定して 30 分にしています。

var SPREADSHEET_CONFIG = {
  hasHeader: true,
  date: 'A',
  bidModifier: '=1.5',
  duration: '=30'
};

日付に関するトラブルシューティング

日付の形式は、地域や言語圏によって異なる場合があります。たとえば、01/02/15 は 1 月 2 日と 2 月 1 日のどちらを指すか、わかりにくいことがあります。

そこで、どんな形式にも対応できるよう、YYYY/mm/DD HH:MM を使用することをおすすめします。また、日付を正しく設定するために、以下のヒントをご覧ください。

  • Excel のスプレッドシートや CSV など、他のソースからデータをコピーして貼り付ける場合は、Excel などのツールのオプションから、曖昧でない形式で日付を先に設定します。
  • スプレッドシートが数値を日付として認識していることを確認します。確認するには、数値をダブルクリックして編集できる状態にします。数値が日付になっていれば、日付選択ツールが表示されます。
  • 日付が正しく読み取られることを確認できるまで、スクリプトのプレビュー機能を繰り返し使用します。

ソースコード

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name TV Advert bid coordination.
 *
 * @overview Allows AdWords and TV-based advert schedules to be coordinated.
 *     See https://developers.google.com/adwords/scripts/docs/solutions/tv-schedule
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.1.1
 *
 * @changelog
 * - version 1.1.1
 *   - Added validation for external spreadsheet setup.
 * - version 1.1
 *   - Fixed minor bug with error messages.
 * - version 1.0
 *   - Released initial version.
 */

// The URL for the spreadsheet of TV advert dates and times. This spreadsheet
// can be used as a template : https://goo.gl/JsqfDq
var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';

// Email addresses for notification of any errors encountered.
var EMAIL_RECIPIENTS = ['INSERT_EMAIL_ADDRESS_HERE'];

// Spreadsheet configuration - see help at for details.
var SPREADSHEET_CONFIG = {
  hasHeader: true,
  date: 'A',
  bidModifier: 'B',
  duration: 'C'
};

// Flag to set for uninstalling the script - see instructions at:
// https://developers.google.com/adwords/scripts/docs/solutions/tv-schedule
var UNINSTALL = false;

// The label applied to all TV scheduled campaigns.
var TV_CAMPAIGN_LABEL = 'TV Scheduled';

var DAYS = [
  'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY'
];

var MILLIS_PER_MIN = 1000 * 60;
var MILLIS_PER_DAY = MILLIS_PER_MIN * 60 * 24;
var SLOT_MINS = 15;
var SLOTS_PER_HOUR = 60 / SLOT_MINS;
var SLOTS_PER_DAY = 60 * 24 / SLOT_MINS;

// The AdWords limit on the number schedule entries per campaign per day.
var MAX_SCHEDULE_ITEMS_PER_DAY = 6;

var MIN_BID_MODIFIER = 0.1;
var MAX_BID_MODIFIER = 9.0;
var MIN_DURATION_MINS = 0;
var MAX_DURATION_MINS = 180;

var ROUND_FORWARD = 1;
var ROUND_BACKWARD = 0;

// The 'range' specifies how far ahead, when setting schedule items to affect
// the immediate future, items should be inserted for.
var DEFAULT_RANGE = MAX_SCHEDULE_ITEMS_PER_DAY * SLOT_MINS;

// Only manual bidding strategies support Ad Scheduling bid adjustments.
var MANUAL_BIDDING_STRATEGIES = ['MANUAL_CPC', 'MANUAL_CPC'];

var SCHEDULE_SHEET_NAME = 'Schedule';

// Variables used to hold Dates for the three days relevant to this execution.
var yesterday = null;
var today = null;
var tomorrow = null;

/**
 * Main entry point
 */
function main() {
  var execDateTime = SimpleDateFactory.fromDate(localDate(new Date(),
      AdWordsApp.currentAccount().getTimeZone()));
  var tvScheduleCreator = new TvScheduleCreator();
  var spreadsheetReader = new SpreadsheetReader(SPREADSHEET_URL,
      SPREADSHEET_CONFIG);
  var errors = spreadsheetReader.getErrors();

  if (UNINSTALL) {
    removeAllSchedulesForUninstall(errors);
  } else {
    // Form relevant dates.
    today = SimpleDateFactory.fromDate(new Date());
    yesterday = SimpleDateFactory.fromDate(getDatePlusDays(new Date(), -1));
    tomorrow = SimpleDateFactory.fromDate(getDatePlusDays(new Date(), 1));

    // Only add items from the spreadsheet if no errors were encountered.
    if (errors.length === 0) {
      var rows = spreadsheetReader.getRows();
      for (var i = 0; i < rows.length; i++) {
        var row = rows[i];
        tvScheduleCreator.addTvAd(row[0], row[1], row[2]);
      }
    }
    var scheduleItems = tvScheduleCreator.getScheduleItemsToAdd(execDateTime);
    var campaigns = getCampaigns();
    for (var i = 0; i < campaigns.length; i++) {
      var campaign = campaigns[i];
      var biddingStrategy = campaign.bidding().getStrategyType();
      if (MANUAL_BIDDING_STRATEGIES.indexOf(biddingStrategy) >= 0) {
        applySchedulesToCampaign(campaign, scheduleItems);
      } else {
        storeAndLogError(errors, '"' + campaign.getName() +
            '" does not use manual bidding, so cannot use Ad Scheduling. ' +
            'See : https://support.google.com/adwords/answer/2732132');
      }
    }
  }
  if (errors.length > 0) {
    validateEmailAddresses();
    sendErrorEmail(errors);
  }
}

/**
 * Returns a Date formatted for the specified timezone.
 *
 * @param {Date} date The date to convert.
 * @param {String} timeZone The desired time zone.
 * @return {Date} The newly-constructed Date.
 */
function localDate(date, timeZone) {
  return new Date(Utilities.formatDate(date, timeZone, 'yyyy MMM dd HH:mm:ss'));
}

/**
 * Creates a Date +/- a given number of days from the specified Date.
 *
 * @param {Date} date The date to start from.
 * @param {number} days The number of days to add (or subtract if negative).
 * @return {Date} The newly created Date.
 */
function getDatePlusDays(date, days) {
  var newDate = new Date(date.getTime());
  newDate.setTime(date.getTime() + days * MILLIS_PER_DAY);
  return newDate;
}

/**
 * SimpleDate represents a date/time with granularity of 15 mins.
 *
 * @param {number} year Year e.g. 2015.
 * @param {number} month 0-indexed month, e.g. 0 = January.
 * @param {number} day 1-31 day of the month.
 * @param {number} hours 0-23
 * @param {number} minutes 0-59, will be rounded to multiple of 15.
 * @param {number} roundingMode Whether to round back (default) or forward.
 * @constructor
 */
function SimpleDate(year, month, day, hours, minutes, roundingMode) {
  var roundedMins = null;
  if (roundingMode === ROUND_FORWARD) {
    roundedMins = Math.ceil(minutes / SLOT_MINS) * SLOT_MINS;
  } else {
    roundedMins = Math.floor(minutes / SLOT_MINS) * SLOT_MINS;
  }
  this.datetime = new Date(year, month, day, hours, roundedMins);
}

/**
 * Returns the slot that this time falls in (for example, between 0 and 95).
 *
 * @return {number} The 15-min slot that the given time falls into.
 */
SimpleDate.prototype.getSlotIndex = function() {
  return this.datetime.getHours() * SLOTS_PER_HOUR +
      Math.floor(this.datetime.getMinutes() / SLOT_MINS);
};

SimpleDate.prototype.getDayOfWeek = function() {
  return this.datetime.getDay();
};

SimpleDate.prototype.getHours = function() {
  return this.datetime.getHours();
};

SimpleDate.prototype.getMinutes = function() {
  return this.datetime.getMinutes();
};

SimpleDate.prototype.equals = function(simpleDate) {
  return this.valueOf() === simpleDate.valueOf();
};

SimpleDate.prototype.valueOf = function() {
  return this.datetime.getTime() / MILLIS_PER_MIN;
};


SimpleDate.prototype.toString = function() {
  return Utilities.formatString('%d-%02d-%02d %02d:%02d',
      this.datetime.getFullYear(), this.datetime.getMonth() + 1,
      this.datetime.getDate(), this.datetime.getHours(),
      this.datetime.getMinutes());
};


SimpleDate.prototype.isSameDay = function(simpleTime) {
  return this.datetime.getFullYear() === simpleTime.datetime.getFullYear() &&
      this.datetime.getMonth() === simpleTime.datetime.getMonth() &&
      this.datetime.getDate() === simpleTime.datetime.getDate();
};

var SimpleDateFactory = (function() {
  /**
   * Creates a SimpleDate from a given Date object
   *
   * @param {Date} date The date to convert.
   * @param {number} roundingMode Whether to round forward or back.
   * @return {SimpleDate} The new SimpleDate, or null if not a Date or invalid.
   */
  function fromDate(date, roundingMode) {
    if (!date || !(date instanceof Date) || isNaN(date.getTime())) {
      return null;
    }
    return new SimpleDate(date.getFullYear(), date.getMonth(), date.getDate(),
        date.getHours(), date.getMinutes(), roundingMode);
  }

  /**
   * Creates a SimpleDate from the given SimpleDate and slot index.
   *
   * @param {SimpleDate} simpleDate The SimpleDate to copy year/month/day from.
   * @param {number} index The 15-min slot index (e.g. 0-95) to represent time.
   * @return {SimpleDate} The new SimpleDate.
   */
  function fromSimpleDateIndex(simpleDate, index) {
    return new SimpleDate(simpleDate.datetime.getFullYear(),
        simpleDate.datetime.getMonth(), simpleDate.datetime.getDate(),
        Math.floor(index / SLOTS_PER_HOUR), index % SLOTS_PER_HOUR * SLOT_MINS);
  }

  return {
    fromDate: fromDate,
    fromSimpleDateIndex: fromSimpleDateIndex
  };
})();

/**
 * Provides the ability to read TV advert details from a Google Spreadsheet.
 */
var SpreadsheetReader = (function() {
  /**
   * Reads a Google Sheet, to obtain the TV ad schedules.
   *
   * @param {string} url The URL of the spreadsheet.
   * @param {object} config Configuration of which fields are in which columns.
   * @constructor
   */
  function SpreadsheetReaderCtor(url, config) {
    this.errors = [];
    var rawData = [];

    try {
      rawData = readDataFromGoogleSheet_(url);
    } catch (e) {
      storeAndLogError(this.errors, e.message);
    }
    if (rawData) {
      this.data = parseData_(this.errors, rawData, config);
    }
  }

  /**
   * Retrieves successfully parsed entries.
   *
   * @return {object[]} The rows
   * @return {Date} object[][0] The start date for the ad.
   * @return {number} object[][1] The bid modifier.
   * @return {number} object[][2] The duration in minutes.
   */
  SpreadsheetReaderCtor.prototype.getRows = function() {
    return this.data;
  };

  /**
   * Retrieves errors encountered in loading data from the spreadsheet.
   *
   * @return {String[]} The list of errors.
   */
  SpreadsheetReaderCtor.prototype.getErrors = function() {
    return this.errors;
  };

  /**
   * Reads a two-dimensional list representing the Google Spreadsheet.
   * Individual cells can vary in their data type, being String, number or Date.
   *
   * @param {string} url The spreadsheet URL.
   * @return {object[][]} 2D representation of the data in the file.
   * @private
   */
  function readDataFromGoogleSheet_(url) {
    var spreadsheet = validateAndGetSpreadsheet(url);
    if (!spreadsheet) {
      throw Error('Unable to open spreadsheet with URL: ' + url);
    }
    var timeZone = spreadsheet.getSpreadsheetTimeZone();
    var sheet = spreadsheet.getSheetByName(SCHEDULE_SHEET_NAME);
    if (!sheet) {
      throw Error('Spreadsheet must have a sheet named: "' +
          SCHEDULE_SHEET_NAME + '"');
    }
    var values = sheet.getDataRange().getValues();
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[i].length; j++) {
        if (values[i][j] instanceof Date) {
          values[i][j] = localDate(values[i][j], timeZone);
        }
      }
    }
    return values;
  }

  /**
   * Determines whether an TV ad satisfies constraints.
   *
   * @param {string[]} errors An array to store errors encountered.
   * @param {Date} date Represents the start date/time.
   * @param {number} bidModifier The bid modifier for the period.
   * @param {number} duration The duration of the modifier, in minutes.
   * @param {number} rowIndex The row number, counting from 1.
   * @return {boolean} Whether these constraints are met.
   * @private
   */
  function isValidEntry_(errors, date, bidModifier, duration, rowIndex) {
    if (AdWordsApp.getExecutionInfo().isPreview()) {
      Logger.log([
        'Row:', rowIndex,
        'startDate (y-m-d h-m):', SimpleDateFactory.fromDate(date),
        'bidModifier:', bidModifier,
        'duration:', duration
      ].join(' '));
    }
    if (bidModifier < MIN_BID_MODIFIER || bidModifier > MAX_BID_MODIFIER ||
        isNaN(bidModifier)) {
      storeAndLogError(errors, 'Invalid bid modifier at row ' + rowIndex);
      return false;
    } else if (duration <= MIN_DURATION_MINS || duration > MAX_DURATION_MINS) {
      storeAndLogError(errors, 'Invalid duration at row ' + rowIndex);
      return false;
    } else if (!date || !(date instanceof Date) || isNaN(date.getTime())) {
      storeAndLogError(errors, 'Invalid date/time at row ' + rowIndex);
      return false;
    }
    return true;
  }

  /**
   * Converts an alphabet-based column index to a numeric one, e.g. "A" -> 0.
   * @param {string} alpha The single character to convert.
   * @return {number} The numerical representation.
   * @private
   */
  function alphaToCol_(alpha) {
    return alpha.charCodeAt() - 'A'.charCodeAt();
  }

  /**
   * Parses the raw data obtained from a Google Spreadsheet to obtain triples of
   * start date/time, bid modifier and duration.
   *
   * @param {string[]} errors An array to store errors encountered.
   * @param {object[][]} rawData 2D list of the raw data obtained from the file.
   * @param {object} config Configuration of which fields are in which columns.
   * @private
   */
  function parseData_(errors, rawData, config) {
    var parsedRows = [];
    for (var r = config.hasHeader ? 1 : 0; r < rawData.length; r++) {
      var date = null;
      var bidModifier = null;
      var duration = null;
      var row = rawData[r];

      // '=' prefix means fixed value, otherwise the value specifies the column.
      if (config.bidModifier[0] === '=') {
        bidModifier = config.bidModifier.substring(1).replace(',', '.');
      } else {
        bidModifier = parseFloat(
            String(row[alphaToCol_(config.bidModifier)]).replace(',', '.'));
      }
      if (config.duration[0] === '=') {
        duration = config.duration.substring(1);
      } else {
        duration = row[alphaToCol_(config.duration)];
      }

      if (config.date[0] === '=') {
        date = new Date(config.date.substring(1));
      } else {
        date = row[alphaToCol_(config.date)];
      }

      if (isValidEntry_(errors, date, bidModifier, duration, r + 1)) {
        parsedRows.push([date, bidModifier, duration]);
      }
    }
    return parsedRows;
  }

  return SpreadsheetReaderCtor;
})();

/**
 * Creates a list of AdWords schedule items based on TV ad times and details.
 */
var TvScheduleCreator = (function() {
  /**
   * Creates a TV schedule object. This is used for calculating which AdWords
   * schedule items to create for a given set of TV ad details.
   *
   * @class
   */
  function TvScheduleCreatorCtor() {
    this.daySlots = [];
    for (var d = 0; d < DAYS.length; d++) {
      var slots = [];
      for (var i = 0; i < SLOTS_PER_DAY; i++) {
        slots.push(null);
      }
      this.daySlots.push(slots);
    }
  }

  /**
   * Retrieves the schedule items that should be added to campaigns.
   *
   * @param {SimpleDate} curDate The date/time of current execution.
   * @param {number=} opt_range The range beyond curDate deemed relevant. If not
   *     specified, defaults to DEFAULT_RANGE.
   * @return {object[]} A list of schedule items for applying to campaigns.
   */
  TvScheduleCreatorCtor.prototype.getScheduleItemsToAdd = function(curDate,
      opt_range) {
    var todaySlots = this.daySlots[curDate.getDayOfWeek()];
    var curModifier = todaySlots[0];
    var start = 0;
    var schedule = [];
    var range = opt_range || DEFAULT_RANGE;

    // If there is a change in bid modifer from the last slot to this then this
    // represents the boundary of a schedule.
    for (var index = 0; index < todaySlots.length; index++) {
      if (todaySlots[index] !== curModifier) {
        addIfInBounds_(schedule, curDate, start, index, range, curModifier);
        start = index;
        curModifier = todaySlots[index];
      }
    }
    addIfInBounds_(schedule, curDate, start, SLOTS_PER_DAY, range, curModifier);

    // If the range of relevant schedule items possibly extends to tomorrow, add
    // results from that part of tomorrow too.
    if (SLOTS_PER_DAY - curDate.getSlotIndex() < range / SLOT_MINS) {
      range -= (SLOTS_PER_DAY - curDate.getSlotIndex()) * SLOT_MINS;
      var nextDay = getDatePlusDays(curDate.datetime, 1);
      nextDay.setHours(0);
      nextDay.setMinutes(0);
      var tomorrowFirstSlot = SimpleDateFactory.fromDate(nextDay);
      var extraResults = this.getScheduleItemsToAdd(tomorrowFirstSlot, range);
      extraResults.forEach(function(entry) {
        schedule.push(entry);
      });
    }
    return schedule;
  };

  /**
   * Adds the specified TV ad start/duration to the schedule. The modifier is
   * only updated where the existing modifier is less, or not specified.
   *
   * @param {Date} start The start time of the TV advert.
   * @param {Number} bidModifier The bid modifier to apply.
   * @param {Number} duration The duration of the uplift, in minutes.
   */
  TvScheduleCreatorCtor.prototype.addTvAd = function(start, bidModifier,
      duration) {
    // Only TV ads scheduled for yesterday, today or tomorrow can be relevant.
    var end = new Date(start.getTime() + duration * MILLIS_PER_MIN);

    var simpleStart = SimpleDateFactory.fromDate(start, ROUND_BACKWARD);
    var simpleEnd = SimpleDateFactory.fromDate(end, ROUND_FORWARD);

    if (!simpleStart.isSameDay(yesterday) && !simpleStart.isSameDay(today) &&
        !simpleStart.isSameDay(tomorrow)) {
      return;
    }

    var todaySlots = this.daySlots[simpleStart.getDayOfWeek()];
    var startIndex = simpleStart.getSlotIndex();
    var endIndex = simpleEnd.getSlotIndex();

    if (endIndex < startIndex) {
      // Adds ads for tomorrow, if the duration takes it into the next day.
      var tomorrowSlots = this.daySlots[(simpleStart.getDayOfWeek() + 1) % 7];
      applyModifierToRange_(tomorrowSlots, 0, endIndex, bidModifier);
      endIndex = SLOTS_PER_DAY;
    }
    applyModifierToRange_(todaySlots, startIndex, endIndex, bidModifier);
  };

  /**
   * Adds a schedule item if the start or end overlap with the period between
   * now and now + range.
   *
   * @param {object[]} schedule The list of schedule items to add to.
   * @param {SimpleDate} date The current date and time of execution.
   * @param {number} startIdx The slot-index that this uplift starts at.
   * @param {number} endIdx The slot-index 1 past the the end of the uplift.
   * @param {number} range Length of the period from now to consider in minutes.
   * @param {number} modifier The bid modifier to apply.
   * @private
   */
  function addIfInBounds_(schedule, date, startIdx, endIdx, range, modifier) {
    var start = SimpleDateFactory.fromSimpleDateIndex(date, startIdx);
    var end = SimpleDateFactory.fromSimpleDateIndex(date, endIdx);
    var endHours = end.getHours();
    if (end.getHours() === 0 && end.getMinutes() === 0) {
      endHours = 24;
    }
    if (start >= date && start - date < range || start < date && end > date) {
      schedule.push({
        dayOfWeek: DAYS[date.getDayOfWeek()],
        startHour: start.getHours(),
        startMinute: start.getMinutes(),
        endHour: endHours,
        endMinute: end.getMinutes(),
        bidModifier: modifier === null ? 1 : modifier
      });
    }
  }

  /**
   * Applies the bid modifier to the slots in the range where no modifier exists
   * or the existing modifier is less.
   *
   * @param {number[]} scheduleSlots List holding the 96 time slots of the day.
   * @param {number} startIndex The starting index of the range.
   * @param {number} endIndex The final slot in the range, plus one.
   * @param {number} modifier The bid modifier to apply.
   * @private
   */
  function applyModifierToRange_(scheduleSlots, startIndex, endIndex,
      modifier) {
    for (var j = startIndex; j < endIndex; j++) {
      if (scheduleSlots[j] === null || modifier > scheduleSlots[j]) {
        scheduleSlots[j] = modifier;
      }
    }
  }

  return TvScheduleCreatorCtor;
})();

/**
 * Applies AdWords schedule items to a given campaign, after first clearing
 * existing items.
 *
 * @param {Campaign} campaign The campaign to apply to.
 * @param {object[]} scheduleItems The schedule items to add.
 */
function applySchedulesToCampaign(campaign, scheduleItems) {
  removePastScheduleDay(campaign, yesterday.getDayOfWeek());
  removePastScheduleDay(campaign, today.getDayOfWeek());
  removePastScheduleDay(campaign, tomorrow.getDayOfWeek());
  for (var i = 0; i < scheduleItems.length; i++) {
    campaign.addAdSchedule(scheduleItems[i]);
  }
}

/**
 * Sends an email of the errors that have been encountered in parsing the
 * spreadsheet file, which are supplied as a list of strings
 *
 * @param {String[]} errors The errors to list in the email.
 */
function sendErrorEmail(errors) {
  var emailDate = localDate(new Date(),
      AdWordsApp.currentAccount().getTimeZone());
  var footerStyle = 'color: #aaaaaa; font-style: italic;';
  var scriptsLink = 'https://developers.google.com/adwords/scripts/';
  var subject = '[Errors] AdWords TV Coordination Script: ' + emailDate;
  var htmlBody = '<html><body><p>Hello,</p>' +
      '<p>An AdWords Script has run and these errors were encountered :<ul>';
  for (var i = 0; i < errors.length; i++) {
    htmlBody += '<li>' + errors[i] + '</li>';
  }
  htmlBody += '</ul></p><p>Regards,</p>' +
      '<span style="' + footerStyle + '">This email was automatically ' +
      'generated by <a href="' + scriptsLink + '">AdWords Scripts</a>.<span>' +
      '</body></html>';
  var body = 'Please enable HTML to view this email.';
  var options = {htmlBody: htmlBody};
  MailApp.sendEmail(EMAIL_RECIPIENTS, subject, body, options);
}

/**
 * Retrieves a list of campaigns that have been targeted for TV scheduling.
 *
 * @return {Campaign[]}
 */
function getCampaigns() {
  var campaignList = [];
  var campaigns = AdWordsApp.campaigns().
      withCondition('LabelNames CONTAINS_ANY ["' + TV_CAMPAIGN_LABEL + '"]').
      withCondition('Status = "ENABLED"').get();
  while (campaigns.hasNext()) {
    campaignList.push(campaigns.next());
  }
  return campaignList;
}

/**
 * Removes schedules for specified day
 *
 * @param {Campaign} campaign The campaign to remove schedule items from.
 * @param {number} dayIndex The day in question (0=Sunday, 1=Monday) etc.
 */
function removePastScheduleDay(campaign, dayIndex) {
  var schedules = campaign.targeting().adSchedules().get();
  while (schedules.hasNext()) {
    var schedule = schedules.next();
    if (schedule.getDayOfWeek() === DAYS[dayIndex]) {
      schedule.remove();
    }
  }
}

/**
 * Removes schedules for all Campaigns, for use when ceasing use of the script.
 *
 * @param {string[]} errors Array to hold errors encountered.
 */
function removeAllSchedulesForUninstall(errors) {
  if (AdWordsApp.getExecutionInfo().isPreview()) {
    storeAndLogError(errors, 'Please run uninstall again in non-preview mode.');
    return;
  }
  var campaigns = getCampaigns();
  for (var i = 0; i < campaigns.length; i++) {
    var campaign = campaigns[i];
    var schedules = campaign.targeting().adSchedules().get();
    while (schedules.hasNext()) {
      var schedule = schedules.next();
      schedule.remove();
    }
  }
}

/**
 * Helper to both log and collect errors.
 *
 * @param {string[]} logArray The array to append entries to.
 * @param {string} errorMessage The message to append.
 */
function storeAndLogError(logArray, errorMessage) {
  Logger.log(errorMessage);
  logArray.push(errorMessage);
}

/**
 * DO NOT EDIT ANYTHING BELOW THIS LINE.
 * Please modify your spreadsheet URL and email addresses at the top of the file
 * only.
 */

/**
 * Validates the provided spreadsheet URL and email address
 * to make sure that they're set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL or email hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'INSERT_SPREADSHEET_URL_HERE') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  return spreadsheet;
}

/**
 * Validates the provided email address to make sure it's not the default.
 * Throws a descriptive error message if validation fails.
 *
 * @throws {Error} If the list of email addresses is still the default
 */
function validateEmailAddresses() {
  if (EMAIL_RECIPIENTS &&
      EMAIL_RECIPIENTS[0] == 'INSERT_EMAIL_ADDRESS_HERE') {
    throw new Error('Please specify a valid email address.');
  }
}

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。