We received another request for an incredibly simple script that apparently didn’t exist on any online library: a simple daily pull of spend data.
Because building this only took a few minutes, we realized we could actually spend just a few minutes more and make this a much more useful script. Hence we turned it into a script that lets you query any report that’s available in Google Ads Scripts (full list available here), and output it to a sheet.
It’s probably easiest to illustrate with an example, so for this one we’ll use the Account Performance Report and use it to pull a simple daily spend and click tracker for every account in an MCC (note that this script will work without any modifications if running on a single account).
All we did was navigate to the Account Performance Report page of the Google Ads Scripts documentation, pick the dimensions, segments, and metrics we wanted, a date range, and formatted the following query:
SELECT AccountDescriptiveName, Clicks, Cost
FROM ACCOUNT_PERFORMANCE_REPORT
DURING LAST_30_DAYS
The FROM statement is the same as the name of the report, but all caps and with underscores instead of spaces (so the Campaign Performance Report becomes CAMPAIGN_PERFORMANCE_REPORT).
The DURING statement can be any of these: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH, ALL_TIME
/*
_ ___ _ _ _____ ___ _
/_\ / __| | | |_ _/ _ \ (_)___
/ _ \ (__| |_| | | || (_) || / _ \
/_/ \_\___|\___/ |_| \___(_)_\___/
*/
var config = {
accountIds: ['151-492-2039', '489-048-0776'], // A comma-separated list of Google Ads account IDs. Max 50. Ignore if running in a single account.
query: 'SELECT AccountDescriptiveName, Clicks, Cost, Date FROM ACCOUNT_PERFORMANCE_REPORT DURING LAST_30_DAYS', // A Google Ads Scripts report query
spreadsheet: 'https://docs.google.com/spreadsheets/d/12pgsJQDbqbPdrZLBFbmQEyk47yQ8TidAmwKDLgMAmqQ/', // The URL of the spreadsheet this will be output to
sheetName: 'Data', // The name of the sheet (i.e. the tab) of the spreadsheet into which the data will be added
};
function main() {
try {
AdsManagerApp.accounts().withIds(accountIds).executeInParallel('getReports', 'processAccounts');
} catch (e) {
Logger.log(e);
var data = getReports();
outputReports(data);
}
}
function getReports() {
Logger.log('Now running in ' + AdsApp.currentAccount().getName());
var data = [];
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var dimensions = Object.keys(row).filter(function (attr) {
if (typeof row[attr] === 'string') return attr;
});
var rowObj = {};
dimensions.forEach(function (dimension) {
rowObj[dimension] = row[dimension];
});
data.push(rowObj);
}
return JSON.stringify(data);
}
function processAccounts(results) {
var allData = [];
for (var i = 0; i < results.length; i++) {
var result = results[i].getReturnValue();
allData = allData.concat(JSON.parse(result));
}
outputReports(allData);
}
function outputReports(data) {
var output = [];
var headers = Object.keys(data[0]);
output.push(headers);
data.forEach(function (row) {
var arrayRow = [];
for (var cell in row) {
arrayRow.push(row[cell]);
}
output.push(arrayRow);
});
var spread = SpreadsheetApp.openByUrl(spreadsheet);
var sheet = spread.getSheetByName(sheetName);
sheet.clearContents();
sheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}