Working with Dates and Times

Google Ads scripts often need to work with dates and times. Common use cases include retrieving reports for a specific date range, scheduling campaigns or ad groups to run at specific times, and outputting to a spreadsheet the time the script last ran. This guide describes important concepts, common pitfalls, and recommended approaches when working with dates and times in Google Ads scripts.

Basic concepts

To work with dates and times in Google Ads scripts, use JavaScript's built-in date object. A JavaScript date object represents a particular moment in time. There are several ways to create a new date object:

// Create a date object for the current date and time.
var now = new Date();

// Create a date object for a past date and time using a formatted string.
var date = new Date('February 17, 2018 13:00:00 -0500');

// Create a copy of an existing date object.
var copy = new Date(date);

New Scripts users are often confused by how date objects handle timezones. A natural but incorrect way to think of a date object is as the time on a clock in a single timezone. For example, in the snippet above, some users mistakenly assume that date is valid only in one timezone, namely the timezone with a -5 hours offset that was used to create it. In that mistaken view, date would need to be "converted" to be used in other timezones.

Instead, the correct way to think about a date object is as a particular moment in time independent of any timezone. Although a particular moment is shown differently on clocks in different timezones, it is the same moment. For example, consider this snippet:

// Create two date objects with different times and timezone offsets.
var date1 = new Date('February 17, 2018 13:00:00 -0500');
var date2 = new Date('February 17, 2018 10:00:00 -0800');

// getTime() returns the number of milliseconds since the beginning of
// January 1, 1970 UTC.
// True, as the dates represent the same moment in time.
Logger.log(date1.getTime() == date2.getTime());

// False, as the dates are separate objects, though they happen to
// represent the same moment in time.
Logger.log(date1 == date2);

Since a date object represents a particular moment in time, it does not need to be "converted" across timezones. Instead, it can be rendered as a string that is formatted for a particular timezone.

To render a date as a string with a particular format and timezone, use Utilities.formatDate(date, timeZone, format). For example:

var date = new Date('February 17, 2018 13:00:00 -0500');

// February 17, 2018 13:00:00 -0500
Logger.log(Utilities.formatDate(date, 'America/New_York', 'MMMM dd, yyyy HH:mm:ss Z'));

// February 17, 2018 10:00:00 -0800
Logger.log(Utilities.formatDate(date, 'America/Los_Angeles', 'MMMM dd, yyyy HH:mm:ss Z'));

// 2018-02-17T18:00:00.000Z
Logger.log(Utilities.formatDate(date, 'Etc/GMT', 'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\''));

These examples specified the timezone directly using a timezone ID. To retrieve the timezone associated with the Google Ads account running your script, use AdsApp.currentAccount().getTimeZone().

Common pitfalls

Default timezone when logging a date object

When directly logging a date object using Logger.log(), it is rendered using a default format and timezone. For example:

var date = new Date('February 17, 2018 13:00:00 -0500');

// Wed Feb 17 10:00:00 GMT-08:00 2018
Logger.log(date);

The default timezone is America/Los_Angeles (Pacific time), regardless of the timezone associated with the Google Ads account. If you would like to render the date object as a string using a custom format and timezone for logging or other purposes, always use Utilities.formatDate(date, timeZone, format).

Default timezone when creating a date object

When creating a date object using a string that does not provide a timezone offset, the timezone is assumed to be America/Los_Angeles (Pacific time), regardless of the timezone associated with the Google Ads account. For example:

// Create a date without specifying the timezone offset.
var date = new Date('February 17, 2018 13:00:00');

// Wed Feb 17 13:00:00 GMT-08:00 2018
Logger.log(date);

When creating a date object using a string, always include a timezone offset to ensure the date object represents the moment in time you actually want.

Default timezone in date object methods

JavaScript date objects have several methods that assume a default timezone, such as

getFullYear()
getMonth()
getDate()
getDay()
getHours()
getMinutes()

their set___() equivalents, and getTimezoneOffset().

In Google Ads scripts, the default timezone is America/Los_Angeles (Pacific time), regardless of the timezone associated with the Google Ads account. Therefore, unless your Google Ads account is in this timezone, you should generally avoid using these methods.

To get the year, month, date, day, hours, or minutes for a date object in your account's timezone, use Utilities.formatDate(date, timeZone, format) with a format specifying the part of the date or time you want, and use AdsApp.currentAccount().getTimeZone() to get your account's timezone.

Creating a date object from a formatted date string

You can create a date object by passing a formatted date string to the date constructor. For example:

var date = new Date('February 17, 2018 13:00:00 -0500');

The constructor can only parse certain date string formats. To make sure your date string is parsed correctly, always provide it in the MMMM dd, yyyy HH:mm:ss Z format.

For example, to construct a date object for noon today in the current account's timezone:

var now = new Date();
var timeZone = AdsApp.currentAccount().getTimeZone();
var noonString = Utilities.formatDate(now, timeZone, 'MMMM dd, yyyy 12:00:00 Z');
var noon = new Date(noonString);

Do not use the 'z' pattern to create date strings that will be passed to a date constructor, as the constructor will not always be able to parse it. Only use the 'Z' pattern.

Date math

Some scripts need to perform simple math with dates, such as finding a date X days before or after a given date. When performing date math, use getTime(). Calling getTime() on a date object returns the number of milliseconds since the beginning of January 1, 1970 UTC. You can perform math on this value, then apply the new value to a date object using setTime() or providing it as a parameter when creating a new date object.

For example:

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var yesterday = new Date(now.getTime() - MILLIS_PER_DAY);

In this example, yesterday is exactly 24 hours ago.

Reporting

When retrieving a report using AdsApp.report(query, optArgs), the AWQL query requires dates to be specified as an 8-digit integer (YYYYMMDD). Likewise, the getStatsFor() method available on many Google Ads scripts objects requires dates to be specified as an 8-digit integer. Use Utilities.formatDate(date, timeZone, format) to format a date object in this format.

For example, to retrieve a report from one to three days ago:

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var from = new Date(now.getTime() - 3 * MILLIS_PER_DAY);
var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

var timeZone = AdsApp.currentAccount().getTimeZone();
var report = AdsApp.report(
  'SELECT CampaignName, Clicks ' +
  'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
  'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','
            + Utilities.formatDate(to, timeZone, 'yyyyMMdd'));

Spreadsheets

Google Ads scripts often write output to a spreadsheet, including date objects. When setting a cell in a spreadsheet by passing a date object, the spreadsheet's timezone is used to interpret that date. For example, suppose we have a spreadsheet whose timezone is set to Pacific Time:

// Suppose today is February 17, 2018 13:00:00 -0500 (Eastern Time)
var now = new Date();
spreadsheet.getRange('A1').setValue(now);

The value in A1 will be 17-Feb-18 10:00:00.

To ensure date objects are written to a spreadsheet as you expect, set the spreadsheet's timezone to match your Google Ads account's timezone:

spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());

You can also set a spreadsheet's time manually.