Export Campaign Performance Data of MCC

[Katrin Munich]

Hi guys! 

I'm looking to export the Campaign Performance Data of an MCC Account. I Have the following script where I tried to specify the steps and the columns I'd like to export and unfortunately it doesn't work at all. 

I would really appreciate it if somebody could have a look at it and help me find a solution .. 

function main() {

    var accountSelector = AdsManagerApp.accounts()

    var accountIterator = accountSelector.get();

    var spreadsheet = SpreadsheetApp.openByUrl('My Spreadsheet URL');

    var sheet = spreadsheet.getSheetByName('Raw Data');

    var range = sheet.getRange("A1:I10000");

   range.clearContent();

  

   var header = [

    'AccountDescriptiveName',

    'CampaignName',

    'Cost',

    'Conversions',

    'Date',

    'Impressions',

    'Clicks',

    'Labels',

     

     ];

     var data = [header];

      

     var report = AdsApp.report(

    'SELECT AccountDescriptiveName, CampaignName, Conversions, Date, Impressions, Clicks, Cost, Labels ' +

    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +

    ' WHERE Cost > 0 ' +

    'DURING 20200426, 20201231 '

    );

      

    var rows = report.rows(); 

  

    while (rows.hasNext()) {

    var row = rows.next(); 

    var AccountDescriptiveName = row.AccountDescriptiveName;

    var CampaignName = row.CampaignName;

    var Cost = row.Cost;

    var Conversions = row.Conversions; 

    var Date = row.Date;

    var Impressions = row.Impressions;

    var Clicks = row.Clicks;

    var Labels = row.Labels

    var newRow = [AccountDescriptiveName,CampaignName,Cost,Conversions,Date,Impressions,Clicks, Labels]; 

    data.push(newRow);

  }

  

  sheet.getRange(1, 1, data.length, 8).setValues(data);

}

Kind regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

The script defines an account iterator, but the account iterator is never iterated over, nor is AdsManagerApp.select used. Please see this guide on working AdsManager scripts.

Since you are running this at the account level, presumably you'll want to output this report to different sheets. If the script only points to one sheet, it will overwrite the sheet with a new report for each account.

Lastly, I would recommend writing data with the AdsApp exportToSheet method instead of using the SpreadsheetApp setValues method. The exportToSheet method will include headers.

Regards,
Matt
Google Ads Scripts Team

ref:_00D1U1174p._5004Q21lZxq:ref

[Katrin Munich]

Hi Matt, 

thanks for the pointers, much appreciated! I'm only starting with Java Script so every input is helpful. 

I've had a look at the links you sent and created this new script, which works perfectly, but creates a new spreadsheet per account, which is unfortunately not what I need. 

I'm really stuck adjusting the spreadsheet part so that the data of every sub-account is pasted into a different tab of one spreadsheet, or even if possible, into one tab. I would like to schedule the script so it updates daily into the same spreadsheet and I can use it as an overall budget control for those sub-accounts. 

New Script:

function main() {

var accountSelector = AdsManagerApp.accounts()

.withCondition("Impressions > 0")

.forDateRange("20200101", "20201231");

accountSelector.executeInParallel("generateReports");

}

function generateReports() {

var report = AdsApp.report(

    'SELECT AccountDescriptiveName, CampaignName, Conversions, Date, Impressions, Clicks, Cost, Labels ' +

    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +

    ' WHERE Cost > 0 ' +

    'DURING 20200101, 20201231 '

    );

var spreadsheet = SpreadsheetApp.create("Report output");

   

   report.exportToSheet(spreadsheet.getActiveSheet());

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

  }

Thanks :) Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

I would recommend creating the spreadsheet before the parallel execution call to avoid creating a new spreadsheet for each new account. In the generateReports function, you could use the insertSheet Spreadsheet method to create a new tab for each account. Please see the changes in bold in the snippet below:
 

var spreadsheet = SpreadsheetApp.create("Report output");

function main() {
  var accountSelector = AdsManagerApp.accounts()
  .withCondition("Impressions > 0")
  .forDateRange("20200101", "20201231");

  accountSelector.executeInParallel("generateReports");
}

function generateReports() {
  var accountId = AdsApp.currentAccount().getCustomerId();
  spreadsheet.insertSheet(accountId); 
  var report = AdsApp.report(
    'SELECT AccountDescriptiveName, CampaignName, Conversions, Date, Impressions, Clicks, Cost, Labels ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    ' WHERE Cost > 0 ' +
    'DURING 20200101, 20201231 '
    );
       
  report.exportToSheet(spreadsheet.getActiveSheet());
  Logger.log("Report available at " + spreadsheet.getUrl());
}

[Katrin Munich]

Hi Matt, 

Thanks for the suggestion and the updated code! Unfortunately it still created one Spreadsheet per Account. I then changed var spreadsheet = SpreadsheetApp.create("Report output"); to var spreadsheet = SpreadsheetApp.openByUrl('My URL'); and this worked. I got one Tab for each account with the Account number as the name of the tab. Triggering it again has not created additional tabs but it seems to overwrite the existing account tabs, which is exaclty what I need. I scheduled it to daily and will check again tomorrow morning if the sheet keeps updating with new data :) 

Thank you so much! 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

Thank you for sharing the workaround you performed in order to fix the script and to meet your requirement.

Let us know if you encounter any issues so that our team could check.

Regards,
Ejay

[Katrin Munich]

Hi Ejay, 

Thanks for your message, I am unfortunately encountering a problem with the script. It doesn't update with the current performance data. Is there a way to adjust the script so that it adds the new performance data to the tabs? I think the problem might be the function spreadsheet.insertSheet, but I don't know what I could use instead so the sheet wouldn't be inserted but updated. 

Best regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

Could you confirm if you encountered the error below? If yes, then you are correct that the error is related to 'spreadsheet.insertSheet' because it is creating a sheet that is existing already.
'A sheet with the name ‘*AccoundId*’ already exists. Please enter another name.'

With this, I would recommend inserting the code below as it will check first whether the sheet is existing or not. Then, the script will or will not create a new sheet based on the condition that is satisfied.
  var sheet;
  
  if (!spreadsheet.getSheetByName(accountId)){
    sheet = spreadsheet.insertSheet(accountId);
  } else {
    sheet = spreadsheet.getSheetByName(accountId); 

  }    
  
  var report = AdsApp.report(
    'SELECT AccountDescriptiveName, CampaignName, Conversions, Date, Impressions, Clicks, Cost, Labels ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    ' WHERE Cost > 0 ' +
    'DURING 20200101, 20201231 '
    );
  

  report.exportToSheet(sheet);

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

Let me know if you encounter any issues with the provided suggestion.

[Katrin Munich]

Hi Ejay, 

Yes, that is the exact error message I got. I implemented your suggestion and triggered the script again, but for some reason it wiped most of the tabs clean. I have started over with a new sheet, will try again tomorrow and see if the same thing happens again or if it inserts the data for the new day.

I'll let you know how it goes! 

Thanks for your help! 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

The script will just create a new sheet or fill out the existing sheets, but there is no method for removing sheets.

With this, you can confirm on your end if those sheets have been deleted manually.

Also, let me know if the script will encounter any issues after trying the updated script tomorrow.

[Katrin Munich]

Hi Ejay, 

Unfortunately, I've run into some difficulty. When I trigger the script once, it does exactly what it should, which is great. But when I trigger it again, there is always some issue with the first tab. At first, I had an additional tab in the sheet named "overview" and the script kept overwriting it with random account data. Moving the additional sheet all the way to the right so it would be the last sheet to get processed by the script helped. Upon another try, the first tab gets filled with the data from another account, and not the one that should be in there. Example: First run: 

tab1 gets created, name of sheet = account number, data from account 1 is inserted, tab2 gets created, name of sheet = account number, data from account 2 is inserted, and so on. Second run: tab1 already exists and name of sheet = account number, data from account 5 is inserted , tab2 already exists, data from account 2 is inserted. Also I get the error message for multiple accounts: "This action would increase the number of cells in the workbook above the limit of 5000000 cells."

What could be the issue there? :/ 

Regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

Thanks for the reply.

To investigate the issue, could you provide the customer ID and the name of the script where the code is implemented via Reply privately to author option? You can provide also an access to the spreadsheet that you are using in the script.

Furthermore, to help investigating the issue, could you provide an actual scenario for the issue where specific sheet gets filled with the data from another account?

[Katrin Munich]

Hi Ejay & Matt, 

thank you so much for helping me out with this script, it works perfectly now. I'm going to share it here in case someone else needs it: 

This Script, applied on MCC Level, will create one tab per account in the defined spreadsheet and insert the report. The report data will update when the script is triggered again.

var spreadsheet = SpreadsheetApp.openByUrl('Insert Spreadsheet URL here');

function main() {

  var accountSelector = AdsManagerApp.accounts()

  .withCondition("Impressions > 0")

  .forDateRange("20200101", "20201231")

  .get();

  

  while(accountSelector.hasNext()) {

    var account = accountSelector.next();

    AdsManagerApp.select(account);

    generateReports();

  }

}

function generateReports() {

  var accountId = AdsApp.currentAccount().getCustomerId();

  var sheet;

  if (!spreadsheet.getSheetByName(accountId)){

    sheet = spreadsheet.insertSheet(accountId);

  } else {

    sheet = spreadsheet.getSheetByName(accountId); 

  }    

  

  var report = AdsApp.report(

    'SELECT AccountDescriptiveName, CampaignName, Conversions, Date, Impressions, Clicks, Cost, Labels ' +

    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +

    ' WHERE Cost > 0 ' +

    'DURING 20200101, 20201231 '

    );

       

    report.exportToSheet(sheet);

Have a great day!

Regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

I am glad to know that our team helped you to address your concern.

Feel free to post your concerns on this forum and our team will be happy to provide support.