Dynamic Date Range for Campaign Performance Report

[Carter Knight]

Hello,

I was wondering if anyone has ever discovered how to write a script that can pull out information from a Google Ads account for a custom date range that changes and put it into a Google Sheet. This script below does work, but I have to go in each day and change the var dateRange to match what I'm looking for. Could anyone please help me to make this script dynamic so that it pulls the month-to-date (without including today) automatically?

function main() {  

  cleardata();

  campaigndata();

}

//Clear Existing Data From Sheet In Googledoc

function cleardata() {

  var gdoc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/10tpelFNXVkDAN5C-URNTlBVPOsLhqlNz7tY-rGi1pxQ/edit#gid=1324939653'); 

  var dbsheet = gdoc.getSheetByName("Google");

  

//dbsheet.activate();

  dbsheet.clearContents();

}

 //Get Data & Export to File

function campaigndata() { 

  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/10tpelFNXVkDAN5C-URNTlBVPOsLhqlNz7tY-rGi1pxQ/edit#gid=1324939653');

  var dateRange = ("20210301,20210328");

  var report = AdsApp.report("SELECT CampaignName, AverageCpc, Cost, Conversions, Clicks, ConversionRate, SearchBudgetLostImpressionShare, Amount, CampaignStatus, Impressions, SearchImpressionShare " +

       "FROM CAMPAIGN_PERFORMANCE_REPORT " + " WHERE AdvertisingChannelType = SEARCH AND Impressions > 0 AND CampaignName DOES_NOT_CONTAIN 'CL_' " +

       "DURING " + dateRange);

  

  

  var rows = report.rows();

 while (rows.hasNext()) {

   var row = rows.next();

 }

  

 report.exportToSheet(spreadsheet.getSheetByName("Google"));

 Logger.log("Report Available at " + spreadsheet.getUrl()); 

} 

Any help would be much appreciated, thanks!

[Google Ads Scripts Forum Advisor]

Hi Carter,

Thanks for reaching out. I am Harry from the Google Ads Scripts Team. Allow me to assist you on this.

You may try to make use of the JavaScript Date object to dynamically retrieve date values to pass in as arguments to the report's date range. You may retrieve first day of the month and the last current day of the month, then deduct 1 from it to exclude the date today. Kindly take note also of the date format in AWQL.

I haven't tried this myself, but this should be possible. You may refer to this example in Stack Overflow. Kindly check out the Dates and Times guide in the Google Ads Scripts for your reference.

Let me know if you have questions or other concerns.


Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2EoApm:ref

[Sigurd Fabrin]

Hi Carter,

Use javascript's Date() as Harry mentioned above. There are a few quirks e.g. month 0 is January, so december is  month 11.

Here's a good resource https://www.w3schools.com/jsref/jsref_obj_date.asp 

I like to have a separate function for creating the dates that I use throughout a script.

Here's an example of how it can be done. 

This script will export a report with a custom date range to a Google sheet

var settings = {  

  url: 'https://docs.google.com/spreadsheets/etc',

  sheet:'SheetName',

  start:90, // Number of days. Start date is this number of days before end date

  end:1 // number of days ago from today. 0 = today, 1 = yesterday etc

}

function main() {

  var sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet);

  var dates = getDates(settings.start,settings.end);

  var report = AdsApp.report(

    'SELECT ' +

    'Date,Impressions,Clicks ' +

    'FROM ACCOUNT_PERFORMANCE_REPORT ' +

    'DURING ' + dates.report

  )

  report.exportToSheet(sheet); Btw: no need to clear sheet for old data first - this function will do it for you

  Logger.log('Data from '+dates.prettyStart+' until '+dates.prettyEnd+', exported to spreadsheet\n\n'+settings.url);

}

function getDates(start,end) {

  var format = 'yyyyMMdd';

  var prettyFormat = 'MMM dd yyyy';

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var today = new Date();

  var oneDay = 1000*60*60*24; // milliseconds in a day

  var endDate = new Date(today - (oneDay * end));

  var formatEndDate = Utilities.formatDate(endDate,timeZone,format);

  var prettyEndDate = Utilities.formatDate(endDate,timeZone,prettyFormat);

  var startDate = new Date(endDate - ((oneDay * start)-oneDay));

  var formatStartDate = Utilities.formatDate(startDate,timeZone,format);

  var prettyStartDate = Utilities.formatDate(startDate,timeZone,prettyFormat);

   return {report:formatStartDate+','+formatEndDate,prettyStart:prettyStartDate,prettyEnd:prettyEndDate}

}

 Sigurd

[Carter Knight]

Thank you both! I will try these methods out.