Google Ads - Automated Reports v1.0

function main() {
    const monthYearFormat = "MMMM";
    const numberFormat = "#,##0";
    const currencyFormat = "€#,##0";
    const percentageFormat = "0.00%";

    var today = new Date();
    var previousMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
    var twoMonthsAgo = new Date(today.getFullYear(), today.getMonth() - 2, 1);

    var mccAccount = AdsApp.currentAccount();
    var accounts = AdsManagerApp.accounts().get();

    var sheet = SpreadsheetApp.openByUrl('<Spreadsheet URL>'); // Change this if using a different spreadsheet
    var dataSheet = sheet.getSheetByName('<Sheet Name>'); // Enter the name of the sheet

    while (accounts.hasNext()) {
        var account = accounts.next();
        AdsManagerApp.select(account);

        var rowPreviousMonth = createRowForMonth(previousMonth);
        var rowTwoMonthsAgo = createRowForMonth(twoMonthsAgo);

        AdsManagerApp.select(mccAccount);

        var roiPreviousMonth = (rowPreviousMonth.conversionValue / rowPreviousMonth.cost).toFixed(2);
        var roiTwoMonthsAgo = (rowTwoMonthsAgo.conversionValue / rowTwoMonthsAgo.cost).toFixed(2);

        dataSheet.appendRow([
            account.getName(),
            '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
            roiTwoMonthsAgo,
            '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
            roiPreviousMonth
        ]);

        // Update the corresponding number formatting as per your new data placement
        dataSheet.getRange(dataSheet.getLastRow(), 7, 1, 2).setNumberFormat(numberFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 9, 1, 2).setNumberFormat(numberFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 11, 1, 1).setNumberFormat(currencyFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 12, 1, 1).setNumberFormat(currencyFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 8, 1, 1).setNumberFormat(percentageFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 16, 1, 2).setNumberFormat(numberFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 18, 1, 2).setNumberFormat(numberFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 20, 1, 1).setNumberFormat(currencyFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 21, 1, 1).setNumberFormat(currencyFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 17, 1, 1).setNumberFormat(percentageFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 13, 1, 1).setNumberFormat(numberFormat);
        dataSheet.getRange(dataSheet.getLastRow(), 25, 1, 1).setNumberFormat(numberFormat);
    }
}

function parseCurrency(value) {
    if (typeof value === "string") {
        // Remove currency symbols, thousand separators and convert to a number
        return parseFloat(value.replace(/[^0-9.-]+/g, ""));
    }
    return value; // If it's already a number, just return it
}

function createRowForMonth(date) {
    var firstDayOfMonth = new Date(date.getFullYear(), date.getMonth(), 1);
    var lastDayOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);
    var report = AdsApp.report(
        `SELECT Clicks, Conversions, Impressions, ConversionValue, Cost
        FROM ACCOUNT_PERFORMANCE_REPORT
        DURING ${Utilities.formatDate(firstDayOfMonth, "PST", "yyyyMMdd")},${Utilities.formatDate(lastDayOfMonth, "PST", "yyyyMMdd")}`
    );
    var rows = report.rows();
    while (rows.hasNext()) {
        var row = rows.next();
        var conversionValue = parseCurrency(row['ConversionValue']);
        var cost = parseCurrency(row['Cost']);
        var roi = (conversionValue / cost).toFixed(2);
        return {
            clicks: row['Clicks'],
            conversions: row['Conversions'],
            impressions: row['Impressions'],
            conversionValue: conversionValue,
            cost: cost,
            conversionRate: row['Conversions'] / row['Clicks'],
            roi: roi
        };
    }
    return {};
}