Re: MCC Script to export all Campaign Data in all Child Accounts to 1 Google Sheet

[Adrian Catambay (AdWords Scripts Team)]

Hello Nguyen,

Allow me to re-post your original question as it contains private information relevant to your account. In the future, please refrain from posting private information (e.g. spreadsheet URL) in the public forum.

Reposting your question with masked private information:

Hi,

I work for agency who manage multiple account in a MCC. I have been using the script below to export campaign-level data to a google sheet, then combine with Google Data Studio to create a template report for our client.

function main() {

  var SPREADSHEET_URL = "****";

  var account = AdWordsApp.currentAccount();

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  

  var report = AdWordsApp.report(

    'SELECT CampaignName, AdNetworkType1, Labels, Date ,Impressions, Clicks , Conversions, Cost FROM CAMPAIGN_PERFORMANCE_REPORT ' +

    'WHERE  Impressions > 0 '+ 'DURING LAST_30_DAYS',

    {

    apiVersion: 'v201710'

    });

  report.exportToSheet(spreadsheet.getActiveSheet());

  Logger.log("Exporting successful");

}

The scripts work fine but the problem is that I have to create & run the script for every child account in MCC account, and the data from each account will be exported to different Google spreadsheet.

Would there be a script that run in MCC-level account that can export all campaign data (maybe with filtering impressions>0) from all account into single sheet only. I has been searching on the internet but cannot find the solution.

Many Thanks,

Thanks,

Adrian

AdWords Scripts Team

[Adrian Catambay (AdWords Scripts Team)]

Hello Nguyen,

To convert this script to run on an MCC account, you can use the MccApp to generate a report for every child account under your MCC account. I would also suggest that you have each child account's report separated in sheets. You may refer to the sample code below:

function main() {
    var accountIterator = MccApp.accounts().get(); // selects all child accounts under this MCC account
    var SPREADSHEET_URL = "YOUR_SPREADSHEET_URL_HERE";
    // var account = AdWordsApp.currentAccount(); // This line is not necessary as you are already using MccApp to select the current account
    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

    var accountCounter = 0;
    while (accountIterator.hasNext()) {
        var account = accountIterator.next();

        MccApp.select(account); // selects the current account to generate report from

        var report = AdWordsApp.report(
            'SELECT CampaignName, AdNetworkType1, Labels, Date ,Impressions, Clicks , Conversions, Cost FROM CAMPAIGN_PERFORMANCE_REPORT ' +
            'WHERE  Impressions > 0 ' + 'DURING LAST_30_DAYS', {
                apiVersion: 'v201710'
            });



        if (accountCounter == 0) { // if first account, use the default active sheet
            spreadsheet.renameActiveSheet(account.getName()) // renames the default active sheet to the child account's name (You may rename sheets based on your requirement)
            report.exportToSheet(spreadsheet.getActiveSheet());
        } else {
            report.exportToSheet(spreadsheet.insertSheet(account.getName())); // adds a new sheet with the account's name as sheet name (You may rename sheets based on your requirement)
        }

        Logger.log(account.getName() + ": Exporting successful");

        accountCounter++; // increment counter
    }
}

The suggested sample script above will generate a report for every child account under your MCC account and have those reports separated in sheets per account. All these reports will be contained in one spreadsheet file which is the spreadsheet URL you specified.

For additional reference on how to process child accounts under your MCC account, you may check these sample MCC Scripts.

Let me know if the suggested sample script above works for your requirement.

[Nguyên Huỳnh]

Hi Adrian,

Thanks so much for your prompt supporting!

I know that separating each account would make the report clean. But I'm creating this spreadsheet to connect to Google Data Studio, and I need all of my child account data be in only on sheet only. Then I can connect it to Data Studio and make a filter on Account Name on the report.

Would there be a solution to put all the data into only 1 sheet only? The table will have one more column displaying ACCOUNT NAME like this:

Many Thanks,

[Adrian Catambay (AdWords Scripts Team)]

Hello Nguyen,

Yes, your use-case is possible.

If you want the Campaign Performance reports of your child accounts combined in only one sheet, then I would suggest the sample code below: 

function main() {
    var accountIterator = MccApp.accounts().get(); // selects all child accounts under this MCC account
    var SPREADSHEET_URL = "YOUR_SPREADSHEET_URL_HERE";
    // var account = AdWordsApp.currentAccount(); // This line is not necessary as you are already using MccApp to select the current account
    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

    var sheet = spreadsheet.getActiveSheet(); // gets the default active sheet

    while (accountIterator.hasNext()) {
        var account = accountIterator.next();

        MccApp.select(account); // selects the current account to generate report from

        var report = AdWordsApp.report(

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

            'WHERE  Impressions > 0 ' + 'DURING LAST_30_DAYS', {
                apiVersion: 'v201710'

            }); // adds the field AccountDescriptiveName in your query to get child account name

        var rows = report.rows();
        while (rows.hasNext()) {
            var row = rows.next();

            sheet.appendRow([row['AccountDescriptiveName'], 
                             row['CampaignName']], 
                            row['AdNetworkType1'], 
                            row['Labels'], 
                            row['Date'], 
                            row['Impressions'], 
                            row['Clicks'], 
                            row['Conversions'], 
                            row['Cost']); // append rows from current account's generated Campaign Performance Report
        }
    }
}

You may try the sample code above by using the Preview button.

Let me know if the suggested sample code meets your requirement.

[Nguyên Huỳnh]

Hi Adrian,

Thanks for supporting. I've tried but the script seem not working with below error noti;

Cannot find method appendRow(object,string,string,string,string,string,string,string). (line 24)

Regards,

[Adrian Catambay (AdWords Scripts Team)]

Hello Nguyen,

Apologies, there seems to be a typo in the sample code I provided. Please update the appendRow() function at line 24 to the following code snippet below:

sheet.appendRow([row['AccountDescriptiveName'],
                             row['CampaignName'],
                            row['AdNetworkType1'],
                            row['Labels'],
                            row['Date'],
                            row['Impressions'],
                            row['Clicks'],
                            row['Conversions'],
                            row['Cost']]); // append rows from current account's generated Campaign Performance Report

Let me know if updating the said line with the code snippet above has resolve the error.

[Nguyên Huỳnh]

Hi Adrian,

The script work quite well now. But every time I run the code, It inserts more row to instead of clearing current data then adding the new data. In addition, There's no header (field name) added to the sheet.

Very appreciate your help Adrian :)

Thanks,

[Adrian Catambay (AdWords Scripts Team)]

Hello Nguyen,

The script is appending more rows whenever it runs instead of clearing the sheet because you are only using the same spreadsheet by specifying your spreadsheet URL with openByUrl() function. If you want to create a new spreadsheet whenever the script runs, you may either use the create() function or clear the contents of the sheet using clearContents() function anywhere before the accountIterator while-loop in your script.

To add column headers to your spreadsheet report, you may also use the appendRow() function before the reports generation in your script. I would also suggest a sample function which you can call anywhere before the accountIterator while-loop in your script, that clears existing content of a sheet and appends column headers after. You may refer to the sample function below:

function clearSheetAndAddHeader(sheet) {
    sheet.clearContents(); // clears content of sheet
    sheet.appendRow(['AccountDescriptiveName', 'CampaignName', 'Impressions']) // appends column headers, you may add more fields based on your requirement
}

Let me know if the following suggestions have resolve your concerns.

[Nguyên Huỳnh]

Hi Adrian,

Thanks to your suggested code, the script works perfectly now.

I will upload the script I used here in case someone else need it. :)

function main() {

    var accountSelector = MccApp.accounts()

    var accountIterator = accountSelector.get();

    var SPREADSHEET_URL = "";

    var account = AdWordsApp.currentAccount(); // This line is not necessary as you are already using MccApp to select the current account

    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

    var sheet = spreadsheet.getActiveSheet(); // gets the default active sheet

    sheet.clearContents(); // clears content of sheet

    sheet.appendRow(['AccountDescriptiveName', 'CampaignName', 'AdNetworkType1', 'Labels', 'Date' ,'Impressions', 'Clicks', 'Conversions', 'Cost'] )

    while (accountIterator.hasNext()) {

        var account = accountIterator.next();

        MccApp.select(account); // selects the current account to generate report from 

      

        var report = AdWordsApp.report(

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

            'WHERE  Impressions > 0 ' + 'DURING 20180101,20201231', {

                apiVersion: 'v201710'

            }); // adds the field AccountDescriptiveName in your query to get child account name

      

     var rows = report.rows();

        while (rows.hasNext()) {

            var row = rows.next();

          sheet.appendRow([row['AccountDescriptiveName'], 

                            row['CampaignName'], 

                            row['AdNetworkType1'], 

                            row['Labels'], 

                            row['Date'], 

                            row['Impressions'], 

                            row['Clicks'], 

                            row['Conversions'], 

                            row['Cost']]); // append rows from current account's generated Campaign Performance Report

        }

    }

}