custom date range

[Zachary CANNON]

hi,

I am trying to use a custom date range in the new interface, but the syntax isn't working. I'm referring to old adwords APi documentation as there isn't anything on date range for the new query type. Can you please advise?

  // get date
  let MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  let now = new Date();
  let from = new Date(now.getTime() - 8 * MILLIS_PER_DAY);
  let to = new Date(now.getTime() - 2 * MILLIS_PER_DAY);
  let timeZone = AdsApp.currentAccount().getTimeZone();
 
  let fromDate = Utilities.formatDate(from, timeZone, 'yyyy-MM-dd')
  let toDate = Utilities.formatDate(to, timeZone, 'yyyy-MM-dd');
 
  let query = 'SELECT ' +
      'metrics.clicks, metrics.search_impression_share, metrics.impressions, segments.product_type_l3, segments.date, metrics.cost_micros, metrics.average_cpc ' +
      'FROM shopping_performance_view ' +
      'WHERE metrics.impressions > 0 '+  
      'AND segments.date BETWEEN ' +  Utilities.formatDate(from, timeZone, 'yyyy-MM-dd') + 'AND' Utilities.formatDate(to, timeZone, 'yyyy-MM-dd');

[Google Ads Scripts Forum Advisor]

Hello,

 

I’m James from the Google Ads Scripts support team. Thank you for reaching out to us. Allow me to assist you.

 

Could you please shared with us with the following information below so that we can take a closer look at your issue?

• Google Ads account ID / CID
• Script name
• Screenshots that shows the issue 
• Shareable link of the involved spreadsheet, if applicable.

 

Kindly send the requested details above via ‘Reply privately to author’ option, if the private option is not available on your end, then please send it over through this email <googleadsscr...@google.com> instead.

 

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2Wwfbj:ref

[Sigurd Fabrin]

Hi Zachary,

This wouldn't work in the old experience either. Use this tool to help build queries in GAQL https://developers.google.com/google-ads/api/fields/v7/shopping_performance_view_query_builder

Check your white spaces, make sure to bond strings and variables together in a way js understands and enclose your dates in double quotes

Compare this line to your version: 

'AND segments.date BETWEEN "' +  Utilities.formatDate(from, timeZone, 'yyyy-MM-dd') + '" AND "' + Utilities.formatDate(to, timeZone, 'yyyy-MM-dd') + '"';

 Sigurd

[Zachary CANNON]

Thanks!

[Google Ads Scripts Forum Advisor]

Hello Zachary,

 

I agree with Sigurd that you may use this Google Ads Query Builder tool to properly set up your queries on your end.

 

I tried to replicate the given query on my end, and it appears that you’re lacking a closing single quote(‘) within double quote(“ ") in the end of your query. You may also try the my test query below if it works on your end.

"SELECT metrics.clicks, metrics.search_absolute_top_impression_share, metrics.impressions, segments.product_type_l3, segments.date, metrics.cost_micros, metrics.average_cpc FROM shopping_performance_view WHERE metrics.impressions > 0 AND segments.date BETWEEN '" + Utilities.formatDate(from, timeZone, 'yyyy-MM-dd') + "' AND '" + Utilities.formatDate(to, timeZone, 'yyyy-MM-dd') + "'";

 

Hope this helps. Let me know if you have any further questions.