January 18, 2022

Output Any Google Ads Scripts Report to a Sheet

With <3 by Acuto

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);
}

Want to automate Google Ads with custom scripts?

If you liked this script or would like a personalized one, we’re here to help! Our team of developers with experience in PPC can create custom scripts to automate any aspect of your Google Ads accounts. Schedule a discovery call to discuss how we can assist you in implementing automation scripts effectively.