MCC Script - Query multiple reports into Google Sheets

[AGorecki]

Hello,

I found a script that works pulls data from a single account into multiple spreadsheets, but I am trying to figure out how update this script to work as an MCC script. I understand that an Account Iterator would need to be applied, but when I try to loop it, it replaces all the data for each account it iterates through.

Can someone please help me to get this working?

Here is the best I have been able to come up with so far:

var QUERIES = [{'query' : 'SELECT AccountDescriptiveName, CampaignName, AdGroupName, ApprovalStatus ' +

    'FROM   KEYWORDS_PERFORMANCE_REPORT ' +

          'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED and ApprovalStatus = DISAPPROVED ' +

    'DURING LAST_30_DAYS',

                'spreadsheetUrl' : 'EXAMPLESPREADSHEETURL',

                'tabName' : 'Disapproved Ads',

                'reportVersion' : 'v201809'

               },

                {'query' : 'SELECT CampaignName, Clicks, Impressions, Cost ' +

    'FROM   AD_PERFORMANCE_REPORT ' +

          'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED and CombinedApprovalStatus IN [DISAPPROVED, APPROVED_LIMITED] ' +

    'DURING LAST_30_DAYS',

                'spreadsheetUrl' : 'EXAMPLESPREADSHEETURL',

                'tabName' : 'Disapproved Keywords',

                'reportVersion' : 'v201809'

               }

               ];

function runQuery() {

  for(var i in QUERIES) {

    var queryObject = QUERIES[i];

    var query = queryObject.query;

    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var tabName = queryObject.tabName;

    var reportVersion = queryObject.reportVersion;

    //Logger.log(spreadsheetUrl + " " + query);

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(tabName);

    var report = AdsApp.report(query, {apiVersion: reportVersion});

    var rows = report.rows()

    //Logger.log({sheet:sheet})

    

    while (rows.hasNext()) {

      var row = rows.next();

      var keys = Object.keys(row);

    

    var insert = [];

      Logger.log({keys:keys})

    

    for (var key in keys) {

      insert.push(key);

      Logger.log({insert:insert})

    }

    

    for (var i = 0; i < keys.length-1; i++) {

      insert.push(row[keys[i]])

    }

    

      sheet.appendRow(insert)

  }

    // report.exportToSheet(sheet);

  }

}

function clearSheetData() {

  for(var i in QUERIES) {

    var queryObject = QUERIES[i];

    var query = queryObject.query;

    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var tabName = queryObject.tabName;

    var reportVersion = queryObject.reportVersion;

    //Logger.log(spreadsheetUrl + " " + query);

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(tabName);

    sheet.clearContents();

  }

}  

function main() {

  clearSheetData();

  var accountIterator = AdsManagerApp.accounts().get();

  while (accountIterator.hasNext()) {

    var account = accountIterator.next();

    // Select the client account.

    AdsManagerApp.select(account);

    // Select campaigns under the client account

    var campaignIterator = AdsApp.campaigns().get();

    runQuery()

  }

}

[Google Ads Scripts Forum Advisor]

Hi,

Thanks for posting your concern.

You are correct that you can implement the account selector and iterator so that the script will work at the MCC level. However, the clearSheetData() method has been implemented in the script which is clearing the content of the sheets and this is the reason why the problem is occurring.

With this, you may try to comment out the clearSheetData() method inside the main() function.

Moving forward, if you have further questions regarding the third-party script, please reach out to the author of this directly to get further support. The reason for this is that our team doesn't provide support to this script.

Regards,
Ejay
Google Ads Scripts Team

ref:_00D1U1174p._5001UOGFi8:ref