Help with Mcc Script

[Mihai Joimir]

Hello,

I'm an adwords script beginner struggling to make to make it work. Please help me with the following problem:

The script below should export the data for all my accounts onto a spreadsheet. Unfortunately, it only exports info about one account (random). Please help me!

function main() {

 var spreadsheet = SpreadsheetApp.openByUrl(

      'https://docs.google.com/spreadsheets/d/1p2jGO-3X9W1H8HRuz_8yah4NaIImlSPI4grdU0epjNg/edit');

 var accountIterator = MccApp.accounts().withLimit(10).get();

 while (accountIterator.hasNext()) {

   var account = accountIterator.next();

   MccApp.select(account);

   // Run reports against child account.

   var report = AdWordsApp.report("Select Clicks, Impressions, Cost from ACCOUNT_PERFORMANCE_REPORT during THIS_MONTH");

   var rowIterator = report.rows();

   while (rowIterator.hasNext()) {

     var row = rowIterator.next();

     Logger.log("%s,%s,%s,%s", account.getCustomerId(), row["Clicks"], row["Impressions"], row["Cost"]);

     report.exportToSheet(spreadsheet.getActiveSheet());

   }

 }

}

[Tyler Sidell (AdWords Scripts Team)]

Hi Mihai,

Every time that the while loop is ran, the content inside the spreadsheet is replaced.  You may want to look at setting up tabs for each account using something similar to this code:

function main() {  var accountIterator = MccApp.accounts().get();
  var spreadsheet = SpreadsheetApp.openById("SPREADSHEET_ID");  
  var mccAccount = AdWordsApp.currentAccount();
  var i=0;

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

    // Switch to the account you want to process.
    MccApp.select(account);

    var report = AdWordsApp.report("Select Clicks, Impressions, Cost from ACCOUNT_PERFORMANCE_REPORT during THIS_MONTH");

    var sheet = spreadsheet.getActiveSheet();
    //Name sheet by account name
    if(i == 0){
      spreadsheet.renameActiveSheet("Report "+account.getName());
    }
    else if(i != 0 && !spreadsheet.getSheetByName("Report "+account.getName())){
      spreadsheet.insertSheet("Report "+account.getName());
    }
    else if(i != 0 && spreadsheet.getSheetByName("Report "+account.getName())){
      spreadsheet.setActiveSheet(spreadsheet.getSheets()[i]);
    }
   
    report.exportToSheet(spreadsheet.getActiveSheet());
    i++;
  }
}

Thanks,
Tyler Sidell
AdWords Scripts Team

[Mihai Joimir]

It's working. Thank you Tyler! :)

[Delana Ricasa]

Hello Tyler:

I am interested in dropping the stats in one sheet, one line per account on current month's spend. I'm trying to avoid  setting up tabs for each account as I want a global view.

Is there a work-around? 

Thanks,

Delana

[Tyler Sidell (AdWords Scripts Team)]

Hi Delana,

You should be able to set up something similar to the code below in order to get your report on one sheet:

var spreadsheetUrl = "SPREADSHEET_URL";
function main() {
  var activeSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getActiveSheet();
  activeSheet.clear();
  var accountIterator = MccApp.accounts().get();

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

    var reportRows = AdWordsApp.report("Select Clicks, Impressions, Cost, Date from ACCOUNT_PERFORMANCE_REPORT during YESTERDAY").rows();
    while (reportRows.hasNext()) {
      var reportRow = reportRows.next();
      var newRow = [account.getCustomerId(), reportRow["Clicks"], reportRow["Impressions"], reportRow["Cost"], reportRow["Date"]];
      activeSheet.appendRow(newRow);
    }
  }
}

Thanks,

Tyler Sidell

AdWords Scripts Team

[Delana Ricasa]

This is GREAT Tyler, thanks! I'm a newbie to scripts so that was super helpful!

In order to add the account name, would i just add account.getAccountName to var newRow?

I also wanted the data dump to include the headers which it isn't by default....I would have to include a snippet to return report rows, right? If you can be so kind (again) as to provide that snippet you would be a life saver! Thanks again!

[Delana Ricasa]

Something to look like this...

[Delana Ricasa]

One last thing Tyler...I want the script to pull the month-to-date spend. When I change YESTERDAY to THIS_MONTH, the script returns rows for everyday of the month. How can I iterate a month-to-date rillup spend per account?

[Tyler Sidell (AdWords Scripts Team)]

Hi Delana,

In order to get the additional field of account name, headers, and grouping by month; you could modify your code to the following:

var spreadsheetUrl = "SPREADSHEET_URL";
function main() {
  var activeSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getActiveSheet();
  activeSheet.clear();
  var accountIterator = MccApp.accounts().get();

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

    var reportRows = AdWordsApp.report("Select MonthOfYear, Clicks, Impressions, Cost from ACCOUNT_PERFORMANCE_REPORT during THIS_MONTH").rows();
    var name = activeSheet.getRange("A1");
    var customerID = activeSheet.getRange("B1");
    var clicks = activeSheet.getRange("C1");
    var impressions = activeSheet.getRange("D1");
    var cost = activeSheet.getRange("E1");
    var monthofyear = activeSheet.getRange("F1");
    name.setValue("Account Name");
    customerID.setValue("Customer ID");
    clicks.setValue("Clicks");
    impressions.setValue("Impressions");
    cost.setValue("Cost");
    monthofyear.setValue("Month of Year");

    while (reportRows.hasNext()) {
      var reportRow = reportRows.next();

      var newRow = [account.getName(), account.getCustomerId(), reportRow["Clicks"], reportRow["Impressions"], reportRow["Cost"],reportRow["MonthOfYear"]];
      activeSheet.appendRow(newRow);
    }
  }
}

Thanks,

Tyler Sidell

AdWords Scripts Team