Quality Score Dashboard v3.0

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Quality Score Dashboard v3.0, powered by SEA Safeguard
 * Written by Martin Roettgerding.
 * © 2024 Martin Roettgerding, Bloofusion Germany GmbH
 * www.sea-safeguard.com/qs-dashboard
 */

var config = {
    /*
     * MCC only: You can use a label to mark the accounts whose data is to be collected for the dashboard.
     * Leave this blank to collect data from all accounts.
     * This setting is only relevant if the script is run in a manager (MCC) account.
     */
    labelName: '',

    /*
     * Running a new version for the first time?
     * If set to true (default), an existing dashboard from a previous version will be replaced with the current version.
     * This is helpful if you have already shared the dashboard with others as everyone can just keep using the same one.
     * Set this to false in order to keep an existing spreadsheet and just make a new one.
     */
    replaceOldVersionDashboard: true,

    /*
     * This is the Google Drive folder where this scripts stores its dashboard spreadsheet.
     * It doesn't have to be in your Drive's root folder. You can also use something like 'Scripts/Ads Scripts/QS Dashboard'.
     * It will be created if it doesn't exist. There's no need to change anything directly in Google Drive.
     * If you move the script to a different folder, either update this setting or use the 'spreadsheetId' config setting.
     * You can use the same folder for different instances of this script, if they are for different accounts.
     * For different instances in the same account, it's easiest just putting them in different folders.
     */
    baseFolder: 'Quality Score Dashboard',

    /*
     * Only use this if you want to re-use an existing spreadsheet.
     * This is helpful if you take over the script from someone else.
     */
    speadsheetUrl: '',

    /*
     * The spreadsheet's name. By default, the current account's name will be included.
     * This is only used when the spreadsheet is created for the first time.
     * You can rename your spreadsheet at any time.
     */
    spreadsheetName: 'Quality Score Dashboard ' + AdsApp.currentAccount().getName(),

    /*
     * Use this to limit data collection to enabled keywords in enabled ad groups in enabled campaigns.
     * Since this script only tracks keywords with impressions, this is usually unnecessary.
     */
    trackOnlyEnabled: false,

    /*
     * If you want to include statistics from search partners as well, set this to false.
     */
    trackOnlyGoogle: true,

    /*
     * Use this to change the number of days for statistics. Quality Score always reflects the current status, but impressions, clicks, etc. refer to this timeframe.
     * This has no effect on the QS history chart.
     */
    daysToTrack: 28,
};

/*** Do not change anything below this line ***/

function main() {
    Logger.log('Quality Score Dashboard v3.0 powered by SEA Safeguard').log('');
    let spreadsheet = getSpreadsheetFromConfig();

    // Depending on whether this is run in a manager account or not, run the appropriate version.
    if (typeof AdsManagerApp === 'undefined') {
        runForSingleAccount(spreadsheet);
    } else {
        runForManagerAccount(spreadsheet);
    }
}

/*
 * If provided via config, opens an existing spreadsheet.
 * Otherwise checks Google Drive for an existing spreadsheet to return that. If necessary, a new spreadsheet is created.
 * @returns Spreadsheet
 */
function getSpreadsheetFromConfig() {
    var baseSpreadsheetId = '110Og9qrPVm6L1q5rmmNtRtk4RUqxoOOtlJNMiWKG1ik';

    if (config['speadsheetUrl']) {
        return SpreadsheetApp.openByUrl(config['speadsheetUrl']);
    } else {
        // Make sure there is a folder in Google Drive to store the spreadsheet.
        let baseFolder = getOrCreateFolder(config['baseFolder']);
        let description = 'Quality Score Dashboard v3.0 for ' + AdsApp.currentAccount().getCustomerId();

        // Is there already a spreadsheet for this dashboard? Then update its info sheets and return it.
        let existingSpreadsheet = getExistingDashboardSpreadsheet(baseFolder, description);
        if (existingSpreadsheet) {
            // Update info sheets.
            updateInfoSheets(existingSpreadsheet, baseSpreadsheetId);
            return existingSpreadsheet;
        }

        // Look for previous versions of the dashboard.
        // If there is one: Update it to the current version and return it.
        if (config['replaceOldVersionDashboard']) {
            let oldVersionNames = ['v1.0', 'v2.0'];
            let olderVersionSpreadsheetFile = getOlderVersionSpreadsheetFile(baseFolder, oldVersionNames);
            if (olderVersionSpreadsheetFile) {
                Logger.log('Updating existing dashboard to current version.');
                return updateOlderVersionSpreadsheet(olderVersionSpreadsheetFile, baseSpreadsheetId, description);
            }
        }

        // Create a new spreadsheet and return it.
        return createDashboardSpreadsheet(baseFolder, description, config['spreadsheetName'], baseSpreadsheetId);
    }
}

/*
 * Returns an older version of the spreadsheet if there is one.
 * @param {Folder} folder The Google Drive folder where to look for existing spreadsheets.
 * @param {Array} oldVersionNames A list of version numbers to look for.
 * @returns {File|Boolean} Either a Google Drive file or false.
 */
function getOlderVersionSpreadsheetFile(folder, oldVersionNames) {
    // Make an array of descriptions to check for.
    var oldVersionDescriptions = [];
    for (var i = 0; i < oldVersionNames.length; i++) {
        oldVersionDescriptions.push('Quality Score Dashboard ' + oldVersionNames[i] + ' for ' + AdsApp.currentAccount().getCustomerId())
    }

    // Go through all spreadsheets in the folder. Return one if its description matches one of the old version names.
    const fileIterator = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while (fileIterator.hasNext()) {
        const file = fileIterator.next();
        if (oldVersionDescriptions.includes(file.getDescription())) {
            return file;
        }
    }
    // Nothing has been found.
    return false;
}

/*
 * Updates an older version spreadsheet file to the current version.
 * Takes the sheets from the template spreadsheet and replaces existing sheets that have the same name.
 * Also fixes arising problems with references that got lost in the process.
 * @param {File} file The older version spreadsheet as a Google Drive file.
 * @param {String} baseSpreadsheetId ID of the template spreadsheet.
 * @param {String} description The description to set for the file.
 * @returns {Spreadsheet} The older version spreadsheet.
 */
function updateOlderVersionSpreadsheet(file, baseSpreadsheetId, description) {
    file.setDescription(description);
    var spreadsheet = SpreadsheetApp.open(file);

    var baseSpreadsheet = SpreadsheetApp.openById(baseSpreadsheetId);

    // Get all sheets from the base spreadsheet and put them into the spreadsheet.

    var baseDataSheet = baseSpreadsheet.getSheetByName('Data');
    insertOrReplaceSheet(spreadsheet, baseDataSheet);

    var baseHistoryDataSheet = baseSpreadsheet.getSheetByName('History');
    insertOrReplaceSheet(spreadsheet, baseHistoryDataSheet).hideSheet();

    var baseCalculationsSheet = baseSpreadsheet.getSheetByName('Calculations');
    var calculationsSheet = insertOrReplaceSheet(spreadsheet, baseCalculationsSheet);

    var baseDashboardSheet = baseSpreadsheet.getSheetByName('Dashboard');
    dashboardSheet = insertOrReplaceSheet(spreadsheet, baseDashboardSheet);

    // Put the account's name into the Dashboard's header and make it the active sheet.
    dashboardSheet.activate().getRange(3, 2).setValue(AdsApp.currentAccount().getName());

    /* 
     * The calculations sheet contains some formulas that reference the dashboard sheet.
     * These references don't work anymore since the old spreadsheet was deleted
     * (even though the new one has the same name so that the same formula should work again).
     * They need to be reset, thus making the connection to the new dashboard sheet.
     */
    var formulas = calculationsSheet.getRange('A:A').getFormulas();
    for (var f = 0; f < formulas.length; f++) {
        if (formulas[f][0]) {
            let rowToSet = f + 1;
            let colToSet = 1;
            calculationsSheet.getRange(rowToSet, colToSet).setFormula(formulas[f][0]);
        }
    }

    /*
     * The charts on the dashboard have forgotten some of their attributes and need to be fixed.
     * This includes their data ranges.
     * Not everything can be fixed programmatically, though.
     */
    var charts = dashboardSheet.getCharts();
    for (var i in charts) {
        var chart = charts[i];
        switch (chart.modify().getChartType()) {
            case Charts.ChartType.AREA:
                let chartDataRange = calculationsSheet.getRange("R13");
                chart = chart.modify()
                        .addRange(chartDataRange)
                        .build();
                dashboardSheet.updateChart(chart);
                break;
            case Charts.ChartType.COLUMN:
                let chartDataRangeQS = calculationsSheet.getRange("E1:E11");
                let chartDataRangeKpis = calculationsSheet.getRange("V1:W11");
                chart = chart.modify()
                        .addRange(chartDataRangeQS)
                        .addRange(chartDataRangeKpis)
                        .setOption('colors', ['#00507d', '#fa9d1c'])
                        .setOption('vAxis.minValue', 0)
                        .setOption('vAxis.maxValue', 1)
                        .build();
                dashboardSheet.updateChart(chart);
                break;
            case Charts.ChartType.LINE:
                let chartDataRangeDates = calculationsSheet.getRange("H21:V21");
                let chartDataRangeQSData1 = calculationsSheet.getRange("H50:V50");
                let chartDataRangeQSData2 = calculationsSheet.getRange("H51:V51");
                chart = chart.modify()
                        .addRange(chartDataRangeDates)
                        .addRange(chartDataRangeQSData1)
                        .addRange(chartDataRangeQSData2)
                        .setOption('series',
                                [{
                                        color: '#00507d',
                                        pointShape: 'circle',
                                        pointSize: 7
                                    },
                                    {
                                        color: '#fa9d1c',
                                        pointShape: 'circle',
                                        pointSize: 7
                                    }
                                ])
                        .setOption('vAxis.minValue', 0)
                        .setOption('vAxis.maxValue', 10)
                        .build();
                dashboardSheet.updateChart(chart);
                break;
        }
    }

    updateInfoSheets(spreadsheet, baseSpreadsheetId);

    return spreadsheet;
}

function runForManagerAccount(spreadsheet) {
    if (config['labelName']) {
        Logger.log('Tracking all accounts with label ' + config['labelName'] + '.');
        var accountIterator = MccApp.accounts().withCondition("LabelNames CONTAINS '" + config['labelName'] + "'").get();
    } else {
        Logger.log('Tracking all accounts in MCC.');
        var accountIterator = MccApp.accounts().get();
    }

    // Collect the IDs of the accounts to be processed in parallel (max 50) and then sequentially (everything over 50).
    var accountIds = [];
    while (accountIterator.hasNext()) {
        let account = accountIterator.next();
        accountIds.push(account.getCustomerId());
    }
    if (!accountIds.length) {
        Logger.log('No accounts to process.');
        Logger.log('The dashboard is here:').log(spreadsheet.getUrl());
        return;
    }

    let parallelIds = accountIds.slice(0, 50);
    let sequentialIds = accountIds.slice(50);

    Logger.log(accountIds.length + " accounts to process");
    var params = {"spreadsheetId": spreadsheet.getId(), "sequentialIds": sequentialIds};
    MccApp.accounts().withIds(parallelIds).executeInParallel('handleAccount', 'callbackAfterParallelRun', JSON.stringify(params));
}

function runForSingleAccount(spreadsheet) {
    let results = handleAccount(null);

    let qsDataHeaderRow = [
        ['Account', 'Campaign', 'Ad group', 'Keyword', 'QS', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conversion value']
    ];
    let qsDataRows = JSON.parse(results)['qsDataRows'];
    replaceDataInSheet(spreadsheet, 'Data', qsDataHeaderRow.concat(qsDataRows));

    let qsHistoryHeaderRow = [
        ['Account', 'Campaign', 'Ad group', 'Keyword', 'Month', 'QS', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conversion value']
    ];
    let qsHistoryRows = JSON.parse(results)['qsHistoryRows'];
    replaceDataInSheet(spreadsheet, 'History', qsHistoryHeaderRow.concat(qsHistoryRows));

    Logger.log('The dashboard is here:').log(spreadsheet.getUrl());
}

function replaceDataInSheet(spreadsheet, sheetName, rows) {
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
        sheet = spreadsheet.insertSheet(sheetName);
    }
    // Clear the sheet.  
    sheet.getDataRange().clear();
    // Put all the result rows into the sheet.
    sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

function handleAccount(params) {
    let qsDataRows = collectAccountQsData();
    let qsHistoryRows = collectAccountQsHistory();

    return JSON.stringify({qsDataRows: qsDataRows, qsHistoryRows: qsHistoryRows, params: JSON.parse(params)});
}

function callbackAfterParallelRun(results) {
    var params;
    for (var i = 0; i < results.length; i++) {
        if (results[i].getStatus() == 'OK') {
            params = JSON.parse(results[i].getReturnValue())['params'];
            break;
        }
    }

    if (!params) {
        Logger.log("No accounts have been processed. Aborting ...");
        return;
    }

    var qsDataRows = [];
    var qsHistoryRows = [];

    // Collect all rows from the account results.
    for (var i = 0; i < results.length; i++) {
        if (results[i].getStatus() == "OK") {
            var accountDataRows = JSON.parse(results[i].getReturnValue())['qsDataRows'];
            if (accountDataRows.length > 0) {
                qsDataRows = qsDataRows.concat(accountDataRows);
            }
            var accountHistoryRows = JSON.parse(results[i].getReturnValue())['qsHistoryRows'];
            if (accountHistoryRows.length > 0) {
                qsHistoryRows = qsHistoryRows.concat(accountHistoryRows);
            }
        }
    }

    var qsDataHeaderRow = [
        ['Account', 'Campaign', 'Ad group', 'Keyword', 'QS', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conversion value']
    ];
    var qsHistoryHeaderRow = [
        ['Account', 'Campaign', 'Ad group', 'Keyword', 'Month', 'QS', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conversion value']
    ];

    // Store the data in the spreadsheet.
    var spreadsheet = SpreadsheetApp.openById(params['spreadsheetId']);
    replaceDataInSheet(spreadsheet, 'Data', qsDataHeaderRow.concat(qsDataRows));
    replaceDataInSheet(spreadsheet, 'History', qsHistoryHeaderRow.concat(qsHistoryRows));

    updateCalculationsSheet(spreadsheet);

    Logger.log('The dashboard is here:').log(spreadsheet.getUrl());

    // If there are still some accounts left: Handle them sequentially.
    if (params['sequentialIds'].length > 0) {
        Logger.log('');
        Logger.log("Processing " + params['sequentialIds'].length + " more accounts sequentially.");

        var accountIterator = MccApp.accounts().withIds(params['sequentialIds']).get();
        var counter = 1;
        while (accountIterator.hasNext()) {
            var account = accountIterator.next();
            runForSubAccount(account, spreadsheet, counter++);
        }

        Logger.log('');
        Logger.log('The dashboard is here:').log(spreadsheet.getUrl());
    }
}

function updateCalculationsSheet(spreadsheet) {
    // Set number of days, startDate and endDate
    spreadsheet.getSheetByName('Calculations').getRange("E18:H18").setValues([[
            config['daysToTrack'],
            Utilities.formatDate(new Date(Date.now() - config['daysToTrack'] * 1000 * 60 * 60 * 24), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd'),
            Utilities.formatDate(new Date(Date.now() - 1000 * 60 * 60 * 24), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd'),
            Utilities.formatDate(new Date(Date.now()), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd HH:mm:ss")
        ]]);
}

function runForSubAccount(account, spreadsheet, counter) {
    MccApp.select(account);
    var results = handleAccount(null);
    var qsDataRows = JSON.parse(results)['qsDataRows'];
    var qsHistoryRows = JSON.parse(results)['qsHistoryRows'];

    // Append data to the sheets.
    if (qsDataRows.length > 0) {
        let dataSheet = spreadsheet.getSheetByName('Data');
        dataSheet.getRange(dataSheet.getLastRow() + 1, 1, qsDataRows.length, qsDataRows[0].length).setValues(qsDataRows);
    }
    if (qsHistoryRows.length > 0) {
        let historySheet = spreadsheet.getSheetByName('History');
        historySheet.getRange(historySheet.getLastRow() + 1, 1, qsHistoryRows.length, qsHistoryRows[0].length).setValues(qsHistoryRows);
    }

    Logger.log(counter + ' ' + account.getName());
}

/*
 * Collects the QS data for the current account. Designed so that it works in executeInParallel().
 * @param params These parameters are simply returned later. They can be used to transfer data to a callback.
 * @return JSON encoded including 'rows' and 'params'
 */
function collectAccountQsData() {
    var accountName = AdsApp.currentAccount().getName();
    /*
     * Prepare the GAQL query to get keyword and QS data.
     */
    let cols = 'campaign.id, campaign.name, ad_group.id, ad_group_criterion.criterion_id, ad_group.name, ad_group_criterion.keyword.text, ad_group_criterion.quality_info.quality_score';
    let metrics = 'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value';
    let startDate = Utilities.formatDate(new Date(Date.now() - config['daysToTrack'] * 1000 * 60 * 60 * 24), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
    let endDate = Utilities.formatDate(new Date(Date.now() - 1000 * 60 * 60 * 24), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');

    var queryConditions = ['ad_group_criterion.quality_info.quality_score > 0', 'segments.date BETWEEN "' + startDate + '" AND "' + endDate + '"'];

    if (config['trackOnlyEnabled']) {
        queryConditions.push('campaign.status = "ENABLED"');
        queryConditions.push('ad_group.status = "ENABLED"');
        queryConditions.push('ad_group_criterion.status = "ENABLED"');
    }
    if (config['trackOnlyGoogle']) {
        queryConditions.push('segments.ad_network_type = "SEARCH"');
        cols += ', segments.ad_network_type'

    }

    var query = 'SELECT  ' + cols + ', ' + metrics + ' FROM keyword_view WHERE ' + queryConditions.join(' AND ');

    /*
     * Execute the query and go through the results, keyword by keyword.
     */
    var search = AdsApp.search(query);
    var resultRows = [];
    while (search.hasNext()) {
        let row = search.next();
        let qi = row.adGroupCriterion.qualityInfo;

        resultRows.push([
            accountName,
            row.campaign.name,
            row.adGroup.name,
            row.adGroupCriterion.keyword.text,
            qi.qualityScore,
            row.metrics.impressions,
            row.metrics.clicks,
            row.metrics.costMicros / 1000000,
            row.metrics.conversions,
            row.metrics.conversionsValue
        ]);
    }

    return resultRows;
}

function collectAccountQsHistory() {
    var accountName = AdsApp.currentAccount().getName();
    /*
     * Prepare the GAQL query to get keyword and QS data.
     */
    let cols = 'segments.month, campaign.id, campaign.name, ad_group.id, ad_group_criterion.criterion_id, ad_group.name, ad_group_criterion.keyword.text';
    let metrics = 'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value';
    let historicalQsCols = 'metrics.historical_quality_score, metrics.historical_creative_quality_score, metrics.historical_landing_page_quality_score, metrics.historical_search_predicted_ctr';

    let currentDate = new Date();
    currentDate.setMonth(currentDate.getMonth() - 12);
    currentDate.setDate(1);

    let year = currentDate.getFullYear();
    let month = ('0' + (currentDate.getMonth() + 1)).slice(-2);
    let day = ('0' + currentDate.getDate()).slice(-2);

    let startDate = year + '-' + month + '-' + day;
    let endDate = Utilities.formatDate(new Date(Date.now() - 1000 * 60 * 60 * 24), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');

    var queryConditions = ['metrics.historical_quality_score > 0', 'segments.date BETWEEN "' + startDate + '" AND "' + endDate + '"'];

    if (config['trackOnlyEnabled']) {
        queryConditions.push('campaign.status = "ENABLED"');
        queryConditions.push('ad_group.status = "ENABLED"');
        queryConditions.push('ad_group_criterion.status = "ENABLED"');
    }

    var query = 'SELECT  ' + cols + ', ' + historicalQsCols + ', ' + metrics + ' FROM keyword_view WHERE ' + queryConditions.join(' AND ');

    /*
     * Execute the query and go through the results, keyword by keyword.
     */
    var search = AdsApp.search(query);
    var resultRows = [];
    var stats = [];
    while (search.hasNext()) {
        let row = search.next();

        let key = AdsApp.currentAccount().getCustomerId() + '#' + row.campaign.id + '#' + row.adGroup.id + '#' + row.adGroupCriterion.criterionI + '#' + row.segments.month + '#' + row.metrics.historicalQualityScore;

        if (!stats.hasOwnProperty(key)) {
            stats[key] = [
                accountName,
                row.campaign.name,
                row.adGroup.name,
                row.adGroupCriterion.keyword.text,
                row.segments.month,
                row.metrics.historicalQualityScore,
                0, 0, 0, 0, 0
            ];
            stats[key][6] += row.metrics.impressions;
            stats[key][7] += row.metrics.clicks;
            stats[key][8] += row.metrics.costMicros / 1000000;
            stats[key][9] += row.metrics.conversions;
            stats[key][10] += row.metrics.conversionsValue;
        }
    }

    for (var key in stats) {
        resultRows.push(stats[key]);
    }

    return resultRows;
}

/*
 * Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created.
 * The folderName can be in the form of a complete path with subfolders, like "something/something else/whatever".
 * Returns the folder.
 */
function getOrCreateFolder(folderName) {
    return getOrCreateFolderFromArray(folderName.toString().split("/"), DriveApp.getRootFolder());
}

/*
 * Does the actual work for getOrCreateFolder.
 * Recursive function, based on an array of folder names (to handle paths with subfolders).
 */
function getOrCreateFolderFromArray(folderNameArray, currentFolder) {
    var folderName = '';
    // Skip empty folders (multiple slashes or a slash at the end).
    do {
        folderName = folderNameArray.shift();
    } while (folderName == '' && folderNameArray.length > 0);

    if (folderName == '') {
        return currentFolder;
    }

    // See if the folder is already there.
    var folderIterator = currentFolder.getFoldersByName(folderName);
    if (folderIterator.hasNext()) {
        var folder = folderIterator.next();
    } else {
        // Create folder.
        var folder = currentFolder.createFolder(folderName);
    }

    if (folderNameArray.length > 0) {
        return getOrCreateFolderFromArray(folderNameArray, folder);
    }
    return folder;
}

/*
 * Checks if there is already a Google Spreadsheet with the given description inside the folder.
 * If one is found, it gets returned. Otherwise, false is returned.
 * @param {Folder} folder
 * @param {String} description
 * @returns {Spreadsheet|Boolean}
 */
function getExistingDashboardSpreadsheet(folder, description) {
    const fileIterator = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while (fileIterator.hasNext()) {
        const file = fileIterator.next();
        if (file.getDescription() == description) {
            var spreadsheet = SpreadsheetApp.open(file);
            return spreadsheet;
        }
    }
    return false;
}

/*
 * Creates a new spreadsheet as a copy of a base spreadsheet and returns it.
 * @param {Folder} folder
 * @param {String} description
 * @param {String} newName
 * @param {String} baseSpreadsheetId
 * @returns {Spreadsheet}
 */
function createDashboardSpreadsheet(folder, description, newName, baseSpreadsheetId) {
    // The spreadsheet has not been found. Create it.
    let newFile = DriveApp.getFileById(baseSpreadsheetId).makeCopy(newName, folder).setDescription(description);
    var spreadsheet = SpreadsheetApp.open(newFile);
    // Put the account's name into the Dashboard's header and make it the active sheet.
    spreadsheet.getSheetByName('Dashboard').activate().getRange(3, 2).setValue(AdsApp.currentAccount().getName());
    return spreadsheet;
}

/*
 * Checks if there is already a Google Spreadsheet with the given description inside the folder.
 * If one is found, it gets updated and returned.
 * Otherwise, a new spreadsheet is created as a copy of a base spreadsheet and then returned.
 * @param {Folder} folder
 * @param {String} description
 * @param {String} newName
 * @param {String} baseSpreadsheetId
 * @returns {Spreadsheet}
 */
function getOrCreateSpreadsheetByDescription(folder, description, newName, baseSpreadsheetId) {
    const fileIterator = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while (fileIterator.hasNext()) {
        const file = fileIterator.next();
        if (file.getDescription() == description) {
            var spreadsheet = SpreadsheetApp.open(file);
            // Update info sheets.
            updateInfoSheets(spreadsheet, baseSpreadsheetId);
            spreadsheet.getSheetByName('Dashboard').activate();
            return spreadsheet;
        }
    }
    // The spreadsheet has not been found. Create it.
    let newFile = DriveApp.getFileById(baseSpreadsheetId).makeCopy(newName, folder).setDescription(description);
    var spreadsheet = SpreadsheetApp.open(newFile);
    // Put the account's name into the Dashboard's header and make it the active sheet.
    spreadsheet.getSheetByName('Dashboard').activate().getRange(3, 2).setValue(AdsApp.currentAccount().getName());
    return spreadsheet;
}

/*
 * Pulls the FAQ sheet from the base spreadsheet and puts it into the spreadsheet.
 * If there are additional sheets in the base spreadsheet (to the right of the FAQ sheet), those are put in as well.
 * In case the spreadsheet already has sheets with the same name, those will be replaced with the up to date versions.
 */
function updateInfoSheets(spreadsheet, baseSpreadsheetId) {
    var baseSpreadsheet = SpreadsheetApp.openById(baseSpreadsheetId);

    var faqSheet = baseSpreadsheet.getSheetByName('About + FAQ');
    insertOrReplaceSheet(spreadsheet, faqSheet);

    // Get all sheets to the right of the FAQ sheet and put them in as well.
    var faqSheetIndex = faqSheet.getIndex();
    var sheets = baseSpreadsheet.getSheets();
    for (var i in sheets) {
        var sheet = sheets[i];
        if (sheet.getIndex() > faqSheetIndex) {
            insertOrReplaceSheet(spreadsheet, sheet);
        }
    }

    return spreadsheet;
}

/*
 * Copys a sheet into a spreadsheet.
 * If a sheet with the same name exists already, it will be removed first.
 * @param {Spreadsheet} spreadsheet
 * @param {Sheet} sheet
 * @returns {Sheet} The inserted sheet.
 */
function insertOrReplaceSheet(spreadsheet, sheet) {
    let sheetName = sheet.getName();
    var oldSheet = spreadsheet.getSheetByName(sheetName);
    if (oldSheet) {
        spreadsheet.deleteSheet(oldSheet);
    }
    return sheet.copyTo(spreadsheet).setName(sheetName);
}