script to export item ids to sheets

124 views
Skip to first unread message

Sebastian

unread,
Sep 21, 2021, 2:26:03 AMSep 21
to Google Ads Scripts Forum
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

unread,
Sep 21, 2021, 5:24:08 AMSep 21
to adwords...@googlegroups.com
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,
Google Logo
Harry Cliford Rivera
Google Ads Scripts Team
 

 

ref:_00D1U1174p._5004Q2OSfGY:ref

Sebastian Zehentner

unread,
Sep 21, 2021, 9:13:21 AMSep 21
to Google Ads Scripts Forum
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

unread,
Sep 21, 2021, 11:50:27 PMSep 21
to adwords...@googlegroups.com
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. Hope this helps. Let me know if you need anything else.
Message has been deleted

Google Ads Scripts Forum Advisor

unread,
Sep 29, 2021, 12:45:12 AMSep 29
to adwords...@googlegroups.com
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

unread,
Sep 29, 2021, 2:11:01 AMSep 29
to Google Ads Scripts Forum
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

unread,
Sep 29, 2021, 6:03:14 AMSep 29
to adwords...@googlegroups.com
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

unread,
Sep 29, 2021, 7:16:49 AMSep 29
to Google Ads Scripts Forum
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

unread,
Sep 29, 2021, 11:00:17 PMSep 29
to adwords...@googlegroups.com
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

unread,
Sep 30, 2021, 7:33:32 AMSep 30
to Google Ads Scripts Forum
Thx you alot. This worked out great for me.

Best

Sebastian

unread,
Oct 12, 2021, 3:22:47 AM (13 days ago) Oct 12
to Google Ads Scripts Forum
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

unread,
Oct 12, 2021, 6:22:17 AM (13 days ago) Oct 12
to adwords...@googlegroups.com
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. Let me know if there's anything else I can assist you with.

Sebastian

unread,
Oct 19, 2021, 2:42:07 AM (6 days ago) Oct 19
to Google Ads Scripts Forum
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

unread,
Oct 20, 2021, 12:30:20 AM (6 days ago) Oct 20
to adwords...@googlegroups.com
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,
Google Logo
Teejay Wennie Pimentel
Google Ads Scripts Team
 


 

ref:_00D1U1174p._5004Q2OSfGY:ref

Sebastian

unread,
Oct 20, 2021, 3:23:57 AM (5 days ago) Oct 20
to Google Ads Scripts Forum
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',
  '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

unread,
Oct 20, 2021, 5:27:56 AM (5 days ago) Oct 20
to Google Ads Scripts Forum
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

unread,
Oct 20, 2021, 5:58:12 AM (5 days ago) Oct 20
to Google Ads Scripts Forum

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

unread,
Oct 20, 2021, 6:29:44 AM (5 days ago) Oct 20
to adwords...@googlegroups.com
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,
Google Logo
Harry Cliford Rivera
Google Ads Scripts Team
 


ref:_00D1U1174p._5004Q2OSfGY:ref

Zehua Shan

unread,
Oct 20, 2021, 6:54:23 AM (5 days ago) Oct 20
to Google Ads Scripts Forum
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

unread,
Oct 21, 2021, 3:29:54 AM (4 days ago) Oct 21
to adwords...@googlegroups.com
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

unread,
Oct 21, 2021, 4:40:53 AM (4 days ago) Oct 21
to Google Ads Scripts Forum
Just sent another email with printscreen!

Google Ads Scripts Forum Advisor

unread,
Oct 22, 2021, 2:36:27 AM (4 days ago) Oct 22
to adwords...@googlegroups.com
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

unread,
Oct 22, 2021, 7:06:36 AM (3 days ago) Oct 22
to Google Ads Scripts Forum
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

unread,
Oct 24, 2021, 10:47:09 PM (yesterday) Oct 24
to adwords...@googlegroups.com
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'
 });
Reply all
Reply to author
Forward
0 new messages