Export Dynamic Year To Date Reports to Google Sheets, Using Google Ads Scripts

You’ve probably seen my previous post about a Google Ads script to fetch data from the MCC level. That script was a modification on this script originally made by Frederick Vallaeys of Optmyzr.com, which utilizes the AdWords Query Language (AWQL) to construct a query.

Here’s another variation on that script: a way to dynamically export Year To Date data from Google Ads to Google Sheets.

Why do we need this?

One AWQL limitation is that the available default date ranges only include the following:

  • TODAY 
  • YESTERDAY 
  • LAST_7_DAYS 
  • LAST_WEEK 
  • LAST_BUSINESS_WEEK 
  • THIS_MONTH
  • LAST_MONTH 
  • LAST_14_DAYS 
  • LAST_30_DAYS 
  • THIS_WEEK_SUN_TODAY 
  • THIS_WEEK_MON_TODAY 
  • LAST_WEEK_SUN_SAT

Since the original script uses AWQL, if you’d want to get a year-to-date report, you’ll need to edit the DURING clause in line 27 to use a static date range in this format: YYYYMMDD

> DURING 20200101, 20200810

Obviously, this is not a good solution, since you’d need to edit the end date in the script every day to fetch updated data.

That’s where this variation of the script comes in. By constructing a date variable in JavaScript to output the date today and in the format that AWQL needs, you can have a dynamic value for the end date. 

After adding this script in Google Ads, make sure that it’s scheduled to run daily, otherwise it won’t pull the data every day.

Here’s the updated script: