Get Total Cost

[jin zhang]

HI,

I'm looking for a script that will get the Cost stat from all accounts added together for a Total cost for each month,

Couldnt figure out how to do it, can someone help?

Thanks in advance

[Google Ads Scripts Forum Advisor]

Hello,

I recommend using the account performance report to get the cost for each account, which you can sum in your script. This script will have to be written on the MCC level, so I recommend looking into working with Ads Manager scripts. 

If you have any questions on the implementation, please let us know.

Regards,
Matt
Google Ads Scripts Team

ref:_00D1U1174p._5004Q22altF:ref

[jin zhang]

Thanks for the response. I've been trying to modify the solution given in the other thread to suit, but running into an error "Invalid reporting query: ACCOUNT_PERFORMANCE_REPORT. (file Code.gs, line 2)"

[jin zhang]

Okay i think i did the first part right

function main() {
  // This is useful when you need to identify accounts that were performing
  // well (or poorly) in a given time frame.

  var accountIterator = AdsManagerApp.accounts()
      .withCondition('Cost > 0')
      .forDateRange('LAST_MONTH')
      .orderBy('Cost DESC')
      .get();

  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    var stats = account.getStatsFor('LAST_MONTH');
    Logger.log('%s,%s', account.getCustomerId(),
        stats.getCost().toFixed(2));
  }
}

how you point me in the right direction on how to add them to a total?

On Friday, July 31, 2020 at 2:20:36 PM UTC-7, adsscriptsforumadvisor wrote:

[Google Ads Scripts Forum Advisor]

Hi,

I work with Matt and let me provide support to your concern.

I've updated the code to sum the cost of all accounts and log it in the console. Let me know how it goes after trying this updated script.

function main() {
  // This is useful when you need to identify accounts that were performing
  // well (or poorly) in a given time frame.

  var totalCostAccounts = 0;
  
  var accountIterator = AdsManagerApp.accounts()
      .withCondition('Cost > 0')
      .forDateRange('LAST_MONTH')
      .orderBy('Cost DESC')
      .get();

  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    var stats = account.getStatsFor('LAST_MONTH');
    Logger.log('%s,%s', account.getCustomerId(),
        stats.getCost().toFixed(2));
    
    totalCostAccounts = parseFloat(totalCostAccounts) + parseFloat(stats.getCost());
  }
  
  Logger.log("Total cost of all accounts: " + totalCostAccounts.toFixed(2));
}

Regards,
Ejay

[jin zhang]

Hi Ejay, thank much! it is working. Could you further help me figure out how to output to a spreadsheet? This is what i have and im not sure how to proceed further.

// URL of the default spreadsheet template. This should be a copy of
// https://goo.gl/21FW5i
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1XgfvsmpsY-uWrc7Od0bgkSaEXKAehYc90r1PMa3i-v4/edit#gid=0';
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getActiveSheet();

function main() {
  // This is useful when you need to identify accounts that were performing
  // well (or poorly) in a given time frame.

  sheet.getRange("A:C").clearContent();
  sheet.getRange("c1").setValue("Spend");
  sheet.getRange("a1").setValue("Account ID");
  sheet.getRange("b1").setValue("Account Name");

[Google Ads Scripts Forum Advisor]

Hi,

You may refer to the updated script below that works on my test account. You can try this on your end and let me know if you encounter any issues.

var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL';
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getActiveSheet();

function main() {
  sheet.getRange("A:C").clearContent();
  sheet.getRange("a1").setValue("Account ID");
  sheet.getRange("b1").setValue("Account Name");
  sheet.getRange("c1").setValue("Spend");


  var totalCostAccounts = 0;

  var accountIterator = AdsManagerApp.accounts()
      .withCondition('Cost > 0')
      .forDateRange('LAST_MONTH')
      .orderBy('Cost DESC')
      .get();

  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    var stats = account.getStatsFor('LAST_MONTH');

    Logger.log('%s,%s,%s', account.getCustomerId(), account.getName(), 
        stats.getCost().toFixed(2));

    sheet.appendRow([account.getCustomerId(), account.getName(), stats.getCost().toFixed(2)]);

    totalCostAccounts = parseFloat(totalCostAccounts) + parseFloat(stats.getCost());

  }

  Logger.log("Total cost of all accounts: " + totalCostAccounts.toFixed(2));

}