google-ads-script-custom-email-report.js

/* AVAILABLE DATE RANGES:
TODAY | YESTERDAY | LAST_7_DAYS | THIS_WEEK_SUN_TODAY | THIS_WEEK_MON_TODAY | LAST_WEEK |
LAST_14_DAYS | LAST_30_DAYS | LAST_BUSINESS_WEEK | LAST_WEEK_SUN_SAT | THIS_MONTH
*/

function getYesterdaysDate() {
    var date = new Date()
    date.setDate(date.getDate() - 1)
    var currentMonth = date.getMonth() + 1
    var formatMonth = currentMonth < 10 ? '0' + currentMonth : currentMonth
    return [date.getFullYear() + "" + formatMonth + "" + date.getDate(), date.getFullYear()]
}

function getFormattedDate(date) {
    var dd = date.substring(8, 10);
    var mm = date.substring(5, 7);
    var yyyy = date.substring(0, 4);
    var formattedDate = mm + "/" + dd + "/" + yyyy;
    return formattedDate;
}

function main() {

    // REPORT SETTINGS
    var dateRangeType   = "LAST_30_DAYS"
    var dateRangeCustom = "" //"20210101, 20211231" // LAST YEAR
    var reportType      = "AD_PERFORMANCE_REPORT";
    var reportOptions   = { includeZeroImpressions: false };
  
    var dateRangeQuery = "" 
    if (dateRangeCustom === "") {
        dateRangeQuery = "DURING " + dateRangeType
    } else {
        dateRangeType = "CUSTOM_DATE"
        dateRangeQuery = "DURING" + dateRangeCustom
    }

    Logger.log("GENERATING REPORT FOR DATE RANGE: " + dateRangeType)

    var mccAccount = MccApp.accounts()
        .withCondition("ManagerCustomerId IN ['<ACCOUNT_ID>']")
        .forDateRange(dateRangeType);

    var accountIterator = mccAccount.get();

    var reportArray = [];
    var fields = [
        "Date",
        "AccountDescriptiveName",
        "CampaignStatus",
        "CampaignId",
        "CampaignName",
        "AdGroupId",
        "AdGroupName",
        "AdGroupStatus",
        "AdType",
        "Labels",
        "Conversions",
        "VideoQuartile100Rate",
        "VideoViews",
        "GmailSecondaryClicks",
        "Engagements",
        "Headline",
        "Impressions",
        "Clicks",
        "Cost"
    ];
    reportArray.push(fields);

    while (accountIterator.hasNext()) {

        var account = accountIterator.next();
        MccApp.select(account);
        Logger.log('GENERATING REPORT FOR MANAGER ID: %s', account.getCustomerId());

        var query = "SELECT " + fields.join(", ") + " FROM " + reportType + " " + dateRangeQuery;
        Logger.log("GENERATING REPORT WITH QUERY: " + query);

        var report = AdWordsApp.report(query, reportOptions);
        var rows = report.rows();

        while (rows.hasNext()) {
            var row = rows.next();
            var row_Array = [];

            for (var i = 0; i < fields.length; i++) {
                var metric = row[fields[i]];
                if (i == 0) {
                    row_Array.push(getFormattedDate(metric));
                } else {
                    row_Array.push(metric);
                }
            }
            reportArray.push(row_Array);
        }
    }

    var csv = [];

    for (var i = 0; i < reportArray.length; i++) {
        csv += reportArray[i].toString().replace(/[[]"]+/g, "") + '\n';
    }

    MailApp.sendEmail(
        '<EMAIL>',
        'Google Ads - Ad Performance Report: <ACCOUNT_ID>',
        'Google Ads Script generated report for the Account ID <ACCOUNT_ID>',
        { attachments: [{ fileName: 'CLIENTNAME_GoogleAds_Report_LAST_30_DAYS.csv', mimeType: 'text/csv', content: csv }] }
    );

    Logger.log('SENDING REPORT TO: %s', '<EMAIL>');

}