大量購物廣告群組建立者

工具圖示

「大量購物廣告群組建立者」功能可讓您在現有的購物廣告活動中,大量建立廣告群組和產品群組。這個表格會讀取廣告群組設定 (例如預設出價和狀態) 和產品群組階層,以及試算表中的出價,然後在 Google Ads 中建立這些層級。您必須使用 Google Ads 編輯器格式為購物廣告活動指定產品群組階層。

運作方式

這個指令碼會接收輸入試算表,並逐列處理。試算表中指定的廣告活動名稱必須已存在 Google Ads 中,否則系統會擲回錯誤。

指令碼必須遵循以下規則:

指定的廣告群組不存在
建立廣告群組時,會採用指定設定 (預設出價、狀態),以及新的產品群組。
指定的廣告群組已存在,且沒有產品群組 (不太可能)
新的廣告群組設定維持不變,系統還會建立新的產品群組。
指定的廣告群組已存在,且有產品群組
廣告群組設定維持不變,現有的產品群組則維持不變。

指令碼會將結果附加到處理中資料列的結尾。

試算表規格

指令碼需要試算表包含下列欄標題:

  • Campaign (廣告活動):廣告活動名稱
  • 廣告群組:廣告群組名稱
  • 最高單次點擊出價:預設廣告群組出價 (如果在「ad group」列中,假如產品群組出價包含產品群組,則產品群組出價)
  • 產品群組:Google Ads 編輯器格式的產品群組階層規格
  • 廣告群組狀態:廣告群組的狀態,可以是 enabledpaused

指令碼會忽略所有其他資料欄。也就是說,只有這些設定會複製到新建的廣告群組。

廣告群組設定列必須放在同一個廣告群組的任何產品群組列之前。

如果在試算表中明確指定 everything else 產品群組,該列必須接在相同廣告群組中所有相同層級的所有產品群組之後。如果沒有明確指定,系統就會使用廣告群組預設出價建立這些出價。

設定

  • 使用上述欄標題建立試算表,並填寫廣告群組和產品群組詳細資料。看起來應與這個範例類似。
    • 或者,您也可以透過 Google Ads 編輯器下載現有廣告活動做為範本,並匯出為 .csv 格式,然後修改 .csv,再上傳至 Google 試算表。如果是這樣,請確認試算表中的廣告群組是新的 (不存在)。
  • 檢查 Google Ads 中是否有目標廣告活動。
  • 使用下方的原始碼建立新的 Google Ads 指令碼。
  • 別忘了更新指令碼中的下列參數:
    • SPREADSHEET_URL:要處理的試算表網址
    • SHEET_NAME:含有待處理資料的工作表名稱

原始碼

/// 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 Bulk Shopping AdGroup Creator
 *
 * @overview The Bulk Shopping AdGroup Creator provides a way to bulk create ad
 *     groups and product groups in existing Shopping Campaigns. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/bulk-shopping-ad-group-creator
 *     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
 *   - Removed use of ProductAdBuilder.withPromotionLine as it is deprecated.
 * - version 1.0.1
 *   - Added validation for external spreadsheet setup.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * SPREADSHEET_URL: URL for spreadsheet to read
 * SHEET_NAME: Name of sheet in spreadsheet to read
 */
const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
const SHEET_NAME = 'YOUR_SHEET_NAME';

/**
 * Column header specification
 * These are the actual text the script looks for in the spreadsheet header.
 */
const CAMPAIGN_COLUMN = 'Campaign';
const AD_GROUP_COLUMN = 'Ad Group';
const MAX_CPC_COLUMN = 'Max CPC';

const PRODUCT_GROUP_COLUMN = 'Product Group';
const AD_GROUP_STATUS_COLUMN = 'AdGroup Status';

const REQUIRED_COLUMN_NAMES = {};
REQUIRED_COLUMN_NAMES[CAMPAIGN_COLUMN] = true;
REQUIRED_COLUMN_NAMES[AD_GROUP_COLUMN] = true;
REQUIRED_COLUMN_NAMES[MAX_CPC_COLUMN] = true;
REQUIRED_COLUMN_NAMES[PRODUCT_GROUP_COLUMN] = true;
REQUIRED_COLUMN_NAMES[AD_GROUP_STATUS_COLUMN] = true;
/** End of column header specification */

/**
 * Reads campaign and bid data from spreadsheet and writes it to Google Ads.
 */
function main() {
  console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
  const sheet = validateAndGetSpreadsheet(SPREADSHEET_URL, SHEET_NAME);
  parseSheetAndConstructProductGroups(sheet);

  console.log('Parsed spreadsheet and completed shopping campaign ' +
      'construction.');
}

//Stores results of each row, along with formatting details
let resultsByRow = ['Result', 'Notes'];
let resultColors = ['Black', 'Black'];

/**
 * Validates header of sheet to make sure that header matches expected format.
 * Throws exception if problems are found. Saves the column number of each
 * expected column to global variable.
 *
 * @param {!Array.<string>} headerRow A list of column header names.
 * @return {!Object.<number>} A mapping from column name to column index.
 * @throws If required column is missing.
 */
function validateHeader(headerRow) {
  const result = {};

  const missingColumns = Object.keys(REQUIRED_COLUMN_NAMES);

  // Grab the column # for each expected input column.
  for (let columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
    const columnName = headerRow[columnIndex];
    if (columnName in REQUIRED_COLUMN_NAMES) {
      result[columnName] = columnIndex;
      const index = missingColumns.indexOf(columnName);
      if (index >= 0) {
        missingColumns.splice(index, 1);
      }
    }
  }

  if (missingColumns.length > 0) {
    throw `Bid sheet data format doesn't match expected format. ` +
        `Missing columns: ${missingColumns.join()}`;
  }
  return result;
}

/**
 * Converts a spreadsheet row into map representation.
 *
 * @param {!Array.<!Object>} row The spreadsheet row.
 * @param {!Object.<number>} headers Mapping from column name to column index.
 * @return {?Object} The row in object form, or null for a parsing error.
 */
function parseRow(row, headers) {
  const parsedRow = {};

  for (const header in headers) {
    const colNum = headers[header];
    let val = row[colNum].toString().trim();
    if (!val) {
      continue;
    }
    // Google Ads Editor will add double quotes (") around string if it contains
    // commas or double quotes, so we need to strip those out.
    if (val.charAt(0) === '"' && val.charAt(val.length - 1) === '"') {
      val = val.substring(1, val.length - 1);
    }
    // Google Ads Editor escapes double quotes (") with another double quote ("").
    val = val.replace(/""/g, '"');
    if (header === PRODUCT_GROUP_COLUMN) {
      const productGroups = [];
      const parsedProductGroups =
          parseEditorFormat(val);
      if (parsedProductGroups.error) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] = parsedProductGroups.error;
        resultColors[0] = 'Red';
        return null;
      }

      for (let x = 0; x < parsedProductGroups.length; x++) {
        // Product group type and value indices are level-1
        // (e.g. for L1, x=0).
        productGroups[x] = {
          type: parsedProductGroups[x][0],
          value: parsedProductGroups[x][1]
        };
      }
      parsedRow[header] = productGroups;
    } else {
      parsedRow[header] = val;
    }
  }

  // Ignore rows that don't have any useful information.
  const testRow = [];
  for (const k in parsedRow) {
    // Remove campaign, ad group columns and test if the rest is empty.
    if (k === CAMPAIGN_COLUMN || k === AD_GROUP_COLUMN) {
      continue;
    }
    testRow.push(parsedRow[k]);
  }
  if (testRow.toString().replace(/[,]+/g, '') === '') {
    resultsByRow[0] = 'SKIPPED';
    resultsByRow[1] = 'Superfluous row';
    return null;
  }
  return parsedRow;
}


/**
 * Parses spreadsheet and constructs ad groups and product groups in Google Ads.
 *
 * @param {!Sheet} sheet The sheet to parse.
 */
function parseSheetAndConstructProductGroups(sheet) {
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
      .getValues()[0];

  const headers = validateHeader(headerRow);

  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1,
      sheet.getLastColumn()).getValues();

  const campaigns = {};

  const outputColumn = sheet.getLastColumn() + 1;
  const resultHeaderRange = sheet.getRange(1, outputColumn, 1, 2);
  resultHeaderRange.setValues([resultsByRow]);
  resultHeaderRange.setFontColors([resultColors]);
  resultHeaderRange.setFontWeights([['Bold', 'Bold']]);

  // Iterate through rows.
  for (let r = 0; r < values.length; r++) {
    resultsByRow = ['', ''];
    resultColors = ['Black', 'Black'];
    const row = values[r];

    const parsedRow = parseRow(row, headers);

    if (parsedRow) {
      let bid = parsedRow[MAX_CPC_COLUMN];
      if (bid) {
        // For European locale, decimal points are commas.
        bid = bid.toString().toLowerCase().replace(/,/g, '.');
        if (bid != 'excluded' && (isNaN(Number(bid)) || !isFinite(bid))) {
          resultsByRow[0] = 'ERROR';
          resultsByRow[1] = 'Invalid bid';
        }
      }
      const campaignName = parsedRow[CAMPAIGN_COLUMN];
      campaigns[campaignName] =
          fetchCampaignIfNecessary(campaigns, campaignName);
      if (campaigns[campaignName]) {
        const adGroupName = parsedRow[AD_GROUP_COLUMN];
        campaigns[campaignName].createdAdGroups[adGroupName] =
            buildAdGroupIfNecessary(parsedRow, campaigns[campaignName],
                adGroupName, bid);
        if (campaigns[campaignName].createdAdGroups[adGroupName]) {
          if (campaigns[campaignName].createdAdGroups[adGroupName].skip) {
            buildProductGroups(parsedRow,
                campaigns[campaignName].createdAdGroups[adGroupName], bid);
          } else {
            resultsByRow[0] = 'SKIPPED';
            resultsByRow[1] =
                'Ad group already exists with product groups';
          }
        }
      }

      if (!resultsByRow[0]) {
        resultsByRow[0] = 'SUCCESS';
      }
    }
    switch (resultsByRow[0]) {
      case 'ERROR':
        resultColors[0] = 'Red';
        break;
      case 'SUCCESS':
        resultColors[0] = 'Green';
        break;
      case 'WARNING':
        resultColors[0] = 'Yellow';
    }
    const resultRange = sheet.getRange(r + 2, outputColumn, 1, 2);
    resultRange.setValues([resultsByRow]);
    resultRange.setFontColors([resultColors]);
    resultRange.setFontWeights([['Bold', 'Normal']]);
  }
}


/**
 * Fetches campaign from Google Ads if it hasn't already been done.
 *
 * @param {!Object.<ShoppingCampaign>} campaigns A cache of campaigns.
 * @param {string} campaignName The name of the campaign to fetch.
 * @return {?ShoppingCampaign} The campaign, or null if not found.
 */
function fetchCampaignIfNecessary(campaigns, campaignName) {
  //Find the campaign
  if (!campaignName) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Missing campaign name';
    return null;
  }
  let campaign = campaigns[campaignName];
  if (!campaign) {
    campaign = findCampaignByName(campaignName);
    campaign.createdAdGroups = {};
    if (!campaign) {
      resultsByRow[0] = 'ERROR';
      resultsByRow[1] = 'Could not find campaign';
    }
  }
  return campaign;
}

/**
 * Builds ad group if necessary, otherwise returns existing ad group.
 *
 * @param {!Array.<Object>} row A spreadsheet row.
 * @param {!ShoppingCampaign} campaign
 * @param {string} adGroupName The ad group to build or fetch.
 * @param {number} bid
 * @return {?ShoppingAdGroup} The ad group of null if there is an error.
 */
function buildAdGroupIfNecessary(row, campaign, adGroupName, bid) {
  if (!adGroupName) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Missing ad group name';
    return null;
  }
  // See if we already fetched/created the ad group.
  let adGroup = campaign.createdAdGroups[adGroupName];
  if (!adGroup) {
    // Only use the bid on this line for the ad group default bid if there are
    // no product groups specified for it. Ad group default bid must be
    // specified.
    if (row[PRODUCT_GROUP_COLUMN]) {
      resultsByRow[0] = 'ERROR';
      resultsByRow[1] = 'Ad Group is missing a default bid.';
      return null;
    }
    // Set default status to enabled.
    let status = 'ENABLED';
    // If ad group status is specified, make sure it's "active", "enabled", or
    // "paused", and set status. Ad group status must be set on the first row
    // that the ad group appears in.
    if (row[AD_GROUP_STATUS_COLUMN]) {
      status = row[AD_GROUP_STATUS_COLUMN].toUpperCase();
      if (status === 'ACTIVE') {
        status = 'ENABLED';
      }
    }
    adGroup = createAdGroup(adGroupName, status, bid, campaign);
    if (adGroup) {
      adGroup.rootProductGroup = adGroup.rootProductGroup();
      adGroup.rootProductGroup.childMap = {};
    }
  }
  return adGroup;
}

/**
 * Builds product groups from row.
 *
 * @param {!Array.<Object>} row A spreadsheet row.
 * @param {!ShoppingAdGroup} adGroup The ad group to operate on.
 * @param {number} bid The product group bid.
 */
function buildProductGroups(row, adGroup, bid) {
  if (!row[PRODUCT_GROUP_COLUMN]) {
    return;
  }
  // Iterate through product groups in row.
  let maxLevel = -1;
  let productGroupsToAdd = row[PRODUCT_GROUP_COLUMN];
  let productGroup = adGroup.rootProductGroup;
  for (let i = 0; i < productGroupsToAdd.length; i++) {
    const type =
        productGroupsToAdd[i].type.toString().toLowerCase()
        .replace(/[ ]+/g, '');
    let val = productGroupsToAdd[i].value.toString().trim();
    if (type) {
      //For each Group level n, row must contain values for 1...n-1
      if (i - maxLevel > 1) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] = 'Every level of the product ' +
            'group type must have all higher ' +
            'level values. L' + i + ' is filled but missing L' +
            (maxLevel + 1);
        return;
      }
      maxLevel = i;

      // Each row must have matching # of bidding attribute type and value.
      if (!val) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] =
            'Every product group type must have an associated value. L' +
            i + ' has a type but no value';
        return;
      }

      // Build product groups.
      if (!productGroup.childMap[val.toLowerCase()]) {
        if (val === '*') {
          if (Object.keys(productGroup.childMap).length === 0) {
            resultsByRow[0] = 'ERROR';
            resultsByRow[1] =
                '"Everything else" product group must come after all others';
            return;
          } else {
            let child = productGroup.childMap[val];
            if (!child) {
              const children = productGroup.children().get();
              for (const element of children) {
                child = element;
                if (child.isOtherCase()) {
                  break;
                }
              }
              child.childMap = {};
            }
            productGroup.childMap[val] = child;
            productGroup = child;
            productGroup.setMaxCpc(Number(adGroup.bidding().getCpc()));
            //Only assign the bid to the lowest level product group
            //on that row
            if (i + 1 === productGroupsToAdd.length) {
              if (bid != 'excluded') {
                productGroup.setMaxCpc(Number(bid));
              } else {
                productGroup.exclude();
              }
            }
          }
        } else {
          let productGroupBuilder = productGroup.newChild();
          // Verify that bidding attribute type is valid, construct
          // productGroupBuilder.
          switch (type) {
            case 'producttype':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.productTypeBuilder()
                                                       .withValue(val);
              break;
            case 'brand':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.brandBuilder()
                                                       .withName(val);
              break;
            case 'category':
              productGroupBuilder = productGroupBuilder.categoryBuilder()
                                                       .withName(val);
              break;
            case 'condition':
              val = val.toUpperCase();
              productGroupBuilder = productGroupBuilder.conditionBuilder()
                                                       .withCondition(val);
              break;
            case 'itemid':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.itemIdBuilder()
                                                       .withValue(val);
              break;
            default:
              if (type.match(/^custom((\\s)?(label|attribute))?/)) {
                val = val.toLowerCase();
                //make sure there's a number at the end that's between 0-4
                if (type.match(/[0-4]$/)) {
                  productGroupBuilder =
                      productGroupBuilder.customLabelBuilder()
                                         .withType('INDEX' +
                                            type.substring(type.length - 1))
                                         .withValue(val);
                } else {
                  resultsByRow[0] = 'ERROR';
                  resultsByRow[1] =
                      'Invalid custom attribute type: ' +
                      productGroupsToAdd[i].type;
                  return;
                }
              } else {
                resultsByRow[0] = 'ERROR';
                resultsByRow[1] =
                    'Invalid bidding attribute type: ' +
                    productGroupsToAdd[i].type;
                return;
              }
          }

          const productGroupOp = productGroupBuilder.build();

          if (!productGroupOp.isSuccessful()) {
            resultsByRow[0] = 'ERROR';
            resultsByRow[1] = 'Error creating product group ' +
                'level ' + (i + 1) + ': ' + productGroupOp.getErrors();
            return;
          }

          const result = productGroupOp.getResult();
          // Only assign the bid to the lowest level product group on that row.
          if (i + 1 === productGroupsToAdd.length) {
            if (bid === 'excluded') {
              result.exclude();
            } else if (bid) {
              result.setMaxCpc(Number(bid));
            }
          }

          result.childMap = {};
          productGroup.childMap[val.toLowerCase()] = result;

          // Set current product group to the newly created product group.
          productGroup = result;
        }
      } else {
        // Set current product group to the last read product group.
        productGroup = productGroup.childMap[val.toLowerCase()];
      }
    }
  }
}

/**
 * Parses Google Ads Editor product group format
 * (e.g. * / Condition='New' / Custom label 2='furniture' /
 *   Product type='bar carts').
 *
 * @param {string} productGroupPath The product group path.
 * @return {!Array.<!Array.<string>>} A list of product group component name/
 *     value pairs.
 */
function parseEditorFormat(productGroupPath) {
  // Ignore * / case which is the root product group.
  if (productGroupPath === '* /' || !productGroupPath) {
    return [];
  }

  const regexVals = productGroupPath.match(new RegExp(/'(.*?)'/g));

  if (regexVals) {
    for (let i = 0; i < regexVals.length; i++) {
      productGroupPath = productGroupPath.replace(regexVals[i], '$' + i);
    }
  }

  const result = [];
  const productGroup = productGroupPath.split('/');

  // Google Ads Editor format starts with '* /' so we ignore first one.
  for (let x = 1; x < productGroup.length; x++) {
    if (!productGroup[x]) {
      continue;
    }
    // Google Ads Editor format looks like: Brand='nike'.
    const pair = productGroup[x].trim().split('=');
    if (pair.length != 2) {
      return {error: 'Product group string malformed. Should have 1 "=", ' +
            'but has ' + (pair.length - 1)};
    }
    let val = pair[1];
    if (val.charAt(0) != '$' && val.charAt(0) != '*') {
      return {error: 'Product group string malformed. Please ensure you are ' +
            'using Google Ads Editor format'};
    }
    // '*' value doesn't have single quotes around it.
    if (val != '*') {
      const values = pair[1].split('$');
      // Skip 0 because it's always blank. String always starts with $.
      for (let i = 1; i < values.length; i++) {
        val = val.replace('$' + values[i], regexVals[values[i]]);
      }
      val = val.substring(1, val.length - 1).replace(/''/g, '\'');
    }

    result.push([pair[0], val]);
  }
  return result;
}

/**
 * Fetches campaign from Google Ads by name.
 *
 * @param {string} name The campaign name.
 * @return {?ShoppingCampaign} The found campaign, or null if not found.
 */
function findCampaignByName(name) {
  const campaignName = name.replace(/'/g, '\\\'');
  const shoppingCampaignSelector = AdsApp
        .shoppingCampaigns()
        .withCondition('Name = \'' + campaignName + '\'');

  let campaign = null;

  const shoppingCampaignIterator = shoppingCampaignSelector.get();
  if (shoppingCampaignIterator.hasNext()) {
    campaign = shoppingCampaignIterator.next();
  }

  return campaign;
}

/**
 * Fetches ad group from Google Ads given ad group name and campaign.
 *
 * @param {string} agName The name of the ad group.
 * @param {!ShoppingCampaign} campaign The campaign within which to search.
 * @return {?ShoppingAdGroup} The ad group or null if not found.
 */
function findAdGroupByName(agName, campaign) {
  const adGroupName = agName.replace(/'/g, '\\\'');
  const adGroupSelector = campaign
        .adGroups()
        .withCondition('Name = \'' + adGroupName + '\'');

  let adGroup = null;

  const adGroupIterator = adGroupSelector.get();
  if (adGroupIterator.hasNext()) {
    adGroup = adGroupIterator.next();
  }

  return adGroup;
}

/**
 * Creates ad group in Google Ads if it doesn't already exist, along with ad
 * group ad.
 *
 * @param {string} name The name of the ad group.
 * @param {string} status The desired status of the ad group.
 * @param {number} defaultBid The max CPC for the ad group.
 * @param {!ShoppingCampaign} campaign The campaign to create the ad group for.
 * @return {?ShoppingAdGroup} The created ad group or null if there is an error.
 */
function createAdGroup(name, status, defaultBid, campaign) {
  // See if ad group exists. If so, fetch it.
  const adGroup = findAdGroupByName(name, campaign);
  if (adGroup != null) {
    if (adGroup.rootProductGroup()) {
      // If root product group exists and not delete, then skip ad group.
      adGroup.skip = true;
    } else {
      adGroup.createRootProductGroup();
    }
    return adGroup;
  }

  // Build ad group.
  const adGroupOp = campaign.newAdGroupBuilder()
        .withName(name)
        .withStatus(status)
        .withMaxCpc(defaultBid)
        .build();

  // Check for errors.
  if (!adGroupOp.isSuccessful()) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Error creating ad group: ' +
        adGroupOp.getErrors();
    return null;
  }

  const adGroupResult = adGroupOp.getResult();

  adGroupResult.createRootProductGroup();

  console.log(`Successfully created ad group [${adGroupResult.getName()}]`);

  // Build ad group ad.
  const adGroupAdOp = adGroupResult.newAdBuilder().build();

  // Check for errors.
  if (!adGroupAdOp.isSuccessful()) {
    resultsByRow[0] = 'WARNING';
    resultsByRow[1] = 'Error creating ad group ad: ' +
        adGroupAdOp.getErrors();
  }

  return adGroupResult;
}

/**
 * DO NOT EDIT ANYTHING BELOW THIS LINE.
 * Please modify your spreadsheet URL and email addresses at the top of the file
 * only.
 */

/**
 * Validates the provided spreadsheet URL and email address
 * to make sure that they're set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @param {string} sheetname The name of the sheet within the spreadsheet that
 *     should be fetched.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL or email hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl, sheetname) {
  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.');
  }
  if (sheetname === 'YOUR_SHEET_NAME') {
    throw new Error('Please specify the name of the sheet you want to use on' +
        ' your spreadsheet.');
  }
  const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  const sheet = spreadsheet.getSheetByName(sheetname);
  return sheet;
}