image
Optimization Up
Optimize

Google Ads Script to Export a Monthly Spend Report vs PO Number

Many media agencies struggle every month to generate a spend report for each purchase order number, so the finance team can settle accounts’ invoices and payments. The manual process can be time-consuming and mistakes could happen, especially if the number of accounts is large.

To automate the process, we’ve written an MCC AdWords script that you can schedule to run every month, then it exports a report to a Google sheet that contains Account name, Account ID, Spend, PO Number, Month, and Currency.

po number script vs spend

Script Source Code

Here’s the source code for the MCC version of the script. All you need to do is to insert your spreadsheet URL in the relevant variable, but please note the max number of accounts the script can process at one time is 50 accounts.

/*********************************************
* PO Number vs Spend Script
* Version 1.0
* Created By: Ahmed Ali
*https://optimizationup.com/
**********************************************/

var SPREADSHEET_URL = 'Insert your spreadsheet URL Here'; var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var sheet = spreadsheet.getActiveSheet(); function main() { sheet.getRange("A:H").clearContent();  sheet.getRange("c1").setValue("PO Number"); sheet.getRange("a1").setValue("Account ID"); sheet.getRange("b1").setValue("Account Name"); sheet.getRange("e1").setValue("Currency"); sheet.getRange("f1").setValue("Month"); sheet.getRange("d1").setValue("Spend"); var accountIterator = MccApp.accounts().withLimit(50).withCondition("Impressions > 0")  .forDateRange("LAST_MONTH").executeInParallel('ponumber'); } function ponumber() {  var budgetOrderIterator = AdWordsApp.budgetOrders().get();  while (budgetOrderIterator.hasNext()) {  var budgetOrder = budgetOrderIterator.next();  var poNumber = budgetOrder.getPoNumber();  var startDate= formatDate(budgetOrder.getStartDateTime());  var endDate= formatDate(budgetOrder.getEndDateTime());  var report = AdWordsApp.report("SELECT Month, Cost " +  "FROM ACCOUNT_PERFORMANCE_REPORT " +  "WHERE Impressions > 0 " +  "DURING " + startDate + ", " + endDate); var rows = report.rows();  while (rows.hasNext()) {  var row = rows.next();  Logger.log(

	


, row['Month'], poNumber]);  sheet.appendRow([AdWordsApp.currentAccount().getCustomerId(), AdWordsApp.currentAccount().getName(), poNumber, row['Cost'], AdWordsApp.currentAccount().getCurrencyCode(), row['Month']]); }  } } function formatDate(date) {  function zeroPad(number) { return Utilities.formatString('%02d', number); }  return (date == null) ? 'None' : zeroPad(date.year) + zeroPad(date.month) +  zeroPad(date.day); }