This script allows you to coordinate the use of bid modifiers on your campaign bids with a predefined schedule in a spreadsheet.
A typical use case would be to coordinate an uplift of bids with TV-based advertising campaigns, but other uses are possible, like coordinating with a series of scheduled sports events.
How the script works
The script works by reading a spreadsheet pre-populated with the dates and times of the uplift periods. The exact bid modifier and duration of uplift can be specified in the spreadsheet, or alternatively, these values can be set globally.
The script runs hourly to update the schedules for the campaigns. The spreadsheet can be updated at any time, and the changes will be reflected the next time the script runs.
For more information on how the script works, see the detailed explanation below.
Caveats for using the script
- Bidding mode
- Campaigns that use automatic bidding may not work with this script, as they may be incompatible with ad schedules. See bid adjustment eligibility.
- Existing use of ad schedules
- If a campaign relies on pre-existing ad schedules or other scripts that rely on setting ad schedules, it should not be used with this script.
- Settings ranges
- Bid modifications can range from -90% to +900%. Uplift duration can range from 15 to 180 mins.
- Timezone
- The timezone of the account will be used. You should check whether or not the account is set up to recognize daylight savings time.
Setup
Create a label in your Google Ads account, called
TV Scheduled.Apply this label to the campaigns for which you want to adjust bid modifiers according to the schedule.
Create a spreadsheet to contain the schedule information. Ideally, you can start by using this template; otherwise, see Spreadsheet Setup for working with a pre-existing spreadsheet you might have.
Create the script in your Google Ads account.
Add the link to your spreadsheet, replacing
INSERT_SPREADSHEET_URL_HEREwith your spreadsheet URL.Replace
INSERT_EMAIL_ADDRESS_HEREwith your email address, to receive notification of any errors.
Testing
Click Preview to run the script. If successful, it should print out the dates from your spreadsheet in the log window. If not successful, see the section on spreadsheet setup below. Also, check the email account you specified, as details of any errors will be sent there.
Repeat the preview until you're sure the dates are being read correctly.
Running live
Schedule the script to run hourly.
Uninstalling
If you decide to stop using the script, perform the following steps to ensure the campaigns return to normal operation:
Change the line in the script that reads
var UNINSTALL = false;to readvar UNINSTALL = true;Run (not just preview) the script.
Remove the hourly schedule for the script.
Spreadsheet setup
A template spreadsheet is available, but you can also use your own.
The configuration of the spreadsheet is in the script, and looks like this:
var SPREADSHEET_CONFIG = {
hasHeader: true,
date: 'A',
bidModifier: 'B',
duration: 'C'
};
The hasHeader field indicates whether the first line should be skipped. The
other values reference the column of the spreadsheet in which the value can be
found. Changing these values to other columns, for example D or E, will
select a different column.
Specifying fixed values
If your spreadsheet does not contain a bid modifier or a duration, you can fix
these values by using the = sign. For example, this configuration has a +50%
bid modifier for all entries in the spreadsheet:
var SPREADSHEET_CONFIG = {
hasHeader: true,
date: 'A',
bidModifier: '=1.5',
duration: 'C'
};
This example has a fixed uplift duration of 30 minutes as well:
var SPREADSHEET_CONFIG = {
hasHeader: true,
date: 'A',
bidModifier: '=1.5',
duration: '=30'
};
Troubleshooting dates
Date formats can be ambiguous depending on your locale: Does 01/02/18 represent the 2nd of January or the 1st of February?
A number of different formats work: YYYY/mm/DD HH:MM can be a good choice. Here are
some tips to get dates working correctly:
If copying from another source, such as an Excel spreadsheet or CSV, use that tool's options to format the dates in an unambigous manner first before copying and pasting.
Check to see if Sheets has recognized the value as a date by double-clicking on the value to edit it: If it is a date, the date picker will appear.
Use the preview capability on the script until you're sure the dates are being read reliably.
Source code
// 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 Google Ads and TV-based advert schedules to be coordinated.
* Visit
* https://developers.google.com/google-ads/scripts/docs/solutions/tv-schedule
* for more details.
*
* @author Google Ads 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://docs.google.com/spreadsheets/d/1Ps3x3cQ7ixBysVfDS8VObbKofcVrOJtXaWvk4eMjAeA
//
var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
// Email addresses for notification of any errors encountered.
var EMAIL_RECIPIENTS = ['INSERT_EMAIL_ADDRESS_HERE'];
// Spreadsheet configuration.
var SPREADSHEET_CONFIG = {
hasHeader: true,
date: 'A',
bidModifier: 'B',
duration: 'C'
};
// Flag to set for uninstalling the script. Instructions at
//
// https://developers.google.com/google-ads/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 Google Ads 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(),
AdsApp.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. ' +
'Visit https://support.google.com/google-ads/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 (AdsApp.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 Google Ads schedule items based on TV ad times and details.
*/
var TvScheduleCreator = (function() {
/**
* Creates a TV schedule object. This is used for calculating which Google Ads
* 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 Google Ads 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(),
AdsApp.currentAccount().getTimeZone());
var footerStyle = 'color: #aaaaaa; font-style: italic;';
var scriptsLink = 'https://developers.google.com/google-ads/scripts/';
var subject = '[Errors] Google Ads TV Coordination Script: ' + emailDate;
var htmlBody = '<html><body><p>Hello,</p>' +
'<p>A Google Ads 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 + '">Google Ads 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 = AdsApp.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 (AdsApp.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.');
}
}