批量购物广告组创建器

工具图标

批量购物广告组创建工具提供了一种在现有购物广告系列中批量创建广告组和产品组的方法。它会读取电子表格中的广告组设置(例如默认出价和状态)和产品组层次结构以及出价,然后在 Google Ads 中创建这些广告组。对于购物广告系列,必须以 Google Ads 编辑器格式指定产品组层次结构。

运作方式

脚本将访问输入电子表格,并逐行进行处理。电子表格中指定的广告系列名称必须已经存在于 Google Ads 中,否则系统将会报错。

该脚本遵循以下规则:

指定的广告组不存在
创建的广告组包含指定设置(默认出价、状态)以及新的产品组。
指定的广告组已存在,且不含产品组(不太可能)
广告组设置保持不变,创建新的产品组。
指定的广告组已存在,且包含产品组
广告组设置保持不变,其现有产品组保持不变。

脚本会将结果附加到所处理的行的末尾。

电子表格规范

该脚本要求电子表格中包含以下列标题:

  • 广告系列:广告系列名称
  • Ad Group(广告组):广告组名称
  • 最高每次点击费用:如果在广告组行中是默认广告组出价,在产品组所在的行中则为默认广告组出价
  • Product 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;
}