script to export item ids to sheets

[Sebastian]

hello all,

i am looking for a way to export costs and sales (ROAS if applicable) from google ads to a google sheet on an item id basis.

has anyone here made experience with this? could someone help me?

would be very pleased

best

sebastian

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for reaching out. Harry here, from the Google Ads Scripts Team.

Can you kindly provide more context to the item you are referring to? Are they items/products stored in your Merchant Center? If yes, then you can pull products data directly from there using the Shopping Content Service. However, I believe a simpler approach here is retrieving Reports through Google Ads scripts. Please see Product Partition Report (AdWords API) and product_group_view (Google Ads API) which you can pull. To export reports into a Google Sheet, you can refer to this example.

Let me know your thoughts and we can proceed from there.

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

 

ref:_00D1U1174p._5004Q2OSfGY:ref

[Sebastian Zehentner]

Hi Harry,

thx you your reply.

Specifically, i want to pull the individual product ids with their costs and sales into a google spreadsheet from the google shopping account. then i want to make roas buckets from them.

the solution via the script would be good since i could then automate this process.

would you have a solution here?

the Ads report via Articel ID is known to me, but as far as I know it always requires a manual adjustment.

best

sebastian

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for coming back. If you want to achieve this through Google Ads Script then I would recommend retrieving either the Product Partition Report (AdWords API) and product_group_view (Google Ads API) as you require metrics for each product. However, kindly take note that ROAS or Target ROAS fields from the mentioned API reports is not offered. Moreover, I believe you would not be able to retrieve product metrics through the Shopping Content Service that could let you retrieve products data resource from your Merchant Center account. If you would like to proceed with creating the report through Google Ads scripts then you can take a look at the following references to get started.

• Reports guide
• AdsApp.​Report reference doc
• Reports examples

Hope this helps. Let me know if you need anything else.

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for getting back to us. Can you kindly try to convert metric values to string via the toString method before appending them to your spreadsheet? If the issue still persists, kindly try changing the format of the columns from your sheet to plain text. Highlight the columns/cells and navigate to Format > Number and select Plain Text then re-run your script and let me know how it goes.

[Sebastian]

hello, thank you for the quick feedback.

i must say that unfortunately i am an absolute beginner when it comes to scripts. i have now formatted the column, but it does not work. 

would it be possible if you could tell me where exactly i have to use this to.string method in my script (see above)?

best thanks for your help

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Can you try the toString method with the val variable that's somewhere around line 109? On the other hand, if you don't add other fields to your spreadsheet apart from the fields you retrieve from the report then you can use the exportToSheet method to automatically export the report results to your spreadsheet instead of writing a script function yourself to append values to a spreadsheet.

Let me know how it goes or if you have questions.

[Sebastian]

hello 

thank you for your feedback.

as I said I am an absolute beginner, would it be possible here to give me a template to the just proposed solution?

I am currently struggling with the terminology and would like someone could give me a template.

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

I do understand that you are not versed with Google Ads scripts so let me make this is as simple as I can for you. However, can you kindly confirm if you do add more fields to your spreadsheet other than what you retrieve from the report? If not, then you can use the simple script below to export the report to your spreadsheet instead which I based from your script.

 var CONFIG = {
  'attributes': ['OfferId'],
  'segments': ['Month'],
  'metrics': ['Cost', 'ConversionValue'],
  'sourceReport': 'SHOPPING_PERFORMANCE_REPORT',
  'dateRange': 'LAST_7_DAYS',
  'spreadsheetUrl': 'mysheet',
  'tabName': 'Campaigns',
  'reportVersion': 'v201809',
  'overWrite': 1,
};

function main() {
  var fields = [].concat(CONFIG['attributes'], CONFIG['segments'], CONFIG['metrics']);
  var query = "SELECT " + fields.join(',') + " FROM " + CONFIG['sourceReport'] + " DURING " + CONFIG['dateRange'];
  var report = AdsApp.report(query);
  if(CONFIG['overWrite']) {
    var spreadsheet = SpreadsheetApp.openByUrl(CONFIG['spreadsheetUrl']);
    var sheet = spreadsheet.getSheetByName(CONFIG['tabName']);
    report.exportToSheet(spreadsheet.getActiveSheet());
  } else {
    var rows = report.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      sheet.appendRow(row);
    }
  }
}

Let me know your thoughts.

[Sebastian]

Thx you alot. This worked out great for me.

Best

[Sebastian]

hello all,

i have another question. in april 2022 there will be a changeover to google ads api and then the report will change from SHOPPING_PERFORMANCE_REPORT to shopping_performance_view.

Would the script still be usable then? I have seen that in the new report the field "cost" is not available at all.

Would you have a possibility to adapt the report to the new format so that I can still use it after april 2022?

that would be great and i would be very grateful

best

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for coming back. Unfortunately, I would not be able to provide you guidance on the changes that will happen with Google Ads scripts for the meantime. You may expect that new updates and releases will be written in our blog so kindly be on the lookout. What I can give for now is that the team over at Google Ads API have made the following tools and references for you to make transitioning from AdWords API easier. I would also recommend that you migrate your scripts to use GAQL reports as soon as possible.

• Query Migration Tool
• Query Validator
• Query Cookbook
• Google Ads Query Builder
• AdWords API reports to Google Ads API resources

Let me know if there's anything else I can assist you with.

[Sebastian]

Hi, thx for the help.

I got one more question. i am currently trying to add a filter to the existing script.

I want to filter out only costs from 5 euro.

'withCondition("Cost > 4.48")

but it does not work in the script. could u assistant me on that? This is my script: 

 var CONFIG = {

  'attributes': ['OfferId'],

  'segments': [],

  'metrics': ['Cost', 'ConversionValue'],  

  'sourceReport': 'SHOPPING_PERFORMANCE_REPORT',

  'dateRange': 'LAST_14_DAYS',

  'spreadsheetUrl': 'XXXXX',

  'tabName': 'NonPerformer',

  'reportVersion': 'v201809',

};

function main() {

  var fields = [].concat(CONFIG['attributes'], CONFIG['segments'], CONFIG['metrics']);

  var query = "SELECT " + fields.join(',') + " FROM " + CONFIG['sourceReport'] + " DURING " + CONFIG['dateRange'];

  var report = AdsApp.report(query);

  if(CONFIG['overWrite']) {

    var spreadsheet = SpreadsheetApp.openByUrl(CONFIG['spreadsheetUrl']);

    var sheet = spreadsheet.getSheetByName(CONFIG['tabName']);

    report.exportToSheet(spreadsheet.getActiveSheet());

  } else {

    var rows = report.rows();

    while (rows.hasNext()) {

      var row = rows.next();

    }

  }

}

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

I work along with Harry. Allow me to assist you in this.

With regard to your question, you will need to update your query and add a WHERE condition. Kindly update your query from:

var query = "SELECT " + fields.join(',') + " FROM " + CONFIG['sourceReport'] + " DURING " + CONFIG['dateRange'];

To:

var query = "SELECT " + fields.join(',') + " FROM " + CONFIG['sourceReport'] + " WHERE Cost > 4.48 DURING " + CONFIG['dateRange'];

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

 

ref:_00D1U1174p._5004Q2OSfGY:ref

[Sebastian]

Hi Teejay,

thx for reply.

I have inserted the formula but added a " before "during".

The script runs successfully, but the values are not transferred to the spreadsheet, it remains empty.

I use the script also without the cost filter, here it runs through without problems and the data are taken over in the spreadsheet.

What can be the reason for this?

thanks for your help

var CONFIG = {

  'attributes': ['OfferId'],

  'segments': [],

  'metrics': ['Cost', 'ConversionValue'],  

  'sourceReport': 'SHOPPING_PERFORMANCE_REPORT',

  'dateRange': 'LAST_14_DAYS',

  'spreadsheetUrl': 'https://docs.google.com/spreadsheets/xxxx',

  'tabName': '1',

  'reportVersion': 'v201809',

};

function main() {

  var fields = [].concat(CONFIG['attributes'], CONFIG['segments'], CONFIG['metrics']);

  var query = "SELECT " + fields.join(',') + " FROM " + CONFIG['sourceReport'] + " WHERE Cost > 18 " + " DURING " + CONFIG['dateRange'];

  var report = AdsApp.report(query);

  if(CONFIG['overWrite']) {

    var spreadsheet = SpreadsheetApp.openByUrl(CONFIG['spreadsheetUrl']);

    var sheet = spreadsheet.getSheetByName(CONFIG['tabName']);

    report.exportToSheet(spreadsheet.getActiveSheet());

  } else {

    var rows = report.rows();

    while (rows.hasNext()) {

      var row = rows.next();

    }

  }

}

[Zehua Shan]

Hi All, I have a similar question to this:
While using AdsApp.report to pull report with a query that selects all items that have 0 impressions in the last 30 days, the query returns much less results than the native reporting tool in Google Ads UI. For comparison, the query returns 61 results while the native reporting tool in Google Ads UI returns 2654 results. Any idea why this is the case? 

Script follows:

function main() {

  var spreadsheet = SpreadsheetApp.openByUrl(*scripturl*);

  var sheetName = *sheetname*;

var report = AdsApp.report(

    "SELECT OfferId, Impressions " +

    "FROM   SHOPPING_PERFORMANCE_REPORT " +

    "WHERE  Impressions = 0 " +

    "DURING LAST_30_DAYS");

    report.exportToSheet(spreadsheet.getSheetByName(sheetName));

   

} 

[Sebastian]

I have now tried it with the script from, now all results are sent to me in the spreadsheet.

but the costs are not filtered correctly. No matter which number I put in, numbers/costs are also displayed below the filter. With the sales, on the other hand, it is not a problem. Here it is filtered correctly.

What could be the reason for this?

function main() {

  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/xxx);

  var sheetName = 'Auto';

var report = AdsApp.report(

    "SELECT OfferId, Cost, ConversionValue " +

    "FROM   SHOPPING_PERFORMANCE_REPORT " +

    "WHERE  Cost > 55.00 AND ConversionValue < 15.00" +

    "DURING LAST_7_DAYS");

    report.exportToSheet(spreadsheet.getSheetByName(sheetName));

   

} 

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for coming back. Kindly provide your Google Ads account ID / CID, the name of the script you are having issues with and a shareable link to the template spreadsheet you are using so I can a closer look at it and guide you accordingly. You may send them here or privately via the reply to author option. If this option is not available at your end, you may send it through our email (googleadsscr...@google.com) instead.

@sh...@bluebirdmedia.se: I would assume that you are also expecting of Smart Shopping Campaigns from the report but this is not currently not supported; hence, why you are not able to see the same report from the script compared to the report in the UI. Kindly check this angle and let me know how it goes but things does not check out still, please provide the details I am requesting from Sebastian as well.

Looking forward to your replies.

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2OSfGY:ref

[Zehua Shan]

Hi Harry, thanks for you response
No I was not expecting to have the distinction of smart shopping campaigns in the query result, but good to know!
After checking reports in the native report tools in Google Ads UI with filter that only shows a standard shopping campaign, it still has many more results (2728 results compared to 61 from the report).
I have sent you an email with client ID and sheet template.

Thanks!

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for providing the requested details. You would have to compare the Cost field with a value in micros to filter out data lower than the threshold which is 11. You can use the script below:

function main() {
  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/xxx

);
  var sheetName = 'Auto';
  
  var costMultiplier = 11 * 1000000;
  var report = AdsApp.report(
    "SELECT OfferId, Cost, ConversionValue " +
    "FROM   SHOPPING_PERFORMANCE_REPORT " +
    "WHERE  Cost > " + costMultiplier + " " +
    "DURING LAST_7_DAYS");
  report.exportToSheet(spreadsheet.getSheetByName(sheetName));
} 

@sh...@bluebirdmedia.se: Thank you as well but could you instead provide me a screenshot of the Google Ad UI - Report you are comparing the script report results with? Kindly send them privately through the options I have mentioned previously. I should be able to pinpoint the discrepancies and guide you accordingly thereafter.

[Zehua Shan]

Just sent another email with printscreen!

[Google Ads Scripts Forum Advisor]

Hi sh...@bluebirdmedia.se,

Thanks for your cooperation. Allow me to reach out to the rest of the team for further investigation on the issue you are having and get back to you once I have more information. Please do let me know if there's anything else I can assist you with in the meantime.

@Sebastian: Let me know if you need more help.

[Sebastian]

hello all,

it worked. thank you very much, you are the best.

is there an explanation why you only need this multiplier for the costs but not for the con. value?

  var costMultiplier = 15 * 1000000;

  var report = AdsApp.report(

    "SELECT OfferId, Cost, ConversionValue " +

    "FROM   SHOPPING_PERFORMANCE_REPORT " +

    "WHERE  Cost > " + costMultiplier + " AND ConversionValue < 20 " +

    "DURING LAST_7_DAYS");

  report.exportToSheet(spreadsheet.getSheetByName(sheetName));

have a nice weekend.

[Google Ads Scripts Forum Advisor]

Hi Sebastian,

Thanks for coming back. It is because of the format of Cost field values which is in micros so you would have to either convert them by multiplying it with 1000000 or use optional argument - returnMoneyInMicros in the report method like the below code:

 var report2 = AdsApp.report(
     'SELECT AdGroupId, Id, KeywordText, Impressions, Clicks ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
     'DURING 20130101,20130301', {
       includeZeroImpressions: false,
       returnMoneyInMicros: true,
       apiVersion: 'v201809'
 });

[Zehua Shan]

Hi!
Any updates?

[Google Ads Scripts Forum Advisor]

Hi Zehua Shan,

Thank you for following up. As per checking, I'm afraid that this is still under investigation. We'll let you know the soonest we have our findings.

Regards,

Teejay Wennie Pimentel Google Ads Scripts Team

ref:_00D1U1174p._5004Q2OSfGY:ref

[Growth MKTG Browze]

Hello,

Is it possible to pull the last 90 days data using this script.

I've run into a few errors when the daterange is exceeding Last 30 Days.

Please confirm,

Thanks in advance

[Google Ads Scripts Forum Advisor]

Hello, 

I’m James, also a member of the Google Ads Scripts support team. Thank you for looping in.

Yes, you can achieve it by using the forDateRange method to filter the specific entity that you’re trying to retrieve. You may refer here for more information and sample snippets.

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2OSfGY:ref

[Growth MKTG Browze]

Hi,

Thanks for getting back to me.
I tried but got the following error message "The daterange is not valid. Please check spelling and casing. Valid values: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH"

Can you please confirm why this wouldn't work?

Thanks in advance!

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/wfd0i000000000000000000000000000000000000000000000R83UNG00IoeSjOpCQxGMZVY5Toj0WQ%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hello,

Can you please share with us the following details below via `Reply privately to author` options so that we can further check?

• Name of the script on where you encountered the error
• Screenshot of the issue.

[Growth MKTG Browze]

Hi James,
The "Reply privately to author" is not available for me for some reason, as it's greyed out.

The name of the script is "Item IDs YTD"

And here is the SS of the issue.

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/Bpyn_000000000000000000000000000000000000000000000R8VN5A00he7brc8MSmGCRU-_4ji9Hw%40sfdc.net.

[Google Ads Scripts Forum Advisor]

Hello, 

Since the private option is not available on your end, then please send the requested information through this email <googleadsscr...@google.com> instead.

Regards,