Google Ads Script (AWQL) get custom date range for reporting

Question

I need to pull a google ads report that will get data from a fixed date (28th May) until today and push the data to a spreadsheet. I can't figure out how to define the date range for this query

I've tried googling and reading the google documentation but I can't figure it out

function main() {
  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/XXX');
  var sheet = spreadsheet.getSheetByName('Data')  
  var report = AdsApp.report(
    'SELECT Date, CampaignName, AverageFrequency, Impressions, ImpressionReach ' +
    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE  Impressions > 0 ' +
    'DURING 20190528,TODAY');

  sheet.clearContents();
  report.exportToSheet(sheet);
}

I need to use today as the end date instead of the campaign end date as the end date for this query as I'm trying to pull frequency as a metric and it will just show blank values if the end date is in the future.

Please let me know if there is a way to make the query work. Thanks!

Answer 1

The TODAY keyword acts as the "full range" of the DURING property and cannot be used as the end part (as far as I know). The following should work.

function main() {
    var endDate = new Date();
    var endRange = Utilities.formatDate(endDate, 'America/Chicago', 'YYYYMMdd');

    var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/XXX');
    var sheet = spreadsheet.getSheetByName('Data')  
    var report = AdsApp.report(
        'SELECT Date, CampaignName, AverageFrequency, Impressions, ImpressionReach ' +
        'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
        'WHERE  Impressions > 0 ' +
        'DURING 20190528,' + endRange);

    sheet.clearContents();
    report.exportToSheet(sheet);
}