Inventory-based Ad Management

If you market several products online, you may have several ad management tasks you need to perform on a regular basis to keep your advertising strategy in sync with your sales and marketing strategy:

  • You need to pause ads for a particular product when the product is out of stock, and resume advertising when you have more stock available.
  • You’re planning a discount sale for a product, and would like to run ads that display the discount information and coupon codes to potential customers online.
  • You want to promote a particular product to mobile users by bidding higher on the mobile platform, if your customers are more likely to purchase on mobile than desktop.

These tasks tend to consume a lot of time when done manually, since

  • The inventory data is usually stored in a database on your application server and tends to change throughout the day, making it difficult to keep track of the inventory details and manually update the ads.
  • As the number of products that you advertise increases, the number of ads you need to manage grows to a point where manual tracking becomes difficult or impossible.

The inventory-based ad management script helps you automate some of these tasks.

Similar use cases

Even though the script is written with a product and inventory stock in mind, you could use the script to fit several similar use cases:

  • You run multiple online courses, and want to control your advertising strategy based on the number of seats left for signup.
  • You run a group of hospitals and clinics, and want to advertise the waiting time in minutes for seeing a doctor in each location.
  • You run a tour trip and want to advertise based on the number of seats left, as well as whether the user is searching from a mobile device or not. This assumes that a user searching from a mobile device during your hours of operation is most likely looking to book a ticket immediately, and it makes sense to bid higher for that user.

Script capabilities

The inventory-based ad management script can perform the following tasks:

  • Pull inventory stock details from a remote database or a Google Cloud SQL database into a Google spreadsheet.
  • Pull report stats for various products into the Google spreadsheet.
  • Based on rules specified on the Google spreadsheet,
    • Enable or disable product-specific ad groups.
    • Apply a mobile bid modifier to a product-specific ad group.
    • Create ads in product-specific ad groups for discounts.
    • Dynamically update discount ads with the latest discount percentages and coupon information.

In addition, Google Spreadsheet formulas may specify additional conditions to define the discount percentage, mobile bid modifier, when to turn the discount on or off, etc., based on the inventory stock details.

Prerequisites

The inventory-based ad management script makes some assumptions about the structure of your account and your products:

  • Each product should have a unique code. The script uses the code to identify the ad group associated with the product, sync inventory information from the remote database to the spreadsheet, and other purposes.
  • Each product has a corresponding ad group that contains all the ads for that product.
  • The product-specific ad group has been labelled with a Google Ads label whose text matches the product code.
  • The inventory stock information is available in a remote SQL database or a Google Cloud SQL database. The script has access to this database. See database setup instructions below.

How it works

The following diagram shows the major interactions between the script and the various components.

The script starts by pulling inventory details from the database, and stats from Google Ads reports. It then uploads these values to the spreadsheet for matching product codes in the spreadsheet. This causes the spreadsheet to re-evaluate the formulas, and product rules are updated. The script then downloads the updated product rules from the spreadsheet, and updates the ads and bids based on those rules. The ad customizer feed is updated with updated placeholder values from both the database and spreadsheet, so that the ads get served with new values. Each of these interactions is explained in detail below.

Retrieving stock information from database

The inventory-based ad management script retrieves the inventory information for your products by connecting to a SQL database on your server or a Google Cloud SQL database. It then stores this data in a Google Spreadsheet. The product code is used to match details from the database with rows in the Google spreadsheet.

For the sake of simplicity, the script assumes the database has a table named items, with the following schema:

Field Name Field type Meaning
code string The unique product code.
stock number The inventory stock details for this item.

The script runs the following query on the remote database and loads the results into the Stats tab of the spreadsheet.

Select code, stock from items;

It's fine for your database to have a different schema; you just need to modify the query such that the query data set has the schema mentioned above.

Retrieving stats for products

The script retrieves the list of all products from the spreadsheet, along with their associated ad groups. It then runs an ad group performance report for last 7 days. The results are loaded into the Stats tab of the spreadsheet.

Reading the product rules

The script reads ad management rules from the Rules tab of the spreadsheet. Each row from the spreadsheet is interpreted as an ad management rule for a particular product, identified by the product code. Each row has the following columns:

Row name Meaning Comments
Code The product code. Your spreadsheet should contain only one row per product code. If there are multiple rows for a product code, the script will raise an exception.  
Stock Product stock. This information is synced from the remote database.  
Name A friendly name for the product. To refer to this field in your ad text, use the notation {=Discount.Name}
Enable Rule? Select Yes to enable this rule, No to disable it.  
Enable Ads? Select Yes to enable ads for this product, No to disable ads.

Specify a formula of the form =IF(Stats!C4 > 0, "Yes", "No") in this cell to pause or enable ads based on product availability.

Enable Discounts? Select Yes to enable discount ads for this product, No to disable discount ads.
If this property is set to Yes, the script will scan for ads labelled Discount. If no such ads are found, the script will create a default ad for discounts.		 You may specify a different label for Discount Ads using the DISCOUNT_LABEL_NAME configuration key. See the configuration section.
Discount percentage The discount percentage. To refer to this field in your ad text, use the notation {=Discount.Discount}
Coupon code The coupon code. To refer to this field in your ad text, use the notation {=Discount.Coupon}
Modify Bid on Mobile? Select Yes to enable a mobile bid modifier, No to skip setting a bid.  
Mobile Bid Modifier Set to a value between -100 and +300. See the bid adjustments help page for more details.
Last updated Provides a timestamp that shows when this rule was last run. This field is readonly.  

Syncing the discount information

The script tries to retrieve an ad customizer source named Discounts in your account. If it is missing, the script tries to create one, with the following fields:

Field Name Field type Meaning
code text The product code.
coupon text The product coupon code.
discount number The discount percentage for this product.
name text A friendly name for the product.

The script then populates the ad customizer source with data from the Rules spreadsheet. It also retrieves the target ad group for each product and sets the targeting of individual ad customizer items to the corresponding ad group.

Running the rules

Depending on the Enable settings, the script then performs the requested operations.

Setting name Action
Enable Rule? If set to “Yes”, the script processes this rule. If set to “No”, this rule is skipped.
Enable Ads? The script tries to retrieve an ad group which has been labelled with a Google Ads label with text as the product code. The rule is skipped if a matching ad group cannot be found. If set to “Yes”, the script enables this ad group. If set to “No”, the ad group is paused instead.
Enable Discounts? The script tries to retrieve an ad group which has been labelled with a Google Ads label with text as the product code. The rule is skipped if a matching ad group cannot be found. The script tries to retrieve all ads within the product’s ad group, labelled “Discounts”. If no such ads are found, the script creates an ad in the account for discounts, and applies the label “Discounts” to that ad. If this setting is set to “Yes”, all the ads with label “Discounts” are enabled. If set to “No”, the ads are paused instead.
Modify Bid on Mobile? The script tries to retrieve an ad group which has been labelled with a Google Ads label with text as the product code. The rule is skipped if a matching ad group cannot be found. If this setting is set to “Yes”, the ad group’s bids are not modified. If set to “No”, the mobile bid modifier for the ad group is updated with the value provided in the “Mobile Bid Modifer” column.

Setting up the database

Follow the instructions on our JDBC guide to allow Google Ads scripts to make calls to your database.

Spreadsheet limits

Since this script depends on recalculating several spreadsheet formulas per product rule, try limiting the number of rules to a reasonable number (say, 10,000 rules) per spreadsheet. If you have a much larger number of products to manage, consider setting up multiple copies of your script, each managing its own set of products through a separate spreadsheet.

Maintaining the spreadsheet

There are a few points to keep in mind when maintaining the spreadsheet:

  1. The script doesn’t manage the list of product codes on the spreadsheet. It's your responsibility to manually manage the list of product codes in the spreadsheet if you have new products, or if you stop selling existing ones.
  2. The spreadsheet uses various named ranges to read data. If you add more rows to the spreadsheet, make sure the named ranges are also updated appropriately.
  3. Do not modify the structure of the spreadsheet; the code relies on its structure to read the data. If you plan on enhancing the script, see the section below.

Frequently asked questions

What if I want to update inventory details manually?

While we recommend connecting to the database to update your inventory details, it is possible that you don’t have access to the database, or you prefer updating the inventory details manually. If you want to update the inventory details manually, be sure to:

  • Comment out the call to syncSpreadsheetWithDatabase method on line 67 in the main() method as follows.

    // syncSpreadsheetWithDatabase(spreadsheet, productKeys);

  • Manually update the inventory information in the Stock column of the Stats spreadsheet.

How do I enhance the script?

There are a number of ways to enhance the script. Some of the most common scenarios are detailed below:

Including more stats on the Stats tab

  1. Modify the retrieveReportStatsForProducts method to retrieve new stats.
  2. Modify the saveStatsToSpreadsheet method to write the new stats to the spreadsheet.
  3. Update the named range Stats on the spreadsheet so that saveStatsToSpreadsheet can save the new columns to the spreadsheet.

Use a different stat to control a rule

Modify the formula of the column (or cell) corresponding to the rule to refer to the new stat column.

Add a new action

  1. Insert new columns on the Rules tab to correspond to the new rule.
  2. Modify the name range RulesData on the spreadsheet so that the new columns are covered.
  3. Modify the getRulesFromSpreadsheet method to read the additional columns.
  4. Modify the applyRules method to implement the logic for the new action.

Add new dynamic fields for the ad customizers

  1. Delete the ad customizer feed named Discounts manually from Google Ads UI.
  2. Update the createAdCustomizerSource method to insert new columns.
  3. Update the updateAdCustomizerItems method to insert new values for the updated columns.

Scheduling

Schedule the script to run hourly.

Setup

  • Set up a spreadsheet-based script with the source code below. Use this template spreadsheet.
  • Update the SPREADSHEET_URL variable in your code with the URL of the new spreadsheet.
  • Update the DATABASE_CREDENTIALS setting in your code so that the script can connect to your database.
  • [Optional] Update the CUSTOMIZER_SOURCE_NAME variable in your code to specify the ad customizer name for storing discount information.
  • [Optional] Update the DISCOUNT_LABEL_NAME variable to specify the label for identifying ads related to product discounts.

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 Inventory management script
 *
 * @overview The Inventory management script allows you to manage ads
 *     for your products based on rules specified in a spreadsheet, and
 *     inventory details from your database. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/inventory-management
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.1.1
 *
 * @changelog
 * - version 1.1.1
 *   - Added validation for external spreadsheet setup.
 *   - Updated to use report version v201609.
 * - version 1.1
 *   - Fixed validation and logging errors.
 *
 * - version 1.0
 *   - Released initial version.
 */

// The spreadsheet for loading rules. This should be a copy of
// https://goo.gl/1aMdnP.
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

// Credentials for connecting to the database.
var DATABASE_CREDENTIALS = {
  ip: 'INSERT_DATABASE_IP_ADDRESS_HERE',
  user: 'INSERT_DATABASE_USERNAME_HERE',
  password: 'INSERT_DATABASE_PASSWORD_HERE',
  database: 'INSERT_DATABASE_NAME_HERE'
};

// The ad customizer name for storing discount information.
var CUSTOMIZER_SOURCE_NAME = 'Discount';

// The label for identifying ads related to product discounts.
var DISCOUNT_LABEL_NAME = 'Discount';

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

function main() {
  ensureLabels([DISCOUNT_LABEL_NAME]);
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  var productCodes = loadProductCodes(spreadsheet);

  saveMetadata(spreadsheet);

  // To update inventory details manually, comment out the line below.
  syncSpreadsheetWithDatabase(spreadsheet, productCodes);

  addReportStatsToSpreadsheet(spreadsheet, productCodes);
  var rules = getRulesFromSpreadsheet(spreadsheet);
  syncDiscountFeed(rules);
  applyRules(rules, spreadsheet, productCodes);
}

/**
 * Checks that the account has all provided labels and creates any that are
 * missing. Since labels cannot be created in preview mode, throws an exception
 * if a label is missing.
 *
 * @param {Array.<string>} labelNames An array of label names.
 */
function ensureLabels(labelNames) {
  for (var i = 0; i < labelNames.length; i++) {
    var labelName = labelNames[i];
    var label = AdsApp.labels()
      .withCondition('Name = "' + labelName + '"')
      .get();

    if (!label.hasNext()) {
      if (!AdsApp.getExecutionInfo().isPreview()) {
        AdsApp.createLabel(labelName);
      } else {
        throw 'Label ' + labelName + ' is missing and cannot be created in ' +
            'preview mode. Please run the script or create the label manually.';
      }
    }
  }
}

/**
 * Save metadata information on the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 */
function saveMetadata(spreadsheet) {
  var customerId = spreadsheet.getRangeByName('CustomerId');
  customerId.setValue(AdsApp.currentAccount().getCustomerId());

  var lastUpdated = spreadsheet.getRangeByName('LastUpdated');
  lastUpdated.setValue(new Date());
}

/**
 * Sync inventory details from the database to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 * @param {Array.<string>} productCodes The list of all product codes.
 */
function syncSpreadsheetWithDatabase(spreadsheet, productCodes) {
  var items = fetchInventoryDetailsFromDatabase();
  saveInventoryDetailsToSpreadsheet(items, spreadsheet, productCodes);
}

/**
 * Fetches inventory details from the database to the spreadsheet.
 *
 * @return {Object.<string, number>} A map with key as product code and value
 *     as inventory stock details.
 */
function fetchInventoryDetailsFromDatabase() {
  Logger.log('Retrieving inventory stock details from database...');

  var retval = {};

  var dbUrl = 'jdbc:mysql://' + DATABASE_CREDENTIALS.ip + '/' +
      DATABASE_CREDENTIALS.database;
  var conn = Jdbc.getConnection(dbUrl, DATABASE_CREDENTIALS.user,
      DATABASE_CREDENTIALS.password);

  var stmt = conn.createStatement();
  var results = stmt.executeQuery('Select code, stock from items');

  while (results.next()) {
    var rowString = '';
    var code = results.getString('code');
    var stock = results.getString('stock');
    if (retval[code]) {
      throw 'The inventory database has multiple rows for product with ' +
          'code : ' + code + '. Please fix your database.';
    }
    retval[code] = stock;
  }

  results.close();
  stmt.close();

  Logger.log('Done');

  return retval;
}

/**
 * Saves inventory details from the database to the spreadsheet.
 *
 * @param {Object.<string, number>} items A map with key as the product code,
 *     and value as the inventory stock detail.
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 * @param {Array.<string>} productCodes The list of all product codes.
 */
function saveInventoryDetailsToSpreadsheet(items, spreadsheet, productCodes) {
  Logger.log('Saving inventory stock details to spreadsheet...');

  var range = spreadsheet.getRangeByName('DbData');
  var data = range.getValues();

  for (var i = 0; i < productCodes.length; i++) {
    var productCode = productCodes[i];
    data[i][0] = items[productCode];
  }
  range.setValues(data);
  Logger.log('Done');
}

/**
 * Adds report stats to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 * @param {Array.<string>} productCodes The list of all product codes.
 */
function addReportStatsToSpreadsheet(spreadsheet, productCodes) {
  var statsMap = retrieveReportStatsForProducts(productCodes);
  saveStatsToSpreadsheet(statsMap, spreadsheet, productCodes);
}

/**
 * Retrieve report stats for the products.
 *
 * @param {Array.<string>} productCodes The list of all product codes.
 *
 * @return {Object.<string, Object>} A map, with key as the product code, and
 *     value as a stats object for that product.
 */
function retrieveReportStatsForProducts(productCodes) {
  Logger.log('Retrieving report stats...');

  var adGroupMap = {};
  var statsMap = {};

  for (var i = 0; i < productCodes.length; i++) {
    var productCode = productCodes[i];
    var adGroup = getTargetAdGroupForProduct(productCode);
    if (adGroup) {
      adGroupMap[adGroup.getId()] = productCode;
    }
  }

  var report = AdsApp.report('Select AdGroupId, Clicks, Impressions, ' +
      'Cost, AverageCpc from ADGROUP_PERFORMANCE_REPORT during ' +
      'LAST_7_DAYS', REPORTING_OPTIONS).rows();

  while (report.hasNext()) {
    var reportRow = report.next();
    var adGroupId = reportRow['AdGroupId'];

    if (adGroupId in adGroupMap) {
      var code = adGroupMap[adGroupId];
      var stats = {
        clicks: reportRow['Clicks'],
        impressions: reportRow['Impressions'],
        cost: reportRow['Cost'],
        averageCpc: reportRow['AverageCpc'],
        adGroupId: adGroupId,
        code: code
      };

      statsMap[code] = stats;
    }
  }

  Logger.log('Done');

  return statsMap;
}

/**
 * Save report stats to the spreadsheet.
 *
 * @param {Object.<string, Object>} statsMap A map, with key as the product
 *    code, and value as a stats object for that product.
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 * @param {Array.<string>} productCodes The list of all product codes.
 */
function saveStatsToSpreadsheet(statsMap, spreadsheet, productCodes) {
  Logger.log('Saving report stats to spreadsheet...');

  range = spreadsheet.getRangeByName('Stats');
  data = range.getValues();

  for (var i = 0; i < productCodes.length; i++) {
    var code = productCodes[i];
    if (code in statsMap) {
      var stats = statsMap[code];
      data[i][0] = stats.clicks;
      data[i][1] = stats.impressions;
      data[i][2] = stats.cost;
      data[i][3] = stats.averageCpc;
    } else {
      data[i][0] = 0;
      data[i][1] = 0;
      data[i][2] = 0;
      data[i][3] = 0;
    }
  }
  range.setValues(data);

  Logger.log('Done');
}

/**
 * Retrieves the product rules from the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 *
 * @return {Array.<Object>} The list of rules.
 */
function getRulesFromSpreadsheet(spreadsheet) {
  Logger.log('Loading rules from database...');

  var retval = [];
  range = spreadsheet.getRangeByName('RulesData');
  data = range.getValues();

  // Ensure that there's exactly one rule row per product code.
  var codeMap = {};

  for (var i = 0; i < data.length; i++) {
    var code = data[i][0];

    // Skip blank code.
    if (!code) {
      continue;
    }

    if (codeMap[code]) {
      throw 'The rules spreadsheet has multiple rows for product with ' +
          'code : ' + code + '. Only one row per product code is allowed. ' +
          'Please fix your spreadsheet.';
    } else {
      codeMap[code] = true;
    }

    retval.push({
      'code': code,
      'name': data[i][1],
      'enable': data[i][2],
      'runAds': data[i][3],
      'runDiscount': data[i][4],
      'discount': data[i][5],
      'coupon': data[i][6],
      'setMobileBidModifier': data[i][7],
      'mobileBidModifier': data[i][8],
    });
  }

  Logger.log('Done');
  return retval;
}


/**
 * Syncs the discount feed with the spreadsheet.
 *
 * @param {Array.<Object>} rules The list of rules from the spreadsheet.
 */
function syncDiscountFeed(rules) {
  var source = createDiscountCustomizerSourceIfRequired();
  updateAdCustomizerItems(source, rules);
}

/**
 * Creates the ad customizer source for storing discount information if it
 * is missing in the account.
 *
 * @return {AdsApp.AdCustomizerSource} The ad customizer source.
 */
function createDiscountCustomizerSourceIfRequired() {
  Logger.log('Retrieving ad customizer source for discounts...');

  var source = getAdCustomizerSource();
  if (!source) {
    source = createAdCustomizerSource();
  }

  Logger.log('Done');

  return source;
}

/**
 * Retrieves an existing ad customizer source for storing discount details.
 *
 * @return {AdsApp.AdCustomizerSource?} The ad customizer source if one
 *     exists, null otherwise.
 */
function getAdCustomizerSource() {
  var sources = AdsApp.adCustomizerSources().get();
  while (sources.hasNext()) {
    var source = sources.next();
    if (source.getName() == CUSTOMIZER_SOURCE_NAME) {
      return source;
    }
  }
  return null;
}

/**
 * Creates an ad customizer source for storing discount details.
 *
 * @return {AdsApp.AdCustomizerSource?} The newly created ad customizer
 *     source.
 */
function createAdCustomizerSource() {
  Logger.log('Creating a new ad customizer source for discounts...');

  if (AdsApp.getExecutionInfo().isPreview()) {
    Logger.log('This step won\'t work with preview mode, and your script may ' +
        'fail at a later stage. Try running this script normally.');
  }

  var source = AdsApp.newAdCustomizerSourceBuilder()
      .withName(CUSTOMIZER_SOURCE_NAME)
      .addAttribute('code', 'text')
      .addAttribute('coupon', 'text')
      .addAttribute('discount', 'number')
      .addAttribute('name', 'text')
      .build().getResult();

  Logger.log('Done');

  return source;
}

/**
 * Updates the ad customizer items with the latest discount details from the
 *     spreadsheet.
 *
 * @param {AdsApp.AdCustomizerSource} source the ad customizer source that
 *     stores discount details.
 * @param {Array.<Object>} rules The list of rules from the spreadsheet.
 */
function updateAdCustomizerItems(source, rules) {
  Logger.log('Syncing the discount ad customizer source with the ' +
      'spreadsheet data...');

  var itemMap = {};

  // Store the existing ad customizer items into a
  // lookup map.
  var items = source.items().get();
  while (items.hasNext()) {
    var item = items.next();
    var productCode = item.getAttributeValue('code');
    itemMap[productCode] = item;
  }

  for (var i = 0; i < rules.length; i++) {
    // For each rule, see if there's an existing customizer
    // item by matching the item code.
    var rule = rules[i];
    var item = itemMap[rule.code];

    if (!item) {
      // This is a new item that was added to the spreadsheet.
      // Create a new customizer item for this code.
      item = source.adCustomizerItemBuilder()
      .withAttributeValue('code', rule.code)
      .withAttributeValue('discount', rule.discount)
      .withAttributeValue('coupon', rule.coupon)
      .withAttributeValue('name', rule.name)
      .build()
      .getResult();
    } else {
      // Update this customizer item with the latest details
      // from the spreadsheet.
      item.setAttributeValue('discount', rule.discount);
      item.setAttributeValue('coupon', rule.coupon);
      item.setAttributeValue('name', rule.name);
    }

    // Load the target ad group for this product.
    var adGroup = getTargetAdGroupForProduct(rule.code);

    if (adGroup) {
      // Target this ad customizer item to only the ad group for this product.
      item.setTargetAdGroup(adGroup.getCampaign().getName(), adGroup.getName());
    } else {
      // The target ad group for the product could not be found. So the
      // corresponding ad customizer item should also be deleted to prevent it
      // from ever serving.
      item.remove();
    }

    // Remove the processed product code from the lookup map.
    delete itemMap[rule.code];
  }

  // Whatever left over in itemMap should be deleted, since these products are
  // missing in the spreadsheet.
  for (productCode in itemMap) {
    Logger.log("Removing item that's not on spreadsheet: %s", productCode);
    itemMap[productCode].remove();
  }

  Logger.log('Done');
}

/**
 * Applies product rules to the account.
 *
 * @param {Array.<Object>} rules The list of rules to be applied.
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 * @param {Array.<string>} productCodes The list of all product codes.
 */
function applyRules(rules, spreadsheet, productCodes) {
  Logger.log('Applying rules...');

  var timestamps = loadTimestamps(spreadsheet, productCodes);

  for (var i = 0; i < rules.length; i++) {
    var rule = rules[i];
    try {
      if (rule.enable != 'Yes') {
        continue;
      }

      var adGroup = getTargetAdGroupForProduct(rule.code);

      if (adGroup) {
        var discountAds = getDiscountAds(adGroup);

        while (discountAds.hasNext()) {
          var discountAd = discountAds.next();
          // Run discounts or not?

          if (rule.runDiscount == 'Yes' && !discountAd.isEnabled()) {
            Logger.log('  - Enabling discount ads for ' + rule.code);
            discountAd.enable();
          } else if (rule.runDiscount == 'No' && !discountAd.isPaused()) {
            Logger.log('  - Disabling discount ads for ' + rule.code);
            discountAd.pause();
          }
        }

        // Run ads or not?
        if (rule.runAds == 'Yes' && !adGroup.isEnabled()) {
          Logger.log('  - Enabling ad group for ' + rule.code);
          adGroup.enable();
        } else if (rule.runAds == 'No' && adGroup.isEnabled()) {
          Logger.log('  - Disabling ad group for ' + rule.code);
          adGroup.pause();
        }

        // Set mobile bid modifier or not?
        if (rule.setMobileBidModifier == 'Yes' &&
            adGroup.getMobileBidModifier() != rule.mobileBidModifier) {
          Logger.log('  - Setting mobile bid modifier for ' + rule.code);
          adGroup.setMobileBidModifier(rule.mobileBidModifier);
        }

        timestamps[rule.code] = new Date();
      }
    } catch (err) {
      Logger.log('An error occurred when running rule for product ' +
          'code "%s". "%s"', rule.code, err);
    }
  }

  saveTimestampsToDatabase(timestamps, spreadsheet, productCodes);

  Logger.log('Done');
}

/**
 * Gets the ads for discounts from an ad group. A new discount ad is created if
 *     no discount ads are found.
 *
 * @param {AdsApp.AdGroup} adGroup The ad group from which ads are
 *    retrieved.
 *
 * @return {AdsApp.AdIterator} An iterator for discount ads.
 */
function getDiscountAds(adGroup) {
  var ads = adGroup.ads().withCondition('LabelNames CONTAINS_ANY ["' +
      DISCOUNT_LABEL_NAME + '"]').get();
  if (ads.hasNext()) {
    return ads;
  } else {
    // Create a new discount ad that dynamically update values based
    // on values available in the Discount ad customizer source.
    var ad = adGroup.newTextAdBuilder()
        .withHeadline('{=Discount.name} for sale')
        .withDescription1('Get {=Discount.discount}% off per dozen!')
        .withDescription2('Use promo code {=Discount.coupon}.')
        .withDisplayUrl('example.com/flowers')
        .withFinalUrl('http://example.com/flowers')
        .build().getResult();
    ad.applyLabel(DISCOUNT_LABEL_NAME);
  }
  return adGroup.ads().withCondition('LabelNames CONTAINS_ANY ["' +
      DISCOUNT_LABEL_NAME + '"]').get();
}

/**
 * Gets the target ad group for a product. The script determines target ad
 * group by finding the one ad group that has the product key as a label.
 *
 * @param {string} code The product code.
 *
 * @return {AdsApp.AdGroup} The ad group for this product or null if the
 *     ad group cannot be found.
 */
function getTargetAdGroupForProduct(code) {
  var labelIterator = AdsApp.labels().withCondition(
      'Name = "' + code + '"').get();
  if (!labelIterator.hasNext()) {
    Logger.log('Label not found for product code : %s. Make sure you ' +
        'create a label of name "%s" and apply the label to exactly one ' +
        'ad group that corresponds to this product.', code, code);
    return null;
  }
  var label = labelIterator.next();

  // A product may have a single adgroup, and that should be labelled
  // with the product code.

  var adGroups = label.adGroups().get();
  if (adGroups.totalNumEntities() == 1) {
    return adGroups.next();
  } else {
    Logger.log('Ad group not found for product code : %s. Make sure you ' +
        'create a label of name "%s" and apply the label to exactly one ' +
        'ad group that corresponds to this product', code, code);
      return null;
  }
}

/**
 * Loads product codes from the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 *
 * @return {Array.<string>} The list of product codes.
 */
function loadProductCodes(spreadsheet) {
  var retval = [];

  var range = spreadsheet.getRangeByName('ProductCodes');
  var data = range.getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][0].toString()) {
      retval.push(data[i][0]);
    }
  }
  return retval;
}

/**
 * Loads rule timestamps from the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 *
 * @param {Array.<string>} productCodes The list of all product codes.
 *
 * @return {Object<string, string>} A map with product code as key and last
 *     execution time of the corresponding rule as value.
 */
function loadTimestamps(spreadsheet, productCodes) {
  var retval = {};

  var range = spreadsheet.getRangeByName('LastUpdated');
  var data = range.getValues();

  for (var i = 0; i < productCodes.length; i++) {
    var productCode = productCodes[i];

    if (data.length > i) {
      retval[productCode] = data[i][0];
    } else {
      retval[productCode] = '';
    }
  }
  return retval;
}

/**
 * Saves rule timestamps to the spreadsheet.
 *
 * @param {Object.<string, Date>} items A dictionary, with key as the product
 *     code and value as the timestamp at which the rule for that product was
 *     executed.
 * @param {Spreadsheet} spreadsheet The spreadsheet with product information
 *     and rules.
 * @param {Array.<string>} productCodes The list of all product codes.
 */
function saveTimestampsToDatabase(items, spreadsheet, productCodes) {
  var range = spreadsheet.getRangeByName('LastUpdated');
  var data = range.getValues();
  for (var i = 0; i < productCodes.length; i++) {
    var productCode = productCodes[i];
    data[i][0] = items[productCode];
  }
  range.setValues(data);
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == '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.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}