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:

Automate Copy-Pasting Google Sheet Contents Using This Google Apps Script

Ever needed to copy all the data from the different tabs in a Google Sheet file and paste them all in one tab, in the same Google Sheet? Then this script is your answer.

Important note: this script is designed for people who are at least familiar with setting up scripts inside Google Sheets. If you’ve never used Google Apps Script before, I highly suggest going through the tutorials on the Google Developers site first. 🙂

How does this script work?

  1. Copies all the contents of all the tabs/sheets inside your Google Sheets file.
  2. Pastes all the contents in the tab/sheet called “CombineSheet”, which should be the last/rightmost tab in your Google Sheets file.

The script:

Fetch Any Data in Your MCC with this Google Ads Script

One of the most useful Google Ads scripts I’ve ever used was this script made by Frederick Vallaeys of Optimyzr.com

I basically used it as a way to export Google Ads data into Google Sheets, and eventually connecting that Sheet to Data Studio, whenever the default connector was missing specific metrics. (I’m looking at you, Placement report.)

However, the script’s biggest limitations was that it can only get data from one Google Ads account at a time. If you have an MCC, then you’ll have to set the script up per account, and then have each account export to a separate sheet.

As such, it’s not very efficient to use in cases when you want to fetch all the metrics from several accounts inside an MCC for a combined total. 

This led me to make some tweaks on the original script to allow it to export data from all–or some–of the Google Ads accounts inside an MCC. And in the spirit of the original script, it’s free for everyone to use. 

Important Notes:

  1. This is designed for MCCs that contain small- to medium-sized Google Ads accounts only. Take note that instead of exporting entire sheets, this script will export contents row-by-row. So if you’re pulling too much data, the script can time out before finishing the entire export.

  2. If you want to pull data from all accounts, simply remove Line 12 and edit Line 11 to AdsManagerApp.accounts(); only

The script: