If you need to change ad copy dramatically at various times during the day it can be a real drag. Nathan’s taken a simple concept written about here and expanded it to add quite a bit of flexibility.
Here’s the basics of the way it works. Start with a google sheet that follows this basic format:
The first four columns are required, as they are specified. Be sure to use AM and PM for the time as well. The remaining columns are the labels applied to any add that you want to have running during that segment of time. It’s possible to have more than one Y in a column. Y means it’s active. N will be paused.
The format for days of the week is a single letter, except for TH for Thursday and SAT for Saturday.
Run the script below every hour and enjoy the ever changing ad copy, automatically
A few comments.
- It’s entirely possible to leave out time periods, and if so no changes will be made. Even so we recommend that the entire week be covered fully, because it will make debugging much harder otherwise.
- There’s no guarantee about when a script runs during a period. While in our experience it tends to run at pretty close to the same time it was originally started it can shift from time to time. Don’t make ad copy too specific as a result.
- If your label doesn’t match it won’t get changed. That means that you can either run a non time sensitive ad all the time by removing a label or that errors won’t result in changes.
- There’s a lot of time zone correction in the script because your sheet and the script might be in different data centers. It makes it possible to run hour long ad copy as a result.
- It’s designed to run on a single campaign.
The script:
var SPREADSHEET_URL = 'urltoyoursheetgoeshere';
var CAMPAIGN_NAME = 'CAMPAIGN';
var SHEET_TAB = 'TABNAME';
var dayKeys = {
'S': 0,
'M': 1,
'T': 2,
'W': 3,
'TH': 4,
'F': 5,
'SAT': 6
}; //Day of week value is returned as an int; match those values
var headers = []; //header row
var sheetData = []; //all data in the sheet
var adLabels = []; //Stores all the column header labels in array. Determines a label whether it's underlined or not
function findAdLabelNames(sheet, headers) {
var headerRow = sheet.getRange(1, 1, 1, headers.length); //.getFontLine();
var numRows = headerRow.getNumRows();
var numCols = headerRow.getNumColumns();
for (var i = 1; i & lt; = numRows; i++) {
for (var j = 1; j & lt; = numCols; j++) {
var currentValue = headerRow.getCell(i, j).getValue();
if (headerRow.getCell(i, j).getFontLine() == 'underline') {
adLabels.push(currentValue);
}
}
}
}
function processSheetData(currentDate) {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var configSheet = spreadsheet.getSheetByName(SHEET_TAB);
var inputData = configSheet.getDataRange().getValues();
headers = inputData[0];
findAdLabelNames(configSheet, headers);
var currentDayOfWeek = currentDate.getDay();
var currentTimeHour = currentDate.getHours();
for (var i = 1; i & lt; inputData.length; i++) {
var rowStartDayOfWeek = dayKeys[inputData[i][0]];
//Find rows that should be enabled today
if (currentDayOfWeek == rowStartDayOfWeek) {
var record = inputData[i];
var tarr = {};
for (var j = 0; j & lt; record.length; j++) {
//Format start & stop date/time; spreadsheet only stores time, add the correct date
if (j == 1) {
var startDate = setCorrectDate(currentDate, setProperTimeZone(record[j]), record[0]);
//Logger.log("Start: " + startDate);
record[j] = startDate;
}
if (j == 3) {
//http://stackoverflow.com/questions/11789647/setting-day-of-week-in-javascript
var endDate = setCorrectDate(currentDate, setProperTimeZone(record[j]), record[2]);
//Logger.log("End: " + endDate);
record[j] = endDate;
}
tarr[headers[j]] = record[j];
}
sheetData.push(tarr);
}
}
return sheetData;
}
/**
* Take the day of week and a time to turn it into a complete date time object
* param {Date} currentDate
* param {Date} date
* param {String} dayOfWeek
*/
function setCorrectDate(currentDate, date, dayOfWeek) {
var endDayOfWeek = dayKeys[dayOfWeek];
date.setFullYear(currentDate.getFullYear());
date.setMonth(currentDate.getMonth());
date.setDate(currentDate.getDate());
//Logger.log("Date 1: " + date);
var currentDay = currentDate.getDay();
var distance = endDayOfWeek - currentDay;
if (distance != 0) {
date.setDate(date.getDate() + 1);
}
return date;
}
/**
* Based on the column data, enable/pause ads
*/
function toggleAdStatus(currentDate, row, shouldEnable) {
var labels = [];
for (i = 0; i & lt; adLabels.length; i++) {
if (row[adLabels[i]].toUpperCase() == shouldEnable) {
labels.push(adLabels[i]);
}
}
if (labels.length & gt; 0) {
var json = Utilities.jsonStringify(labels);
Logger.log("Enable: " + shouldEnable + " Labels: " + json);
myAdGroups = [];
var adsIterator = getCampaign().ads().withCondition('LabelNames CONTAINS_ANY ' + json + '').get();
if (adsIterator.hasNext()) {
//Find all ad groups and store them in an array
while (adsIterator.hasNext()) {
var ad = adsIterator.next();
if (shouldEnable == 'Y') {
if (!ad.isEnabled()) {
//Logger.log("Enabling Ad: " + ad.getHeadline());
ad.enable();
}
if (!inArray(ad.getAdGroup().getName(), myAdGroups)) {
myAdGroups.push(ad.getAdGroup());
}
} else if (shouldEnable == 'N') {
//Disable all ads that have passed expiration for these labels
//disable ad
if (ad.isEnabled()) {
//Logger.log("Disabling Ad: " + ad.getHeadline());
ad.pause();
}
}
}
}
for (i = 0; i & lt; myAdGroups.length; i++) {
updateAdParameterValues(myAdGroups[i], hoursLeft);
}
}
}
/**
* If the date range passes, go through the labels in the spreadsheet row and enabling/disabling ads
*/
function processAds(currentDate, row) {
if (isDateWithinRange(currentDate, row['Start Time'], row['End Time'])) {
toggleAdStatus(currentDate, row, 'Y');
toggleAdStatus(currentDate, row, 'N');
}
}
function main() {
var currentDate = getAccountCurrentDateTime();
Logger.log("Current Process Time: " + currentDate);
//Read all fields from spreadsheet
sheetData = processSheetData(currentDate);
//Loop records we should be processing today
for (var i = 0; i & lt; sheetData.length; i++) {
processAds(currentDate, sheetData[i]);
}
} /** * Find out if the specified date falls within the start & end dates */
function isDateWithinRange(currentDate, startDate, endDate) {
if ((currentDate & gt; = startDate) & amp; & amp;
(currentDate & lt; = endDate)) {
return true;
}
return false;
}
/**
* Get a specified campaign based on a global var we've set
*/
function getCampaign() {
return AdWordsApp.campaigns().withCondition("Name = '" + CAMPAIGN_NAME + "'").get().next();
}
/**
* Make sure when getting a date object, we're basing it off the account time, not the data center server time
*/
function getAccountCurrentDateTime() {
return new Date(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "MMM dd,yyyy HH:mm:ss"));
}
//The time will convert to whatever server/data center the script is running on. Make sure the time is set to the AW account time
//Accepts a date object
function setProperTimeZone(date) {
return new Date(Utilities.formatDate(date, AdWordsApp.currentAccount().getTimeZone(), "MMM dd,yyyy HH:mm:ss"));
}
function arrayCompare(a1, a2) {
if (a1.length != a2.length) return false;
var length = a2.length;
for (var i = 0; i & lt; length; i++) {
if (a1[i] !== a2[i]) return false;
}
return true;
}
function inArray(needle, haystack) {
var length = haystack.length;
for (var i = 0; i & lt; length; i++) {
if (haystack[i].getName() == needle) return true;
}
return false;
}