1
\$\begingroup\$

Scenario goes like this:

  1. User enables plugin from within Google Spreadsheet
  2. Authenticates with their Google Analytics Account
  3. They select which accounts they'd like to perform an audit on
  4. Four spreadsheets are returned with information on their account filters, view filters, goals, and view settings.

I played around with different ways to write this: creating a constructor and methods, name-spaced object, everything is a function, and finally settled on a pattern of dependency injection.

This is written in JavaScript, but Google Spreadsheets does have its own API for working with files within their code editor.

I realize that this is a lot of code, but a lot of it is static values (setting sheet headers) or the methods responsible for looping through the json that is returned from the Google Analytics Management API.

Questions:

  1. I'm trying to write my code in a way that is more testable - preferable I'd be following TDD. I figure a way to do write more testable code is to remove tightly-coupled code and replace with the dependency pattern, as I could then mock or the GA Management API. Am I correct in thinking this?

  2. My functions like factories.getViewFilterData and factories.getGoalData both loop through accounts, properties, and views. This doesn't follow DRY principals, and I believe I can break it out into it's own function; however, I'm not sure how about going about this as there are so many nested for loops, and even if I did break it out into its own function and it did return a JSON object, I'd still need to loop through it within both functions. Is there a way to break this common functionality out?

  3. Any other advice on architecture, readability, other JS patterns (yes, I've read Addy's JavaScript patterns)?

globals SpreadsheetApp, HtmlService, Logger, Analytics

// dependecies
var dependencies = {
    sheetApp: SpreadsheetApp,
    html: HtmlService,
    analytics: Analytics.Management
};

// sheet names/headers
var sheets = [{
    name: 'Filters - Account Level',
    headers: [
        'Account Name',
        'Account Id',
        'Filter Name',
        'Filter Id',
        'Filter Type',
        'Filter Field',
        'Filter MatchType',
        'Filter ExpressionValue',
        'Filter CaseSensitive',
        'Filter SearchString',
        'Filter ReplaceString',
        'Filter FieldA',
        'Filter ExtractA',
        'Filter FieldB',
        'Filter ExtractB',
        'Filter OutputToField',
        'Filter OutputConstructor',
        'Filter FieldARequired',
        'Filter FieldBRequired',
        'Filter OverrideOutputField',
        'Filter CaseSensitive'
    ]
}, {
    name: 'Filters - View Level',
    headers: [
        'Account Name',
        'Account Id',
        'Property Name',
        'Property Id',
        'View Name',
        'View Id',
        'Filter Name',
        'Filter Id'
    ]
}, {
    name: 'Goals',
    headers: [
        'Account Name',
        'Account Id',
        'Property Name',
        'Property Id',
        'View Name',
        'View Id',
        'Goal Name',
        'Goal Id',
        'Goal Type',
        'Goal Active',
        'Goal Value',
        'Goal Detail URL',
        'Goal Detail CaseSensitive',
        'Goal Detail MatchType',
        'Goal Detail FirstStepRequired',
        'Goal Detail Step 1',
        'Goal Detail Step 2',
        'Goal Detail Step 3',
        'Goal Detail Step 4',
        'Goal Detail Step 5',
        'Goal Detail Step 6',
        'Goal Detail Step 7',
        'Goal Detail Step 8',
        'Goal Detail Step 9',
        'Goal Detail Step 10',
        'Goal Detail ComparisonType',
        'Goal Detail ComparisonValue',
        'Goal Event Condition Type',
        'Goal Event Condition MatchType',
        'Goal Event Condition Expression',
        'Goal Event Condition Type',
        'Goal Event Condition MatchType',
        'Goal Event Condition Expression',
        'Goal Event Condition Type',
        'Goal Event Condition MatchType',
        'Goal Event Condition Expression',
        'Goal Event Condition Type',
        'Goal Event Condition ComparisonType',
        'Goal Event Condition ComparisonValue'
    ]
}, {
    name: 'Views',
    headers: [
        'Account Name',
        'Account Id',
        'Property Name',
        'Property Id',
        'View Name',
        'View Id',
        'Profile Website URL',
        'Profile Timezone',
        'Profile Default Page',
        'Profile Exclude Query Parameters',
        'Profile Currency',
        'Profile Site Search Query Parameters',
        'Profile Strip Site Search Query Parameters',
        'Profile Site Search Category Parameters',
        'Profile Strip Site Search Category Parameters'
    ]
}];

// helper function
function getNumberOfColumnsInSheet(sheet) {
    var columnCount, i;

    for (i = 0; i < this.sheets.length; i++) {
        if (sheet === this.sheets[i].name) {
            columnCount = this.sheets[i].headers.length;
        }
    }

    return columnCount;
}

// dependency factory
function createFactories(dp) {
    var getUi = dp.sheetApp.getUi();
    var workbook = dp.sheetApp.getActiveSpreadsheet();
    var properties = dp.analytics.Webproperties;
    var profiles = dp.analytics.Profiles;
    var viewFilters = dp.analytics.ProfileFilterLinks;
    var filters = dp.analytics.Filters;
    var goals = dp.analytics.Goals;

    return {

        buildMenu: function (name, action) {
            return getUi.createAddonMenu()
                .addItem(name, action)
                .addToUi();
        },

        showSidebar: function (templateFile, title) {
            var ui = dp.html.createTemplateFromFile(templateFile)
                .evaluate()
                .setTitle(title)
                .setSandboxMode(dp.html.SandboxMode.IFRAME);

            return getUi.showSidebar(ui);
        },

        getAccounts: function () {
            var accounts = dp.analytics.Accounts.list().getItems();

            if (!accounts) { return []; }

            return accounts.map(function (account) {
                return {
                    id: account.getId(),
                    name: account.getName()
                };
            });
        },

        addCreateSheetNames: function (callback) {
            sheets.forEach(function (sheet) {
                return workbook.getSheetByName(sheet.name) || workbook.insertSheet(sheet.name);
            });

            callback();
        },

        addHeaderRowToSheets: function () {
            sheets.forEach(function (sheet) {
                workbook.getSheetByName(sheet.name)
                    .getRange(1, 1, 1, sheet.headers.length)
                    .setValues([sheet.headers]);
            });
        },

        addDataToSheet: function (data, sheet) {
            var numOfColumns = getNumberOfColumnsInSheet(sheet);

            return workbook.getSheetByName(sheet)
                .getRange(2, 1, data.length, numOfColumns)
                .setValues(data);
        },

        removeDataFromSheets: function () {
            sheets.forEach(function (sheet) {
                workbook.getSheetByName(sheet.name)
                    .getRange(2, 1, this.getMaxRows(), this.getMaxColumns()) // this probably won't work
                    .clearContent();
            });
        },

        getViewFilterData: function (accountsList) {
            var propertiesList, profilesList, filtersList;
            var i, j, k, l;
            var data = {};
            var results = [];

            for (i = 0; i < accountsList.length; i++) {
                data.accountName = accountsList[i].name;
                data.accountId = accountsList[i].id;

                propertiesList = properties.list(data.accountId).getItems();

                for (j = 0; j < propertiesList.length; j++) {
                    data.propertyName = propertiesList[j].name;
                    data.propertyId = propertiesList[j].id;

                    profilesList = profiles.list(data.accountId, data.propertyId).getItems();

                    for (k = 0; k < profilesList.length; k++) {
                        data.viewName = profilesList[k].name;
                        data.viewId = profilesList[k].id;

                        filtersList = viewFilters.list(data.accountId, data.propertyId, data.viewId).getItems();

                        for (l = 0; l < filtersList.length; l++) {

                            data.filterName = filtersList[l].filterRef.name;
                            data.filterId = filtersList[l].filterRef.id;

                            results.push([
                                data.accountName,
                                data.accountId,
                                data.propertyName,
                                data.propertyId,
                                data.viewName,
                                data.viewId,
                                data.filterName,
                                data.filterId
                            ]);
                        }
                    }
                }
            }

            return results;
        },

        getViewData: function (accountsList) {
            var propertiesList, profilesList;
            var i, j, k;
            var data = {};
            var results = [];

            for (i = 0; i < accountsList.length; i++) {
                data.accountName = accountsList[i].name;
                data.accountId = accountsList[i].id;

                propertiesList = properties.list(data.accountId).getItems();

                for (j = 0; j < propertiesList.length; j++) {
                    data.propertyName = propertiesList[j].name;
                    data.propertyId = propertiesList[j].id;

                    profilesList = profiles.list(data.accountId, data.propertyId).getItems();

                    for (k = 0; k < profilesList.length; k++) {
                        data.viewName = profilesList[k].name;
                        data.viewId = profilesList[k].id;

                        data.profileWebsiteUrl = profilesList[k].websiteUrl;
                        data.profileTimezone = profilesList[k].timezone;
                        data.profileDefaultPage = profilesList[k].defaultPage;
                        data.profileExcludeQueryParameters = profilesList[k].excludeQueryParameters;
                        data.profileCurrency = profilesList[k].currency;
                        data.profileSiteSearchQueryParameters = profilesList[k].siteSearchQueryParameters;
                        data.profileStripSiteSearchQueryParameters = profilesList[k].stripSiteSearchQueryParameters;
                        data.profileSiteSearchCategoryParameters = profilesList[k].siteSearchCategoryParameters;
                        data.profileStripSiteSearchCategoryParameters = profilesList[k].stripSiteSearchCategoryParameters;

                        results.push([
                            data.accountName,
                            data.accountId,
                            data.propertyName,
                            data.propertyId,
                            data.viewName,
                            data.viewId,
                            data.profileWebsiteUrl,
                            data.profileTimezone,
                            data.profileDefaultPage,
                            data.profileExcludeQueryParameters,
                            data.profileCurrency,
                            data.profileSiteSearchQueryParameters,
                            data.profileStripSiteSearchQueryParameters,
                            data.profileSiteSearchCategoryParameters,
                            data.profileStripSiteSearchCategoryParameters
                        ]);
                    }
                }
            }

            return results;
        },

        getAccountFilterData: function (accountsList) {
            var filtersList, details;
            var i, j;

            var data = {};
            var results = [];
            var empty = '-';

            for (i = 0; i < accountsList.length; i++) {
                data.accountName = accountsList[i].name;
                data.accountId = accountsList[i].id;

                filtersList = filters.list(data.accountId).getItems();

                for (j = 0; j < filtersList.length; j++) {
                    data.filterType = filtersList[j].type;
                    data.filterName = filtersList[j].name;
                    data.filterId = filtersList[j].id;

                    if (data.filterType === 'EXCLUDE' || data.filterType === 'INCLUDE') {
                        details = filtersList[j].getIncludeDetails() || filtersList[j].getExcludeDetails();

                        data.filterField = details.field;
                        data.filterMatchType = details.matchType;
                        data.filterExpressionValue = details.expressionValue;
                        data.filterCaseSensitive = details.caseSensitive;

                        results.push([
                            data.accountName,
                            data.accountId,
                            data.filterName,
                            data.filterId,
                            data.filterType,
                            data.filterField,
                            data.filterMatchType,
                            data.filterExpressionValue,
                            data.filterCaseSensitive,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty
                        ]);

                    }

                    if (data.filterType === 'UPPERCASE' || data.filterType === 'LOWERCASE') {
                        details = filtersList[j].uppercaseDetails || filtersList[j].lowercaseDetails;

                        data.filterField = details.field;

                        results.push([
                            data.accountName,
                            data.accountId,
                            data.filterName,
                            data.filterId,
                            data.filterType,
                            data.filterField,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty
                        ]);

                    }

                    if (data.filterType === 'SEARCH_AND_REPLACE') {
                        details = filtersList[j].searchAndReplaceDetails;

                        data.filterField = details.field;
                        data.filterSearchString = details.searchString;
                        data.filterReplaceString = details.replaceString;
                        data.filterCaseSensitive = details.caseSensitive;

                        results.push([
                            data.accountName,
                            data.accountId,
                            data.filterName,
                            data.filterId,
                            data.filterType,
                            data.filterField,
                            empty,
                            empty,
                            data.filterCaseSensitive,
                            data.filterSearchString,
                            data.filterReplaceString,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty
                        ]);

                    }

                    if (data.filterType === 'ADVANCED') {
                        details = filtersList[j].advancedDetails;

                        data.filterFieldA = details.fieldA;
                        data.filterExtractA = details.extractA;
                        data.filterFieldB = details.fieldB;
                        data.filterExtractB = details.extractB;
                        data.filterOutputToField = details.outputToField;
                        data.filterOutputConstructor = details.outputConstructor;
                        data.filterFieldARequired = details.fieldARequired;
                        data.filterFieldBRequired = details.fieldBRequired;
                        data.filterOverrideOutputField = details.overrideOutputField;
                        data.filterCaseSensitive = details.caseSensitive;

                        results.push([
                            data.accountName,
                            data.accountId,
                            data.filterName,
                            data.filterId,
                            data.filterType,
                            data.filterField,
                            empty,
                            empty,
                            empty,
                            empty,
                            empty,
                            data.filterFieldA,
                            data.filterExtractA,
                            data.filterFieldB,
                            data.filterExtractB,
                            data.filterOutputToField,
                            data.filterOutputConstructor,
                            data.filterFieldARequired,
                            data.filterFieldBRequired,
                            data.filterOverrideOutputField,
                            data.filterCaseSensitive
                        ]);

                    }
                }
            }

            return results;
        },

        getGoalData: function (accountsList) {
            var propertiesList, profilesList, goalsList, steps, details, conditions;
            var i, j, k, l, m, n;
            var data = {};
            data.goalDetailStep = [];
            data.conditionDetail = [];
            var results = [];
            var empty = '-';

            for (i = 0; i < accountsList.length; i++) {
                data.accountName = accountsList[i].name;
                data.accountId = accountsList[i].id;

                propertiesList = properties.list(data.accountId).getItems();

                for (j = 0; j < propertiesList.length; j++) {
                    data.propertyName = propertiesList[j].name;
                    data.propertyId = propertiesList[j].id;

                    profilesList = profiles.list(data.accountId, data.propertyId).getItems();

                    for (k = 0; k < profilesList.length; k++) {
                        data.viewName = profilesList[k].name;
                        data.viewId = profilesList[k].id;

                        goalsList = goals.list(data.accountId, data.propertyId, data.viewId).getItems();

                        for (l = 0; l < goalsList.length; l++) {
                            data.goalName = goalsList[l].name;
                            data.goalId = goalsList[l].id;
                            data.goalValue = goalsList[l].value;
                            data.goalActive = goalsList[l].active;
                            data.goalType = goalsList[l].type;
                            data.goalCreated = goalsList[l].created;
                            data.goalUpdated = goalsList[l].updated;

                            if (goalsList[l].urlDestinationDetails) {
                                details = goalsList[l].urlDestinationDetails;

                                data.goalDetailUrl = details.url;
                                data.goalDetailCaseSensitive = details.caseSensitive;
                                data.goalDetailMatchType = details.matchType;
                                data.goalDetailFirstStepRequired = details.firstStepRequired;

                                steps = details.steps;

                                if (steps) {
                                    for (m = 0; m < steps.length; m++) {

                                        data.goalDetailStep.push(steps[m].url);

                                    }
                                }

                                results.push([
                                    data.accountName,
                                    data.accountId,
                                    data.propertyName,
                                    data.propertyId,
                                    data.viewName,
                                    data.viewId,
                                    data.goalName,
                                    data.goalId,
                                    data.goalType,
                                    data.goalActive,

                                    data.goalValue,
                                    data.goalDetailUrl,
                                    data.goalDetailCaseSensitive,
                                    data.goalDetailMatchType,
                                    data.goalDetailFirstStepRequired,
                                    data.goalDetailStep[0],
                                    data.goalDetailStep[1],
                                    data.goalDetailStep[2],
                                    data.goalDetailStep[3],
                                    data.goalDetailStep[4],

                                    data.goalDetailStep[5],
                                    data.goalDetailStep[6],
                                    data.goalDetailStep[7],
                                    data.goalDetailStep[8],
                                    data.goalDetailStep[9],
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,

                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty
                                ]);

                                data.goalDetailStep = [];

                            } else if (goalsList[l].visitTimeOnSiteDetails || goalsList[l].visitNumPagesDetails) {
                                details = goalsList[l].visitTimeOnSiteDetails || goalsList[l].visitNumPagesDetails;

                                data.goalDetailComparisonType = details.comparisonType;
                                data.goalDetailComparisonValue = details.comparisonValue;

                                results.push([
                                    data.accountName,
                                    data.accountId,
                                    data.propertyName,
                                    data.propertyId,
                                    data.viewName,
                                    data.viewId,
                                    data.goalName,
                                    data.goalId,
                                    data.goalType,
                                    data.goalActive,

                                    data.goalValue,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,

                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    data.goalDetailComparisonType,
                                    data.goalDetailComparisonValue,
                                    empty,
                                    empty,
                                    empty,

                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty
                                ]);

                            } else if (goalsList[l].eventDetails) {
                                details = goalsList[l].eventDetails;
                                conditions = details.eventConditions;

                                if (conditions) {
                                    for (n = 0; n < conditions.length; n++) {
                                        data.conditionType = conditions[n].type;

                                        if (data.conditionType === 'VALUE') {
                                            data.conditionComparisonType = conditions[n].comparisonType;
                                            data.conditionComparisonValue = conditions[n].comparisonValue;

                                        } else {
                                            data.conditionMatchType = conditions[n].matchType;
                                            data.conditionExpression = conditions[n].expression;
                                        }

                                        data.conditionDetail.push(
                                            data.conditionComparisonType,
                                            data.conditionMatchType,
                                            data.conditionExpression,
                                            data.conditionComparisonType,
                                            data.conditionComparisonValue
                                        );
                                    }
                                }

                                results.push([
                                    data.accountName,
                                    data.accountId,
                                    data.propertyName,
                                    data.propertyId,
                                    data.viewName,
                                    data.viewId,
                                    data.goalName,
                                    data.goalId,
                                    data.goalType,
                                    data.goalActive,

                                    data.goalValue,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,

                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    empty,
                                    data.conditionDetail[0],
                                    data.conditionDetail[1],
                                    data.conditionDetail[2],

                                    data.conditionDetail[5],
                                    data.conditionDetail[6],
                                    data.conditionDetail[7],
                                    data.conditionDetail[10],
                                    data.conditionDetail[11],
                                    data.conditionDetail[12],
                                    data.conditionDetail[15],
                                    data.conditionDetail[18],
                                    data.conditionDetail[19]
                                ]);

                                data.conditionDetail = [];

                            }
                        }
                    }
                }
            }

            return results;
        }
    };
}

// assign factories
var factories = createFactories(dependencies);

// this is a built-in function for google spreadsheets, so on open of the spreadsheet, create menu
function onOpen() {
    factories.buildMenu('Select Accounts', 'showSidebar');
}

// called from onOpen
function showSidebar() {
    factories.showSidebar('index', 'Google Analytics Audit');
}

// called from another file, js.html within plugin project
function getAccounts() {
    return factories.getAccounts();
}

function doAuditOfAccounts(accounts) {

    factories.addCreateSheetNames(function () {
        factories.addHeaderRowToSheets();
            // add remove data here? would need to add callback fn to removeData
    });

    // create a loop to do this for me

    try {
        var viewLevelFilters = factories.getViewFilterData(accounts);
        var viewSettings = factories.getViewData(accounts);
        var accountLevelFilters = factories.getAccountFilterData(accounts);
        var goals = factories.getGoalData(accounts);

        factories.addDataToSheet(viewLevelFilters, 'Filters - View Level');
        factories.addDataToSheet(viewSettings, 'Views');
        factories.addDataToSheet(accountLevelFilters, 'Filters - Account Level');
        factories.addDataToSheet(goals, 'Goals');
    } catch (error) {
        Logger.log(error);
    }

}

// called from another file, js.html within plugin project
function prepareIncomingAccounts(accounts) {
    var accountsObj = accounts.map(function (account) {
        var value = account.value.split('|');
        return {
            name: value[0],
            id: value[1]
        };
    });

    doAuditOfAccounts(accountsObj);
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.