Kratu Report

Stay organized with collections Save and categorize content based on your preferences.

Reports icon

Kratu Report is a manager account-level reporting script that shows several performance signals for each account visually as a heat map. It is a simplified version of the same-named Google Ads tool that lets you calculate a score for each client account based on configurable signals.

Spreadsheet screenshot

How it works

The script maintains information about the current and past reports in a configuration spreadsheet consisting of the following sheets:

Signals
Contains a list of account signals that are downloaded and shown in the reports. You can add or remove signals here, as long as you maintain the following format.
  • Signal: Name of the signal as per customer report.
  • Display Name: Name for this signal displayed in the report.
  • Include In Report (Yes/No): Whether to include this signal in the report.
  • Type (String/Number): Type of this signal. Only numbers can be used in the formula for calculation.
  • Direction (High/Low): Defines if high or low values of the signals are better (for example, high values are better for Clicks while low values are better for AverageCpc).
  • Format: How to format the signal values.
  • Weight: Weight of the signal when calculating the account score. Each Signal value is multiplied by its weight and added for the overall score.
  • Min, Max: Minimum and maximum signal values for normalization. Each signal value is normalized to a range between 0 and 1 using the values in Min and Max. If the signal value is outside this range, it is set to 0 or 1, respectively.
Settings
Contains a list of settings the script uses when creating a report. Column D explains the meaning of each setting. There are three types of settings:
  • String: See comments for allowed values.
  • Number: A valid number has to be specified as a value.
  • Color: Uses the background color of Column C as a value.
Template
Template sheet for creating new accounts. Feel free to change colors and fonts, but don't change the structure of the template which can lead to unexpected errors if you don't modify the code accordingly.
History
This is where the script stores temporary information about all reports created in the past. Don't edit this sheet.
Accounts
This is where the script stores temporary information about the accounts processed in the current report. Don't edit this sheet.
Parameters (hidden)
This sheet defines data validation parameters and should not be edited.

The following table shows at what time changes are allowed.

Sheet Changes allowed During report processing
Signals Yes No
Settings Yes No
Template Format only No
History No No
Accounts No No
Parameters No No

Each time a new report is generated, the script creates a report spreadsheet based on the template. It gathers all client accounts under the manager account and starts processing these one by one. For each account, it retrieves the specified signals, calculates the overall score based on the given weights, and writes a line to the spreadsheet. If the specified limit of accounts per execution is reached, the script stops. During the next execution, it picks up the missing accounts and continues until all accounts are processed.

Setup

  • Set up a spreadsheet-based script with the source code below. Make a copy of this sample configuration spreadsheet.
  • Create a new script with the source code below.
  • Update SPREADSHEET_URL in your code to your copy of the configuration spreadsheet.
  • Review the Settings sheet and update the settings according to your needs. We provided default values for you, but we recommend reviewing at least ReportPeriod, ReportFrequency, and NumAccountsProcess.
  • Schedule the script to run Hourly.

Scheduling

You can schedule this script on an hourly basis, and define how many accounts to process during each execution in order to avoid execution limits. As long as there are unprocessed client accounts left for the current report, the script will continue to process these accounts. When the script finishes and the latest report is older than a specified period (for example, a week or a month), the script creates a new report based on the specified template and starts processing client accounts from the beginning.

Source code

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Kratu
 *
 * @overview The Kratu script is a flexible MCC-level report showing several
 *     performance signals for each account visually as a heat map. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/kratu
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.0
 *
 * @changelog
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.0.2
 *   - Fixed bug with run frequency to allow the script to run daily.
 * - version 1.0.1
 *   - Added validation for external spreadsheet setup.
 *   - Updated reporting version to v201609.
 * - version 1.0
 *   - Released initial version.
 */

const CONFIG = {
  // URL to the main / template spreadsheet
  SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL'
};

/**
 * Configuration to be used for running reports.
 */
const REPORTING_OPTIONS = {
  // Comment out the following line to default to the latest reporting version.
  apiVersion: 'v11'
};

let signalManager;
let spreadsheetManager;
let settingsManager;

/**
 * Main method, coordinate and trigger either new report creation or continue
 * unfinished report.
 */
function main() {
  validateConfig();
  signalManager = new SignalManager();
  spreadsheetManager = new SpreadsheetManager();
  settingsManager = new SettingsManager();
  init();

  if (spreadsheetManager.hasUnfinishedRun()) {
    continueUnfinishedReport();
  } else {
    const reportFrequency = settingsManager.getSetting('ReportFrequency', true);
    const lastReportStart = spreadsheetManager.getLastReportStartTimestamp();

    if (!lastReportStart ||
        dayDifference(lastReportStart, getTimestamp()) >= reportFrequency) {
      startNewReport();
    } else {
      log('Nothing to do');
    }
  }
}

/**
 * Initialization procedures to be done before anything else.
 */
function init() {
  spreadsheetManager.readSignalDefinitions();
  settingsManager.readSettings();
}

/**
 * Continues an unfinished report. This happens whenever there are accounts
 * that are not processed within the last report. This method picks these
 * up, processes them and marks the report as completed if no accounts are
 * left.
 */
function continueUnfinishedReport() {
  log(`Continuing unfinished report: ` +
    `${spreadsheetManager.getCurrentRunSheet().getUrl()}`);

  const iterator = spreadsheetManager.getUnprocessedAccountIterator();
  let processed = 0;
  while (iterator.hasNext() &&
    processed++ < settingsManager.getSetting('NumAccountsProcess', true)) {

    const account = iterator.next();
    processAccount(account);
  }

  writeAccountDataToSpreadsheet();

  if (processed > 0 && spreadsheetManager.allAccountsProcessed()) {
    log('All accounts processed, marking report as complete');

    // Remove protection from sheets, allow changes again
    spreadsheetManager.removeProtection();

    spreadsheetManager.markRunAsProcessed();
    sendEmail();
  }

  log(`Processed ${processed} accounts`);
}

/**
 * Creates a new report by copying the report template to a new spreadsheet,
 * gathering all accounts under the MCC and mark them as not processed.
 * Please note that this method will not actually process any accounts.
 */
function startNewReport() {
  log('Creating new report');

  // Protect the sheets that shouldn't be changed during execution
  spreadsheetManager.setProtection();

  // Delete all account info
  spreadsheetManager.clearAllAccountInfo();

  // Iterate over accounts
  const accountSelector = AdsManagerApp.accounts();
  const accountLabel = settingsManager.getSetting('AccountLabel', false);
  if (accountLabel) {
    const labelIterator = AdsApp.labels().withCondition(`label.name = "${accountLabel}"`).get();
    if (labelIterator.hasNext()) {
      const label = labelIterator.next();
      accountSelector.withCondition(`customer_client.applied_labels CONTAINS ANY ('${label.getResourceName()}')`);
    } else {
      throw `Could not find label name ${accountLabel}`;
    }
  }
  const accountIterator = accountSelector.get();

  for (const account of accountIterator) {
    log(`Adding account: ${account.getCustomerId()}`);

    spreadsheetManager.addAccount(account.getCustomerId());
  }

  // Now add the run
  const newRunSheet = spreadsheetManager.addRun();
  log(`New report created at ${newRunSheet.getUrl()}`);
}

/**
 * Processes a single account.
 *
 * @param {object} account the Google Ads account object
 */
function processAccount(account) {
  log(`- Processing ${account.getCustomerId()}`);
  AdsManagerApp.select(account);
  signalManager.processAccount(account);

  spreadsheetManager.markAccountAsProcessed(account.getCustomerId());
}

/**
 * After processing & gathering data for all accounts,
 * write it to the spreadsheet.
 */
function writeAccountDataToSpreadsheet() {
  const accountInfos = signalManager.getAccountInfos();

  spreadsheetManager.writeHeaderRow();

  for (const accountInfo of accountInfos) {
    spreadsheetManager.writeDataRow(accountInfo);
  }
}

/**
 * Sends email if an email was provided in the settings.
 * Otherwise does nothing.
 */
function sendEmail() {
  const recipientEmail = settingsManager.getSetting('RecipientEmail', false);

  if (recipientEmail) {
    MailApp.sendEmail(recipientEmail,
      'Kratu Report is ready',
      spreadsheetManager.getCurrentRunSheet().getUrl());
    log(`Email sent to ${recipientEmail}`);
  }
}

/**
 * Returns the number of days between two timestamps.
 *
 * @param {number} time1 the newer (more recent) timestamps
 * @param {number} time2 the older timestamps
 * @return {number} number of full days between the given dates
 */
function dayDifference(time1, time2) {
  return parseInt((time2 - time1) / (24 * 3600 * 1000));
}

/**
 * Returns the current timestamp.
 *
 * @return {number} the current timestamp
 */
function getTimestamp() {
  return new Date().getTime();
}

/**
 * Module for calculating account signals and infos to be shown in the report.
 */
class SignalManager {

  constructor() {
    this.accountInfos = new Array();
  }
  /**
   * Processes one account, which in 2 steps adds an accountInfo object
   * to the list.
   * - Calculate the raw signals
   * - Postprocess the raw signals (normalize scores, ...)
   *
   * @param {object} account the Google Ads account object
   */
  processAccount(account) {
    const rawSignals = this.calculateRawSignals(account);

    const accountInfo = {
      account: account,
      rawSignals: rawSignals
    };

    this.processSignals(accountInfo);

    this.accountInfos.push(accountInfo);
  }

  /**
   * Returns an array of all processed accounts so far. These are ordered by
   * decreasing score.
   *
   * @return {object} array of the accountInfo objects
   */
  getAccountInfos() {
    this.accountInfos.sort(function(a, b) {
      return b.score - a.score;
    });

    return this.accountInfos;
  }

  /**
   * Normalizes a raw signal value based in the signal's definition
   * (min, max values).
   *
   * @param {object} signalDefinition definition of the signal
   * @param {number} value numeric value of that signal
   * @return {number} the normalized value
   */
  normalize(signalDefinition, value) {
    const min = signalDefinition.min;
    const max = signalDefinition.max;

    if (signalDefinition.direction == 'High') {
      if (value >= max)
        return 1;
      if (value <= min)
        return 0;

      return (value - min) / (max - min);
    } else if (signalDefinition.direction == 'Low') {
      if (value >= max)
        return 0;
      if (value <= min)
        return 1;

      return 1 - ((value - min) / (max - min));
    } else {
      return value;
    }
  }

  /**
   * Post-processes the raw signals.
   *
   * @param {object} accountInfo the object storing all info about that account
   *                 (including raw signals)
   */
  processSignals(accountInfo) {
    const signalDefinitions = spreadsheetManager.getSignalDefinitions();
    const sumWeights = spreadsheetManager.getSumWeights();
    let sumScore = 0;

    accountInfo.signals = {};

    for (const signalDefinition of signalDefinitions) {
      if (signalDefinition.includeInReport == 'Yes') {
        const value = accountInfo.rawSignals[signalDefinition.name];

        accountInfo.signals[signalDefinition.name] = {
          definition: signalDefinition,
          value: value,
          displayValue: value
        };

        if (signalDefinition.type == 'Number') {
          const normalizedValue = normalize(signalDefinition, value);
          const signalScore = normalizedValue * signalDefinition.weight;
          sumScore += signalScore;

          accountInfo.signals[signalDefinition.name].normalizedValue =
            normalizedValue;
          accountInfo.signals[signalDefinition.name].signalScore = signalScore;
        }
      }
    }

    accountInfo.scoreSum = sumScore;
    accountInfo.scoreWeights = sumWeights;
    accountInfo.score = sumScore / sumWeights;
  }

  /**
   * Calculate the raw signals.
   *
   * @param {object} account the Google Ads account object
   * @return {object} an associative array containing raw signals
   *                  (as name -> value pairs)
   */
  calculateRawSignals(account) {
    // Use reports for signal creation, dynamically create an AWQL query here
    const signalDefinitions = spreadsheetManager.getSignalDefinitions();

    const signalFields = [];
    for (const signalDefinition of signalDefinitions) {
      signalFields.push(signalDefinition.name);
    }

    const query = `SELECT ${signalFields.join(',')}` +
                  ` FROM customer DURING ` +
                  `${settingsManager.getSetting('ReportPeriod', true)}`;

    const report = AdsApp.report(query, REPORTING_OPTIONS);
    const rows = report.rows();

    // analyze the rows (should be only one)
    const rawSignals = {};
    for (const row of rows) {

      for (const signalDefinition of signalDefinitions) {

        let value = row[signalDefinition.name];
        if (value.indexOf('%') > -1) {
          value = parseFloat(value) / 100.0;
        }

        rawSignals[signalDefinition.name] = value;
      }

    }

    return rawSignals;
  }
}

/**
 * Module for interacting with the spreadhsheets. Offers several
 * functions that other modules can use when storing / retrieving data
 * In general, there are two spreadsheets involved:
 * - a main spreadsheet containing processing information, settings
 *   and a template for the reports
 * - a report spreadsheet for each run (one loop over all accounts)
 */
class SpreadsheetManager {

  constructor() {
    this.spreadsheet = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
    this.currentRunSheet = null;
    this.accountsTab = this.spreadsheet.getSheetByName('Accounts');
    this.historyTab = this.spreadsheet.getSheetByName('History');
    this.signalsTab = this.spreadsheet.getSheetByName('Signals');
    this.settingsTab = this.spreadsheet.getSheetByName('Settings');
    this.templateTab = this.spreadsheet.getSheetByName('Template');
    this.processedAccounts = 0;
    this.signalDefinitions = [];
    this.sumWeights = 0;
  }

  /**
   * Adds protection and notes to all sheets that should not be
   * changed while a report is being processed.
   */
  setProtection() {
    this.setSheetProtection(this.signalsTab);
    this.setSheetProtection(this.settingsTab);
    this.setSheetProtection(this.templateTab);
  }

  /**
   * Adds protection and notes to a sheet / tab.
   *
   * @param {!object} tab the sheet to add protection to
   */
  setSheetProtection(tab) {
    const protection = tab.protect().setDescription(tab.getName() +
                       ' Protection');

    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
    tab.getRange('A1').setNote('A report is currently being executed, ' +
                       'you can not edit this sheet until it is finished.');
  }

  /**
   * Adds a protection and notes to all sheets that should not be
   * changed while a report is being processed.
   */
  removeProtection() {
    this.removeSheetProtection(this.signalsTab);
    this.removeSheetProtection(this.settingsTab);
    this.removeSheetProtection(this.templateTab);
  }

  /**
   * Remove the protection from a sheet / tab.
   *
   * @param {!object} tab the sheet to remove protection from
   */
  removeSheetProtection(tab) {
    const protection = tab.getProtections(
        SpreadsheetApp.ProtectionType.SHEET)[0];
    if (protection && protection.canEdit()) {
      protection.remove();
    }
    tab.clearNotes();
  }

  /**
   * Reads and returns the range of settings in the main spreadsheet.
   *
   * @return {object} the range object containing all settings
   */
  readSettingRange() {
    return this.settingsTab.getRange(2, 1, this.settingsTab.getLastRow(), 3);
  }

  /**
   * Read and return the signal definitions as defined in the Signals tab
   * of the general spreadsheet. See below for how a signal definition object
   * looks like.
   *
   * @return {!object} an array of signal definition objects
   */
  readSignalDefinitions() {
    this.signalDefinitions = new Array();

    const range = this.signalsTab.getRange(
        2, 1, this.signalsTab.getLastRow(), 9);
    const values = range.getValues();
    for (let i = 0; i < range.getNumRows(); i++) {
      if (values[i][0] == '')
        continue;

      const signalDefinition = {
        name: values[i][0],
        displayName: values[i][1],
        includeInReport: values[i][2],
        type: values[i][3],
        direction: values[i][4],
        format: values[i][5],
        weight: values[i][6],
        min: values[i][7],
        max: values[i][8]
      };

      this.signalDefinitions.push(signalDefinition);
    }

    this.calculateSumWeights();

    log(`Using ${this.signalDefinitions.length} signals`);
  }

  /**
   * Returns an array of signal definitions to work with.
   *
   * @return {object} array of signal definitions to work with
   */
  getSignalDefinitions() {
    return this.signalDefinitions;
  }

  /**
   * Returns the sum of weights of all signal definitions
   *
   * @return {number} sum of weights of all signal definitions
   */
  getSumWeights() {
    return this.sumWeights;
  }

  /**
   * Calculates the overall sum of score weights for normalization of the score.
   */
  calculateSumWeights() {
    this.sumWeights = 0;

    for (const signalDefinition of this.signalDefinitions) {
      if (signalDefinition.type == 'Number' &&
          signalDefinition.includeInReport == 'Yes') {
       this.sumWeights += signalDefinition.weight;
      }
    }
  }

  /**
   * Adds a "run" (loop over all accounts) to the general spreadsheet.
   *
   * @return {!object} the spreadsheet
   */
  addRun() {
    // use formatted date in spreadsheet name and date cell
    const timezone = AdsApp.currentAccount().getTimeZone();
    const formattedDate = Utilities.formatDate(new Date(),
                          timezone, 'MMM dd, yyyy');

    const runSpreadsheet = this.spreadsheet.copy(this.spreadsheet.getName() +
                          ' - ' + formattedDate);

    runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName('Accounts'));
    runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName('History'));
    runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName('Settings'));
    runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName('Parameters'));
    runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName('Signals'));
    runSpreadsheet.getSheetByName('Template').setName('Report');
    this.removeSheetProtection(runSpreadsheet.getSheetByName('Report'));

    this.historyTab.appendRow([getTimestamp(), null, runSpreadsheet.getUrl()]);
    this.historyTab.getRange(
        this.historyTab.getLastRow(), 1, 1, 3).clearFormat();

    runSpreadsheet.getRangeByName('AccountID').setValue(
      AdsApp.currentAccount().getCustomerId());
    runSpreadsheet.getRangeByName('Date').setValue(formattedDate);

    return runSpreadsheet;
  }

  /**
   * Checks if there is an unfinished (=not all accounts processed yet)
   * report in the run history list.
   *
   * @return {boolean} whether there is an unfinished report
   */
  hasUnfinishedRun() {
    const lastRow = this.historyTab.getLastRow();

    // has no run at all
    if (lastRow == 1) {
      return false;
    }

    const lastRunEndDate = this.historyTab.getRange(
        lastRow, 2, 1, 1).getValue();
    if (lastRunEndDate) {
      return false;
    }

    return true;
  }

  /**
   * Marks the current report (a.k.a run) as finished by adding an end date.
   */
  markRunAsProcessed() {
    const lastRow = this.historyTab.getLastRow();
    if (lastRow > 1) {
      this.historyTab.getRange(lastRow, 2, 1, 1).setValue(getTimestamp());
    }
  }

  /**
   * Returns the start timestamp of the last unfinished report.
   *
   * @return {number} the timestamp of the last unfinished report (null if
   *                  there is none)
   */
  getLastReportStartTimestamp() {
    const lastRow = this.historyTab.getLastRow();
    if (lastRow > 1) {
      return this.historyTab.getRange(lastRow, 1, 1, 1).getValue();
    } else {
      return null;
    }
  }

  /**
   * Returns the current run sheet to be used for report generation.
   * This is always the last one in the History tab of the general sheet.
   *
   * @return {object} the current run sheet
   */
  getCurrentRunSheet() {
    if (this.currentRunSheet != null)
      return this.currentRunSheet;

    const range = this.historyTab.getRange(
        this.historyTab.getLastRow(), 3, 1, 1);
    const url = range.getValue();
    this.currentRunSheet = SpreadsheetApp.openByUrl(url);
    return this.currentRunSheet;
  }

  /**
   * Adds an account to the list of 'known' accounts.
   *
   * @param {string} cid the cid of the account
   */
  addAccount(cid) {
    this.accountsTab.getRange(
        this.accountsTab.getLastRow(), 1, 1, 2).clearFormat();
  }

  /**
   * Marks an account as processed in the general sheet. Like this,
   * the script can be executed several times and will always
   * run for a batch of unprocessed accounts.
   *
   * @param {string} cid the customer id of the account that has been processed
   */
  markAccountAsProcessed(cid) {
    const range = this.accountsTab.getRange(
        2, 1, this.accountsTab.getLastRow() - 1, 2);

    const values = range.getValues();
    for (let i = 0; i < range.getNumRows(); i++) {
      const rowCid = values[i][0];
      if (cid == rowCid) {
        this.accountsTab.getRange(i + 2, 2).setValue(getTimestamp());
        this.processedAccounts++;
      }
    }

  }

  /**
   * Clears the list of 'known' accounts.
   */
  clearAllAccountInfo() {
    const lastRow = this.accountsTab.getLastRow();

    if (lastRow > 1) {
      this.accountsTab.deleteRows(2, lastRow - 1);
    }
  }

  /**
   * Creates a selector for the next batch of accounts that are not
   * processed yet.
   *
   * @return {object} a selector that can be used for parallel processing or
   *                  getting an iterator
   */
  getUnprocessedAccountIterator() {
    const accounts = this.getUnprocessedAccounts();

    const selector = AdsManagerApp.accounts().withIds(accounts);
    const iterator = selector.get();
    return iterator;
  }

  /**
   * Reads and returns the next batch of unprocessed accounts from the general
   * spreadsheet.
   *
   * @return {object} an array of unprocessed cids
   */
  getUnprocessedAccounts() {
    const accounts = [];

    const range = this.accountsTab.getRange(
        2, 1, this.accountsTab.getLastRow() - 1, 2);

    for (let i = 0; i < range.getNumRows(); i++) {
      const cid = range.getValues()[i][0];
      const processed = range.getValues()[i][1];

      if (processed != '' || accounts.length >=
              settingsManager.getSetting('NumAccountsProcess', true)) {
        continue;
      }

      accounts.push(cid);
    }

    return accounts;
  }

  /**
   * Scans the list of accounts and returns true if all of them
   * are processed.
   *
   * @return {boolean} true, if all accounts are processed
   */
  allAccountsProcessed() {
    const range = this.accountsTab.getRange(
        2, 1, this.accountsTab.getLastRow() - 1, 2);

    for (let i = 0; i < range.getNumRows(); i++) {
      const processed = range.getValues()[i][1];

      if (processed) {
        continue;
      }

      return false;
    }

    return true;
  }

  /**
   * Writes the data headers (signal names) in the current run sheet.
   */
  writeHeaderRow() {
    const sheet = this.getCurrentRunSheet();
    const reportTab = sheet.getSheetByName('Report');

    const row = [''];
    for (const signalDefinition of this.signalDefinitions) {
      if (signalDefinition.includeInReport == 'Yes') {
        row.push(signalDefinition.displayName);
      }
    }
    row.push('Score');

    const range = reportTab.getRange(4, 1, 1, row.length);
    range.setValues([row]);
    range.clearFormat();
    range.setFontWeight('bold');
    range.setBackground('#38c');
    range.setFontColor('#fff');
  }

  /**
   * Writes a row of data (signal values) in the current run sheet.
   *
   * @param {object} accountInfo the accountInfo object containing the
   *                 calculated signals
   */
  writeDataRow(accountInfo) {
    // prepare the data
    const sheet = this.getCurrentRunSheet();
    const tab = sheet.getSheetByName('Report');

    const row = [''];
    for (const signalDefinition of this.signalDefinitions) {
      if (signalDefinition.includeInReport == 'Yes') {
        const displayValue =
             accountInfo.signals[signalDefinition.name].displayValue;

        row.push(displayValue);
      }
    }
    row.push(accountInfo.score);

    // write it
    tab.appendRow(row);

    // now do the formatting
    const currentRow = tab.getLastRow();
    const rowRange = tab.getRange(currentRow, 1, 1, row.length);
    rowRange.clearFormat();

    // arrays for number formats and colors, first fill them with values
    // and later apply to the row
    const dataRange = tab.getRange(currentRow, 2, 1, row.length - 1);
    const fontColors = [[]];
    const backgroundColors = [[]];
    const numberFormats = [[]];
    let colIndex = 0;

    for (const signalDefinition of this.signalDefinitions) {
      if (signalDefinition.includeInReport == 'Yes') {
        const value = accountInfo.signals[signalDefinition.name].value;
        const normalizedValue =
              accountInfo.signals[signalDefinition.name].normalizedValue;

        let colors = [2];
        if (signalDefinition.type == 'Number') {
          numberFormats[0][colIndex] = signalDefinition.format;
          colors = getNumberColors(normalizedValue);
        } else if (signalDefinition.type == 'String') {
          colors = getStringColors(value);
        }

        fontColors[0][colIndex] = colors[0];
        backgroundColors[0][colIndex] = colors[1];

        colIndex++;
      }
    }

    // formatting for the score (last column)
    numberFormats[0][colIndex] = '0.00%';
    const scoreColors = getNumberColors(accountInfo.score);
    fontColors[0][colIndex] = scoreColors[0];
    backgroundColors[0][colIndex] = scoreColors[1];

    // now actually apply the formats
    dataRange.setNumberFormats(numberFormats);
    dataRange.setFontColors(fontColors);
    dataRange.setBackgroundColors(backgroundColors);
  }

  /**
   * Helper method for creating the array of colors based on the given
   * setting names.
   *
   * @param {string} settingFontColor name of the setting to use as font color
   * @param {string} settingBackgroundColor name of the setting to use as
   *                                        background color
   * @return {!object} an array with the colors to apply
   *                 (index 0 -> font color, index 1 -> background color)
   */
  getColors(settingFontColor, settingBackgroundColor) {
     const colors = [];

     colors[0] = settingsManager.getSetting(settingFontColor, false);
     colors[1] = settingsManager.getSetting(settingBackgroundColor, false);

     return colors;
  }

  /**
   * Helper method for returning the "string" colors for a certain value.
   *
   * @param {string} stringValue the value of the cell
   * @return {!object} an array with the colors to apply
   *                 (index 0 -> font color, index 1 -> background color)
   */
  getStringColors(stringValue) {
     return getColors('StringFgColor', 'StringBgColor');
  }

  /**
   * Helper method for applying the "number" format to a certain range.
   * Numeric value cells have different formats depending on their score value
   * (defined by the settings), this method applies these formats.
   *
   * @param {number} numericValue the value of the cell
   * @return {!object} an array with the colors to apply
   *                 (index 0 -> font color, index 1 -> background color)
   */
  getNumberColors(numericValue) {
    const level1MinValue = settingsManager.getSetting('Level1MinValue', false);
    const level2MinValue = settingsManager.getSetting('Level2MinValue', false);
    const level3MinValue = settingsManager.getSetting('Level3MinValue', false);
    const level4MinValue = settingsManager.getSetting('Level4MinValue', false);
    const level5MinValue = settingsManager.getSetting('Level5MinValue', false);

    if (level5MinValue && numericValue > level5MinValue) {
      return getColors('Level5FgColor', 'Level5BgColor');
    } else if (level4MinValue && numericValue > level4MinValue) {
      return getColors('Level4FgColor', 'Level4BgColor');
    } else if (level3MinValue && numericValue > level3MinValue) {
      return getColors('Level3FgColor', 'Level3BgColor');
    } else if (level2MinValue && numericValue > level2MinValue) {
      return getColors('Level2FgColor', 'Level2BgColor');
    } else if (level1MinValue && numericValue > level1MinValue) {
      return getColors('Level1FgColor', 'Level1BgColor');
    }

    // if no level reached, no coloring
    const defaultColors = [null, null];
    return defaultColors;
  }
}

/**
 * Module responsible for maintaining a list of common settings. These
 * settings are read from the general spreadsheet (using the
 * spreadsheetManager) and are then retrieved by other modules during
 * processing.
 */
class SettingsManager {

  constructor() {
    this.settings = [];
  }

  /**
   * Reads the settings from the general spreadsheet.
   */
  readSettings() {
    const settingsRange = spreadsheetManager.readSettingRange();

    for (let i = 1; i <= settingsRange.getNumRows(); i++) {
      const key = settingsRange.getCell(i, 1).getValue();
      const type = settingsRange.getCell(i, 2).getValue();
      let value = settingsRange.getCell(i, 3).getValue();

      if (type == 'Color') {
       value = settingsRange.getCell(i, 3).getBackground();
      }

      if (!key || !value) {
        continue;
      }

      const setting = {
        key: key,
        type: type,
        value: value
      };

      this.settings.push(setting);
    }

    log(`Read ${this.settings.length} settings`);
  }

  /**
   * Returns the value of a particular setting.
   *
   * @param {string} key the name of the setting
   * @param {boolean} mandatory flag indicating this is a mandatory setting
   *                            (has to return a value)
   * @return {object} the value of the setting
   */
  getSetting(key, mandatory) {
    for (const setting of this.settings) {
      if (setting.key == key && setting.value)
        return setting.value;
    }

    if (mandatory) {
      throw `Setting '${key}' is not set!`;
    }

    return null;
  }
}

/**
 * Wrapper for console.log.
 *
 * @param {string} t The text to log
 */
function log(t) {
  console.log(t);
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateConfig() {
  if (CONFIG.SPREADSHEET_URL == 'YOUR_SPREADSHEET_URL') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
}